Trucos y tretas en Excel VBA para programadores - Dando la vuelta a los fastidiosos bugs de Microsoft

Este contenido tiene casi 6 años

Ya Microsoft nos invita a comprar su nuevo Office 2016, pero su Office 2013 ya está bien cargado de bugs.  Y como en internet aplica el dicho "nunca actualices algo que funciona", lo esperable es que al actualizar tengas sorpresas adicionales.  ¿Qué significa eso de que VBA tiene bugs?  Veamos bugs horripilantes en VBA para Office 2013.

Issues o bugs

Una cosa es tener "issues" (asuntos) y otra es tener "bugs" (errores, defectos).  Los issues son cosas solucionables, o donde no se ha demostrado la existencia de un bug.  Desafortunadamente Excel VBA no está lleno de issues, sino bugs.  Lo digo con cierto dolor, porque era algo que tenía el potencial de funcionar.

Los programadores "hardcore" que programan en C++ y plataformas basadas en Unix, miran a Microsoft de reojo por encima del hombro.  Miran a Visual Basic con desprecio.  Y desafortunadamente los bugs de Excel VBA (que usa algunos comandos de Visual Basic) no ayudan mucho a desmentir, sino más bien a fortalecer la idea de los programadores acerca del desprecio que podrían sentir hacia el software de Microsoft.  Para estos programadores, dignidad es no programar para Windows o al menos no en Visual Basic, porque consideran que es un producto casero.  Yo me negaba a pensar así, hasta que me entré en colisión con los bugs de Microsoft.

Como herramienta casera funciona, como por ejemplo para programar los registros de notas de un maestro de escuela.  Para uso casero es una muy buena herramienta.

Tablas pivote

Si viste mi post anterior Trucos y tretas en Excel VBA para programadores (Tablas pivote) verás que el código muchas veces se cae en la parte de crear tablas pivote.  Como usuario es facilísimo crear tablas pivote, pero en código te hace la vida miserable. 

Si grabas la creación de una tabla pivote tendrás código que parece muy claro, y si tratas de parametrizar para adaptar ese código literal a tus necesidades, y tratas de reutilizarlo vas a tener errores debido a bugs de Microsoft.

Cuando tenías Sheet1 creas una hoja, la siguiente será Sheet2 y luego Sheet3 y luego Sheet4. Si habías creado Sheet4 pero borraste todas las hojas excepto Sheet1, supondrías que al crear una hoja obtendrías Sheet2, pero en su lugar obtienes Sheet5 que es la que sigue de Sheet4 que es la última que fue creada.  Esto no debería ser un problema, excepto por el hecho de que si tratas de usar un nombre que no sea el del siguiente Sheet al crear un pivot table, vas a tener un error.  Es una conducta absurda.

Lo mismo sucede para las tablas pivote.  Y si no escoges el nombre de la siguiente tabla pivote en la secuencia, tendrás un error.  Entonces creas el código para detectar nombres de tabla pivote y encontrar el siguiente nombre entre las pivotes existentes en el libro de Excel.  Pero si tomas el código para crear la tabla pivote y reemplazas el valor literal (valor de texto entre comillas) y lo reemplazas por una variable tipo String, aunque tengas el nombre correcto de tabla pivote, vas a obtener un error.

De esta manera tus sueños de usar código genérico para crear tablas pivote se va por el drenaje.  Este código no va a funcionar porque al cambiar el valor literal de la tabla pivote por una variable String llamada PivotName, no sirve. Este código no sirve.


Sub CreatePivot2013()
    Dim SourceSheet As String
    Dim DestinationSheet As String
    Dim PivotName As String
    
    SourceSheet = ActiveSheet.Name
    Sheets.Add
    DestinationSheet = ActiveSheet.Name
    PivotName = NextPivotTableName
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        SourceSheet & "!R1C1:R1048576C4", Version:=xlPivotTableVersion15).CreatePivotTable _
        TableDestination:=DestinationSheet & "!R3C1", TableName:=PivotName, DefaultVersion _
        :=xlPivotTableVersion15
End Sub

Eso significa que la creación de tablas pivote está "hardcoded" y no puede realizarse por medio de un programa.  El código que grabaste sirve sólo una vez, para la ocasión en que grabaste el código de macro, y luego nunca más.

Variables tipo Byte

Con las variables byte deberias tener 8 bits, que te alcanza para valores de 0 a 255.  Si pensaste que ibas a ahorrar memoria porque los valores que ibas a usar eran enteros que estaban en rango de 0 a 255, te encontrarás una sorpresa.  vas a tener errores con valores entre 0 y 255.

De esta manera el código que habrías usado para encontrar el siguiente valor de tabla pivote, usando variables tipo byte, va a verse con errores debido a bugs de Microsoft.  Vas a ver error de overflow con un valor de 255 en la variable i.  Este código no sirve por eso


Private Function NextPivotTableName() As String
    Dim i As Byte
    Dim PrevExistingPivotName As Byte
    For i = 1 To 255
        If PivotNameExists("PivotTable" & CStr(i)) Then
            PrevExistingPivotName = i
        Else
            If i = PrevExistingPivotName + 1 Then
                NextPivotTableName = "PivotTable" & CStr(i)
            End If
        End If
    Next i
End Function

De esta manera te despides del sueño de ahorrar memoria, y te despides del sueño de crear tablas pivote, porque VBA está lleno de bugs. 

Otros bugs

Sharepoint queries

Cuando creas queries en Sharepoint (archivos con extensión .iqy), y grabas la operación de abrir el query en Excel, el código que obtienes es "hardcoded".  Si quieres convertir ese código en código genérico donde puedes parametrizar la operación, va a fracasar miserablemente.  De este modo cada vez que tengas un sharepoint dado deberás transcribir el código literal para ese sharepoint especifico.  Al menos el código literal funciona, pero no así con tablas pivote.

Esconder blancos en tablas pivote

Si tienes (blank) en las filas de una tabla pivote, y grabas macro para su eliminación, Excel grabará código.  Pero si tienes (blank) en las filas y columnas de la tabla pivote, al eliminar este item no grabará nada.  Si tratas de usar el código que grabaste cuando sólo había un (blank) en las filas, obtendrás un error.

Como puedes ver, si querías hacer algo profesional en Excel VBA y creiste que las cosas eran fáciles, y habías prometido a tu jefe que ibas a terminar tu proyecto, vas a llevarte sorpresas desagradables.

Dando la vuelta al problema

Yo sé que esperabas una solución, un truco para saltarse el bug. 

Bug de tipo Byte

Para el bug del tipo Byte lo mejor es que uses variables tipo Long. 

Bug de blancos en tablas pivote

Convierte el rango en una tabla.

Bug al crear tablas pivote

Para la creación de tablas pivote convierte el rango a formato de tabla, y borra todas las tablas pivote del Workbook antes de crear las tablas. 

La solución a los bugs

Al final tu programa principal dirá esto (UltimaFila y UltimaColumna son variables que debes calcular):

    ConvertRangeToTable 1, 1, UltimaFila, UltimaColumna, "Table1"
    DeleteAllPivotsInWorkbook
    CreatePivot2013 "Table1"

Y tendrás que agregar este código para usar tablas pivote.

Sub DeleteAllPivotsInWorkbook()
    Dim wks As Worksheet
    Dim pt As PivotTable
    
    For Each wks In Worksheets
        For Each pt In wks.PivotTables
            wks.Range(pt.TableRange2.Address).Delete Shift:=xlUp
        Next pt
    Next wks
    
    Set pt = Nothing
    Set wks = Nothing
End Sub

