Crear funciones personalizadas en Excel

Nota: Nos gustaría proporcionarle el contenido de ayuda actual lo más rápido posible en su idioma. Esta página se ha traducido mediante un sistema automático y es posible que contenga imprecisiones o errores gramaticales. Nuestro objetivo es que este contenido sea útil para usted. ¿Puede informarnos sobre si la información le ha sido útil al final de la página? Aquí tiene el artículo en inglés para que pueda consultarlo fácilmente.

Aunque Excel incluye una gran variedad de funciones de hoja de cálculo integradas, lo más probable es que no tiene una función para cada tipo de cálculo que realiza. Los diseñadores de Excel posiblemente no pudo anticipar las necesidades de cálculo de todos los usuarios. En su lugar, Excel le ofrece la capacidad para crear funciones personalizadas, que se explican en este artículo.

Funciones personalizadas, como las macros, utilizan el lenguaje de programación Visual Basic para aplicaciones (VBA). Se diferencian de las macros de dos maneras significativas. En primer lugar, los procedimientos de la función que utilizar en lugar de los procedimientos Sub . Es decir, comienzan con una instrucción de función en lugar de una instrucción Sub y end con Función final en lugar de End Sub. En segundo término, realizan los cálculos en lugar de llevar a cabo acciones. Funciones personalizadas se excluyen determinados tipos de instrucciones, como las instrucciones que seleccione y rangos, el formato. En este artículo, aprenderá cómo crear y utilizar funciones personalizadas. Para crear las 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 cantidad del 10 por ciento en la venta de un producto, que proporciona el orden es de más de 100 unidades. En los siguientes párrafos, mostraremos una función para calcular este descuento.

El ejemplo siguiente muestra un formulario de pedido que enumera cada elemento, cantidad, precio, descuento (si existe) y el precio total resultante.

Formulario de pedido de ejemplo sin una función personalizada

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

  1. Presione Alt + F11 para abrir el Editor de Visual Basic (en el Mac, pulse FN + ALT + F11 ) y, a continuación, 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 código siguiente al 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

Nota: Para hacer más legible el código, puede usar la tecla Tab para aplicar sangría a líneas. La sangría es sólo informativo y es opcional, como el código se ejecutará con o sin él. Después de escribir una línea con sangría, el Editor de Visual Basic supone que la siguiente línea será del mismo modo con sangría. Para mover (es decir, a la izquierda) una pestaña de caracteres, presione Mayús + Tab.

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

=DISCOUNT(D7,E7)

Excel calcula el descuento del 10 por ciento en 200 unidades en $47.50 por unidad y devuelve $950.00.

En la primera línea del código VBA, función DISCOUNT(quantity, price), indica 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 los dos argumentos. En la fórmula = DISCOUNT(D7,E7), D7 es el argumento 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 muestra a continuación.

Veamos cómo interpreta Excel este procedimiento function. Al presionar ENTRAR, Excel busca el nombre de descuento en el libro actual y encuentra que es una función personalizada en un módulo VBA. Los nombres de los argumentos entre paréntesis, cantidad y precio, son los marcadores de posición para los valores en el que se calcula el descuento.

Formulario de pedido de ejemplo con una función personalizada

La si instrucción en el bloque de código siguiente examina el argumento 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 a 100, VBA ejecuta la instrucción siguiente, que multiplica el valor de cantidad por el valor del precio y, a continuación, multiplica el resultado por 0,1:

Discount = quantity * price * 0.1

El resultado se almacena como la variable de descuento. Una instrucción de VBA que almacena un valor en una variable se denomina una 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 variable de la izquierda. Dado que la variable descuento tiene el mismo nombre que el procedimiento de la función, se devuelve el valor almacenado en la variable a la fórmula de hoja de cálculo que llama la función descuento.

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

Discount = 0

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

Discount = Application.Round(Discount, 2)

VBA no tiene ninguna función redondear, pero sí de Excel. Por lo tanto, para usar redondear en esta declaración, indique a VBA para buscar el método Round (función) en el objeto Application (Excel). Para ello, agregar la palabra de la aplicación antes de la palabra redondear. Utilice la siguiente sintaxis siempre que necesite obtener 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 terminan con una instrucción End Function. Además del nombre de la función, la instrucción de la función 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 utilizan los argumentos.

Después de la instrucción de la función, un procedimiento de función incluye una o más instrucciones de VBA que tomar decisiones y realizan cálculos con los argumentos que se pasa a la función. Por último, en algún lugar en el procedimiento de la función, debe incluir una instrucción que asigna 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 VBA puede usar en funciones personalizadas es menor que el número puede usar en macros. Funciones personalizadas no se permiten hacer algo distinto de retorno un valor en una fórmula en una hoja de cálculo o a una expresión usada en otra función o macros VBA. Por ejemplo, funciones personalizadas no pueden cambiar el tamaño de windows, editar una fórmula en una celda o cambiar la fuente, color o las opciones de trama para el texto en una celda. Si incluye código de este tipo "acción" en un procedimiento function, la función devuelve #VALUE! error.

La una acción que puede realizar un procedimiento de función (aparte de realizar cálculos) se muestra un cuadro de diálogo. Puede usar una instrucción CuadroEntr en una función personalizada como una forma de obtener información de la ejecución de la función de usuario. Puede usar una instrucción CuadroMsj como medio para transmitir información al usuario. También puede usar los cuadros de diálogo personalizados o formularios del usuario, pero es un asunto fuera del alcance de esta introducción.

