Las funciones personalizadas en VBA parte II

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 procedimientos Sub y Function de todos los módulos del libro. Esto es útil cuando quieres llamar a una función desde un procedimiento Sub (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:

1. Haz clic en el botón Insertar función en la ficha Fórmulas.

2. En el cuadro de diálogo selecciona la categoría Definida por el usuario.

3. Selecciona la función que desees introducir.

4. Acepta el cuadro de diálogo. Aparecerá el cuadro Argumentos de función indicando que no hay argumentos que introducir.

5. 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.