Las funciones personalizadas en VBA parte I

Es difícil saber con exactitud el número de funciones de hoja que tiene Excel. Microsoft está publicando funciones constantemente y su web no está muy actualizada que digamos ????. Además, los usuarios que cuentan con Office 365 pueden disfrutar de algunas funciones más que el resto. Otros usuarios, incluidos en el programa Insiders de Office todavía pueden utilizar más funciones.

Pese a todo esto la mayoría de usuarios de Excel (a partir de la versión Excel 2013) tenemos disponibles más de 350 funciones para solucionar cualquier cálculo que podamos imaginar.

Aun así podrías necesitar funciones adicionales por alguna de estas razones:

• Porque ni combinando las funciones existentes podrías calcular el resultado que deseas.
• Porque deseas poner tu plantilla a disposición de otras personas pero no quieres que se compliquen introduciendo fórmulas complicadas. Prefieres simplificar esta tarea.

Con VBA puedes crear funciones personalizadas (o UDF por sus siglas en inglés) para solucionar cualquiera de estos problemas

Como he comentado, cuando las funciones existentes no son suficiente puedes crear tu propia función para satisfacer cualquier necesidad de cálculo.

Los procedimientos Function

Un procedimiento Function es un código escrito en VBA que realiza cálculos y devuelve un resultado (o una matriz).

Por poner un ejemplo, podrías crear una función personalizada similar a BUSCARV, que en lugar de devolver el primer resultado encontrado, devolviera el segundo o el tercero ¿te parece útil?

Una vez creada la función personalizada se puede usar de tres formas (te lo enseño en la segunda parte del artículo):

1. De la misma manera que una fórmula normal (como SUMA o CONTAR.SI). La función personalizada puede tener argumentos, y el resultado del cálculo se muestra en la celda.
2. Como parte de un procedimiento sub o de otro procedimiento Function.
3. En un formato condicional.

Antes de seguir déjame advertirte de que las funciones personalizadas pueden ser significativamente más lentas que las funciones de hoja. Por tanto recomiendo solo usarlas cuando no se pueda obtener el mismo resultado utilizando funciones de hoja y sin abusar de ellas (es decir debes evitar introducir un gran número de funciones personalizadas en la misma hoja).

Una función de ejemplo

Déjame mostrarte una función personalizada y su funcionamiento. El siguiente código extrae los números existentes en una cadena alfanumérica:

Function ExtraeNumeros(Celda As String) As Long
'Extrae la parte numérica de una cadena alfanumérica
    Dim Longitud As Integer
    Dim Resultado as String
    Longitud = Len(Celda)
    For i = 1 To Longitud
        If IsNumeric(Mid(Celda, i, 1)) Then Resultado = Resultado & Mid(Celda, i, 1)
    Next i
    ExtraeNumeros = Resultado
End Function

Una vez introducido el procedimiento en un módulo podrás usar esta función en el libro de esta forma:

Antes de hablarte del funcionamiento del procedimiento anterior hay algunas cosas que debes saber:

• Cuando creas una función personalizada (UDF), está disponible en todo el libro como cualquier otra función normal.
• Cuando comiences a escribir el nombre de la función después del signo igual, Excel te mostrará el nombre de la función en la lista de funciones encontradas. Fíjate qué ocurre cuando escribas en la hoja =extraen…

Este ejemplo ilustra perfectamente cuándo usar una función personalizada en lugar de una función de hoja.

En Excel puedes hacer un cálculo de mil formas diferentes y en este caso también puedes usar fórmulas de hoja:

