«Guía para crear tus propias Funciones en Excel.»
Notablemente el uso de funciones en Excel es uno de sus grandes pilares, no por nada tenemos a nuestra disposición más de 400 funciones, desde matemática y financieras, a estadísticas y de ingeniería, algunas de ellas bastante sencillas e intuitivas como: SUMA, PROMEDIO, MAX, etc. Otras un poco más complejas como: DESREF, SUMAPRODUCTO, TEXTO, etc. Todo ello pone en nuestras manos un innumerable conjunto de alternativas, si además sumamos la potencia del manejo matricial prácticamente tenemos una gran caja de herramientas para atacar los problemas en el día a día.
– ¿Por qué queremos entonces crear nuestras propias funciones?
Es fácil de hecho, porque encontraremos situaciones en las cuales tener funciones más apropiadas agiliza nuestro trabajo, además es imposible abarcar todas las ecuaciones, fórmulas y expresiones matemáticas que existen en las diversas áreas del conocimiento, digamos, por ejemplo: Minería de Datos, Costos de Manufactura, etc.
UDF: Funciones Definidas por el Usuario (User Define Functions) es el nombre que se le da a nuestras propias funciones en Excel.
Veamos entonces una guía de introducción para crear UDF.
.
Procedimientos Function
Recordemos que en VBA tenemos dos «contenedores» para nuestras líneas de código, el procedimiento SUB, aquel que contiene toda la programación cuando utilizamos la grabadora de macros y los procedimientos functions, los cuales nos permitirán hacer nuestras propias funciones en Excel.
Repasemos la anatomía de un procedimiento function.
– Anatomía de un Procedimiento Function
La sintaxis es sencilla, enseguida se describe cada parte:
Veamos cada parte una por una
.
- Public: Esta palabra clave es opcional e indica si el procedimiento es visible para todos los módulos del libro.
- Private: Esta palabra clave es opcional también y cuando se escribe indica que el procedimiento solamente es visible para el módulo actual.
- Static: Otra palabra opcional que indica si el valor en las variables se mantiene entre llamada y llamada del procedimiento
- Function: Esta palabra es obligatoria y define que el procedimiento actual es Function y no Sub.
- Nombre: Es el nombre que le damos a nuestro procedimiento y el cual es una variable que va contener un dato arrojado por la ejecución del procedimiento
- ListaArg: Es la lista de argumentos, una o más variables que se le pasan a la función para su ejecución.
- Tipo: Es el tipo de variable para el nombre.
- Línea de Código: Son las líneas de programación que se ajusten a nuestra necesidad
- Exit Funtion: Esta palabra clave es opcional y la colocamos cuando dada una condición lógica queramos salir si es verdadera y no ejecutar el resto de líneas de código.
- End Function: Obligatorio, ponemos esto al final del procedimiento para indicar que hasta ese punto llega..
.
Acerca de Procedimientos y Variables
Tal vez no estes muy familiarizado con procedimientos, declaración de variables y nombres apropiados para dichas variables, puedes pausar un momento y estudiar el siguiente vídeo: Macros 06: Variables, Constantes y Otros Conceptos Relacionados.
Función UDF: SUMARVISIBLES
¡Vamos directamente a un ejemplo!
Antes que nada, es supremamente vital que la función se cree en su propio módulo para trabaje que correctamente en la hoja de cálculo.
Considere la siguiente tabla:
Una situación frecuente, por ejemplo, es filtrar por departamento y mirar cuánto se está pagando en salarios, este cálculo es útil para usarlo en otras fórmulas; pero veamos que sucede cuando aplicamos la suma al rango de la tabla cuando el filtro por departamento se hace con recursos humanos:
Note como la suma realizada en la celda F4 no coincide con la suma en la barra de estado, esto es así porque la función SUMA tiene en cuenta todas las celdas a pesar de ue algunas esten ocultas, hay soluciones bien conocidas a este problema, por ejemplo: Utilizar la función AGREGAR, utilizar la función SUMAR.SI, etc. Pero qué pasa si esto es una tarea repetitiva, la sumatorio de esos pasos extra nos quitará tiempo y disminuirá nuestra productividad, para solucionar este problema podemos crear la función SUMARVISIBLE.
– Agregar Módulo
Vamos a el menú INSERTAR y pulsamos clic en la opción Módulo
– Renombramos el módulo por algo más descriptivo
Para esto vamos a la venta propiedades teniendo seleccionado el nuevo módulo y cambiamos su nombre, para este caso utilizamos: MSUMARVISIBLES:
– Líneas de Código
Ahora escribimoslas líneas de código que nos permitan solo sumar las celdas visibles en el rango proporcionado por el usuario.(La Imagen tiene comentarios para explicar brevemente las líneas de código)
Observemos la función aplicada en Excel:
El resultado ahora coincide con el de la barra de estado.
Que la UDF se sienta nativa
– Descripción
Para que la función se parezca más a las nativas de Excel, debemos añadirle una descripción y una categoría. Si dejamos la celda activa donde se creó la función SUMARVISIBLES y pulsamos la combinación de teclas Shift + F3, entonces aparece el cuadro de diálogo Argumentos de Funciones, allí podemos ver que no hay ninguna descripción, en su lugar aparece el texto: No hay ayuda disponible.
Para asignar la descripción vamos a la pestaña DESARROLLADOR, grupo CÓDIGO y pulsamos clic en el comando MACRO, allí se despliega el cuadro de diálogo Macro y en la parte superior ponemos en la caja de texto NOMBRE DE LA MACRO ponemos el nombre de nuestro procedimiento function, para este caso: SUMARVISIBLE, luego de ello se habilita el botón OPCIONES, pulsamos clic en él, con lo cual aparece el cuadro de diálogo OPCIONES DE LA MACRO, allí en la caja de text inferior ponemos la descripción y clic en ACEPTAR
– Categoría
Por otra parte, también es familiar para nosotros encontrar las distintas funciones de Excel agrupadas en categorías, como: Matemáticas, Texto, Fecha y hora, etc.
Asignar nuestra función a una de las categorías es simple, solamente use el siguiente procedimiento sub y ejecútelo (En lo posible en su proprio módulo)
Sub AsignarCategoria()
Application.MacroOptions Macro:=»SUMARVISIBLES», Category:=3
End Sub
Todo lo que tenemos que hacer es utilizar el método MacroOptions del objeto Aplications y en su parámetro Macro indicar el nombre de nuestra función entre comillas dobles, mientras que con el parámetro Category indicamos el número de la categoría, para conocer los números y su equivalencia con la categoría vea la tabla siguiente:
Función UDF: SUMARCOLORES
¡Qué tal contar con una función que sume solo aquellas celdas que tenga un color de fondo determinado!
He Aquí la Función
Falta mucha tela por cortar para conocer lo básico de la creación de funciones definidas personalizadas, pero dejaremos hasta aquí este post e iremos viendo poco a poco más tema acerca de los UDF más adelante.
Eso es todo por mí el día de hoy, saludos cordiales.
