Creación de una función definida por el usuario (UDF) en Excel VBA (Guía definitiva)

Con VBA, puede crear una función personalizada (también llamada función definida por el usuario) que se puede utilizar en las hojas de trabajo al igual que las funciones normales.

Estos son útiles cuando las funciones existentes de Excel no son suficientes. En tales casos, puede crear su propia función definida por el usuario (UDF) personalizada para satisfacer sus necesidades específicas.

En este tutorial, cubriré todo sobre la creación y el uso de funciones personalizadas en VBA.

Si está interesado en aprender VBA de forma sencilla, consulte mi Capacitación en línea de Excel VBA.

¿Qué es un procedimiento de función en VBA?

Un procedimiento de función es un código VBA que realiza cálculos y devuelve un valor (o una matriz de valores).

Con un procedimiento de función, puede crear una función que pueda usar en la hoja de trabajo (como cualquier función normal de Excel, como SUMA o BUSCARV).

Cuando haya creado un procedimiento de función usando VBA, puede usarlo de tres maneras:

1. Como una fórmula en la hoja de trabajo, donde puede tomar argumentos como entradas y devolver un valor o una matriz de valores.
2. Como parte de su código de subrutina VBA u otro código de función.
3. En formato condicional.

Si bien ya hay más de 450 funciones de Excel incorporadas disponibles en la hoja de trabajo, es posible que necesite una función personalizada si:

• Las funciones incorporadas no pueden hacer lo que quieres hacer. En este caso, puede crear una función personalizada según sus requisitos.
• Las funciones incorporadas pueden hacer el trabajo, pero la fórmula es larga y complicada. En este caso, puede crear una función personalizada que sea fácil de leer y usar.

Tenga en cuenta que las funciones personalizadas creadas con VBA pueden ser significativamente más lentas que las funciones incorporadas. Por lo tanto, estos son los más adecuados para situaciones en las que no puede obtener el resultado utilizando las funciones incorporadas.

Función vs. Subrutina en VBA

Una "subrutina" le permite ejecutar un conjunto de código mientras que una "función" devuelve un valor (o una matriz de valores).

Para darle un ejemplo, si tiene una lista de números (tanto positivos como negativos) y desea identificar los números negativos, esto es lo que puede hacer con una función y una subrutina.

Una subrutina puede recorrer cada celda del rango y resaltar todas las celdas que tienen un valor negativo. En este caso, la subrutina termina cambiando las propiedades del objeto de rango (cambiando el color de las celdas).

Con una función personalizada, puede usarla en una columna separada y puede devolver un VERDADERO si el valor en una celda es negativo y FALSO si es positivo. Con una función, no puede cambiar las propiedades del objeto. Esto significa que no puede cambiar el color de la celda con una función en sí (sin embargo, puede hacerlo usando formato condicional con la función personalizada).

Cuando crea una función definida por el usuario (UDF) usando VBA, puede usar esa función en la hoja de trabajo como cualquier otra función. Cubriré más sobre esto en la sección "Diferentes formas de usar una función definida por el usuario en Excel".

Creación de una función simple definida por el usuario en VBA

Permíteme crear una función simple definida por el usuario en VBA y mostrarte cómo funciona.

El siguiente código crea una función que extraerá las partes numéricas de una cadena alfanumérica.

Función GetNumeric (CellRef As String) as Long Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If IsNumeric (Mid (CellRef, i, 1)) Then Result = Result & Mid (CellRef, i, 1) Siguiente i GetNumeric = Función de fin de resultado

Cuando tenga el código anterior en el módulo, puede usar esta función en el libro de trabajo.

A continuación se muestra cómo esta función: GetNumeric - se puede utilizar en Excel.

Ahora, antes de decirle cómo se crea esta función en VBA y cómo funciona, hay algunas cosas que debe saber:

• Cuando crea una función en VBA, está disponible en todo el libro de trabajo como cualquier otra función regular.
• Cuando escribe el nombre de la función seguido del signo igual a, Excel le mostrará el nombre de la función en la lista de funciones coincidentes. En el ejemplo anterior, cuando ingresé = Obtener, Excel me mostró una lista que tenía mi función personalizada.

Creo que este es un buen ejemplo cuando puede usar VBA para crear una función fácil de usar en Excel. También puede hacer lo mismo con una fórmula (como se muestra en este tutorial), pero eso se vuelve complicado y difícil de entender. Con esta UDF, solo necesita pasar un argumento y obtendrá el resultado.

Anatomía de una función definida por el usuario en VBA

En la sección anterior, le di el código y le mostré cómo funciona la función UDF en una hoja de trabajo.

Ahora, profundicemos y veamos cómo se crea esta función. Debe colocar el siguiente código en un módulo en el Editor de VB. Cubro este tema en la sección "Dónde colocar el código VBA para una función definida por el usuario".

Función GetNumeric (CellRef As String) as Long 'Esta función extrae la parte numérica de la cadena Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If IsNumeric (Mid (CellRef, i, 1)) Then Result = Result & Mid (CellRef, i, 1) Siguiente i GetNumeric = Función de fin de resultado

La primera línea del código comienza con la palabra - Función.

Esta palabra le dice a VBA que nuestro código es una función (y no una subrutina). La palabra Función va seguida del nombre de la función: GetNumeric. Este es el nombre que usaremos en la hoja de trabajo para usar esta función.

• El nombre de la función no puede tener espacios. Además, no puede nombrar una función si entra en conflicto con el nombre de una referencia de celda. Por ejemplo, no puede nombrar la función ABC123 ya que también se refiere a una celda en la hoja de cálculo de Excel.
• No debe dar a su función el mismo nombre que el de una función existente. Si hace esto, Excel le daría preferencia a la función incorporada.
• Puede utilizar un guión bajo si desea separar palabras. Por ejemplo, Get_Numeric es un nombre aceptable.

El nombre de la función va seguido de algunos argumentos entre paréntesis. Estos son los argumentos que nuestra función necesitaría del usuario. Estos son como los argumentos que debemos proporcionar a las funciones integradas de Excel. Por ejemplo, en una función CONTAR.SI, hay dos argumentos (rango y criterio)

Entre paréntesis, debe especificar los argumentos.

En nuestro ejemplo, solo hay un argumento: CellRef.

También es una buena práctica especificar qué tipo de argumento espera la función. En este ejemplo, dado que alimentaremos la función con una referencia de celda, podemos especificar el argumento como un tipo "Rango". Si no especifica un tipo de datos, VBA lo consideraría una variante (lo que significa que puede usar cualquier tipo de datos).

Si tiene más de un argumento, puede especificarlos en el mismo paréntesis, separados por una coma. Veremos más adelante en este tutorial sobre cómo usar múltiples argumentos en una función definida por el usuario.

Tenga en cuenta que la función se especifica como el tipo de datos "Cadena". Esto le diría a VBA que el resultado de la fórmula sería del tipo de datos String.

Si bien puedo usar un tipo de datos numéricos aquí (como Long o Double), hacerlo limitaría el rango de números que puede devolver. Si tengo una cadena larga de 20 números que necesito extraer de la cadena general, declarar la función como Long o Double daría un error (ya que el número estaría fuera de su rango). Por lo tanto, he mantenido el tipo de datos de salida de la función como String.

La segunda línea del código, la que está en verde y comienza con un apóstrofe, es un comentario. Al leer el código, VBA ignora esta línea. Puede usar esto para agregar una descripción o un detalle sobre el código.

La tercera línea del código declara una variable "StringLength" como un tipo de datos Integer. Esta es la variable donde almacenamos el valor de la longitud de la cadena que está siendo analizada por la fórmula.

La cuarta línea declara la variable Result como un tipo de datos String. Esta es la variable donde extraeremos los números de la cadena alfanumérica.

La quinta línea asigna la longitud de la cadena en el argumento de entrada a la variable "StringLength". Tenga en cuenta que "CellRef" se refiere al argumento que dará el usuario mientras usa la fórmula en la hoja de trabajo (o la usa en VBA, que veremos más adelante en este tutorial).

Las líneas sexta, séptima y octava son parte del ciclo For Next. El bucle se ejecuta tantas veces como caracteres haya en el argumento de entrada. Este número lo da la función LEN y se asigna a la variable "StringLength".

Por lo tanto, el ciclo va de "1 a Stringlength".

Dentro del ciclo, la declaración IF analiza cada carácter de la cadena y, si es numérico, agrega ese carácter numérico a la variable de resultado. Utiliza la función MID en VBA para hacer esto.

La penúltima línea del código asigna el valor del resultado a la función. Es esta línea de código la que garantiza que la función devuelva el valor "Resultado" en la celda (desde donde se llama).

La última línea del código es End Function. Esta es una línea de código obligatoria que le dice a VBA que el código de función termina aquí.

El código anterior explica las diferentes partes de una función personalizada típica creada en VBA. En las siguientes secciones, profundizaremos en estos elementos y también veremos las diferentes formas de ejecutar la función VBA en Excel.

Argumentos en una función definida por el usuario en VBA

En los ejemplos anteriores, donde creamos una función definida por el usuario para obtener la parte numérica de una cadena alfanumérica (GetNumeric), la función fue diseñada para tomar un solo argumento.

En esta sección, cubriré cómo crear funciones que no tengan argumentos en comparación con las que tienen múltiples argumentos (tanto obligatorios como opcionales).

Creando una función en VBA sin ningún argumento

En la hoja de cálculo de Excel, tenemos varias funciones que no toman argumentos (como ALEATORIO, HOY, AHORA).

Estas funciones no dependen de ningún argumento de entrada. Por ejemplo, la función HOY devolvería la fecha actual y la función ALEATORIA devolvería un número aleatorio entre 0 y 1.

También puede crear una función similar en VBA.

A continuación se muestra el código que le dará el nombre del archivo. No toma ningún argumento, ya que el resultado que necesita devolver no depende de ningún argumento.

Función WorkbookName () como cadena WorkbookName = ThisWorkbook.Name Fin de la función

El código anterior especifica el resultado de la función como un tipo de datos String (ya que el resultado que queremos es el nombre del archivo, que es una cadena).