• Para Excel 2016 y superiores: =UNIRCADENAS(“”;VERDADERO;SI.ERROR((EXTRAE(A2;FILA(INDIRECTO(“1:”&LARGO(A2)));1)*1);””))
• Para versiones anteriores a Excel 2016: =SI(SUMA(LARGO(A2)-LARGO(SUSTITUIR(A2; {“0″\”1″\”2″\”3″\”4″\”5″\”6″\”7″\”8″\”9”}; “”)))>0; SUMAPRODUCTO(EXTRAE(0&A2; K.ESIMO.MAYOR(INDICE(ESNUMERO(–EXTRAE(A2;FILA(INDIRECTO(“$1:$”&LARGO(A2)));1))* FILA(INDIRECTO(“$1:$”&LARGO(A2)));0); FILA(INDIRECTO(“$1:$”&LARGO(A2))))+1;1) * 10^FILA(INDIRECTO(“$1:$”&LARGO(A2)))/10);””)

Supongo que entiendes que usar la función personalizada EXTRAENUMEROS es más adecuado que cualquiera de las dos anteriores… ????

Partes de una función personalizada

Vamos a ver cómo crear la función personalizada ExtraeNumeros.

Function ExtraeNumeros(Celda As String) As Long

'Extrae la parte numérica de una cadena alfanumérica
    Dim Longitud As Integer
    Dim Resultado As String
    Longitud = Len(Celda)
    For i = 1 To Longitud
        If IsNumeric(Mid(Celda, i, 1)) Then Resultado = Resultado & Mid(Celda, i, 1)
    Next i
    ExtraeNumeros = Resultado
End Function

La palabra Function de la primer línea le indica a VBA que el código es una función. Function va seguida del nombre de la función (ExtraerNumeros). Este es el nombre que se usará en la hoja para usar la función.

• Los nombres de las funciones no pueden tener espacios ni ser iguales a referencias de celdas. Por ejemplo no puedes crear una función con el nombre ABC34524.
• Tampoco puedes nombras a una función como otra existente. Si lo haces, Excel dará preferencia a la función antigua.
• Puedes utilizar el guion bajo (_) si deseas separar palabras. Podrías usar, por ejemplo Extraer_Numeros.

Después del nombre de la función se introducen los argumentos entre paréntesis. Los argumentos son los datos que necesita la función para devolver el resultado y los suele proporcionar el usuario (igual que en las funciones de hoja).

En el ejemplo solo hay un argumento (Celda).

Una buena práctica que te recomiendo es la de especificar el tipo de argumento a introducir en la función. En este ejemplo, como se necesita un valor alfanumérico del que extrae el número he utilizado el tipo String. Si no especificas un tipo de dato, VBA lo considerará como Variant (lo cual significa que puedes usar cualquier tipo de dato). Aquí encontrarás las ventajas de especificar el tipo de dato.

Si tienes más de un argumento puedes especificarlos en el mismo paréntesis separándolos con una coma. Te mostraré ejemplos más adelante.

La segunda línea del código es un comentario (los comentarios comienzan con un apóstrofe). Cuando el flujo del código llega a esta línea la ignora y continúa con la siguiente. Puedes usarla para agregar una descripción sobre lo que hacen las líneas superior, inferior o la propia línea que lo contiene.

'Extrae la parte numérica de una cadena alfanumérica

En la siguiente línea se declara la variable Longitud como Integer (número entero). Esta es la variable donde se almacenará el número de caracteres de la cadena que está siendo analizada por la fórmula.

Dim Longitud As Integer

La cuarta línea declara la variable Resultado como String. En ella se van almacenando los caracteres numéricos.

Dim Resultado As String

En la siguiente línea se asigna la longitud del valor de la variable Celda a la variable Longitud. Se trata del número total de caracteres del valor introducido como argumento en la función (o la celda seleccionada). Esto lo abordaré más adelante.

Longitud = Len(Celda)

A continuación comienza un bucle For-Next. El bucle se ejecuta tantas veces como caracteres tenga el argumento de entrada. Este número viene dado por la función Len (que se extrae de la celda).

For i = 1 To Longitud
    If IsNumeric(Mid(Celda, i, 1)) Then Resultado = Resultado & Mid(Celda, i, 1)
Next i

