Este tutorial demostrará cómo trabajar con Tablas y ListObjects en VBA.
Tablas de VBA y ListObjects
Las tablas son una de las características más útiles y poderosas de Excel, en este tutorial, repasaremos cómo usar VBA para crear una tabla, agregar una ordenación simple a una tabla, filtrar una tabla y realizar otras tareas relacionadas con la tabla.
Crear una tabla con VBA
El método ListObjects.Add puede agregar una tabla a una hoja de trabajo, según un rango en esa hoja de trabajo. Tenemos el rango que se muestra en ($ A $ 1: $ B $ 8) en una hoja de trabajo llamada Hoja1.
El siguiente código agregará una tabla, llamada Table1 a su hoja de trabajo, basada en el rango ($ A $ 1: $ B $ 8) usando el Estilo de tabla predeterminado:
| 123456 | Sub CreateTableInExcel ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects.Add (xlSrcRange, Range ("$ A $ 1: $ B $ 8"),, xlYes) .Name = _"Tabla 1"End Sub |
El resultado es:
Insertar una columna al final de la tabla con VBA
Puede usar el método ListColumns.Add para agregar una columna al final de su tabla. Tenemos nuestra tabla llamada Tabla1 que se muestra a continuación.
Puede agregar una columna a su tabla usando el siguiente código, que siempre agregará una columna al final de la tabla:
| 12345 | Sub AddColumnToTheEndOfTheTable ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). ListColumns.AddEnd Sub |
El resultado es:
Insertar una fila en la parte inferior de la tabla con VBA
Puede usar el método ListRows.Add para agregar una fila al final de su tabla. Tenemos nuestra tabla llamada Tabla1 que se muestra a continuación.
El siguiente código siempre agregará una fila al final de su tabla.
| 12345 | Sub AddRowToTheBottomOfTheTable ()ActiveSheet.ListObjects ("Tabla1"). ListRows.AddEnd Sub |
El resultado es:
Agregar 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 clasificará la columna Ventas en orden ascendente.
| 12345678910111213141516171819 | Sub SimpleSortOnTheTable ()Rango ("Tabla1 [[# encabezados], [Ventas]]"). SeleccioneActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sort.SortFields.Add _Clave: = Rango ("Tabla1 [[# Todos], [Ventas]]"), SortOn: = xlSortOnValues, Order: = _xlAscending, DataOption: = xlSortNormalCon ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Ordenar.Encabezado = xl Sí.MatchCase = Falso.Orientación = xlTopToBottom.SortMethod = xlPinYin.SolicitarTerminar conEnd Sub |
El resultado es:
Filtrar una tabla con VBA
También puede filtrar una tabla de Excel usando VBA. Tenemos nuestra tabla llamada Tabla1 y nos gustaría filtrar la tabla para que solo se muestren las ventas superiores a 1500.
Podemos utilizar el método Autofilter, que tiene cinco parámetros opcionales. Como nos gustaría filtrar la columna Ventas, que es la segunda columna, establecemos el Campo en 2 y usamos el parámetro del operador xlAnd, que se usa para fechas y números.
| 123456 | Sub SimpleFilter ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). Range.AutoFilter Field: = 2, Criteria1: = _"> 1500", Operador: = xlAndEnd Sub |
El resultado es:
Borrar el filtro con el método ShowAllData en VBA
Puede acceder al método ShowAllData de la clase Worksheet para borrar el filtro. Si desea borrar los filtros de una tabla, primero debe seleccionar una celda de la tabla, lo que puede hacer en VBA.
El método ShowAllData generará un error si uno no usa la lógica condicional para verificar si se ha aplicado un filtro en la hoja de trabajo. El siguiente código le muestra cómo hacer esto:
| 123456789 | Sub ClearingTheFilter ()Rango ("Tabla1 [[# encabezados], [Ventas]]"). SeleccioneSi ActiveWorkbook.Worksheets ("Sheet1"). FilterMode = True EntoncesActiveSheet.ShowAllDataTerminara siEnd 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 le muestra cómo hacer esto:
| 123 | Sub ClearAllTableFilters ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). AutoFilter.ShowAllDataEnd Sub |
Eliminar una fila con VBA
Puede eliminar una fila en el cuerpo de datos de su tabla usando el método ListRows.Delete. Tienes que especificar qué fila usando el número de fila. Tenemos la siguiente tabla llamada Table1.
Supongamos que desea eliminar la segunda fila en el cuerpo de datos de su tabla, el siguiente código le permitiría hacer esto:
| 12345 | Sub DeleteARow ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListRows (2) .DeleteEnd Sub |
El resultado es:
Eliminar una columna con VBA
Puede eliminar una columna de su tabla utilizando el método ListColumns.Delete. Tenemos la siguiente tabla llamada Tabla1 que se muestra a continuación:
Para eliminar la primera columna, usaría el siguiente código:
| 12345 | Sub DeleteAColumn ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListColumns (1) .DeleteEnd Sub |
El resultado es:
Convertir una tabla de nuevo a un rango en VBA
Puede convertir una tabla a un rango normal usando VBA. El siguiente código le muestra cómo convertir una tabla llamada Table1 de nuevo a un rango:
| 12345 | Conversión secundariaATableBackToANormalRange ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). UnlistEnd Sub |
Agregar columnas con bandas y formatear todas las tablas en una hoja de trabajo usando VBA
Puede acceder a todas las tablas en su hoja de trabajo usando la colección ListObjects. En la hoja a continuación, tenemos dos tablas y nos gustaría agregar 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, usando VBA.
| 12345678910111213 | Sub AddingBandedColumns ()Dim tbl como ListObjectDim sht como hoja de trabajoEstablecer sht = ThisWorkbook.ActiveSheetPara cada tbl en sht.ListObjectstbl.ShowTableStyleColumnStripes = Verdaderotbl.DataBodyRange.Font.Bold = TrueSiguiente tblEnd Sub |
El resultado es:
Creando una tabla en Access en VBA usando 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 declaración SQL.
Tenemos un botón en nuestro formulario de muestra 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 principal llamado ProductsID y el otro sería un campo llamado Ventas.
Para crear esta tabla usaríamos el siguiente código:
| 123456 | Sub privado cmdCreateProductsTable_Click ()DoCmd.RunSQL "CREATE TABLE ProductsTable" _& "(ProductID INTEGER PRIMARY KEY, Sales Integer);"End Sub |
El resultado es:
Filtrar una tabla en Access usando VBA
También puede filtrar una tabla en Access mediante el método DoCmd.ApplyFilter. Tenemos nuestra tabla simple que se muestra a continuación en Access llamada ProductsTable.
Nos gustaría presionar este botón en nuestro formulario y luego solo ver las Ventas que sean superiores a 1500.
Entonces, usaríamos el siguiente código para hacer esto:
| 1234567 | Sub privado cmdFilter_Click ()DoCmd.OpenTable "ProductsTable"DoCmd.ApplyFilter, "[Ventas]> 1500"End Sub |
El resultado es:
