enero 2021 - MAGUU

Ene, Vie, 2021

VBA

Arrays VBA para convertir columna de datos en tabla

Los arrays VBA nos permiten organizar los datos que aparecen en una columna en forma de tabla.

En el post De datos en una columna a tabla hemos visto cómo organizar los datos de una columna en una tabla usando funciones y fórmulas.

Ahora resolveremos el problema con otro enfoque. Por tanto, obtenemos el mismo resultado que con las fórmulas, pero con una técnica distinta: arrays VBA.

Los arrays VBA son una parte muy importante del lenguaje de programación de Excel. Un ejemplo sencillo de array es la lista de meses del año o una lista que resuma las ventas totales de cada mes.

Sub ejemploArray()
Dim miSemana As Variant
     miSemana = Array("Lunes", "Martes", "Miércoles", "Jueves", "Viernes")
     Debug.Print miSemana(0)
     Debug.Print miSemana(2)
     Debug.Print miSemana(4)
End Sub

arrays VBA resultado código ejemplo — *resultado en la ventgana Inmediato*

¿Qué es un array?

Los arrays VBA son un tipo de variable. Lo usamos para almacenar una lista de datos del mismo tipo. En cierto modo podemos definirlo como un almacén temporal de datos.

más info en: Arrays · Lo básico

Y puesto que trabajan como listas, resultan muy útiles para manejar los valores de las celdas de una hoja de cálculo por dos buenas razones:
primero, porque un array VBA es una estructura de datos muy flexible,
y segundo, porque podemos procesar esos datos (para hacer cálculos, ordenar valores, eliminar elementos…) antes de volcarlos sin alterar las celdas de origen.

Cómo convertimos los datos de una columna en tabla

Así que tenemos apilados los datos de una lista de productos en una columna, y queremos convertir la columna en una tabla.

arrays VBA convertimos una columna de datos en una tabla con campos — *de columna de datos a tabla con array VBA*

En la columna, hay cuatro tipos de datos que se repiten hacia abajo y que se corresponden con cuatro campos.

arrays VBA campos de los datos en la columna — *campos de los datos*

Lo que pretendemos es muy sencillo: en primer lugar que cada celda de la tabla albergue el valor que le corresponda de la columna de datos, y después que los valores se ordenen según los campos a los que pertenecen.

arrays VBA tabla con datos organizados en campos — *tabla con datos*

Paso 1: insertamos un nuevo módulo

Abrimos el editor de VBA (ALT+F11)

Insertamos un módulo (menú Insertar >> Módulo, y damos un nombre al procedimiento. Para el nombre del método evitamos tildes.

Sub DatosColumnaATabla()

End Sub

Paso 2: declaramos las variables

Empezamos declarando las variables:

Sub DatosColumnaATabla()
' Declaramos las variables
     Dim i, j As Integer
     Dim primeraFila, ultimaFila As Integer
     Dim contadorArray As Integer
     Dim arrayColumna() As Variant
 End Sub

Las variables i y j serán contadores para los bucles FOR … NEXT.

La variable primeraFila recorrerá la columna de datos para incluir los valores de sus celdas en nuestro array.
En este caso, como es un rango pequeño, podríamos saber cuál es el tamaño del rango con echar un vistazo hacia abajo en la hoja de cálculo.

arrays VBA última celda de la columna de datos — *última fila de la columna de datos*

Sin embargo, cuando el rango de la columna tiene un número impreciso o muy enorme de celdas, mover la barra es una mala práctica para conocer el tamaño del rango.

Con la variable ultimaFila sabemos cuál es la última fila de la columna de datos para, a partir de ahí, dar tamaño al array.

La variable contadorArray llenará las celdas destino con los valores del array.

La variable arrayColumna será nuestro array.
Como no sabemos el número de elementos que contendrá, no le damos tamaño y lo declaramos vacío.
El tamaño del array depende del número de celdas de la columna y no siempre sabremos cuántas son.
Por lo tanto, primero nos vamos a asegurar el número exacto de elementos que contendrá con la variable ultimaFila. En otro caso, obtendríamos un error.

Paso 3: valor de la última fila

Damos valor a la variable ultimaFila:

Sub DatosColumnaATabla()
' Declaramos las variables
     Dim i, j As Integer
     Dim primeraFila, ultimaFila As Integer
     Dim contadorArray As Integer
     Dim arrayColumna() As Variant
' Valor de la variable ultimaFila
     ultimaFila = Cells(3, 2).End(xlDown).Row
End Sub

Cells(3, 2) es la celda donde arranca el rango, B3: fila 3, columna 2.

End(xlDown).Row nos lleva hasta la última celda de la columna 2 (B). Así sabremos cuántas filas tiene.

Paso 4: dimensionamos el array

Ahora tenemos que dar tamaño al array.
La cantidad de celdas de la columna de datos nos da el número de elementos del array, es decir, su tamaño.

Con esta técnica, el rango de la columna de datos es “B3:BultimaFila”, donde ultimaFila es el valor de la variable.
ultimaFila nos devuelve el valor de la última fila del rango, pero no es el número de celdas del rango. Por lo tanto, el número de celdas del rango es el valor de ultimaFila – 3, porque 3 es la celda donde arranca el rango.
Ahora ya podemos dar tamaño al rango con la sentencia REDIM.

REDIM es la sentencia que nos permite dimensionar un array cuando no hemos declarado inicialmente su tamaño.

Sub DatosColumnaATabla()
' Declaramos las variables
      Dim i, j As Integer
      Dim primeraFila, ultimaFila As Integer
      Dim contadorArray As Integer
      Dim arrayColumna() As Variant
' Valor de la variable ultimaFila
      ultimaFila = Cells(3, 2).End(xlDown).Row
' Damos tamaño al array con ReDim
      ReDim arrayColumna(ultimaFila - 3)
 End Sub

Si supiéramos el tamaño del array escribiríamos REDIM array(n), donde n es el número de elementos que tendrá el array.

Paso 5: poblamos el array

Ahora que el array ya tiene tamaño, vamos a poblarlo con los elementos de la columna de datos. Para hacerlo, usamos un bucle FOR … NEXT que recorre la columna, celda a celda, desde la primera a la última.

Preparamos el bucle. Como la primera celda está en la fila 3, damos valor a la variable.

' Damos valor a la variable primeraFila
     primeraFila = 3

En cada iteración, el bucle tomará el valor de la celda y lo añadirá como elemento al array. Después, pasará a la siguiente fila y repetirá la acción. Cuando llegue a la última celda se detendrá.

' Bucle que puebla el array
     For i = LBound(arrayColumna) To UBound(arrayColumna)
         arrayColumna(i) = Cells(primeraFila, 2).Value
         primeraFila = primeraFila + 1
     Next i

LBound y UBound son los límites inferior y superior del array.

más info en: Funciones LBound y UBound para arrays

Con esta línea

arrayColumna(i) = Cells(primeraFila, 2).Value

el bucle arranca en la primera fila y asigna a cada elemento del array el valor que le toca.

Tras añadir ese valor de la celda al array, el bucle avanza una celda hacia abajo con la sentencia:

primeraFila = primeraFila + 1

Cuando alcanza la ultima celda, el procedimeinto abandona el bucle FOR … NEXT

El código hasta este punto:

Sub DatosColumnaATabla()
' Declaramos las variables
     Dim i, j As Integer
     Dim primeraFila, ultimaFila As Integer
     Dim contadorArray As Integer
     Dim arrayColumna() As Variant
' Valor de la variable ultimafila
     ultimaFila = Cells(3, 2).End(xlDown).Row
' Damos tamaño al array con ReDim
     ReDim arrayColumna(ultimaFila - 3)
' Damos valor a la variable primeraFila
     primeraFila = 3
' Bucle que puebla el array
     For i = LBound(arrayColumna) To UBound(arrayColumna)
         arrayColumna(i) = Cells(primeraFila, 2).Value
         primeraFila = primeraFila + 1
     Next i
 End Sub

Paso 6: escribimos en la tabla con arrays VBA

Una vez poblado el array con los valores de la columna de datos, los escribimos en las celdas de la tabla.

La tabla es una matriz de dos dimensiones: filas y columnas. Para recorrerla tenemos que usar dos bucles FOR … NEXT. Uno primero para recorrer las filas de la tabla y el segundo bucle FOR … NEXT, anidado dentro del primero, para recorrer las columnas.

El esquema del bloque de código es el siguiente:

For recorremos filas
     For recorremos columnas
     Salimos de las columnas
Salimos de las filas

Recorremos, uno a uno, los elementos del array con la otra variable que usamos como contador: contadorArray.
Esta variable la iniciamos en 0.

contadorArray = 0

El límite superior del array es múltiplo de 4, ya que cuatro son los campos de la tabla. Así que tenemos que dividir el valor de cada elemento del array entre cuatro para que el bucle pueda avanzar a la siguiente fila:

contadorArray = 0 
For i = LBound(arrayColumna) To (UBound(arrayColumna) / 4)     
 
Next i

En las celdas, se escribe el valor que les corresponde.

     For j = 4 To 7
         Cells(i + 4, j).Value = arrayColumna(contadorArray)
         contadorArray = contadorArray + 1         
     Next j

El valor de j (las columnas de la tabla) se ajusta según nuestras necesidades. En este caso, las columnas van desde la 4 hasta la 7.

Después el valor de fila (variable i). Ajustamos el valor de la celda de inicio de la tabla según nos convenga. En nuestro caso, está en la fila 4.

Este es el bloque de código que necesitamos para escribir en las celdas de la tabla:

contadorArray = 0
For i = LBound(arrayColumna) To (UBound(arrayColumna) / 4)     
     For j = 4 To 7
         Cells(i + 4, j).Value = arrayColumna(contadorArray)
         contadorArray = contadorArray + 1         
     Next j 
Next i

Ejecutamos con F5 para la tabla con los valores de la columna.

imagen

Este es el código hasta este punto

For i = LBound(arrayColumna) To (UBound(arrayColumna) / 4)     
     For j = 4 To 7
         Cells(i + 4, j).Value = arrayColumna(contadorArray)
         contadorArray = contadorArray + 1         
     Next j 
Next i

Ya solo tenemos que darle formato. Nuestro siguiente paso.

Paso 7: damos formato a la tabla

Para terminar damos formato a los bordes de las celdas y así tener una apariencia uniforme.

Usamos un bloque WITH … END para asignar las propiedades a los bordes de la tabla, dentro del segundo bucle FOR … NEXT.

For recorremos filas
     For recorremos columnas
         With elemento
         Salimos de with
     Salimos de las columnas
Salimos de las filas

Para cada celda aplicamos un estilo de línea contínua con

.LineStyle = XlLineStyle.xlContinuous

Damos un tamaño de 2 al grosor de la línea:

.Weight = 2

y un color:

.Color = RGB(0, 112, 192)

Y ya hemos dado a la tabla un formato muy legible.

       ' Damos formato a las celdas de la tabla
       With Cells(i + 4, j).Borders
             .LineStyle = XlLineStyle.xlContinuous
             .Weight = 2
             .Color = RGB(0, 112, 192)
       End With

Código del procedimiento

Sub DatosColumnaATabla()
' Declaramos las variables
     Dim i, j As Integer
     Dim primeraFila, ultimaFila As Integer
     Dim contadorArray As Integer
     Dim arrayColumna() As Variant
' Valor de la variable ultimafila
     ultimaFila = Cells(3, 2).End(xlDown).Row
' Damos tamaño al array con ReDim
     ReDim arrayColumna(ultimaFila - 3)
' Damos valor a la variable primeraFila
     primeraFila = 3
' Bucle que puebla el array
     For i = LBound(arrayColumna) To UBound(arrayColumna)
         arrayColumna(i) = Cells(primeraFila, 2).Value
         primeraFila = primeraFila + 1
     Next i
' Escribimos los valores del array en las celdas de la tabla
     contadorArray = 0
     For i = LBound(arrayColumna) To (UBound(arrayColumna) / 4)
          For j = 4 To 7
              Cells(i + 4, j).Value = arrayColumna(contadorArray)
              contadorArray = contadorArray + 1
         ' Damos formato a las celdas de la tabla
              With Cells(i + 4, j).Borders
                  .LineStyle = XlLineStyle.xlContinuous
                  .Weight = 2
                  .Color = RGB(0, 112, 192)
              End With
          Next j
     Next i
 End Sub

Descarga el libro de trabajo

Libro de trabajo con datos y código Este es un archivo con extensión .xlsm. Comprueba que el navegador no cambia la extensión al archivo.

www.maguu.es

Ene, Vie, 2021

Excel

De datos en una columna a tabla

¿Cómo organizar los datos de una columna en una tabla?

Tenemos los datos de una serie de productos apilados en una columna y queremos organizar esos datos en una tabla.

Observamos que hay cuatro tipos de datos que se corresponden con varios campos distintos y que se van repitiendo hacia abajo.

cuatro tipos de datos en columna — *los cuatro tipos de datos*

Queremos una tabla que presente los datos en campos.

La tabla tendrá una fila de encabezados para los campos.

Cada celda de la tabla tiene que albergar el valor que le corresponda de la columna de datos. Para conseguirlo vamos a utilizar una técnica que usa la función INDICE.

La función INDICE nos devuelve el valor de una celda dentro de una matriz o tabla, según el número de fila que la celda ocupa dentro de la matriz. En la versión matricial de esta función, son argumentos obligatorios la matriz (o tabla) y el número de fila.

INDICE(matriz; núm_fila; [núm_columna])

Esta técnica completará las celdas de la tabla con los valores de las celdas de la columna de datos. La técnica consiste en emparejar las celdas de la columna de datos con las celdas que les correspondan en la tabla mediante indicadores. Estos indicadores los conseguimos combinando las funciones FILAS y COLUMNAS, que usaremos como argumento para el número de fila de la función INDICE.

La técnica

La función INDICE devuelve el valor de una celda dentro de una matriz, siempre que sepamos en qué fila está dicha celda.

De los dos argumentos obligatorios para usar la función INDICE, ahora mismo tenemos la matriz, que es el rango de celdas que contiene los valores en la columna de datos. Nos hace falta saber en qué fila está cada celda de la columna de datos. Pero la columna de datos puede estar colocada en cualquier lugar de la hoja de cálculo así que tenemos que asegurarnos una manera exacta para saber en qué fila está cada celda de la columna de datos.

Paso 1: indicadores numéricos para la matriz

Supongamos que cada celda de la columna de datos tuviese un identificador numérico; la primera celda sería el 1, la siguiente celda sería el número 2 y así sucesivamente hasta el final de la columna de datos.

*indicadores numéricos para la columna de datos*

Paso 2: indicadores numéricos para la tabla

Consideraremos la tabla como una cuadrícula.

Cada celda de la cuadrícula tendrá su indicador numérico. Este nos permitirá emparejar las celdas de la columna de datos con su correspondiente lugar en la cuadrícula. Así, el valor de la celda número 1 de la columna de datos irá a la celda número 1 de la tabla y después el siguiente valor hasta llegar al final de la columna.

*indicadores numéricos para la cuadrícula*

El indicador numérico para cada celda de la cuadrícula lo obtendremos mediante una fórmula que combina las funciones COLUMNAS y FILAS.

Paso 3: conseguimos el número de columna en la tabla

La función COLUMNAS nos devuelve el número de columnas de una matriz o de una referencia.

COLUMNAS(matriz)

El número de columna nos servirá para recorrer la tabla en sentido vertical. Nos situamos en la celda D3 (primera celda de la tabla a donde queremos llevar el primer valor de la columna de datos) y escribimos esta función:

Como queremos saber el número de columna de la primera celda de la cuadrícula (D3), la matriz que necesitamos para usar la función COLUMNAS será exclusivamente la propia celda D3. Cuando extendamos la selección al resto de columnas y filas, Excel tomará como referencia la columna D. Será la columna número 1 de la cuadrícula. Lo conseguimos usando una referencia mixta a la columna D (el símbolo dólar delante de la primera D).

Aplicamos y extendemos la selección al resto de columnas y conseguimos este resultado:

Ya tenemos un número para cada columna la tabla. Al extender la selección hacia abajo, aparece el valor de la columna a la que pertenece cada celda:

Paso 4: conseguimos el número de fila en la tabla

La función FILAS nos devuelve el número de filas de una matriz o de una referencia.

FILAS(matriz)

El número de fila nos servirá para recorrer la tabla en sentido horizontal. Igual que para las columnas, nos situamos en la celda D3 y escribimos esat función:

Queremos saber el número de fila de la primera celda de la cuadrícula (D3), por lo tanto la matriz que necesitamos para usar la función FILAS será exclusivamente la propia celda D3. Cuando extendamos la selección al resto de columnas y filas, Excel tomará como referencia la fila 3. Será lafila número 1 de la cuadrícula. Lo conseguimos usando una referencia mixta a la fila 3 (el símbolo dólar delante del primer 3).

Aplicamos y extendemos la selección al resto de columnas y conseguimos:

Ya tenemos un número para cada fila de la tabla. Cuando extendemos la selección hacia abajo, aparece el valor de cada fila en las celdas:

Paso 5: conseguimos un único número para cada celda de la tabla

Los resultados de las funciones COLUMNAS y FILAS utilizados por separado son inútiles. Son un par de valores que no identifican de manera única cada celda. Sin embargo, si los sumamos conseguimos un valor más cercano a lo que pretendemos.

*fórmula que suma los indicadores de columna y fila*

La fórmula proporciona estos resultados en las celdas:

*resultados de la fórmula para la primera fila*

Es decir, cada celda es el resultado de esta operación:

Extendemos la selección a toda la cuadrícula y conseguimos:

Fijándonos en los resultados, comprobamos que si restamos uno al valor de la fila, conseguimos la sucesión numérica que queremos para la primera fila: 1, 2, 3, 4. Ajustamos la fórmula restando 1 a la función FILAS.

y conseguimos la sucesión numérica que queremos para la primera fila:

Para el resto de celdas de la tabla conseguimos este resultado:

Tras este ajuste, para el valor de la función FILAS hemos conseguido esta serie numérica: 0, 1, 2 … 13 y 14. Como son cuatro los campos, la serie numérica tiene que ser múltiplo de 4, es decir, tenemos que ajustar de nuevo la fórmula mulitplicando el valor de (FILAS – 1) por 4.

Como el valor de la función COLUMNAS no ha cambiado (1, 2, 3, 4), la fórmula va a operar con estos valores en cada celda:

*origen de los valores ajustados de las celdas*

El resultado es este:

Acabamos de conseguir el valor de fila que necesitamos en cada celda para la función INDICE.

Paso 6: usamos la función INDICE

El primer argumento de la función INDICE (matriz) es la columna de datos como referencia absoluta:

El segundo argumento, número de fila, es el resultado de la fórmula que hemos construido en el paso anterior:

El resultado para la primera fila:

Extendemos la selección a toda la tabla:

*tabla con los valores de la columna de datos*

Hemos transferido correctamente los datos apilados de una columna a una tabla, ordenándolos por campos.

Ene, Vie, 2021

VBA

Formato moneda con VBA

¿Cómo podemos aplicar el formato de moneda a los datos procedentes de nuestro equipo de ventas?

Recibimos los datos de nuestro equipo de ventas y necesitamos darles formato de moneda para nuestros informes.

Automatizar esta tarea con VBA es muy sencillo. Tenemos dos formas posibles de hacerlo: conociendo completamente la referencia del rango, o conociendo parcialmente la referencia del rango.

Conocemos la referencia completa del rango

Conocemos la celda de comienzo y de final del rango. En nuestro ejemplo, el rango de datos que nos interesa es C3:C16, cuyos valores no tienen el formato de moneda.

Con este método usaremos un bucle FOR EACH para dar formato de moneda al rango que contiene los valores.

Declaramos la variable rango de tipo range, para referirnos al rango de celdas, y la variable celda (también de tipo range para poder recorrer las celdas del rango.

Establecemos el rango con la instrucción SET:

Escribimos el bucle FOR EACH

La instrucción FOR EACH … NEXT repite un grupo de instrucciones para cada elemento de una matriz o una colección, es decir, ejecuta un bloque de código en bucle. En nuestro caso, ejecuta una acción en cada celda del rango de las ventas (la matriz); cuando se haya ejecutado el bloque de código en la primera celda, el bucle seguirá con la siguiente celda hasta recorrerlas todas.

Ahora escribimos dentro del bloque FOR EACH, la acción a ejecutar, en este caso dar formato moneda. Se trata de la propiedad NumberFormat del objeto range.

Para conseguir el punto de millar y dos decimales empleamos la máscara: «#,##0.00 €, y por si existieran valores negativos que tuviéramos que resaltar en rojo, añadimos la máscara: [Red]-#,##0.00 €»

Ejecutamos con F5, y este es el resultado:

Este es el código:

Sub Metodo1()
    Dim rango As Range
    Dim celda As Range
    
    Set rango = Range("C3:C16")
    
    For Each celda In rango
        celda.NumberFormat = "#,##0.00 € ;[Red]-#,##0.00 €"
    Next celda    
End Sub

BONUS

¿Y cuando el rango es muy grande y no sabemos cuál es la última celda?

Solo tenemos que definir el rango usando la propiedad End(direction). En este caso, la dirección de la propiedad es xlDown (hacia abajo). Esta propiedad detecta la última celda con valores en esa columna y estira el rango hasta ella.

El resto del código no cambia:

Tenemos una referencia aproximada del rango

Cuando los datos entran en una columna procedentes de una fuente externa, por ejemplo, y no sabemos el número exacto de filas que tendrá el rango, podemos usar otro método para dar formato moneda a los valores de las ventas.

Con la instrucción FOR … NEXT no tenemos que preocuparnos por el número total de celdas del rango. Solo necesitamos saber el número de columna adonde llegan los datos.

En lugar del objeto range, usaremos el objeto cells.

Abrimos el editor de VBA (ALT+F11)

Insertamos un módulo (menú Insertar >> Módulo, y damos un nombre al procedimiento: Metodo2(). Evitamos las tildes para el nombre del método.

Con este método usaremos un bucle FOR … NEXT para dar formato de moneda al rango que contiene los valores.

Tomamos la celda C3 como referencia para el comienzo del rango:

Declaramos una variable integer que actuará como contador del bucle FOR … NEXT: i

Declaramos una variable de tipo long para el número de la última fila del rango: ultimaFila

La variable ultimaFila es el valor de la propiedad row, que nos devuelve un número de fila. En nuestro caso, usamos la propiedad End(xlDown) para indicarle cuál será la última fila del rango.

Con las dos variables tenemos la celda de comienzo y la celda final del rango. Por lo tanto, ya podemos iterar sobre las celdas del rango.

El bucle comenzará en la celda que le indiquemos (i = 3) y recorrerá todas las celdas hasta el final (ultimaFila).

Usamos el objeto cells, para indicarle la fila (que será el valor de i) y la columna que será 3 para todas las celdas (es decir, la columna C).

Ahora escribimos dentro del bloque FOR NEXT, la acción a ejecutar, en este caso dar formato moneda. Ya hemos visto anteriormente que es la propiedad NumberFormat.

Después del signo igual, hemos incluido el guion bajo porque nos permite dividir líneas de código muy largas y así hacerlo más legible.

Presionamos la tecla F5 y vemos el resultado: