Excel Avanzado

Macros, Vba en Excel y muchos ejemplos de nuestro Curso de Excel Avanzado

Excel Avanzado
Ejemplo Funciones Personalizadas

¿Qué son las funciones definidas por el usuario?

| 7 comentarios

¿Qué son las funciones personalizadas?

Las ‘funciones definidas por el usuario’, conocidas como UDF (User Defined Functions) o como funciones personalizadas permiten al usuario de Excel, a través del uso de macros (Visual Basic for Applications – VBA), crear/implementar funciones personalizadas, distintas, o complementarias a las que están integradas por defecto en Excel (más de 300). Una vez creadas, éstas pueden ser usadas tal como actualmente se usan las funciones como buscarv, suma, etc.

Ejemplo de Función Personalizada en Excel

Las UDF pueden contener operaciones o fórmulas simples a complejas. Aquí un caso creado:

Debido a que la venta de productos de categorías distintas y pesos distintos no puede ser comparable, se crea un “Stat Factor” de conversión, a través del cual las cajas vendidas de un producto (Actual Cases) se convierten a cajas estadísticas (Stat Cases) para una mejor comparabilidad de ventas de una unidad de negocio o país a otro. Así, tenemos la siguiente UDF creada para dos productos (por simplicidad):

Function SC(AC, Producto)
If Producto = "Clorox Lejia Tradicional 2kg" Then
SC = AC * 2
Else
If Producto = "Poett Aroma Bebe 900ml" Then
SC = AC * 0.9
End If
End If
End Function

¿Cómo se utilizan las funciones definidas por el usuario?

Luego de colocar dicha sentencia en VBA, esta función puede ser ubicada así:
imagen1 300x247 ¿Qué son las funciones definidas por el usuario?

imagen21 300x165 ¿Qué son las funciones definidas por el usuario?

(Para una mayor visualización de la imagen, hacer click en la misma)

Las variables son “SC”, “AC” y “Producto” y el resultado final (SC) será mostrado en la celda donde se ejecute la fórmula (función).

Cabe señalar que en la versión Excel 2007 el límite de argumentos dentro de una UDF es de 255, en anteriores versiones es de 30 (si se requiere mantener compatibilidad se usa la opción ParamArray).

Si se desea colocar una descripción al UDF, una vez que se tiene la función, se puede grabar una macros, y en la ventana para poner el nombre de la macro, se agrega la descripción deseada. Una vez grabada la macros, en el editor de VBA se reemplaza SUB por FUCTION y finalmente resulta en la siguiente sentencia:

Function StatCases(AC, Producto)
' Esta UDF es creada para obtener el valor en Stat Cases de una cantidad especifica de actual cases por un producto en especifico.

If Producto = "Clorox Lejia Tradicional 2kg" Then
SC = AC * 2
Else
If Producto = "Poett Aroma Bebe 900ml" Then
SC = AC * 0.9
End If
End If
End Function

imagen3 300x212 ¿Qué son las funciones definidas por el usuario?

(Para una mayor visualización de la imagen, hacer click en la misma)

Otra forma de realizarlo es, en el visor de VBA, poner F2, escoger de la lista despegable la opción VBA Project, y en la sección Miembros de ‘<globales>’ escoger la UDF creada y darle click derecho en Propiedades y colocar el texto deseado en la sección “Descripción”, luego dar Aceptar y Grabar.

imagen4 300x201 ¿Qué son las funciones definidas por el usuario?

imagen4.5 ¿Qué son las funciones definidas por el usuario?

imagen5 ¿Qué son las funciones definidas por el usuario?

(Para una mayor visualización de la imagen, hacer click en la misma)

¿Cómo agregar una función de Excel a una Categoría?

Por otro lado, si se desea agregar la UDF a una categoría en particular definida en Excel se puede colocar lo siguiente en el VBA:

Public Sub Workbook_Open()
Application.MacroOptions Macro:="SC", Description:="Cálculo de Stat Cases", Category:=9
End Sub

El número de las categorías se listan a continuación:
0 Ninguna categoría, aparece solo en Todas
1 Financieras
2 Fecha y hora
3 Matemáticas y trigonométricas
4 Estadísticas
5 Búsqueda y referencia
6 Base de datos
7 Texto
8 Lógicas
9 Información
.. otras…

imagen6 ¿Qué son las funciones definidas por el usuario?

(Para una mayor visualización de la imagen, hacer click en la misma)

¿Cómo crear un complemento de Excel?

Por último, esta UDF también puede ser grabada como un Complemento de Excel  (*.xlam). Esto se logra poniendo Grabar como, se escoge la opción Complemento de Excel  (*.xlam).  Al abrir una nueva hoja de Excel, en las Opciones de Excel – Complementos – Complemento de Excel, se puede dar check al UDF creado, y este automáticamente es cargado en nuestra hoja.

imagen7 300x244 ¿Qué son las funciones definidas por el usuario?

imagen8 ¿Qué son las funciones definidas por el usuario?

(Para una mayor visualización de la imagen, hacer click en la misma)

 

Beneficios de las Funciones Personalizadas:

  1. Crear funciones matemáticas complejas o personalizadas o manipuleo más sencillo de textos.
  2. Simplificar fórmulas que en otros casos hubieren resultado en fórmulas extensas.

Limitaciones de las Funciones Personalizadas:

  1. No pueden ser “grabadas” como se graban las macros.
  2. Estas UDF’s no aparecen en la lista de Macros (Alt+F8), sino de inserción de funciones (botón fx en la barra de fórmulas o Menú Fórmulas – Insertar Función).
  3. Si se llama a otra función o macro desde una UDF, dicha macro está bajo las mismas limitaciones que la UDF.
  4. No se puede colocar un valor en una celda, fuera de la celda (o rango) conteniendo la fórmula; en otras palabras, las UDF’s sólo pueden ser usadas como fórmulas, no como las macros.
  5. Las UDF’s no cambian la estructura de una hoja de Excel o los formatos (color, fuente, etc).
  6. Dificultad en identificar errores (no se puede ir paso a paso como con F8 en una macros).

Elaborado por: Pamela Gonzales

7 comentarios

  1. Una función si se puede depurar, solo hay que poner un break (F9) en la linea donde queramos comenzar a la depuración y luego llamar a la función desde una celda o desde una subrutina, luego podemos presionar F8 para ir depurándola al igual que a una macro.

    Saludos

  2. Son interesante las aplicaciones que se le puede dar, sin embargo, el tema es como escribirlo de manera perfecta. Bueno para eso estamos aquí. Particularmente yo le veo innumerables aplicaciones en mi trabajo, hoy me pase todo el domingo trabajando una base de datos y que trabajo me di haciendo las cosas de forma casi manual.

  3. Intersante la funcion. gracias a tu ejemplo pude entender mas sobre la instrucion if y else que son de bastante uso en la formulacion de UDF

  4. Interesante función. Ahondar más en este tema es importante porque facilitan bastante las especificaciones en el trabajo.

  5. Es importante que el usuario pueda definir sus propias funciones, puesto que en el día a día nos encontramos con diferentes situaciones que hace que personalicemos nuestras propias funciones.

  6. Ustedes que saben, tengo la sigueiente duda: temgo una superformula que es la convinación de 4 formulas de excel, como puedo ver su codigo de programación para juntarlas y darles un nombre, de tal manera que solo escriba el nombre d emi formula y ya se haga el resto sin que se vea tan grande.

  7. Quiero pner la descripción de la UDF, pero no queda grabada. Como hago?
    Gracias, y buen día!
    Alfredo

Deja una respuesta

Los campos requeridos estan marcados con *.