Esta función asigna el valor de "ThisWorkbook.Name" a la función, que se devuelve cuando la función se utiliza en la hoja de trabajo.

Si el archivo se ha guardado, devuelve el nombre con la extensión del archivo, de lo contrario, simplemente da el nombre.

Sin embargo, lo anterior tiene un problema.

Si el nombre del archivo cambia, no se actualizará automáticamente. Normalmente, una función se actualiza cada vez que hay un cambio en los argumentos de entrada. Pero como no hay argumentos en esta función, la función no vuelve a calcular (incluso si cambia el nombre del libro de trabajo, ciérrelo y vuelva a abrirlo).

Si lo desea, puede forzar un nuevo cálculo mediante el método abreviado de teclado: Control + Alt + F9.

Para hacer que la fórmula se vuelva a calcular siempre que haya un cambio en la hoja de trabajo, necesita una línea de código.

El siguiente código hace que la función se vuelva a calcular siempre que haya un cambio en la hoja de trabajo (al igual que otras funciones similares de la hoja de trabajo, como la función HOY o RAND).

Función WorkbookName () como cadena Application.Volatile True WorkbookName = ThisWorkbook.Name Fin de la función

Ahora, si cambia el nombre del libro de trabajo, esta función se actualizará siempre que haya algún cambio en la hoja de trabajo o cuando vuelva a abrir este libro.

Creando una función en VBA con un argumento

En una de las secciones anteriores, ya hemos visto cómo crear una función que solo toma un argumento (la función GetNumeric descrita anteriormente).

Creemos otra función simple que solo tome un argumento.

La función creada con el siguiente código convertiría el texto referenciado en mayúsculas. Ahora ya tenemos una función para ello en Excel, y esta función es solo para mostrarle cómo funciona. Si tiene que hacer esto, es mejor usar la función SUPERIOR incorporada.

Función ConvertToUpperCase (CellRef As Range) ConvertToUpperCase = UCase (CellRef) End Function

Esta función usa la función UCase en VBA para cambiar el valor de la variable CellRef. Luego asigna el valor a la función ConvertToUpperCase.

Dado que esta función toma un argumento, no necesitamos usar la parte Application.Volatile aquí. Tan pronto como cambie el argumento, la función se actualizará automáticamente.

Creando una función en VBA con múltiples argumentos

Al igual que las funciones de la hoja de trabajo, puede crear funciones en VBA que toman múltiples argumentos.

El siguiente código crearía una función que extraerá el texto antes del delimitador especificado. Se necesitan dos argumentos: la referencia de celda que tiene la cadena de texto y el delimitador.

Función GetDataBeforeDelimiter (CellRef As Range, Delim As String) como String Dim Resultado como String Dim DelimPosition As Integer DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 Resultado = Left (CellRef, DelimPosition) GetDataBeforeDelimiter = Result End Function

Cuando necesite usar más de un argumento en una función definida por el usuario, puede tener todos los argumentos entre paréntesis, separados por una coma.

Tenga en cuenta que para cada argumento, puede especificar un tipo de datos. En el ejemplo anterior, "CellRef" se ha declarado como un tipo de datos de rango y "Delim" se ha declarado como un tipo de datos de cadena. Si no especifica ningún tipo de datos, VBA considera que se trata de un tipo de datos variante.

Cuando usa la función anterior en la hoja de trabajo, debe proporcionar la referencia de celda que tiene el texto como primer argumento y los caracteres delimitadores entre comillas dobles como segundo argumento.

Luego verifica la posición del delimitador usando la función INSTR en VBA. Esta posición luego se usa para extraer todos los caracteres antes del delimitador (usando la función IZQUIERDA).

Finalmente, asigna el resultado a la función.

Esta fórmula está lejos de ser perfecta. Por ejemplo, si ingresa un delimitador que no se encuentra en el texto, daría un error. Ahora puede usar la función IFERROR en la hoja de trabajo para deshacerse de los errores, o puede usar el siguiente código que devuelve el texto completo cuando no puede encontrar el delimitador.

Función GetDataBeforeDelimiter (CellRef As Range, Delim As String) como String Dim Resultado como String Dim DelimPosition As Integer DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 Si DelimPosition <0 Then DelimPosition = Len (CellRef) Resultado = Izquierda ( CellRef, DelimPosition) GetDataBeforeDelimiter = Función de fin de resultado

Podemos optimizar aún más esta función.

Si ingresa el texto (del cual desea extraer la parte antes del delimitador) directamente en la función, le dará un error. Adelante… ¡pruébalo!

Esto sucede porque hemos especificado "CellRef" como un tipo de datos de rango.

O, si desea que el delimitador esté en una celda y use la referencia de celda en lugar de codificarla en la fórmula, no puede hacerlo con el código anterior. Es porque Delim se ha declarado como un tipo de datos de cadena.

Si desea que la función tenga la flexibilidad de aceptar la entrada de texto directa o las referencias de celda del usuario, debe eliminar la declaración del tipo de datos. Esto terminaría haciendo que el argumento sea un tipo de datos variante, que puede tomar cualquier tipo de argumento y procesarlo.

El siguiente código haría esto:

Función GetDataBeforeDelimiter (CellRef, Delim) Como cadena Dim Resultado como cadena Dim DelimPosition Como entero DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 Si DelimPosition <0 Then DelimPosition = Len (CellRef) Resultado = Izquierda (CellRef, DelimPosition) GetDataBeforeDelimiter = Función de fin de resultado

Crear una función en VBA con argumentos opcionales

Hay muchas funciones en Excel donde algunos de los argumentos son opcionales.

Por ejemplo, la función legendaria BUSCARV tiene 3 argumentos obligatorios y un argumento opcional.

Un argumento opcional, como sugiere el nombre, es opcional para especificar. Si no especifica uno de los argumentos obligatorios, su función le dará un error, pero si no especifica el argumento opcional, su función funcionará.

Pero los argumentos opcionales no son inútiles. Le permiten elegir entre una variedad de opciones.

Por ejemplo, en la función BUSCARV, si no especifica el cuarto argumento, BUSCARV realiza una búsqueda aproximada y si especifica el último argumento como FALSO (o 0), entonces hace una coincidencia exacta.

Recuerde que los argumentos opcionales siempre deben ir después de todos los argumentos requeridos. No puede tener argumentos opcionales al principio.

Ahora veamos cómo crear una función en VBA con argumentos opcionales.

Función con solo un argumento opcional

Hasta donde yo sé, no hay una función incorporada que solo tome argumentos opcionales (puedo estar equivocado aquí, pero no puedo pensar en ninguna función de ese tipo).

Pero podemos crear uno con VBA.

A continuación se muestra el código de la función que le dará la fecha actual en el formato dd-mm-aaaa si no ingresa ningún argumento (es decir, déjelo en blanco), y en formato "dd mmmm, aaaa" si ingresa algo como argumento (es decir, cualquier cosa para que el argumento no esté en blanco).

Función CurrDate (Opcional fmt como variante) Dim Result If IsMissing (fmt) Then CurrDate = Format (Date, "dd-mm-aaaa") Else CurrDate = Format (Date, "dd mmmm, aaaa") End If End Función

Tenga en cuenta que la función anterior utiliza "IsMissing" para comprobar si falta el argumento o no. Para usar la función IsMissing, su argumento opcional debe ser del tipo de datos variante.

La función anterior funciona sin importar lo que ingrese como argumento. En el código, solo verificamos si se proporciona el argumento opcional o no.

Puede hacer esto más robusto tomando solo valores específicos como argumentos y mostrando un error en el resto de los casos (como se muestra en el siguiente código).

Función CurrDate (Opcional fmt como variante) Dim Result If IsMissing (fmt) Then CurrDate = Format (Date, "dd-mm-aaaa") ElseIf fmt = 1 Then CurrDate = Format (Date, "dd mmmm, aaaa") Else CurrDate = CVErr (xlErrValue) End If End Function

El código anterior crea una función que muestra la fecha en formato "dd-mm-aaaa" si no se proporciona ningún argumento, y en formato "dd mmmm, aaaa" cuando el argumento es 1. Da un error en todos los demás casos.

Función con argumentos obligatorios y opcionales

Ya hemos visto un código que extrae la parte numérica de una cadena.

Ahora echemos un vistazo a un ejemplo similar que toma tanto argumentos obligatorios como opcionales.

El siguiente código crea una función que extrae la parte de texto de una cadena. Si el argumento opcional es VERDADERO, da el resultado en mayúsculas, y si el argumento opcional es FALSO o se omite, da el resultado como está.

Función GetText (CellRef como rango, TextCase opcional = False) Como cadena Dim StringLength como entero Dim Resultado como cadena StringLength = Len (CellRef) For i = 1 To StringLength If Not (IsNumeric (Mid (CellRef, i, 1))) Entonces Result = Result & Mid (CellRef, i, 1) Siguiente i Si TextCase = True Entonces Result = UCase (Result) GetText = Result End Función

Tenga en cuenta que en el código anterior, hemos inicializado el valor de "TextCase" como False (mire entre paréntesis en la primera línea).

Al hacer esto, nos aseguramos de que el argumento opcional comience con el valor predeterminado, que es FALSO. Si el usuario especifica el valor como VERDADERO, la función devuelve el texto en mayúsculas, y si el usuario especifica el argumento opcional como FALSO o lo omite, el texto devuelto es como está.

Creación de una función en VBA con una matriz como argumento

Hasta ahora, hemos visto ejemplos de creación de una función con argumentos Opcionales / Requeridos, donde estos argumentos eran un solo valor.

También puede crear una función que pueda tomar una matriz como argumento. En las funciones de la hoja de cálculo de Excel, hay muchas funciones que toman argumentos de matriz, como SUM, VLOOKUP, SUMIF, COUNTIF, etc.

A continuación se muestra el código que crea una función que da la suma de todos los números pares en el rango especificado de celdas.

Función AddEven (CellRef as Range) Atenuar celda como rango para cada celda en CellRef If IsNumeric (Cell.Value) Then If Cell.Value Mod 2 = 0 Then Result = Result + Cell.Value End If End If Next Cell AddEven = Result End Función

