Matrices dinámicas y funciones definidas por el usuario (UDF) de VBA

Los suscriptores de Office 365 ahora tienen disponible el nuevo motor de cálculo de Excel, lo que significa que muchos usuarios de repente tienen acceso a matrices dinámicas. Esto me hizo pensar si las funciones definidas por el usuario (UDF) de VBA también pueden hacer uso de la nueva funcionalidad.

No soy un experto en esto; Sólo estoy compartiendo todo lo que he aprendido hasta la fecha. Si tiene un enfoque mejor o diferente, compártalo en los comentarios en la parte inferior.

Si no estás familiarizado con los arreglos dinámicos, te recomiendo leer este post: Arreglos dinámicos en Excel .

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

Nombre del archivo: 0018 Matriz dinámica UDF.zip

Tener acceso

¿Cómo funcionan los cálculos de matrices dinámicas?

Para comprender las matrices dinámicas, primero debemos comprender cómo operan las funciones. Cada argumento de una función acepta diferentes tipos de datos (texto, números, rangos, etc.). Si ponemos texto donde debería haber un número, o un rango donde debería haber texto, encontraremos el #¡VALOR! error.

Los tipos de datos necesarios para cada argumento son visibles en la ventana Argumentos de fórmula.

La captura de pantalla anterior muestra que la función ÍNDICE tiene 3 argumentos, cada uno de los cuales tiene un tipo de datos requerido:

• Matriz : Referencia: puede ser un rango o una constante de matriz.
• Row_num y Column_num : Número: puede ser una celda que contiene un número o una única constante numérica.

Muchas funciones, al igual que INDEX, contienen argumentos que esperan un valor único (este valor se conoce como escalar ) . Si proporcionamos múltiples valores en ese argumento (como rango o constante de matriz), Excel realiza un cálculo para cada elemento en esa matriz/rango (esto se conoce como elevación ). Excel siempre ha funcionado de esa manera, pero solo vimos múltiples resultados si supiéramos usar Ctrl+Shift+Enter. En cambio, las matrices dinámicas muestran todos los resultados calculados distribuyéndolos en celdas a lo largo y debajo.

Veamos dos ejemplos.

Ejemplo 1: matriz no dinámica

La función ÍNDICE estándar calcula un valor único, como se muestra en la celda G4 a continuación. Observe que los argumentos Row_num y Column_num son valores individuales.

Ejemplo 2: matriz dinámica

Ahora demos múltiples valores a Row_num y Column_num como rangos.

Mira la captura de pantalla de arriba. La celda G6 contiene la fórmula, pero dado que Row_num y Column_num son ambos 2 valores, los resultados calculados se insertan en las celdas de arriba y abajo.

Usando UDF

Probemos una función definida por el usuario para ver qué sucede.

La UDF utilizada en el ejemplo calcula el volumen de una esfera. Solo requiere una única entrada, que es el radio.

Función VolumenDeEsfera(Radio Como Doble)VolumenDeEsfera = (4/3) * Aplicación.WorksheetFunction.Pi() * Radio ^ 3Función Final

Cuando se utiliza un valor único, la UDF funciona correctamente. Mire la captura de pantalla a continuación, la celda D2 calcula el volumen usando el radio en la celda A2.

Ahora apliquemos los principios de la matriz dinámica y seleccionemos varias celdas... Dios mío, no funciona. A la UDF se le ha asignado un rango como entrada (celdas A2:A4), pero se calcula como un #¡VALOR! error (celda D4).

Bien, ahora veamos si podemos hacer que esto funcione.

Crear una UDF de matriz dinámica

Para que una UDF derrame resultados en otras celdas, necesita generar una matriz. Además, para aceptar múltiples escalares en un argumento, es necesario permitir matrices de entrada. El tamaño del rango/matriz lo determina el usuario a través de los argumentos, por lo tanto, la opción más sencilla para esto es utilizar el tipo de datos Variante.

Se requiere una comprensión razonable de las matrices VBA para convertir la UDF en una función compatible con matrices dinámicas. Consulte mi publicación sobre matrices VBA para obtener más información.

Aquí está la UDF actualizada. Como el argumento Radio ahora permite múltiples escalares, la función debe poder realizar un cálculo para cada elemento del rango o matriz.

Función VolumeOfSphereDA(Radius As Variant) As VariantDim i As LongDim j As LongDim test2DArray As LongDim tempArray() As VariantDim resultArray As Variant'Resize Radius if 1D arrayIf TypeName(Radius) = "Variant()" Luego, en caso de error, reanudar siguiente test2DArray = UBound (Radio, 2) En caso de error, vaya a 0 Si test2DArray = 0 Entonces ReDim temp(1 a 1, LBound(Radius) a UBound(Radius)) Para j = LBound(temp, 2) a UBound(temp, 2) temp(1 , j) = Radio(j) Siguiente j Radio = temp End IfEnd If'Forzar que resultArray tenga el mismo tamaño que la matriz de entradaresultArray = Radius'Calcular el resultadoFor i = LBound(resultArray) To UBound(resultArray) For j = LBound (resultArray, 2) A UBound(resultArray, 2) resultArray(i, j) = (4/3) * Application.WorksheetFunction.Pi() _ * Radius(i, j) ^ 3 Siguiente jNext iVolumeOfSphereDA = resultArrayEnd Function

Cuando se proporcionan varios escalares a la UDF anterior, los valores se extienden a las celdas de arriba a abajo. Se comporta como deberían hacerlo las matrices dinámicas. Mire la captura de pantalla a continuación, la fórmula en la celda D6 acepta un rango como entrada y divide el resultado en D7 y D8.

La UDF también funcionará con constantes de matriz.

NOTAS:

Todos los rangos se tratan automáticamente como si tuvieran dos dimensiones, incluso si son solo una fila. Por el contrario, las constantes de matriz de una sola fila, como {1,2,3}, siempre se tratan como si tuvieran una dimensión.

Como resultado, la UDF convierte matrices unidimensionales en matrices bidimensionales para evitar errores en el proceso de cálculo.

Gracias a dos lectores habituales; Aryeh Nielsen y David Newell por ayudarme con esto.

Trabajar con muchos escalares múltiples

¿Qué sucede cuando tenemos más de un argumento que acepta escalares? Excel nativo utiliza un concepto conocido como levantamiento por pares . Aquí es donde las filas y columnas coinciden según su posición relativa. Las primeras celdas de cada rango se utilizan para calcular el primer resultado. Las segundas celdas de cada rango se utilizan para calcular el segundo resultado, etc, etc.

Si uno de los argumentos contiene un único escalar, Excel cambia el tamaño del escalar para que sea una matriz con un valor coherente para cada elemento; esto se conoce como radiodifusión . Una vez que se ha completado la transmisión, las matrices tienen el mismo tamaño, por lo que el levantamiento por pares puede continuar con normalidad.

Las UDF no utilizan inherentemente la transmisión o el levantamiento por pares, tenemos que codificar explícitamente de una manera que cree el resultado equivalente. Esto hace que las cosas sean significativamente más complejas. Para entender esto, cambiemos nuestra UDF para calcular el volumen de un cilindro.

La UDF toma dos argumentos, ambos escalares.

• Radio: El radio del cilindro.
• Longitud: La longitud del cilindro.

Función VolumenDeCilindroDA(Radio como variante, Longitud como variante) Como VariantDim i Como LongDim j Como LongDim test2DArray Como LongDim temp Como VariantDim resultadoArray Como VariantDim resultadoArrayFilas Como LongDim resultArrayColumnas Como LongDim radioArray Como VariantDim lengthArray Como Variante'Redimensionar radio si matriz 1DSi TipoNombre(Radio) = "Variant()" Luego, en caso de error, reanudar el siguiente test2DArray = UBound(Radius, 2) En caso de error, Ir a 0 Si test2DArray = 0 Entonces ReDim temp(1 a 1, LBound(Radius) a UBound(Radius)) Para j = LBound( temp, 2) To UBound(temp, 2) temp(1, j) = Radio(j) Siguiente j Radio = temp End IfEnd If'Resize Longitud si matriz 1DSi TypeName(Longitud) = "Variante()" Luego, en caso de error, reanudar Siguiente test2DArray = UBound(Length, 2) En caso de error Ir a 0 Si test2DArray = 0 Entonces ReDim temp(1 a 1, LBound(Length) a UBound(Length)) Para j = LBound(temp, 2) To UBound(temp, 2 ) temp(1, j) = Longitud(j) Siguiente j Longitud = temp End IfEnd If'Convertir rangos a matrices o retener si no es rangeradiusArray = RadiuslengthArray = Longitud'Obtener dimensiones de la matriz/rango más grande, ignorar si no es array/rangeOn Error Reanudar NextIf TypeName(radiusArray) = "Variant()" Entonces si UBound(radiusArray) resultArrayRows Entonces _ resultArrayRows = UBound(radiusArray) If UBound(radiusArray, 2) resultArrayColumns Entonces _ resultArrayColumns = UBound(radiusArray, 2)End IfIf TypeName(lengthArray) ) = "Variant()" Entonces si UBound(lengthArray) resultArrayRows Entonces _ resultArrayRows = UBound(lengthArray) Si UBound(lengthArray, 2) resultArrayColumns Entonces _ resultArrayColumns = UBound(lengthArray, 2)End IfIf resultArrayRows = 0 Entonces resultArrayRows = 1If resultArrayColumns = 0 Entonces resultArrayColumns = 1En caso de error Ir a 0'Forzar que resultArray tenga las dimensiones de las matrices más grandesReDim resultArray(1 To resultArrayRows, 1 To resultArrayColumns)'Transmitir para radioArrayIf TypeName(radiusArray) "Variant()" Luego ReDim temp(1 A resultArrayRows, 1 A resultArrayColumns) Para i = LBound(temp) A UBound(temp) Para j = LBound(temp, 2) A UBound(temp, 2) temp(i, j) = radioArray Siguiente j Siguiente i radioArray = tempEnd If'Broadcast para lengthArrayIf TypeName(lengthArray) "Variant()" Luego ReDim temp(1 A resultArrayRows, 1 A resultArrayColumns) Para i = LBound(temp) A UBound(temp) Para j = LBound(temp, 2) A UBound(temp, 2 ) temp(i, j) = lengthArray Next j Next i lengthArray = tempEnd If'Calcular el resultadoPara i = LBound(resultArray) A UBound(resultArray) Para j = LBound(resultArray, 2) A UBound(resultArray, 2) resultArray( i, j) = Application.WorksheetFunction.Pi() _ * radioArray(i, j) ^ 3 * lengthArray(i, j) Siguiente jNext iVolumeOfCylinderDA = resultArrayEnd Función

La UDF tiene los siguientes pasos:

• Las matrices de radio o longitud unidimensionales cambian de tamaño a matrices de dos dimensiones.
• Los rangos de radio y longitud se convierten en matrices; esto ayuda a simplificar cálculos futuros.
• Cree una matriz para contener el resultado, el tamaño se basa en el máximo de filas/columnas de Radio y Longitud.
• Cuando Radio o Longitud son un escalar único, cree un efecto de transmisión cambiando el tamaño a una matriz de dos dimensiones.
• Para calcular el resultado, el bucle For Each coincide con los valores para crear un efecto de elevación por pares.

En la captura de pantalla siguiente, nuestra UDF ha aplicado una lógica que imita el levantamiento por pares. La primera celda usa 1 y 10 como entradas, la segunda celda usa 2 y 20 como entradas, y así sucesivamente.

Conclusión

Bueno, eso es todo lo que tengo. Con suerte, esto le ayudará a convertir sus UDF en UDF de matriz dinámica.

Si tienes más ideas sobre esto, me encantaría escucharlas.

---