Sub CreatePivot2013(TableName As String) 'Use DeleteAllPivotsInWorkbook before using this sub for the fort time
    Dim SourceSheet As String
    Dim DestinationSheet As String
    Dim MyPivotName As String
    
    SourceSheet = ActiveSheet.Name
    GotoSheet SourceSheet
    Sheets.Add
    DestinationSheet = ActiveSheet.Name
    MyPivotName = NextPivotTableName
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        TableName, Version:=xlPivotTableVersion15).CreatePivotTable TableDestination _
        :=DestinationSheet & "!R3C1", TableName:=NextPivotTableName, DefaultVersion:= _
        xlPivotTableVersion15
End Sub

Private Function NextPivotTableName() As String
    Dim i As Long
    Dim PrevExistingPivotName As Long
    For i = 1 To 255
        If PivotNameExists("PivotTable" & CStr(i)) Then
            PrevExistingPivotName = i
        Else
            If i = PrevExistingPivotName + 1 Then
                NextPivotTableName = "PivotTable" & CStr(i)
            End If
        End If
    Next i
End Function

Sub ConvertRangeToTable(Row1 As Long, Column1 As Long, Row2 As Long, Column2 As Long, TableName As String)
    Dim tbl As ListObject
    Dim rng As Range

    If Row1 > 0 And Column1 > 0 And Row2 > 0 And Column2 > 0 Then
        Set rng = Range(Cells(Row1, Column1), Cells(Row2, Column2))
        If TableExistsInSheet(TableName) Then
            'Update
            ActiveSheet.ListObjects(TableName).Resize rng
        Else
            'Create
            Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, rng, , xlYes)
            tbl.TableStyle = "TableStyleMedium15"
            tbl.Name = TableName
        End If
    End If
End Sub

Como que la solución era intuitivamente obvia.  ¿O no?  Pues no.  Es bastante desagradable gastar mucho tiempo lidiando con bugs.  Realmente desagradable.  Es improductivo.  Pero he aquí la solución para que no sufras tú.

Reflexiones finales

Imagno que los bugs vienen de programadores baratos que hacen todo "harcoded", lo que hace que tratar de hacer cambios empuje el suirgimiento de bugs donde no había problemas.  Y encima no documentan bien, no simplifican el código mediante encapsulamiento, ni usan procedimientos genéricos reutilizables.

El problema es que en la idiosincrasia de estas gentes, la consistencia no es uno de sus atributos culturales.  Y el resultado natural es esto que vemos.

VBA quizás no sea tan profesional como C++, pero si no tuviera bugs, sería herramienta de elección para muchos propósitos.  Pero ya hay proyectos en grandes empresas que se están migrando a otros lenguajes debido a este problema.  El mayor problema viene de que actualmente los bancos basan una buena parte de su eficiencia en el uso de macros en VBA, y con los bugs sorpresivos que vemos, los bancos podrían ver una fuente de riesgo en el uso de VBA.

Ya con Internet Explorer (IE) hay fuga de usuarios, grandes compañías que están prefiriendo usar Chrome de Google, que ofrece más velocidad, y una buena seguridad.  Es qie IE empezó como una pila de ladrillos, y cada versión era el resultado de amontonar pilas de ladrillos sobre pilas de ladrillos para terminar creando un mamarracho ineficiente sin arquitectura.  No es de extrañar que Google haya tomado su lugar.

Que no puedas crear tablas pivote con código es una monstruosidad.  Que no puedas usar variables tipo Byte como se ha usado desde eras inmemoriales, es el fin del mundo.  Te deja atónito y estupefacto.

¿Acaso esto presenta una oportunidad para otras empresas o entidades que hagan bien las cosas desplazar las macros de Microsoft Office? ¿Acaso será buena idea comprar Microsoft Office 2016 si te gusta tener programas cosas que funcionan o es mejor buscar otras opciones?  Bill Gates dejó un monopolio imbatible.  Hoy no estoy seguro de saber lo que estoy mirando...

 

Accede a Rankia
¡Sé el primero en comentar!