Tablas en excel-vba-macros

tablas excel
manejo de tablas en excel

La posibilidad de convertir un rango en Tabla es uno de los mayores avances de Excel de los últimos años. Como veremos, las Tablas nos facilitan la gestión y el análisis de datos. Son varias e importantes las ventajas.

Referencias a las Tablas

Antes de las Tablas, cualquier referencia a un rango tenía que modificarse manualmente si cambiaba el número de filas o columnas de dicho rango o cambiaba su posición dentro de la hoja. Nada de esto debe preocuparte ya si utilizas Tablas, las referencias se actualizan automáticamente. Esta importante mejora es aplicable tanto a las referencias que hagas en el código VBA de tus macros como en cualquier lugar donde utilices fórmulas con referencias: celdas, formatos, validación de datos, etc.

Para ello, Excel utiliza unas referencias especiales que mejoran la claridad y comprensión de las fórmulas, haciendo su diseño y mantenimiento mucho más sencillo e intuitivo. Además, el menú contextual nos va ayudando a escribir las referencias:

Uso de tablas en excel
referenciando tablas en excel.

Para convertir un rango en Tabla basta con hacer clic dentro del rango y pulsar la combinación de teclas: CTRL+T. También puedes hacerlo desde el menú INICIO -> Dar formato como tabla.

