VBA – Tablas y ListObjects

Este tutorial demostrará cómo trabajar con Tablas y ListObjects en VBA.

Tablas y ListObjects en VBA

Las tablas son una de las características más útiles y poderosas de Excel, en este tutorial, repasaremos cómo utilizar VBA para crear una tabla, añadir una ordenación simple a una tabla, filtrar una tabla y realizar otras tareas relacionadas con las tablas.

Crear una tabla con VBA

El método ListObjects.Add puede añadir una tabla a una hoja de cálculo, basándose en un rango de dicha hoja. Tenemos el rango mostrado en ($A$1:$B$8) en una hoja de trabajo llamada Hoja1.

El siguiente código añadirá una tabla, llamada Tabla1 a la hoja de trabajo, basada en el rango ($A$1:$B$8) utilizando el estilo de tabla por defecto:

Sub Crear_Tabla_En_Excel()

    ActiveWorkbook.Sheets("Hoja1").ListObjects.Add(xlSrcRange, Range("$A$1:$B$8"), , xlYes).name = "Tabla1"

End Sub

El resultado es:

 

Insertar una columna al final de la tabla con VBA

Puedes utilizar el método ListColumns.Add para añadir una columna al final de tu tabla. Tenemos nuestra tabla llamada Tabla1 que se muestra a continuación.

Puede añadir una columna a su tabla utilizando el siguiente código, que siempre añadirá una columna al final de la tabla:

Sub Añadir_columna_al_final_de_tabla()

    ActiveWorkbook.Sheets("Hoja1").ListObjects("Tabla1").ListColumns.Add

End Sub

El resultado es:

Inserción de una fila en la parte inferior de la tabla con VBA

Puedes utilizar el método ListRows.Add para añadir una fila en la parte inferior de tu tabla. Tenemos nuestra tabla llamada Tabla1 que se muestra a continuación.

El siguiente código siempre agregará una fila al fondo de su tabla.

Sub Añadir_fila_al_final_de_tabla()

    ActiveSheet.ListObjects("Tabla1").ListRows.Add

End Sub

El resultado es:

Añadir una ordenación simple con VBA

Puede ordenar una tabla con VBA. Tenemos nuestra tabla llamada Tabla1 que se muestra a continuación y podemos usar VBA para ordenar la columna de ventas de menor a mayor.

El siguiente código ordenará la columna Ventas en orden ascendente.

Sub Ordenar_tabla()
    Range("Tabla1[[#Headers],[Ventas]]").Select
    ActiveWorkbook.Worksheets("Hoja1").ListObjects("Tabla1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Hoja1").ListObjects("Tabla1").Sort.SortFields.Add _
    Key:=Range("Tabla1[[#All],[Ventas]]"), SortOn:=xlSortOnValues, Order:= _
    xlAscending, DataOption:=xlSortNormal
     
    With ActiveWorkbook.Worksheets("Hoja1").ListObjects("Tabla1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

El resultado es:

Filtrar una tabla con VBA

También puede filtrar una tabla de Excel utilizando VBA. Tenemos nuestra tabla llamada Tabla1 y nos gustaría filtrar la tabla para que sólo se muestren las ventas superiores a 1500.

Podemos utilizar el método Autofiltro, que tiene cinco parámetros opcionales. Como queremos filtrar la columna Ventas, que es la segunda columna, ponemos el campo 2, y utilizamos el parámetro operador xlAnd, que se utiliza para fechas y números.

Sub Filtro_simple()

     ActiveWorkbook.Sheets("Hoja1").ListObjects("Tabla1").Range.AutoFilter Field:=2, Criteria1:= _
    ">1500", Operator:=xlAnd
 
End Sub

El resultado es:

 

Borrar el filtro con el método ShowAllData en VBA

Puedes acceder al método ShowAllData de la clase Worksheet para borrar el filtro. Si lo que quieres borrar es el filtro de una tabla, primero tienes que seleccionar una celda de la tabla, lo que puedes hacer en VBA.

El método ShowAllData generará un error si no se utiliza la lógica condicional para comprobar si se ha aplicado un filtro en la hoja de cálculo. El siguiente código muestra cómo hacerlo:

Sub Borrando_Filtro()

    Range("Tabla1[[#Headers],[Ventas]]").Select
    If ActiveWorkbook.Worksheets("Hoja1").FilterMode = True Then
        ActiveSheet.ShowAllData
     
    End If
 
End Sub

Borrar Todos los Filtros de una Tabla de Excel

Puede acceder al método ShowAllData de la clase ListObject sin tener que seleccionar primero una celda de la tabla. El siguiente código muestra cómo hacerlo:

Sub Borrar_todos_los_filtros_de_tabla()
    ActiveWorkbook.Worksheets("Hoja1").ListObjects("Tabla1").AutoFilter.ShowAllData
End Sub

Eliminación de una fila con VBA

Puedes eliminar una fila en el cuerpo de datos de tu tabla utilizando el método ListRows.Delete. Tienes que especificar qué fila usando el número de fila. Tenemos la siguiente tabla llamada Tabla1.

Digamos que quieres borrar la segunda fila en el cuerpo de datos de tu tabla, el siguiente código te permitiría hacerlo:

Sub Eliminar_fila()

    ActiveWorkbook.Worksheets("Hoja1").ListObjects("Tabla1").ListRows(2).Delete

End Sub

El resultado es:

Eliminación de una columna con VBA

Puedes eliminar una columna de tu tabla utilizando el método ListColumns.Delete. Tenemos la siguiente tabla llamada Tabla1 que se muestra a continuación:

Para eliminar la primera columna, utilizarías el siguiente código:

Sub Eliminar_columna_de_tabla()
 
    ActiveWorkbook.Worksheets("Hoja1").ListObjects("Tabla1").ListColumns(1).Delete
 
End Sub

El resultado es:

Convertir una tabla de nuevo en un rango en VBA

Puedes volver a convertir una tabla en un rango normal utilizando VBA. El siguiente código muestra cómo convertir una tabla llamada Tabla1 de nuevo en un rango:

Sub Convertir_tabla_en_un_rango_normal()

    ActiveWorkbook.Sheets("Hoja1").ListObjects("Tabla1").Unlist

End Sub

Añadir columnas con bandas y formato a todas las tablas de una hoja de cálculo utilizando VBA

Puedes acceder a todas las tablas de tu hoja de cálculo utilizando la colección ListObjects. En la hoja de abajo tenemos dos tablas y nos gustaría añadir una Columna con bandas a ambas tablas a la vez y cambiar la fuente de la sección de datos de ambas tablas a negrita, utilizando VBA.

Sub Añadiendo_Columnas_Bandas()

    Dim tbl As ListObject
    Dim sht As Worksheet
     
    Set sht = ThisWorkbook.ActiveSheet
     
    For Each tbl In sht.ListObjects
        tbl.ShowTableStyleColumnStripes = True
        tbl.DataBodyRange.Font.Bold = True
    Next tbl
 
End Sub

El resultado es:

 

Creación de una tabla en Access en VBA utilizando DoCmd.RunSQL

Una de las principales formas de crear una tabla en Access en VBA, es mediante el uso del método DoCmd.RunSQL para ejecutar una consulta de acción con una sentencia SQL.

Tenemos un botón en nuestro formulario de ejemplo y cuando hacemos clic en el botón nos gustaría crear una tabla llamada ProductsTable con dos campos o columnas, uno sería el campo de clave primaria llamado ProductsID y el otro sería un campo llamado Sales.

Para crear esta tabla utilizaríamos el siguiente código:

Private Sub btEnlace_Click()

    DoCmd.RunSQL "CREATE TABLE TablaProductos " _
    & "(IdProducto INTEGER PRIMARY KEY, Ventas Integer);"

End Sub

El resultado es:

Filtrar una tabla en Access usando VBA

También puede filtrar una tabla en Access utilizando el método DoCmd.ApplyFilter. Tenemos nuestra tabla simple mostrada abajo en Access llamada TablaProductos.

Nos gustaría pulsar este botón en nuestro formulario y entonces sólo ver las ventas que son mayores de 1500.

Para ello utilizaríamos el siguiente código

Private Sub btFilter_Click()
    
    DoCmd.OpenTable "TablaProductos"
    
    DoCmd.ApplyFilter , "[Ventas]>1500"
End Sub

El resultado es: