Crear funciones personalizadas en Excel

Aunque Excel incluye una multitud de funciones de hoja de cálculo integradas, es muy probable que no tenga una función para cada tipo de cálculo que realice. Los diseñadores de Excel no podían anticipar las necesidades de cálculo de cada usuario. En su lugar, Excel le proporciona la posibilidad de crear funciones personalizadas, que se explican en este artículo.

Crear una función personalizada sencilla

Las funciones personalizadas, como las macros, usan el lenguaje de programación Visual Basic para Aplicaciones (VBA ). Se diferencian de las macros de dos maneras significativas. Primero, usan procedimientos Function en lugar de procedimientos Sub . Es decir, comienzan con una instrucción Function en lugar de una instrucción Sub y terminan con End Function en lugar de End Sub. En segundo lugar, realizan cálculos en lugar de tomar medidas. Ciertos tipos de instrucciones, como las instrucciones que seleccionan y dan formato a rangos, se excluyen de las funciones personalizadas. En este artículo, aprenderá a crear y usar funciones personalizadas. Para crear funciones y macros, se trabaja con el Editor de Visual Basic (VBE), que se abre en una nueva ventana independiente de Excel.

Supongamos que su empresa ofrece un descuento por cantidad del 10 por ciento en la venta de un producto, siempre que el pedido sea de más de 100 unidades. En los párrafos siguientes, demostraremos una función para calcular este descuento.

En el ejemplo siguiente se muestra un formulario de pedido que enumera cada artículo, cantidad, precio, descuento (si lo hay) y el precio extendido resultante.

Para crear una función de DESCUENTO personalizada en este libro, siga estos pasos:

1. Presione Alt+F11 para abrir el Editor de Visual Basic (en un equipo Mac, presione FN+ALT+F11) y luego haga clic en Insertar>módulo. Aparece una nueva ventana de módulo en el lado derecho del Editor de Visual Basic.

2. Copie y pegue el siguiente código en el nuevo módulo.

   Function DISCOUNT(quantity, price)
    If quantity >=100 Then
    DISCOUNT = quantity * price * 0.1
    Else
    DISCOUNT = 0
    End If
   
    DISCOUNT = Application.Round(Discount, 2)
   End Function
   
   

Uso de funciones personalizadas

Ahora estás listo para usar la nueva función DESCUENTO. Cierre el Editor de Visual Basic, seleccione la celda G7 y escriba lo siguiente:

=DESCUENTO(D7,E7)

Excel calcula el descuento del 10 por ciento en 200 unidades a 47,50 $ por unidad y devuelve 950,00 $.

En la primera línea del código de VBA, Función DESCUENTO(cantidad, precio), indicó que la función DESCUENTO requiere dos argumentos, cantidad y precio. Cuando llame a la función en una celda de hoja de cálculo, debe incluir esos dos argumentos. En la fórmula =DESCUENTO(D7,E7), D7 es el argumento de cantidad y E7 es el argumento de precio . Ahora puede copiar la fórmula de DESCUENTO en G8:G13 para obtener los resultados que se muestran a continuación.

Veamos cómo interpreta Excel este procedimiento de función. Al presionar ENTRAR, Excel busca el nombre DESCUENTO en el libro actual y descubre que es una función personalizada de un módulo de VBA. Los nombres de argumento incluidos entre paréntesis, cantidad y precio, son marcadores de posición para los valores en los que se basa el cálculo del descuento.

La instrucción If del siguiente bloque de código examina el argumento de cantidad y determina si el número de artículos vendidos es mayor o igual a 100:

If quantity >= 100 Then
 DISCOUNT = quantity * price * 0.1
Else
 DISCOUNT = 0
End If

Si el número de artículos vendidos es mayor o igual que 100, VBA ejecuta la siguiente instrucción, que multiplica el valor de cantidad por el valor de precio y, a continuación, multiplica el resultado por 0,1:

Discount = quantity * price * 0.1

El resultado se almacena como la variable Discount. Una instrucción VBA que almacena un valor en una variable se denomina instrucción de asignación , porque evalúa la expresión en el lado derecho del signo igual y asigna el resultado al nombre de la variable a la izquierda. Debido a que la variable Discount tiene el mismo nombre que el procedimiento de la función, el valor almacenado en la variable se devuelve a la fórmula de la hoja de cálculo que llamó a la función DISCOUNT.

Si la cantidad es inferior a 100, VBA ejecuta la siguiente instrucción:

Discount = 0

Por último, la instrucción siguiente redondea el valor asignado a la variable Discount a dos decimales:

Discount = Application.Round(Discount, 2)

VBA no tiene función REDONDEAR, pero Excel sí. Por lo tanto, para usar ROUND en esta instrucción, debe indicar a VBA que busque el método Round (función) en el objeto Application (Excel). Para ello, agregue la palabra Aplicación antes de la palabra Ronda. Use esta sintaxis siempre que necesite acceder a una función de Excel desde un módulo de VBA.

Descripción de las reglas de funciones personalizadas

Una función personalizada debe comenzar con una instrucción Function y terminar con una instrucción End Function. Además del nombre de la función, la instrucción Función suele especificar uno o varios argumentos. Sin embargo, puede crear una función sin argumentos. Excel incluye varias funciones integradas (por ejemplo, ALEATORIO y AHORA) que no usan argumentos.

