Tablas VBA y ListObjects

Las tablas son una de las características más poderosas de Excel. Controlarlos usando VBA proporciona una forma de automatizar ese poder, lo que genera un doble beneficio.

A Excel le gusta almacenar datos dentro de tablas. Las reglas estructurales básicas, como (a) los encabezados deben ser únicos (b) solo se permite una fila de encabezado, hacen que las tablas sean compatibles con herramientas más complejas. Por ejemplo, Power Query, Power Pivot y SharePoint enumeran todas las tablas de uso como origen o salida. Por lo tanto, la intención de Microsoft es claramente que utilicemos tablas.

Sin embargo, el mayor beneficio para el usuario cotidiano de Excel es mucho más simple; Si agregamos nuevos datos al final de una tabla, cualquier fórmula que haga referencia a la tabla se expandirá automáticamente para incluir los nuevos datos.

Ya sea que ames las tablas tanto como a mí o no, esta publicación te ayudará a automatizarlas con VBA.

Las tablas, tal como las conocemos hoy, aparecieron por primera vez en Excel 2007. Fue un reemplazo de la funcionalidad Listas que se encuentra en Excel 2003. Desde una perspectiva de VBA, el modelo de objetos del documento (DOM) no cambió con la funcionalidad actualizada. Entonces, aunque usamos el término "tablas" en Excel, todavía se las conoce como ListObjects en VBA.

Tabla de contenido

• Estructura de una mesa
• Hacer referencia a las partes de una tabla.
• Seleccione toda la tabla
• Seleccionar los datos dentro de una tabla
• Obtener un valor de una celda individual dentro de una tabla
• Seleccionar una columna completa
• Seleccione una columna (solo datos)
• Seleccione un encabezado de columna específico
• Seleccione una columna específica dentro de la sección de totales
• Seleccione una fila completa de datos
• Seleccione la fila del encabezado
• Seleccione la fila de totales
• Crear y convertir tablas
• Convertir la selección en una tabla
• Convertir una tabla nuevamente en un rango
• Cambiar el tamaño del rango de la tabla.
• Estilos de mesa
• Cambiar el estilo de la tabla
• Obtener el nombre del estilo de la tabla
• Aplicar un estilo a la primera o última columna.
• Agregar o quitar rayas
• Establecer el estilo de tabla predeterminado
• Recorriendo tablas
• Recorrer todas las tablas en una hoja de trabajo
• Recorrer todas las tablas de un libro de trabajo
• Agregar eliminar filas y columnas
• Agregar columnas a una tabla
• Agregar filas al final de una tabla
• Eliminar columnas de una tabla
• Eliminar filas de una tabla
• Agregar fila total a una tabla
• Visibilidad del encabezado de la tabla
• Quitar filtro automático
• Otras técnicas de rango
• Usando el operador de unión
• Asignar valores de una matriz variante a una fila de la tabla
• Partes de referencia de una tabla usando el objeto de rango
• Contando filas y columnas
• Contando filas
• Contando columnas
• Técnicas de mesa útiles.
• Mostrar el formulario de entrada de datos de la tabla.
• Comprobar si existe una tabla
• Descubra si se ha seleccionado una mesa y, en caso afirmativo, cuál
• Conclusión

Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.

Nombre de archivo: 0009 Tablas VBA y ListObjects.zip

Tener acceso

Estructura de una mesa

Antes de profundizar en cualquier código VBA, es útil comprender cómo se estructuran las tablas.

Rango de cuerpo de datos

El rango es toda el área de la mesa.

El rango del cuerpo de datos solo incluye las filas de datos, excluye el encabezado y los totales.

Encabezado y filas totales

El rango de la fila del encabezado es la fila superior de la tabla que contiene los encabezados de las columnas.

El rango de filas de totales , si se muestra, incluye cálculos en la parte inferior de la tabla.

Listar columnas y listar filas

Las columnas individuales se conocen como columnas de lista .

Cada fila se conoce como fila de lista .

El código VBA en esta publicación detalla cómo administrar todos estos objetos de tabla.

Hacer referencia a las partes de una tabla.

Si bien puede tener la tentación de saltarse esta sección, le recomiendo que la lea en su totalidad y analice los ejemplos. Comprender el modelo de objetos de documentos de Excel es la clave para leer y escribir código VBA. Domine esto y su capacidad para escribir su propio código VBA será mucho mayor.

Muchos de los ejemplos de esta primera sección utilizan el método de selección, esto es para ilustrar cómo hacer referencia a partes de la tabla. En realidad, rara vez utilizarías el método de selección.

Seleccione toda la tabla

La siguiente macro seleccionará toda la tabla, incluidos los totales y las filas de encabezado.

Sub SelectTable()ActiveSheet.ListObjects("miTabla").Range.SelectEnd Sub

Seleccionar los datos dentro de una tabla

DataBodyRange excluye las secciones de encabezado y totales de la tabla.

Sub SelectTableData()ActiveSheet.ListObjects("miTabla").DataBodyRange.SelectEnd Sub

Obtener un valor de una celda individual dentro de una tabla

La siguiente macro recupera el valor de la tabla de la fila 2, columna 4 y lo muestra en un cuadro de mensaje.

Sub GetValueFromTable()MsgBox ActiveSheet.ListObjects("miTabla").DataBodyRange(2, 4).valueEnd Sub

Seleccionar una columna completa

La siguiente macro muestra cómo seleccionar una columna por su posición o por su nombre.

Sub SelectAnEntireColumn()'Seleccione la columna según la posiciónActiveSheet.ListObjects("myTable").ListColumns(2).Range.Select'Seleccione la columna según el nombreActiveSheet.ListObjects("myTable").ListColumns("Category").Range.SelectEnd Sub

Seleccione una columna (solo datos)

Esto es similar a la macro anterior, pero utiliza DataBodyRange para seleccionar solo los datos; excluye los encabezados y los totales.

Sub SelectColumnData()'Seleccione los datos de la columna según la posiciónActiveSheet.ListObjects("myTable").ListColumns(4).DataBodyRange.Select'Seleccione los datos de la columna según el nombreActiveSheet.ListObjects("myTable").ListColumns("Category").DataBodyRange .Seleccione Fin Sub

Seleccione un encabezado de columna específico

Esta macro muestra cómo seleccionar la celda del encabezado de la quinta columna.

Sub SelectCellInHeader()ActiveSheet.ListObjects("miTabla").HeaderRowRange(5).SelectEnd Sub

Seleccione una columna específica dentro de la sección de totales

Este ejemplo demuestra cómo seleccionar la celda en la fila de totales de la tercera columna.

Sub SelectCellInTotal()ActiveSheet.ListObjects("miTabla").TotalsRowRange(3).SelectEnd Sub

Seleccione una fila completa de datos

La siguiente macro selecciona la tercera fila de datos de la tabla.
NOTA – La fila de encabezado no se incluye como ListRow. Por lo tanto, ListRows(3) es la tercera fila dentro de DataBodyRange y no la tercera fila desde la parte superior de la tabla.

Sub SelectRowOfData()ActiveSheet.ListObjects("miTabla").ListRows(3).Range.SelectEnd Sub

Seleccione la fila del encabezado

La siguiente macro selecciona la sección del encabezado de la tabla.

Sub SelectHeaderSection()ActiveSheet.ListObjects("miTabla").HeaderRowRange.SelectEnd Sub

Seleccione la fila de totales

Para seleccionar la fila de totales de la tabla, utilice el siguiente código.

Sub SelectTotalsSection()ActiveSheet.ListObjects("miTabla").TotalsRowRange.SelectEnd Sub

Bien, ahora que sabemos cómo hacer referencia a las partes de una tabla, es hora de entrar en algunos ejemplos más interesantes.

Crear y convertir tablas

Esta sección de macros se centra en la creación y el cambio de tamaño de tablas.

Convertir la selección en una tabla

La siguiente macro crea una tabla basada en la región seleccionada actualmente y la denomina myTable . Se hace referencia al rango como Selection.CurrentRegion, pero se puede sustituir por cualquier objeto de rango.

Si está trabajando junto con el archivo de ejemplo, esta macro generará un error, ya que ya existe una tabla llamada myTable en el libro de trabajo. Se seguirá creando una nueva tabla con un nombre predeterminado, pero el código VBA generará un error en el paso de cambio de nombre.

Sub ConvertRangeToTable()tableName As StringDim tableRange As RangeSet tableName = "myTable"Set tableRange = Selection.CurrentRegionActiveSheet.ListObjects.Add(SourceType:=xlSrcRange, _ Source:=tableRange, _ xlListObjectHasHeaders:=xlYes _ ).Name = tableNameEnd Sub

Convertir una tabla nuevamente en un rango

Esta macro convertirá una tabla nuevamente a un rango estándar.

Sub ConvertTableToRange()ActiveSheet.ListObjects("miTabla").UnlistEnd Sub

NOTA: Desafortunadamente, al convertir una tabla a un rango estándar, el formato de la tabla no se elimina. Por lo tanto, las celdas pueden seguir pareciendo una tabla, incluso cuando no lo son – ¡¡¡eso es frustrante!!!

Cambiar el tamaño del rango de la tabla.

La siguiente macro cambia el tamaño de una tabla a la celda A1 – J100.

Sub ResizeTableRange()ActiveSheet.ListObjects("myTable").Resize Range("$A$1:$J$100")End Sub

Estilos de mesa

Hay muchas opciones de formato de tabla, las más comunes se muestran a continuación.

Cambiar el estilo de la tabla

Cambie el estilo de una tabla a un estilo predefinido existente.

Sub ChangeTableStyle()ActiveSheet.ListObjects("myTable").TableStyle = "TableStyleLight15"End Sub

Para aplicar diferentes estilos de tabla, el método más sencillo es utilizar la grabadora de macros. El código VBA grabado incluirá el nombre de cualquier estilo que seleccione.

Obtener el nombre del estilo de la tabla

Utilice la siguiente macro para obtener el nombre del estilo ya aplicado a una tabla.

Sub GetTableStyleName()MsgBox ActiveSheet.ListObjects("miTabla").TableStyleEnd Sub

Aplicar un estilo a la primera o última columna.

La primera y la última columna de una tabla se pueden formatear de forma diferente utilizando las siguientes macros.

Sub ColumnStyles()'Aplicar estilo especial a la primera columnaActiveSheet.ListObjects("myTable").ShowTableStyleFirstColumn = True'Aplicar estilo especial a la última columnaActiveSheet.ListObjects("myTable").ShowTableStyleLastColumn = TrueEnd Sub

Agregar o quitar rayas

De forma predeterminada, las tablas tienen filas con bandas, pero existen otras opciones para esto, como eliminar bandas de filas o agregar bandas de columnas.

Sub ChangeStripes()'Aplicar franjas de columnaActiveSheet.ListObjects("myTable").ShowTableStyleColumnStripes = True'Eliminar franjas de filaActiveSheet.ListObjects("myTable").ShowTableStyleRowStripes = FalseEnd Sub

Establecer el estilo de tabla predeterminado

La siguiente macro establece el estilo de tabla predeterminado.

Sub SetDefaultTableStyle()'Establecer estilo de tabla predeterminadoActiveWorkbook.DefaultTableStyle = "TableStyleMedium2"End Sub

Recorriendo tablas

Las macros de esta sección recorren todas las tablas de la hoja de trabajo o el libro de trabajo.

Recorrer todas las tablas en una hoja de trabajo

Si queremos ejecutar una macro en cada tabla de una hoja de trabajo, debemos recorrer la colección ListObjects.

Sub LoopThroughAllTablesWorksheet()'Crea variables para contener la hoja de trabajo y la tablaDim ws As WorksheetDim tbl As ListObjectSet ws = ActiveSheet'Recorre cada tabla en la hoja de trabajoPara cada tbl En ws.ListObjects 'Haz algo en la tabla....Next tblEnd Sub

En el código anterior, hemos configurado la tabla como una variable, por lo que debemos hacer referencia a la tabla de la manera correcta. En la sección denominada ' Hacer algo en la tabla...' , inserte la acción que se realizará en cada tabla, utilizando tbl para hacer referencia a la tabla.

Por ejemplo, lo siguiente cambiará el estilo de tabla de cada tabla.

tbl.TableStyle = "TableStyleLight15"

Recorrer todas las tablas de un libro de trabajo

En lugar de recorrer una sola hoja de trabajo, como se muestra arriba, la siguiente macro recorre cada tabla de cada hoja de trabajo.

Sub LoopThroughAllTablesWorkbook()'Crea variables para contener la hoja de trabajo y la tablaDim ws As WorksheetDim tbl As ListObject'Recorre cada hoja de trabajo para cada ws en ActiveWorkbook.Worksheets 'Recorre cada tabla en la hoja de trabajo para cada tbl en ws.ListObjects 'Haz algo con Tabla.... Siguiente tblSiguiente wsEnd Sub

