En la última publicación comencé a tratar el tema de las funciones personalizadas en Excel.
Estuve haciendo una introducción a las funciones personalizadas en VBA, describí su sintaxis hablé de los diferentes tipos de funciones según el número y tipo de argumentos.
En esta publicación abordaré las funciones que devuelven matrices, el ámbito de uso y te mostraré varias formas de usarlas y finalizaré con los errores más habituales al crearlas (y cómo solucionarlos, claro).
¡Comenzamos!
Cómo crear una función personalizada que devuelve una matriz
En la publicación anterior únicamente traté funciones personalizadas que daban como resultado un único valor.
Con VBA también puedes crear funciones personalizadas cuyo resultado es una matriz de valores.
Si no sabes qué es una matriz te invito a hacer clic aquí.
Excel cuenta con algunas funciones matriciales de hoja. Si tu versión de Excel es 365 basta con presionar la tecla Intro para que devuelva el resultado. Si tienes una versión anterior debes usar la combinación de teclas Ctrl + Mayús + Intro. Observa el siguiente código. Se trata de una función que devuelve una matriz de tres números (1, 2 y 3).
Function TresNumeros() As Variant
Dim ValorNumero(1 To 3)
ValorNumero(1) = 1
ValorNumero(2) = 2
ValorNumero(3) = 3
TresNumeros = ValorNumero
End Function
Para que la función pueda devolver una matriz la he declarado como Variant.
La variable FuncionNumero también está declarada como matriz de tres elementos. Los valores 1, 2 y 3 se guardan en los elementos de la matriz y son asignados a la función.
REPITO: según la versión de Excel que uses deberás introducirla presionando Intro (para Excel 365) o Ctrl + Mayús + Intro (para el resto de versiones).
Puedes usar un código similar para almacenar los nombres de los meses del año:
Function Meses() As Variant
Dim Mes(1 To 12)
Mes(1) = "enero"
Mes(2) = "febrero"
Mes(3) = "marzo"
Mes(4) = "abril"
Mes(5) = "mayo"
Mes(6) = "junio"
Mes(7) = "julio"
Mes(8) = "agosto"
Mes(9) = "septiembre"
Mes(10) = "octubre"
Mes(11) = "noviembre"
Mes(12) = "diciembre"
Meses = Mes
End Function
Si introduces en una celda la fórmula =Meses(), Excel devolverá toda la matriz de nombres de meses. En caso de que aparezca solo el mes de enero significa que para introducir la fórmula tienes que presionar las teclas Ctrl + Mayus + Intro.
La fórmula, escrita tal cual, devuelve los nombres de los meses de forma horizontal (en la misma fila). Si buscas mostrarlos de forma vertical (en una sola columna) puedes introducir la fórmula =TRANSPONER(meses()).
El ámbito de las funciones personalizadas
Al igual que las variables y los procedimientos, las funciones personalizadas también cuentan con ámbitos de aplicación (desde dónde se puede llamar a la función):
- El ámbito Público (
Public) significa que podrás usar la función en todas las hojas del libro, así como en todos los procedimientosSubyFunctionde todos los módulos del libro. Esto es útil cuando quieres llamar a una función desde un procedimientoSub(te lo enseño un poco más abajo). - En cambio, en el ámbito Privado (
Private) la función está disponible solo en el módulo en el que se escribe. No puedes usarla en otro módulos ni tampoco aparecerá en la lista de funciones cuando comiences a escribirla en una celda (pero sí puedes usarla en la hoja).
En caso de que no especifiques el ámbito la función se considera pública.
Para establecer una función como privada tienes que incluir la palabra Private delante de la declaración de la función:
Private Function NombreLibro() As String
NombreLibro = ThisWorkbook.Name
End Function
Puedes usar la función anterior en los procedimientos del mismo módulo donde la introduces pero nunca en otros módulos. Pero sí puedes usarla como función de hoja.
El siguiente procedimiento hace que la función sea pública (también puedes omitir la palabra Public):
Public Function NombreLibro() As String
NombreLibro = ThisWorkbook.Name
End Function
Varias formas de usar las funciones personalizadas
Hasta ahora te he hablado de la creación de funciones personalizadas y, aunque ya has visto cómo implementarlas en la hoja (en los ejemplos anteriores y de la publicación anterior ya lo hago), merece la pena tratarlo aparte.
Existen dos formas de utilizar las funciones personalizadas:
Igual que una función de hoja
Es la más sencilla. Lo único que tienes que hacer es introducir el nombre de la función (después del igual) para que aparezca en el listado de funciones disponibles.
ATENCIÓN: Para que la función aparezca en el listado anterior debes establecerla como pública.
Además de directamente en la hoja puedes utilizar el cuadro de diálogo Insertar función para introducir la función. Para esto:
- Haz clic en el botón Insertar función en la ficha Fórmulas.
- En el cuadro de diálogo selecciona la categoría Definida por el usuario.
- Selecciona la función que desees introducir.
- Acepta el cuadro de diálogo. Aparecerá el cuadro Argumentos de función indicando que no hay argumentos que introducir.
- Acepta este otro cuadro y aparecerá el resultado en la celda.
Puedes usar una función personalizada como cualquier otra función en Excel. Esto significa que puedes utilizarla en combinación con otras funciones de hoja de Excel. Por ejemplo, para mostrar en mayúsculas el nombre del libro, tendrás que introducir la siguiente fórmula:
=MAYUSC(nombrelibro())
En procedimientos Sub y otros procedimientos Function
La otra forma que existe de usar una función personalizada es llamarla desde otros procedimientos.
Recuerda que si la función se define como pública podrás utilizarla en cualquier procedimiento de todos los módulos y objetos del proyecto VBA. Si es privada solo podrás usarla en el mismo módulo.
Una vez que has introducido la función personalizada
Function NombreLibro() As String
NombreLibro = ThisWorkbook.Name
End Function
Puedes utilizarla en un procedimiento de la siguiente forma:
Sub MuestraNombreLibro()
MsgBox NombreLibro
End Sub
Al ejecutar el procedimiento anterior se mostrará un cuadro de diálogo con el resultado de la función NombreLibro
Qué hacer cuando falla una función personalizada
Cuando creas una función personalizada siempre esperas que funcione a la primera, ¿verdad? ????????????Pero en algunas ocasiones no es así. Para depurar los posibles errores que se producen puedes usar algunas técnicas:
- Cuadros de mensaje. Haz que se muestre un cuadro de mensaje en la parte del código que desees. De esta forma puedes comprobar si el código se está ejecutando o no (si el flujo de código pasa por el lugar donde has colocado la instrucción
MsgBox).
- Con puntos de ruptura. Puedes establecer un punto de ruptura en la línea que desees. Estos puntos pararán el código (sin finalizar la macro) para que puedas evaluar y comprobar que las variables están tomando los valores adecuados. Un punto de ruptura se crea haciendo en el margen izquierdo de la ventana de código:
- Con la instrucción
Debug.Print. Esta instrucción muestra el valor de las variables que especifiques en la ventana Inmediato.
Cuando ejecutas el siguiente código:
Function ExtraeNumeros(Celda As String) As Long
Dim Longitud As Integer
Longitud = Len(Celda)
For i = 1 To Longitud
Debug.Print i
If IsNumeric(Mid(Celda, i, 1)) Then Resultadoado = Resultadoado & Mid(Celda, i, 1)
Next i
ExtraeNumeros = Resultadoado
End Function
Cada vez que el flujo de código pasa por la instrucción Debug.Print (fíjate que se encuentra en un bucle y por tanto se ejecutará varias veces), en la ventana Inmediato aparecerá el valor de la variable i.
Ventajas e inconvenientes de las funciones personalizadas
Esto de las funciones personalizadas está bien pero no es oro todo lo que reluce. ¿Quieres conocer algunas desventajas con respecto a las funciones de hoja?
- Las funciones personalizadas son mucho más lentas que las funciones de hoja. Por eso te recomiendo que, en la medida de lo posible utilices las funciones ya integradas en Excel.
- Cuando creas un informe usando funciones personalizadas para enviárselo a tu jefe o a un cliente, debes tener en cuenta que si su ordenador no tiene las macros habilitadas aparecerá un mensaje para que las habilite. Eso genera cierta desconfianza. En ocasiones no se habilitan y por tanto las funciones personalizadas no harán su trabajo.
- Cuando utilizas funciones personalizadas estás obligado a guardar el archivo con extensión .xlsm (habilitado para macros). Esta extensión, debido a su peligrosidad por la posible entrada de virus, se encuentra deshabilitada en algunos sistemas y quizá no puedas importar este tipo de archivos en ciertos programas.
Pero tampoco es todo tan malo... ¿cuándo es mejor usar funciones personalizadas en lugar de funciones de hoja?
- Cuando el cálculo es complicado, sobre todo si otras personas deben manipular la fórmula. Las funciones personalizadas reducen la introducción de errores en las fórmulas.
- Cuando debas hacer cálculos complejos que no sea posible realizar con funciones de hoja, por ejemplo la función para extraer los números de una celda de la publicación anterior.
Resumen
Las más de 350 funciones con las que cuenta Excel (llegando a las 450 en algunas versiones) son más que suficientes para obtener el resultado de todos los cálculos que necesites.
Sin embargo cuando debas obtener el resultado de un cálculo realmente complejo o si deseas simplificar una megafórmula para evitar errores puedes crear tus propias funciones personalizadas con VBA.