Después de la instrucción Function, un procedimiento de función incluye una o varias instrucciones VBA que toman decisiones y realizan cálculos mediante los argumentos pasados a la función. Finalmente, en algún lugar del procedimiento function, debe incluir una instrucción que asigne un valor a una variable con el mismo nombre que la función. Este valor se devuelve a la fórmula que llama a la función.

Usar palabras clave de VBA en funciones personalizadas

El número de palabras clave de VBA que puede usar en funciones personalizadas es menor que el número que puede usar en macros. Las funciones personalizadas no pueden hacer otra cosa que devolver un valor a una fórmula en una hoja de cálculo o a una expresión usada en otra macro o función de VBA. Por ejemplo, las funciones personalizadas no pueden cambiar el tamaño de las ventanas, editar una fórmula de una celda ni cambiar las opciones de fuente, color o trama del texto de una celda. Si incluye código de "acción" de este tipo en un procedimiento de función, la función devuelve el #VALUE! al escribir la fórmula =SUMA(C2:C3 E4:E6).

La única acción que puede hacer un procedimiento de función (aparte de realizar cálculos) es mostrar un cuadro de diálogo. Puede usar una instrucción InputBox en una función personalizada como medio para obtener información del usuario que ejecuta la función. Puede utilizar una instrucción MsgBox como medio para transmitir información al usuario. También puede usar cuadros de diálogo personalizados o formularios de usuario, pero este es un tema que está más allá del ámbito de esta introducción.

Documentar macros y funciones personalizadas

Incluso las macros sencillas y las funciones personalizadas pueden resultar difíciles de leer. Puedes hacerlos más fáciles de entender escribiendo un texto explicativo en forma de comentarios. Para agregar comentarios, preceda el texto explicativo con un apóstrofe. Por ejemplo, en el ejemplo siguiente se muestra la función DESCUENTO con comentarios. Agregar comentarios como estos hace que sea más fácil para usted u otros usuarios mantener su código VBA a medida que pasa el tiempo. Si necesita realizar un cambio en el código en el futuro, le resultará más fácil comprender lo que hizo originalmente.

Un apóstrofo indica a Excel que ignore todo lo que se encuentra a la derecha en la misma línea, por lo que puede crear comentarios en líneas por sí mismos o en el lado derecho de líneas que contienen código VBA. Puede comenzar un bloque de código relativamente largo con un comentario que explique su propósito general y, a continuación, usar comentarios en línea para documentar instrucciones individuales.

Otra forma de documentar las macros y funciones personalizadas es asignarles nombres descriptivos. Por ejemplo, en lugar de asignar a una macro Etiquetas, podría llamarla MonthLabels para describir más específicamente el propósito de la macro. El uso de nombres descriptivos para macros y funciones personalizadas es especialmente útil cuando ha creado muchos procedimientos, especialmente si crea procedimientos que tienen propósitos similares pero no idénticos.

La forma de documentar las macros y las funciones personalizadas es una cuestión de preferencia personal. Lo importante es adoptar algún método de documentación y usarlo de forma coherente.

Hacer que las funciones personalizadas estén disponibles en cualquier lugar

Para usar una función personalizada, el libro que contiene el módulo en el que creó la función debe estar abierto. Si ese libro no está abierto, ¿obtiene una #NAME? al intentar usar la función. Si hace referencia a la función en un libro diferente, debe preceder el nombre de la función con el nombre del libro en el que reside la función. Por ejemplo, si crea una función llamada DESCUENTO en un libro llamado Personal.xlsb y llama a esa función desde otro libro, debe escribir =personal.xlsb!descuento(), no simplemente =descuento().

Puede ahorrarse algunas pulsaciones de teclas (y posibles errores de escritura) seleccionando sus funciones personalizadas en el cuadro de diálogo Insertar función. Las funciones personalizadas aparecen en la categoría Definida por el usuario:

Una manera más sencilla de hacer que las funciones personalizadas estén disponibles en todo momento es almacenarlas en un libro independiente y guardar ese libro como un complemento. Después, puede hacer que el complemento esté disponible siempre que ejecute Excel. Aquí te explicamos cómo hacerlo:

Después de seguir estos pasos, las funciones personalizadas estarán disponibles cada vez que ejecute Excel. Si desea agregar algo a la biblioteca de funciones, vuelva al Editor de Visual Basic. Si busca en el Explorador de proyectos del Editor de Visual Basic bajo un encabezado de VBAProject, verá un módulo con el nombre del archivo de complemento. El complemento tendrá la extensión .xlam.

Al hacer doble clic en ese módulo en el Explorador de proyectos, el Editor de Visual Basic muestra el código de función. Para agregar una nueva función, coloque el punto de inserción después de la instrucción End Function que termina la última función de la ventana Código y comience a escribir. Puede crear tantas funciones como necesite de esta manera, y siempre estarán disponibles en la categoría Definida por el usuario en el cuadro de diálogo Insertar función .

Sobre los autores

Este contenido fue creado originalmente por Mark Dodge y Craig Stinson como parte de su libro Microsoft Office Excel 2007 Inside Out. Desde entonces, se ha actualizado para aplicarse también a las versiones más recientes de Excel.

¿Necesitas más ayuda?

Siempre puede preguntar a un experto en Excel Tech Community u obtener soporte técnico en Comunidades.