Referencias como las siguientes son las que verás en tus fórmulas:

  • Para sumar los datos de la columna Ventas: =SUMA(MiTabla[Ventas])
  • Para referirnos a la celda de la fila actual en la columna Ventas: =MiTabla[@Ventas]
  • Importe de la fila Total de la columna Ventas: =MiTabla[[#Totales];[Ventas]]
  • El nombre del encabezado de la columna Ventas, sería: =MiTabla[[#Encabezados];[Ventas]]
  • Una combinación de 2 ó más columnas: =MiTabla[Pais];MiTabla[Ventas]
  • Toda la tabla, incluidos los encabezados de columna, datos y totales: =MiTabla[#Todas]
  • Sólo datos: =MiTabla[#Datos]

Trabajar con Tablas en VBA

Pero es sobre todo cuando desarrollas en VBA cuando descubres todas las ventajas. Vamos a ver ejemplos del código que necesitarás para trabajar con ellas:

Crear un Tabla

El siguiente código crea una Tabla en un rango, o da formato Tabla al rango:

'Crea una Tabla en Excel VBA
Hoja1.ListObjects.Add(xlSrcRange, Range("A1:D10"), xlYes).Name = "MiTabla"

Eliminar las filas de una Tabla

Para eliminar filas de una Tabla podemos hacer:

Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("MiTabla")
'Para borrar una columna determinada
tbl.ListColumns(3).Delete
'Para borrar una fila determinada
tbl.ListRows(6).Delete

Para limpiar la tabla eliminando todas las filas de datos puedes utilizar la siguiente instrucción. Ten en cuenta que si en la Tabla no existe ninguna fila que borrar arrojará un error, por lo que es conveniente utilizarlo con un condicional como el del ejemplo:

'Elimina todas las filas de MiTabla
If ActiveSheet.ListObjects("MiTabla").ListRows.Count > 0 Then
     ActiveSheet.Range("MiTabla").Delete
End If

Insertar filas y columnas

Para insertar en una posición determinada:

'Insertar una columna en una posición específica
Hoja1.ListObjects("MiTabla").ListColumns.Add Position:=5
 
'Insertar una fila en la fila 3
Hoja1.ListObjects("MiTabla").ListRows.Add(3)

Para insertar una columna a la derecha o una fila al final:

'Insertar en una columna a la derecha
Hoja1.ListObjects("MiTabla").ListColumns.Add
 
'Añadir una fila al final
Hoja1.ListObjects("MiTabla").ListRows.Add AlwaysInsert:= True

Añadir fila de totales

'Incluye las cabeceras y totales
Hoja1.ListObjects("MiTabla").Range.Select
 
'Incluye sólo los datos, sin cabeceras ni totales
Hoja1.ListObjects("MiTabla").DataBodyRange.Select

Seleccionar elementos de la tabla

Selección de una Tabla completa:

'Incluye las cabeceras y totales
Hoja1.ListObjects("MiTabla").Range.Select
 
'Incluye sólo los datos, sin cabeceras ni totales
Hoja1.ListObjects("MiTabla").DataBodyRange.Select

Para seleccionar una determinada columna de una Tabla:

'Para seleccionar la segunda columna de la Tabla, sólo los datos
'sin cabecera:
Hoja1.ListObjects("MiTabla").ListColumns(2).DataBodyRange.Select
 
'Si quieres incluir también la cabecera:
Hoja1.ListObjects("MiTabla").ListColumns(1).Range.Select

Para seleccionar la tercera fila de la Tabla:

Hoja1.ListObjects("MiTabla").ListRows(3).Range.Select

Para seleccionar una determinada celda dentro de la tabla, por ejemplo la fila 5 y columna 2:

Hoja1.ListObjects("MiTabla").DataBodyRange(5, 2).Select

Selección de la fila de encabezados:

Hoja1.ListObjects("MiTabla").HeaderRowRange.Select

Selección de la fila de totales:

Hoja1.ListObjects("MiTabla").TotalsRowRange.Select

Rellenar un ComboBox o ListBox con datos de una Tabla

'Para llevar datos de una Tabla a un ComboBox
Me.cbxMiComboBox.RowSource = "MiTabla"
 
'Para llevar datos de una Tabla a un ListBox
Me.lbxMiListBox.RowSource = "MiTabla"

Rellenar un Array con datos de una Tabla

Sub CargarArray()
   Dim MiArray() As Variant
   Dim tbl As Range
 
   Set tbl = Hoja1.Range("MiTabla")
   MiArray = tbl
End Sub

Fuente original: http://blogexcelyvba.com/tablas-excel/

Ejemplo practico:

Se tiene una tabla de pedidos de productos como se muestra a continuación, su nombre es “tblPedidos”, que esta en la hoja4 :

Se intenta filtrar por “Nro” de pedido para luego enviar los item visibles a otra tabla que la hara de facturador. pero solo se enviara los datos filtrados, como se aprecia en la siguiente imagen, el nombre de esta tabla es “tblFactura”, que esta en la hoja 8:

La solucion la implementariamos en un userform, como el que sigue:

Todo el codigo estaria en el boton de “Realizar Factura” y seria el siguiente:

Private Sub CommandButton1_Click()
Dim FilasActuales As Integer
Dim FilasActualesPedido As Integer
Hoja8.Activate
Hoja8.Range("NroPedidoFactura").Value = ComboBox1.Value
FilasActuales = Hoja8.ListObjects("tblFactura").ListRows.Count
Hoja4.ListObjects("tblPedidos").Range.AutoFilter Field:=1, Criteria1:=ComboBox1.Value
Application.ScreenUpdating = True
FilasActualesPedido = Hoja4.Range("tblPedidos[Nro]").SpecialCells(xlCellTypeVisible).Count
lblfilas.Caption = FilasActualesPedido
For i = 1 To FilasActualesPedido
    FilasActuales = FilasActuales + 1
    Hoja8.ListObjects("tblFactura").ListRows.Add AlwaysInsert:=True
    Hoja8.ListObjects("tblFactura").DataBodyRange(FilasActuales, 1).Value = Hoja4.ListObjects("tblPedidos").DataBodyRange.SpecialCells(xlCellTypeVisible).Cells(i, 1)
    Hoja8.ListObjects("tblFactura").DataBodyRange(FilasActuales, 2).Value = Hoja4.ListObjects("tblPedidos").DataBodyRange.SpecialCells(xlCellTypeVisible).Cells(i, 5)
    Hoja8.ListObjects("tblFactura").DataBodyRange(FilasActuales, 3).Value = Hoja4.ListObjects("tblPedidos").DataBodyRange.SpecialCells(xlCellTypeVisible).Cells(i, 7)
    Hoja8.ListObjects("tblFactura").DataBodyRange(FilasActuales, 4).Value = Hoja4.ListObjects("tblPedidos").DataBodyRange.SpecialCells(xlCellTypeVisible).Cells(i, 8)
    Hoja8.ListObjects("tblFactura").DataBodyRange(FilasActuales, 5).Value = Hoja4.ListObjects("tblPedidos").DataBodyRange.SpecialCells(xlCellTypeVisible).Cells(i, 8)
Next i
Hoja8.ListObjects("tblFactura").DataBodyRange(FilasActuales, 5).Select
End Sub
Comentarios