Puede usar esta función en la hoja de trabajo y proporcionar el rango de celdas que tienen los números como argumento. La función devolvería un solo valor: la suma de todos los números pares (como se muestra a continuación).

En la función anterior, en lugar de un solo valor, hemos proporcionado una matriz (A1: A10). Para que esto funcione, debe asegurarse de que el tipo de datos del argumento pueda aceptar una matriz.

En el código anterior, especifiqué el argumento CellRef como Range (que puede tomar una matriz como entrada). También puede utilizar el tipo de datos de variante aquí.

En el código, hay un bucle For Each que pasa por cada celda y verifica si es un número de no. Si no es así, no pasa nada y pasa a la siguiente celda. Si es un número, verifica si es par o no (usando la función MOD).

Al final, se suman todos los números pares y la suma se vuelve a asignar a la función.

Crear una función con un número indefinido de argumentos

Al crear algunas funciones en VBA, es posible que no sepa la cantidad exacta de argumentos que un usuario desea proporcionar. Entonces, la necesidad es crear una función que pueda aceptar tantos argumentos como se proporcionen y usarlos para devolver el resultado.

Un ejemplo de dicha función de hoja de trabajo es la función SUMA. Puede proporcionarle múltiples argumentos (como este):

= SUMA (A1, A2: A4, B1: B20)

La función anterior agregaría los valores en todos estos argumentos. Además, tenga en cuenta que pueden ser una sola celda o una matriz de celdas.

Puede crear una función de este tipo en VBA si tiene el último argumento (o podría ser el único argumento) como opcional. Además, este argumento opcional debe estar precedido por la palabra clave "ParamArray".

"ParamArray" es un modificador que le permite aceptar tantos argumentos como desee. Tenga en cuenta que el uso de la palabra ParamArray antes de un argumento hace que el argumento sea opcional. Sin embargo, no es necesario que utilice la palabra Opcional aquí.

Ahora creemos una función que pueda aceptar un número arbitrario de argumentos y sumaría todos los números en los argumentos especificados:

Función AddArguments (ParamArray arglist () como variante) Para cada argumento en arglist AddArguments = AddArguments + arg Siguiente arg End Función

La función anterior puede tomar cualquier número de argumentos y agregar estos argumentos para dar el resultado.

Tenga en cuenta que solo puede utilizar un valor único, una referencia de celda, un valor booleano o una expresión como argumento. No puede proporcionar una matriz como argumento. Por ejemplo, si uno de sus argumentos es D8: D10, esta fórmula le daría un error.

Si desea poder usar ambos argumentos de varias celdas, debe usar el siguiente código:

Función AddArguments (ParamArray arglist () como variante) Para cada arg en arglist Para cada celda en arg AddArguments = AddArguments + Cell Siguiente celda Siguiente arg End Función

Tenga en cuenta que esta fórmula funciona con varias celdas y referencias de matriz; sin embargo, no puede procesar valores o expresiones codificados. Puede crear una función más sólida al verificar y tratar estas condiciones, pero esa no es la intención aquí.

La intención aquí es mostrarle cómo funciona ParamArray para que pueda permitir un número indefinido de argumentos en la función. En caso de que desee una función mejor que la creada por el código anterior, use la función SUMA en la hoja de trabajo.

Creación de una función que devuelve una matriz

Hasta ahora hemos visto funciones que devuelven un solo valor.

Con VBA, puede crear una función que devuelva una variante que puede contener una matriz completa de valores.

Las fórmulas de matriz también están disponibles como funciones integradas en las hojas de cálculo de Excel. Si está familiarizado con las fórmulas de matriz en Excel, sabrá que estas se ingresan usando Control + Shift + Enter (en lugar de solo Enter). Puede leer más sobre fórmulas de matriz aquí. Si no conoce las fórmulas de matriz, no se preocupe, siga leyendo.

Creemos una fórmula que devuelva una matriz de tres números (1,2,3).

El siguiente código haría esto.

Función ThreeNumbers () como variante Dim NumberValue (1 a 3) NumberValue (1) = 1 NumberValue (2) = 2 NumberValue (3) = 3 ThreeNumbers = NumberValue Función final

En el código anterior, hemos especificado la función "Tres números" como una variante. Esto le permite contener una matriz de valores.

La variable "NumberValue" se declara como una matriz con 3 elementos. Contiene los tres valores y lo asigna a la función "Tres números".

Puede usar esta función en la hoja de trabajo ingresando la función y presionando la tecla Control + Shift + Enter (mantenga presionadas las teclas Control y Shift y luego presione Enter).

Cuando haga esto, devolverá 1 en la celda, pero en realidad, contiene los tres valores. Para verificar esto, use la siguiente fórmula:

= MAX (tres números ())

Utilice la función anterior con Control + Shift + Enter. Notará que el resultado ahora es 3, ya que son los valores más grandes en la matriz devuelta por la función Max, que obtiene los tres números como resultado de nuestra función definida por el usuario: ThreeNumbers.

Puede usar la misma técnica para crear una función que devuelva una matriz de nombres de meses como se muestra en el siguiente código:

Función Meses () como variante Dim MonthName (1 a 12) MonthName (1) = "Enero" MonthName (2) = "Febrero" MonthName (3) = "Marzo" MonthName (4) = "Abril" MonthName (5) = "May" MonthName (6) = "junio" MonthName (7) = "julio" MonthName (8) = "agosto" MonthName (9) = "septiembre" MonthName (10) = "octubre" MonthName (11) = "noviembre "MonthName (12) =" December "Months = Función de fin de MonthName

Ahora, cuando ingrese la función = Meses () en la hoja de cálculo de Excel y use Control + Shift + Enter, devolverá la matriz completa de nombres de meses. Tenga en cuenta que solo ve enero en la celda, ya que ese es el primer valor de la matriz. Esto no significa que la matriz solo devuelva un valor.

Para mostrarle el hecho de que está devolviendo todos los valores, haga esto: seleccione la celda con la fórmula, vaya a la barra de fórmulas, seleccione la fórmula completa y presione F9. Esto le mostrará todos los valores que devuelve la función.

Puede usar esto usando la fórmula de ÍNDICE a continuación para obtener una lista de todos los nombres de los meses de una sola vez.

= ÍNDICE (meses (), FILA ())

Ahora bien, si tiene muchos valores, no es una buena práctica asignar estos valores uno por uno (como hicimos anteriormente). En su lugar, puede usar la función Array en VBA.

Por lo tanto, el mismo código en el que creamos la función "Meses" se acortaría, como se muestra a continuación:

Meses de función () como meses variantes = Matriz ("enero", "febrero", "marzo", "abril", "mayo", "junio", _ "julio", "agosto", "septiembre", "octubre" , "Noviembre", "diciembre") Finalizar función

La función anterior usa la función Array para asignar los valores directamente a la función.

Tenga en cuenta que todas las funciones creadas anteriormente devuelven una matriz horizontal de valores. Esto significa que si selecciona 12 celdas horizontales (digamos A1: L1) e ingresa la fórmula = Meses () en la celda A1, le dará todos los nombres de los meses.

Pero, ¿qué pasa si desea estos valores en un rango vertical de celdas?

Puede hacer esto usando la fórmula TRANSPONER en la hoja de trabajo.

Simplemente seleccione 12 celdas verticales (contiguas) e ingrese la siguiente fórmula.

Comprensión del alcance de una función definida por el usuario en Excel

Una función puede tener dos ámbitos: Público o Privado.

• A Ámbito público significa que la función está disponible para todas las hojas del libro de trabajo, así como para todos los procedimientos (Sub y Función) en todos los módulos del libro de trabajo. Esto es útil cuando desea llamar a una función desde una subrutina (veremos cómo se hace en la siguiente sección).
• A Ámbito privado significa que la función está disponible solo en el módulo en el que existe. No puede usarlo en otros módulos. Tampoco lo verá en la lista de funciones en la hoja de trabajo. Por ejemplo, si el nombre de su función es "Meses ()" y escribe función en Excel (después del signo =), no le mostrará el nombre de la función. Sin embargo, aún puede usarlo si ingresa el nombre de la fórmula.

Si no especifica nada, la función es una función pública de forma predeterminada.

A continuación se muestra una función que es una función privada:

Función privada WorkbookName () como cadena WorkbookName = ThisWorkbook.Name Fin de la función

Puede usar esta función en las subrutinas y los procedimientos en los mismos módulos, pero no puede usarla en otros módulos. Esta función tampoco aparecería en la hoja de trabajo.

El siguiente código haría pública esta función. También aparecerá en la hoja de trabajo.

Función WorkbookName () como cadena WorkbookName = ThisWorkbook.Name Fin de la función

Diferentes formas de usar una función definida por el usuario en Excel

Una vez que haya creado una función definida por el usuario en VBA, puede usarla de muchas formas diferentes.

Primero veamos cómo usar las funciones en la hoja de trabajo.

Usar UDF en hojas de trabajo

Ya hemos visto ejemplos del uso de una función creada en VBA en la hoja de trabajo.

Todo lo que necesita hacer es ingresar el nombre de la función y aparecerá en intellisense.

Tenga en cuenta que para que la función aparezca en la hoja de trabajo, debe ser una función pública (como se explica en la sección anterior).

También puede utilizar el cuadro de diálogo Insertar función para insertar la función definida por el usuario (siguiendo los pasos a continuación). Esto funcionaría solo para funciones que son públicas.

• Vaya a la pestaña Datos.
• Haga clic en la opción "Insertar función".
• En el cuadro de diálogo Insertar función, seleccione Definido por el usuario como categoría. Esta opción aparece solo cuando tiene una función en el Editor de VB (y la función es Pública).
• Seleccione la función de la lista de todas las funciones públicas definidas por el usuario.
• Haga clic en el botón Aceptar.

Los pasos anteriores insertarían la función en la hoja de trabajo. También muestra un cuadro de diálogo Argumentos de función que le brindará detalles sobre los argumentos y el resultado.

