Crear funciones personalizadas en Excel

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. En primer lugar, usan procedimientos de función en lugar de sub procedimientos. 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 realizar acciones. Ciertos tipos de instrucciones, como las instrucciones que seleccionan y formatea rangos, se excluyen de las funciones personalizadas. En este artículo, aprenderá a crear y usar funciones personalizadas. Para crear funciones y macros, trabaje con la Editor de Visual Basic (VBE), que se abre en una nueva ventana independiente de Excel.

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

En el ejemplo siguiente se muestra un formulario de pedido en el que se muestra cada artículo, cantidad, precio, descuento (si procede) y el precio total resultante.

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

1. Presione Alt+F11 para abrir la Editor de Visual Basic (en Mac, presione FN+ALT+F11) y, después, haga clic en Insertar > módulo. Aparece una nueva ventana de módulo en la parte derecha de la Editor de Visual Basic.

2. Copie y pegue el código siguiente 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
   

Ahora ya está listo para usar la nueva función DESCUENTO. Cierre la 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 de su código VBA, Descuento de función(cantidad, precio), indicó que la función DESCUENTO requiere dos argumentos, cantidad y precio. Al llamar a la función en una celda de la hoja de cálculo, debe incluir esos dos argumentos. En la fórmula =DESCUENTO(D7,E7), D7 es el argumento cantidad y E7 es el argumento precio . Ahora puede copiar la fórmula DESCUENTO a 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 busca que es una función personalizada en un módulo de VBA. Los nombres de argumento 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 bloque de código siguiente examina el argumento cantidad y determina si el número de artículos vendidos es mayor o igual que 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 la 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 de VBA que almacena un valor en una variable se denomina instrucción de asignación , ya que evalúa la expresión en el lado derecho del signo igual y asigna el resultado al nombre de la variable a la izquierda. Dado que la variable Descuento tiene el mismo nombre que el procedimiento de función, el valor almacenado en la variable se devuelve a la fórmula de la hoja de cálculo que llamó la función DESCUENTO.

Si la cantidad es menor que 100, VBA ejecuta la siguiente instrucción:

Discount = 0

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

Discount = Application.Round(Discount, 2)

VBA no tiene la función REDONDEAR, pero Excel sí. Por lo tanto, para usar REDONDEAR en esta instrucción, indique 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 Redondear. Use esta sintaxis siempre que necesite tener acceso a una función de Excel desde un módulo de VBA.

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

Después de la instrucción Function, un procedimiento de función incluye una o más instrucciones de VBA que toman decisiones y realizan cálculos con los argumentos pasados a la función. Por último, en algún lugar del procedimiento de función, 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.

El número de palabras clave de VBA que puede usar en las funciones personalizadas es menor que el número que puede usar en las macros. Las funciones personalizadas no pueden hacer nada más que devolver un valor a una fórmula de 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 en una celda o cambiar las opciones de fuente, color o patrón 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! #VALOR!.

La única acción que puede realizar 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 de obtener información del usuario que ejecuta la función. Puede usar una instrucción MsgBox como medio para transmitir información al usuario. También puede usar cuadros de diálogo personalizados o formularios del usuario, pero ese tema está más allá del ámbito de esta introducción.

Incluso las macros sencillas y las funciones personalizadas pueden ser difíciles de leer. Puede facilitar su comprensión escribiendo texto explicativo en forma de comentarios. Agregue comentarios precediendo el texto explicativo con un apóstrofo. Por ejemplo, en el ejemplo siguiente se muestra la función DESCUENTO con comentarios. Agregar comentarios como estos facilita que usted u otros usuarios mantengan el código VBA a medida que pasa el tiempo. Si necesita realizar un cambio en el código en el futuro, le será más fácil comprender lo que hizo originalmente.

Un apóstrofo indica a Excel que ignore todo a la derecha en la misma línea, por lo que puede crear comentarios solos en línea o en el lado derecho de las 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 las funciones personalizadas es darles nombres descriptivos. Por ejemplo, en lugar de asignar un nombre a una macro Labels, podría denominarla 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 se han creado muchos procedimientos, especialmente si se crean procedimientos con fines similares pero no idénticos.

La manera 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.

Para usar una función personalizada, el libro que contiene el módulo en el que creó la función debe estar abierto. Si el libro no está abierto, ¿obtiene un #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 se encuentra la función. Por ejemplo, si crea una función denominada DESCUENTO en un libro llamado Personal.xlsb y llama a esa función desde otro libro, debe escribir =personal.xlsb!discount(), no simplemente =discount().

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

Una forma más sencilla de hacer que sus funciones personalizadas estén disponibles en todo momento es almacenarlas en un libro separado y, a continuación, guardar ese libro como un complemento. A continuación, 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 a la biblioteca de funciones, vuelva a la Editor de Visual Basic. Si busca en visual Basic Editor Explorador de proyectos 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 en la ventana Código y empiece a escribir. Puede crear todas las funciones que necesite de esta manera y siempre estarán disponibles en la categoría Definida por el usuario del cuadro de diálogo Insertar función .

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