Como se indicó en el apartado anterior, debemos referirnos a la tabla utilizando su variable. Por ejemplo, lo siguiente mostrará la fila de totales para cada tabla.

tbl.ShowTotals = Verdadero

Agregar eliminar filas y columnas

Las siguientes macros agregan y eliminan filas, encabezados y totales de una tabla.

Agregar columnas a una tabla

La siguiente macro agrega una columna a una tabla.

Sub AddColumnToTable()'Agregar columna al finalActiveSheet.ListObjects("myTable").ListColumns.Add'Agregar columna en la posición 2ActiveSheet.ListObjects("myTable").ListColumns.Add Position:=2End Sub

Agregar filas al final de una tabla

La siguiente macro agregará una fila al final de una tabla.

Sub AddRowsToTable()'Agregar fila en la parte inferiorActiveSheet.ListObjects("myTable").ListRows.Add'Agregar fila en la primera filaActiveSheet.ListObjects("myTable").ListRows.Add Position:=1End Sub

Eliminar columnas de una tabla

Para eliminar una columna, es necesario utilizar el número de índice de la columna o el encabezado de la columna.

Sub DeleteColumnsFromTable()'Eliminar columna 2ActiveSheet.ListObjects("myTable").ListColumns(2).Delete'Eliminar una columna por nombreActiveSheet.ListObjects("myTable").ListColumns("Feb").DeleteEnd Sub

Eliminar filas de una tabla

En la estructura de la tabla, las filas no tienen nombres y, por lo tanto, solo se pueden eliminar haciendo referencia al número de fila.

Sub DeleteRowsFromTable()'Eliminar fila 2ActiveSheet.ListObjects("myTable").ListRows(2).Delete'Eliminar varias filasActiveSheet.ListObjects("myTable").Range.Rows("4:6").DeleteEnd Sub

Agregar fila total a una tabla

La fila total en la parte inferior de una tabla se puede utilizar para los cálculos.

Sub AddTotalRowToTable()'Muestra la fila total con el valor en la última columnaActiveSheet.ListObjects("myTable").ShowTotals = True'Cambia el total de la "Columna total" a un promedioActiveSheet.ListObjects("myTable").ListColumns("TotalColumn" ).TotalsCalculation = _ xlTotalsCalculationAverage'Los totales se pueden agregar por posición, en lugar de por nombreActiveSheet.ListObjects("myTable").ListColumns(2).TotalsCalculation = _ xlTotalsCalculationAverageEnd Sub

Tipos de cálculo de totales

xlTotalesCálculoNonexlTotalesCálculoPromedioxlTotalesCálculoCountxlTotalsCálculoCountNumsxlTotalsCálculoMaxxlTotalsCálculoMinxlTotalsCálculoSumxlTotalsCálculoStdDevxlTotalsCálculoVar

Visibilidad del encabezado de la tabla

Los encabezados de las tablas se pueden activar o desactivar. Lo siguiente ocultará los encabezados.

Sub ChangeTableHeader()ActiveSheet.ListObjects("myTable").ShowHeaders = FalseEnd Sub

Quitar filtro automático

El filtro automático se puede ocultar. Tenga en cuenta que el encabezado de la tabla debe estar visible para que este código funcione.

Sub RemoveAutoFilter()ActiveSheet.ListObjects("myTable").ShowAutoFilterDropDown = FalseEnd Sub

Tengo una publicación separada sobre el control de la configuración del filtro automático; compruébala aquí . La mayor parte de esa publicación también se aplica a las tablas.

Otras técnicas de rango

También se pueden aplicar a las tablas otras técnicas de VBA existentes para gestionar rangos.

Usando el operador de unión

Para seleccionar múltiples rangos, podemos usar el operador de unión de VBA. Aquí hay un ejemplo, seleccionará las filas 4, 1 y 3.

Sub SelectMultipleRangesUnionOperator()Union(ActiveSheet.ListObjects("myTable").ListRows(4).Range, _ ActiveSheet.ListObjects("myTable").ListRows(1).Range, _ ActiveSheet.ListObjects("myTable").ListRows (3).Rango).Seleccione Fin Sub

Asignar valores de una matriz variante a una fila de la tabla

Para asignar valores a una fila completa desde una matriz de variantes, use un código similar al siguiente:

Sub AssignValueToTableFromArray()'Asignar valores a la matriz (para ilustración)Dim myArray As VariantmyArray = Range("A2:D2")'Asignar valores en la matriz a la tablaActiveSheet.ListObjects("myTable").ListRows(2).Range.Value = miArrayEnd Sub

Partes de referencia de una tabla usando el objeto de rango

Dentro de VBA, se puede hacer referencia a una tabla como si fuera un objeto de rango estándar.

Sub SelectTablePartsAsRange()ActiveSheet.Range("miTabla[Categoría]").SelectEnd Sub

Contando filas y columnas

A menudo resulta útil contar el número de filas o columnas. Este es un buen método para hacer referencia a filas o columnas que se han agregado.

Contando filas

Para contar el número de filas dentro de la tabla, utilice la siguiente macro.

Sub CountNumberOfRows()Msgbox ActiveSheet.ListObjects("myTable").ListRows.CountEnd Sub

Contando columnas

La siguiente macro contará el número de columnas dentro de la tabla.

Sub CountNumberOfColumns()Msgbox ActiveSheet.ListObjects("miTabla").ListColumns.CountEnd Sub

Técnicas de mesa útiles.

Los siguientes son algunos otros códigos VBA útiles para controlar tablas.

Mostrar el formulario de entrada de datos de la tabla.

Si una tabla comienza en la celda A1, hay un formulario de entrada de datos simple que se puede mostrar.

Sub ShowDataEntryForm()'Solo funciona si la tabla comienza en la celda A1ActiveSheet.ShowDataFormEnd Sub

La siguiente captura de pantalla muestra el formulario de datos para la tabla de ejemplo.

Comprobar si existe una tabla

La siguiente macro comprueba si ya existe una tabla dentro de un libro. Cambie la variable tblName para adaptarla a sus necesidades.

Sub CheckIfTableExists()'Crea variables para contener la hoja de trabajo y la tablaDim ws As WorksheetDim tbl As ListObjectDim tblName As StringDim tblExists As BooleantblName = "myTable"'Recorre cada hoja de trabajo para cada ws en ActiveWorkbook.Worksheets 'Recorre cada tabla en la hoja de trabajo para cada tbl En ws.ListObjects Si tbl.Name = tblName Entonces tblExists = True End If Next tblNext wsIf tblExists = True Entonces MsgBox "Tabla " tblName " existe." De lo contrario MsgBox "Tabla " tblName " no existe."End IfEnd Sub

Descubra si se ha seleccionado una mesa y, en caso afirmativo, cuál

Las siguientes macros encuentran el nombre de la tabla seleccionada.

Método 1

Como verá en los comentarios, Jon Peltier tuvo un enfoque sencillo para esto, que ahora se ha convertido en mi enfoque preferido.

Sub SimulateActiveTable()Dim ActiveTable As ListObjectOn Error Reanudar NextSet ActiveTable = ActiveCell.ListObjectOn Error GoTo 0'Confirmar si una celda está en una tabla. Si ActiveTable no es nada, entonces MsgBox "Seleccione la tabla e intente nuevamente"De lo contrario MsgBox "La celda activa está en una tabla llamado: "ActiveTable.NameEnd IfEnd Sub

Método 2

Esta opción, que era mi método original, recorre cada tabla de la hoja de trabajo y comprueba si se cruzan con la celda activa.

Sub SimulateActiveTable_Method2()Dim ActiveTable As ListObjectDim tbl As ListObject'Recorra cada tabla, verifique si la tabla se cruza con la celda activa Para cada tbl en ActiveSheet.ListObjects Si no Intersect(ActiveCell, tbl.Range) no es nada, entonces establezca ActiveTable = tbl MsgBox "El la celda activa está en una tabla llamada: "ActiveTable.Name End IfNext tbl'Si no hay intersección, entonces no hay ninguna tabla seleccionada. Si ActiveTable no es nada, entonces MsgBox "Seleccione una tabla de Excel e intente nuevamente"End IfEnd Sub

Conclusión

¡Guau! Fueron muchos ejemplos de código.

Hay más de 30 macros de VBA arriba, e incluso esto no cubre todo, pero con suerte cubre el 99% de sus requisitos. Para el resto de requisitos, puede probar la biblioteca de referencia de objetos VBA de Microsoft ( https://docs.microsoft.com/en-us/office/vba/api/Excel.ListObject ) .

---