Macros incluso sencillas y funciones personalizadas pueden ser difíciles de leer. Puede hacerlas más fácil de entender escribiendo texto explicativo en el formulario de comentarios. Agregar comentarios antes del texto explicativo con un apóstrofo. Por ejemplo, en el ejemplo siguiente se muestra la función descuento con comentarios. Agregar comentarios como los siguientes hace que sea más fácil para usted u otros usuarios mantener el código VBA transcurso del tiempo. Si necesita realizar un cambio en el código en el futuro, tendrá un tiempo sea más fácil de entender lo que hizo originalmente.

Ejemplo de una función VBA con comentarios

Un apóstrofo indica a Excel para omitir todo el contenido a la derecha en la misma línea, para que puedan crear comentarios en las líneas por sí mismos o en el lado derecho de las líneas de VBA de código. Puede comenzar un bloque de código con un comentario que explique su finalidad general relativamente largo y luego use los comentarios en línea para instrucciones individuales del documento.

Otra manera de documentar las macros y funciones personalizadas es darles nombre descriptivo. Por ejemplo, en lugar del nombre de una macro de etiquetas, se podría asígnele el nombre MonthLabels para describir específicamente el propósito que sirve la macro. Uso de nombres descriptivos de macros y funciones personalizadas es especialmente útil cuando se ha creado muchos de los procedimientos, especialmente si se crean los procedimientos que tienen propósitos similares pero no idénticos.

Cómo documentar su macros y funciones personalizadas es una cuestión de preferencia personal. ¿Qué es importante es adoptar algún método de documentación y usar de forma uniforme.

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, obtendrá una #NAME? Error al intentar utilizar la función. Si hace referencia a la función en otro libro, debe preceder al nombre de la función con el nombre del libro en el que reside la función. Por ejemplo, si crea una función denominada descuento de un libro denominado Personal.xlsb y llamar a esa función de otro libro, debe escribir =personal.xlsb!discount(), simplemente no =discount().

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

cuadro de diálogo insertar función

Una manera más fácil de hacer sus funciones personalizadas esté disponible en todo momento es almacenarlos en otro libro y guarde el libro como un complemento. A continuación, hacer el complemento disponible siempre que se ejecuta en Excel. Aquí le mostramos cómo hacerlo:

  1. Después de haber creado las funciones que necesita, haga clic en archivo > Guardar como.

    En Excel 2007, haga clic en el Botón de Microsoft Office y haga clic en Guardar como

  2. En el cuadro de diálogo Guardar como, abra la lista desplegable Guardar como tipo y seleccione Complemento de Excel. Guarde el libro en un nombre reconocible, como MyFunctions, en la carpeta complementos . El cuadro de diálogo Guardar como propone esa carpeta, por lo que todo lo que debe hacer es aceptar la ubicación predeterminada.

  3. Después de guardar el libro, haga clic en archivo > Opciones de Excel.

    En Excel 2007, haga clic en el Botón de Microsoft Office y haga clic en Opciones de Excel.

  4. En el cuadro de diálogo Opciones de Excel, haga clic en la categoría Complementos.

  5. En la lista desplegable Administrar, seleccione Complementos de Excel. A continuación, haga clic en el botón Ir.

  6. En el cuadro de diálogo Complementos, active la casilla de verificación junto al nombre que se usó para guardar el libro, tal como se muestra a continuación.

    cuadro de diálogo Complementos

  1. Después de haber creado las funciones que necesita, haga clic en archivo > Guardar como.

  2. En el cuadro de diálogo Guardar como, abra la lista desplegable Guardar como tipo y seleccione Complemento de Excel. Guarde el libro en un nombre reconocible, como MyFunctions.

  3. Después de guardar el libro, haga clic en Herramientas > Complementos de Excel.

  4. En el cuadro de diálogo Complementos, seleccione el botón Examinar para buscar el complemento, haga clic en Abrir y después active la casilla junto al complemento en el cuadro Complementos disponibles.

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

módulo con nombre en vbe

Haga doble clic en módulo en el Explorador de proyectos hace que el Editor de Visual Basic para mostrar el código de la función. Para agregar una nueva función, coloque el punto de inserción después de la instrucción de la función de finalización que finaliza la última función en la ventana de código y empiece a escribir. Puede crear como muchas funciones como necesite en este modo y, a continuación, aparecerán siempre estén disponibles en la categoría definidas por el usuario en el cuadro de diálogo Insertar función.

Este contenido se creó originalmente por Mark Dodge y Craig Stinson como parte de su libro de Microsoft Office Excel 2007 Inside Out. Ya se ha actualizado para aplicar a versiones más recientes de Excel.

¿Necesitas más ayuda?

Siempre puede preguntar a un experto en Excel Tech Community, obtener soporte técnico en la Comunidad de respuestas o sugerir una característica nueva o mejora en el UserVoice de Excel.

Ampliar sus conocimientos de Office
Explorar los cursos
Obtener nuevas características primero
Únase a los participantes de Office Insider

¿Le ha sido útil esta información?

¡Gracias por sus comentarios!

Gracias por sus comentarios. Quizá le interese ponerse en contacto con uno de nuestros agentes de soporte de Office.

×