Por tanto el bucle se ejecuta desde 1 hasta Longitud.

En el interior del bucle la instrucción If analiza cada carácter de la cadena y si es numérico se añade a la variable resultado. Para ello utiliza la función Mid de VBA (correspondiente a la función EXTRAE).

If IsNumeric(Mid(Celda, i, 1)) Then Resultado = Resultado & Mid(Celda, i, 1)

En la penúltima línea se asigna el valor de la variable Resultado a la función. Esta es la línea que se encarga de que la función devuelva el valor de Resultado en la celda.

ExtraeNumeros = Resultado

Para finalizar se escribe la línea obligatoria End Function para indicar que la función termina ahí.

End Function

¿Te ha parecido sencillo o complicado?

El código anterior ha servido para explicar las diferentes partes de la función. Desde aquí, y en la parte II del artículo profundizaré en estos elementos para crear funciones eficientes.

Cómo usar argumentos en las funciones personalizadas

El ejemplo anterior (la función que extraía la parte numérica de una cadena alfanumérica) está diseñado para solicitar un solo argumento (Celda).

En esta sección abordaré las funciones personalizadas según sus argumentos (número de ellos, obligatoriedad, etc.).

Funciones sin argumentos

Estoy seguro de que ya conoces varias funciones de hoja que no precisan argumentos (HOY, ALEATORIO, AHORA, etc).

En VBA también es posible funciones personalizadas sin argumentos. El siguiente código devolverá el nombre del archivo. Como puedes comprobar no necesita ningún argumento, pues para devolver el resultado no necesita información externa.

Function NombreLibro() As String
    NombreLibro = ThisWorkbook.Name
End Function

Este procedimiento especifica el resultado de la función como String (porque devolverá el nombre del archivo, que es una cadena de texto).

La línea principal asigna el valor de ThisWorkbook.Name a la función.

Si el archivo se ha guardado con anterioridad, la función devuelve el nombre con la extensión del libro. En caso contrario solo devuelve el nombre.

Aunque el código anterior funciona bien, tiene un problema. Si cambias el nombre del archivo, no se actualizará automáticamente. Normalmente una función se actualiza cada vez que hay un cambio en sus argumentos. Pero en esta ocasión no hay argumentos y por tanto la función no se recalcula (incluso si cambias el nombre del archivo y lo vuelves a abrir).

Si lo deseas puedes forzar el recálculo de la hoja presionando la tecla F9. Tendrás que hacer esto cada vez que cambies el nombre del archivo. Para evitar el recálculo manual puedes agregar una línea a la función anterior. El siguiente procedimiento hace que la función se recalcule cada vez que haya un cambio en la hoja (como ocurre con las funciones HOY y ALEATORIO).

Function NombreLibro() As String
    Application.Volatile True
    NombreLibro = ThisWorkbook.Name
End Function

Desde ahora si cambias el nombre del libro, la función se actualizará cada vez que haya algún cambio en la hoja o cuando vuelvas a abrir el libro.

Funciones con un argumento

En el primer ejemplo del artículo te mostré la función ExtraerNumeros, que tomaba un solo argumento.

Aquí tienes otra, que puedes usar para convertir a mayúsclas el texto introducido como argumento. Es el equivalente de la función MAYUSC pero en VBA:

Function TransformarMayusculas(Celda As Range)
    TransformarMayusculas = UCase(Celda)
End Function

Esta función usa la función Ucase de VBA para cambiar el valor de la variable Celda. Luego asigna el valor a la función TransformarMayusculas.

Como esta función usa un argumento no es necesario usar la propiedad Application.Volatile. Si el argumento cambia, la función se actualizará automáticamente.

Funciones con varios argumentos

La siguiente función personalizada extrae el texto previo al delimitador especificado. En esta ocasión se necesitan dos argumentos: la referencia de la celda que contiene la cadena de texto y el delimitador que quieres usar:

Function ExtraeIzquierda(Celda As Range, Delimitador As String) As String
    Dim Resultado As String
    Dim PosDelimitador As Integer
    PosDelimitador = InStr(1, Celda, Delimitador, vbBinaryCompare) - 1
    Resultado = Left(Celda, PosDelimitador)
    ExtraeIzquierda = Resultado
End Function

Cuando necesites usar más de un argumento en una función personalizada, puedes declarar todos los argumentos en el mismo par de paréntesis separados por una coma.

Fíjate en que para cada argumento puedes especificar un tipo diferente de datos. En el ejemplo anterior, Celda se ha declarado como Range y Delimitador como String. Como comenté anteriormente puedes omitir los tipos de datos. VBA los tomará como Variant y con ello aumentará la cantidad de memoria utilizada.

Cuando se usa la función en la hoja es necesario introducir la referencia de la celda y el carácter o caracteres que quieres usar como delimitador (entre comillas dobles).

Cuando presionas Intro para introducir la fórmula, VBA comprueba la posición del delimitador con la función InStr. A continuación utiliza esta posición para extrae todos los caracteres a la izquierda del delimitador (usando la función Left).

Por último, asigna el resultado a la función (recuerda que tienes que hacer esto sí o sí).

Pero este procedimiento todavía no está perfecto. Por ejemplo, si introduces un delimitador que no se encuentra en el texto, se mostrará un error. Para evitarlo puedes usar la función SI.ERROR directamente en la hoja o mejorar la función personalizada:

Function ExtraeIzquierda(Celda As Range, Delimitador As String) As String
    Dim Resultado As String
    Dim PosDelimitador As Integer
    PosDelimitador = InStr(1, Celda, Delimitador, vbBinaryCompare) - 1
    If PosDelimitador < 0 Then PosDelimitador = Len(Celda)
    Resultado = Left(Celda, PosDelimitador)
    ExtraeIzquierda = Resultado
End Function

Todavía no funciona bien. Puede optimizarse todavía más. Si introduces el texto directamente en la fórmula obtendrás un error, pues el argumento Celda se ha declarado como Range. También da error cuando quieres utilizar como delimitador el valor de una celda. Si quieres que la función tenga la flexibilidad suficiente como para poder introducir referencias de celda o textos literales te propongo este nuevo código:

Function ExtraeIzquierda(Celda, Delimitador) As String
    Dim Resultado As String
    Dim PosDelimitador As Integer
    PosDelimitador = InStr(1, Celda, Delimitador, vbBinaryCompare) - 1
    If PosDelimitador < 0 Then PosDelimitador = Len(Celda)
    Resultado = Left(Celda, PosDelimitador)
    ExtraeIzquierda = Resultado
End Function

Con esto consigues que la función acepte los dos tipos de datos (String y Range)

Funciones con argumentos opcionales

Estoy seguro de que conoces decenas de funciones de hoja de Excel que tienen uno o varios argumentos opcionales. Sin ir más lejos me viene a la cabeza la función SUMAR.SI, cuyo último argumento (rango_suma) es opcional. Recuerda que los argumentos opcionales se suelen expresar entre corchetes.

Si no se especifica un argumento opcional la función no dará ningún error. Los argumentos opcionales te permiten elegir algunos parámetros.

Por ejemplo, en SUMAR.SI el argumento rango_suma te permite elegir el rango a sumar cuando se cumpla la condición especificado en los argumentos anteriores. Si se omite, la función tomará el rango por defecto (el rango).

En una función personalizada los argumentos opcionales deben ir siempre detrás de todos los obligatorios. No es posible establecerlos al principio.

La siguiente función devolverá la fecha de hoy en el formato dd/mm/aaaa si no se introduce ningún argumento (si lo dejas en blanco) y en formato dd-mm-aaaa si introduces cualquier cosa como argumento (un símbolo, una letra, etc).

Function FechaHoy(Optional Hoy As Variant)
    Dim Result
    If IsMissing(Hoy) Then
        FechaHoy = Format(Date, "dd/mm/yyyy")
    Else
        FechaHoy = Format(Date, "dd-mmmm-yyyy")
    End If
End Function

Observa que el procedimiento anterior usa IsMissing para comprobar si se ha introducido el argumento o no.

La función anterior funciona independientemente de lo que introduzcas como argumento. En el código solo se comprueba si se ha introducido el argumento opcional.

Este código se podría hacer más robusto permitiendo solo valores específicos como argumento. En caso de que no se introduzca el valor adecuado devolvería un error.

Function FechaHoy(Optional Hoy As Variant)
    Dim Result
    If IsMissing(Hoy) Then
        FechaHoy = Format(Date, "dd/mm/yyyy")
    ElseIf Hoy = 1 Then
        FechaHoy = Format(Date, "dd/mm/yyyy")
    Else
        FechaHoy = CVErr(xlErrValue)
    End If
End Function

En la función anterior, puedes dejar el argumento en blanco o introducir un uno. Si escribes cualquier otro valor, se mostrará un error.

Funciones con argumentos de matriz

En los ejemplos anteriores he utilizado argumentos de un solo valor (una celda, un número, un texto, un delimitador…).

También es posible crear funciones cuyos argumentos sean matrices. Una matriz, en este caso, es un rango de celdas. Funciones como SUMA, BUSCARV o SUMAR.SI utilizan matrices como argumentos. La siguiente función personalizada sirve para sumar solo los números pares en el rango que especifiques:

Function SumaPares(Rango As Range)
    Dim Celda As Range
    For Each Celda In Rango
        If IsNumeric(Celda.Value) Then
            If Celda.Value Mod 2 = 0 Then
                Resultado = Resultado + Celda.Value
            End If
        End If
    Next Celda
    SumaPares = Resultado
 End Function

Usa esta función e introduce un rango de celdas con números. El resultado de la función devolverá un valor: la suma de todos los números pares:

En la función anterior, en lugar de un único valor he introducido el rango A1:A10.

Como puedes comprobar, el tipo de datos que he especificado para el argumento es Range.

En el código hay un bucle For Each que recorre cada celda del rango y comprueba si se trata de un número o no. Si no lo es, no ocurre nada y pasa a la siguiente celda. Si es un número comprueba si es par o no (utilizando la función Mod).

Al final se suman todos los números pares y la suma se asigna de nuevo a la función

Funciones con número de argumentos indefinido

Mientras estás creando una función personalizada con vba es posible que no conozcas el número exacto de argumentos a suministrar. Por ejemplo, en la función SUMA puedes definir de uno a 255 argumentos.

En VBA también puedes crear funciones de este tipo, incluso haciendo que los últimos argumentos sean opcionales. Para ello el argumento debe ir precedido de la palabra ParamArray.

ParamArray es un modificador que te permite aceptar tantos argumentos que necesites. Ten en cuenta que el uso de esta palabra antes de un argumento hace que éste sea opcional. En este caso puedes omitir la palabra Optional.

La siguiente función suma todos los argumentos que se especifiquen:

Function SumaArgumentos(ParamArray ListaArgumentos() As Variant)
    For Each arg In ListaArgumentos
        For Each Celda In arg
            SumaArgumentos = SumaArgumentos + Celda
        Next Celda
    Next arg
End Function

En esta función puedes utilizar celdas individuales y matrices de celdas pero no puede sumar valores introducidos a mano. La intención era mostrarte cómo funciona ParamArray para que permitas un número indefinido de argumento.

Resumen

En esta primera parte del artículo te he mostrado qué es una función personalizada, los elementos de una función y he profundizado en los diferentes tipos de argumentos que puedes usar.

Con una función personalizada (o UDF) vas a poder calcular resultados a los que nunca llegarías con funciones de hoja.

Lee la segunda parte del artículo la semana que viene.