Puede utilizar una función definida por el usuario como cualquier otra función en Excel. Esto también significa que puede usarlo con otras funciones integradas de Excel. Por ejemplo. la siguiente fórmula daría el nombre del libro en mayúsculas:

= SUPERIOR (Nombre del libro de trabajo ())

Uso de funciones definidas por el usuario en procedimientos y funciones de VBA

Cuando haya creado una función, también podrá utilizarla en otros subprocedimientos.

Si la función es Pública, se puede utilizar en cualquier procedimiento en el mismo módulo o en uno diferente. Si es privado, solo se puede usar en el mismo módulo.

A continuación se muestra una función que devuelve el nombre del libro de trabajo.

Función WorkbookName () como cadena WorkbookName = ThisWorkbook.Name Fin de la función

El siguiente procedimiento llama a la función y luego muestra el nombre en un cuadro de mensaje.

Sub ShowWorkbookName () MsgBox WorkbookName End Sub

También puede llamar a una función desde otra función.

En los códigos siguientes, el primer código devuelve el nombre del libro de trabajo y el segundo devuelve el nombre en mayúsculas llamando a la primera función.

Función WorkbookName () como cadena WorkbookName = ThisWorkbook.Name Fin de la función

Función WorkbookNameinUpper () WorkbookNameinUpper = UCase (WorkbookName) End Function

Llamar a una función definida por el usuario desde otros libros de trabajo

Si tiene una función en un libro de trabajo, también puede llamar a esta función en otros libros de trabajo.

Hay varias formas de hacer esto:

1. Crear un complemento
2. Función de guardado en el libro de macros personal
3. Hacer referencia a la función de otro libro de trabajo.

Crear un complemento

Al crear e instalar un complemento, tendrá la función personalizada disponible en todos los libros de trabajo.

Suponga que ha creado una función personalizada: "GetNumeric" y la desea en todos los libros de trabajo. Para hacer esto, cree un nuevo libro de trabajo y tenga el código de función en un módulo en este nuevo libro de trabajo.

Ahora siga los pasos a continuación para guardarlo como un complemento y luego instálelo en Excel.

• Vaya a la pestaña Archivo y haga clic en Guardar como.
• En el cuadro de diálogo Guardar como, cambie el tipo "Guardar como" a .xlam. El nombre que asigne al archivo sería el nombre de su complemento. En este ejemplo, el archivo se guarda con el nombre GetNumeric.
  • Notarás que la ruta del archivo donde se guarda cambia automáticamente. Puede usar el predeterminado o cambiarlo si lo desea.
• Abra un nuevo libro de Excel y vaya a la pestaña Desarrollador.
• Haga clic en la opción Complementos de Excel.
• En el cuadro de diálogo Complementos, busque y busque el archivo que guardó y haga clic en Aceptar.

Ahora se ha activado el complemento.

Ahora puede utilizar la función personalizada en todos los libros de trabajo.

Guardar la función en el libro de macros personal

Un libro de macros personal es un libro de trabajo oculto en su sistema que se abre cada vez que abre la aplicación Excel.

Es un lugar donde puede almacenar códigos de macro y luego acceder a estas macros desde cualquier libro de trabajo. Es un gran lugar para almacenar las macros que desea utilizar con frecuencia.

De forma predeterminada, no hay un libro de macros personal en su Excel. Debe crearlo grabando una macro y guardándola en el libro de trabajo de macros personales.

Puede encontrar los pasos detallados sobre cómo crear y guardar macros en el libro de trabajo de macros personal aquí.

Hacer referencia a la función de otro libro

Si bien los dos primeros métodos (crear un complemento y usar un libro de macro personal) funcionarían en todas las situaciones, si desea hacer referencia a la función desde otro libro, ese libro debe estar abierto.

Suponga que tiene un libro de trabajo con el nombre "Libro de trabajo con fórmula ", y tiene la función con el nombre "GetNumeric ’.

Para usar esta función en otro libro de trabajo (mientras que el Libro de trabajo con fórmula está abierto), puede utilizar la siguiente fórmula:

= 'Libro de trabajo con fórmula'! GetNumeric (A1)

La fórmula anterior utilizará la función definida por el usuario en el Libro de trabajo con fórmula archivo y darle el resultado.

Tenga en cuenta que, dado que el nombre del libro de trabajo tiene espacios, debe encerrarlo entre comillas simples.

Uso de la declaración de función de salida VBA

Si desea salir de una función mientras el código se está ejecutando, puede hacerlo mediante la instrucción "Exit Function".

El siguiente código extraería los primeros tres caracteres numéricos de una cadena de texto alfanumérico. Tan pronto como obtiene los tres caracteres, la función finaliza y devuelve el resultado.

Función GetNumericFirstThree (CellRef como rango) Tan largo Dim StringLength como entero StringLength = Len (CellRef) For i = 1 To StringLength If J = 3 Then Salir de la función If IsNumeric (Mid (CellRef, i, 1)) Entonces J = J + 1 Result = Result & Mid (CellRef, i, 1) GetNumericFirstThree = Result End If Next i End Función

La función anterior verifica el número de caracteres que son numéricos, y cuando obtiene 3 caracteres numéricos, sale de la función en el siguiente ciclo.

Depurar una función definida por el usuario

Hay algunas técnicas que puede usar mientras depura una función definida por el usuario en VBA:

Depurar una función personalizada mediante el cuadro de mensaje

Utilice la función MsgBox para mostrar un cuadro de mensaje con un valor específico.

El valor que muestra puede basarse en lo que desea probar. Por ejemplo, si desea verificar si el código se está ejecutando o no, cualquier mensaje funcionaría, y si desea verificar si los bucles están funcionando o no, puede mostrar un valor específico o el contador de bucles.

Depurar una función personalizada estableciendo el punto de interrupción

Establezca un punto de interrupción para poder pasar por cada línea de una en una. Para establecer un punto de interrupción, seleccione la línea donde lo desee y presione F9, o haga clic en el área vertical gris que queda a las líneas de código. Cualquiera de estos métodos insertaría un punto de interrupción (verá un punto rojo en el área gris).

Una vez que haya establecido el punto de interrupción y ejecute la función, va hasta la línea del punto de interrupción y luego se detiene. Ahora puede recorrer el código con la tecla F8. Al presionar F8 una vez, se pasa a la siguiente línea del código.

Depurar una función personalizada mediante Debug.Print en el código

Puede usar la declaración Debug.Print en su código para obtener los valores de las variables / argumentos especificados en la ventana inmediata.

Por ejemplo, en el siguiente código, he usado Debug.Print para obtener el valor de dos variables: "j" y "Resultado"

Función GetNumericFirstThree (CellRef como rango) Tan largo Dim StringLength como entero StringLength = Len (CellRef) For i = 1 To StringLength If J = 3 Then Salir de la función If IsNumeric (Mid (CellRef, i, 1)) Entonces J = J + 1 Result = Result & Mid (CellRef, i, 1) Debug.Print J, Result GetNumericFirstThree = Result End If Next i End Function

Cuando se ejecuta este código, muestra lo siguiente en la ventana inmediata.

Funciones integradas de Excel vs. Función definida por el usuario de VBA

Existen pocos beneficios importantes de usar funciones integradas de Excel sobre las funciones personalizadas creadas en VBA.

• Las funciones integradas son mucho más rápidas que las funciones de VBA.
• Cuando crea un informe / panel con las funciones de VBA y lo envía a un cliente / colega, este no tendrá que preocuparse por si las macros están habilitadas o no. En algunos casos, los clientes / clientes se asustan al ver una advertencia en la barra amarilla (que simplemente les pide que habiliten las macros).
• Con las funciones integradas de Excel, no necesita preocuparse por las extensiones de archivo. Si tiene macros o funciones definidas por el usuario en el libro de trabajo, debe guardarlo en .xlsm.

Si bien existen muchas razones de peso para utilizar las funciones integradas de Excel, en algunos casos es mejor utilizar una función definida por el usuario.

• Es mejor utilizar una función definida por el usuario si su fórmula incorporada es enorme y complicada. Esto se vuelve aún más relevante cuando necesita que otra persona actualice las fórmulas. Por ejemplo, si tiene una fórmula enorme compuesta por muchas funciones diferentes, incluso cambiar una referencia a una celda puede ser tedioso y propenso a errores. En su lugar, puede crear una función personalizada que solo tome uno o dos argumentos y haga todo el trabajo pesado del backend.
• Cuando tiene que hacer algo que no se puede hacer con las funciones integradas de Excel. Un ejemplo de esto puede ser cuando desee extraer todos los caracteres numéricos de una cadena. En tales casos, el beneficio de utilizar una función definida por el usuario gar supera sus aspectos negativos.

Dónde colocar el código VBA para una función definida por el usuario

Al crear una función personalizada, debe colocar el código en la ventana de código del libro de trabajo en el que desea la función.

A continuación se muestran los pasos para colocar el código de la función "GetNumeric" en el libro de trabajo.

1. Vaya a la pestaña Desarrollador.
2. Haga clic en la opción de Visual Basic. Esto abrirá el editor de VB en el backend.
3. En el panel Explorador de proyectos en el Editor de VB, haga clic con el botón derecho en cualquier objeto del libro en el que desea insertar el código. Si no ve el Explorador de proyectos, vaya a la pestaña Ver y haga clic en Explorador de proyectos.
4. Vaya a Insertar y haga clic en Módulo. Esto insertará un objeto de módulo para su libro de trabajo.
5. Copie y pegue el código en la ventana del módulo.

También le pueden gustar los siguientes tutoriales de Excel VBA:

• Trabajar con celdas y rangos en Excel VBA.
• Trabajar con hojas de trabajo en Excel VBA.
• Trabajar con libros de trabajo usando VBA.
• Cómo usar Loops en Excel VBA.
• Eventos de Excel VBA: una guía fácil (y completa)
• Uso de declaraciones IF Then Else en VBA.
• Cómo grabar una macro en Excel.
• Cómo ejecutar una macro en Excel.
• Cómo ordenar datos en Excel usando VBA (una guía paso a paso).
• Función Excel VBA InStr: explicada con ejemplos.