Trabajando con Funciones de la Hoja de Cálculo Excel

Una función es un bloque de código que ejecuta una tarea y devuelve un solo valor. Existen tres tipos de función:

1. Funciones VBA
2. Funciones de la Hoja de Cálculo Excel
3. Funciones definidas por el Usuario (UDF)

Una Función VBA es una función suministrada por VBA.

Una Función de Excel es una fórmula que ya ha sido predefinida dentro de Excel para su uso en las hojas de cálculo. Pueden ser utilizadas para realizar una infinidad de operaciones, tales como sumar números, contar, obtener promedios, etc. Existen más de 300 funciones de Excel.

Las Funciones definidas por el usuario trabajan de igual forma que las funciones de Excel, pero son creadas por el usuario. (Etheridge, 2007)

La propiedad WorksheetFuntion es utilizada para insertar una función de Excel en un procedimiento VBA.

La propiedad WorksheetFunction está disponible a través del objeto Application, así que para accesar una función de Excel se debe colocar:

Application.WorksheetFunction.NombreFunción(Argumentos). También puede omitirse el objeto Application de la expresión, de forma que las siguientes dos expresiones son válidas.

Suma = Application.WorksheetFunction.Sum(Range(“B2:B8”))

Suma = WorksheetFunction.Sum(Range(“B2:B8”))

Por lo general si VBA tiene una función que es equivalente a una Función en Excel, no se podrá utilizar la función de Excel, sino la función VBA.

En Excel y VBA existe la función Log, y aunque se escriben igual no son equivalentes, la función Log de Excel calcula el logaritmo decimal y la función Log de VBA calcula el logaritmo natural. Así que a pesar de que ambas funciones se escriben igual no son equivalente, por lo que puede utilizarse la función Log de Excel dentro de un procedimiento VBA.

La función de Excel Ln también calcula el logaritmo natural de un número, al igual que la función de VBA Log, sin embargo la función de Ln es permitida.

 

A continuación se muestran los resultados de los procedimientos utilizando las funciones de Excel Log y Ln, y la función de VBA Log. Todas estas variantes son admitidas en procedimientos. En VBA no existe una función que calcule el logaritmo decimal, por lo que la función de Excel Log llena ese vacío.

Aunque la función VBA Log y la función Excel Ln ambas calculan el logaritmo natural, la función Excel Ln es permitida.

En este otro ejemplo se utiliza la función VBA Cos, y al intentar utilizar la función Excel Cos dentro de un procedimiento da error. En este caso ambas funciones tienen igual nombre y son equivalentes.

Adicionalmente en la modalidad de autocompletado (intellisense) cuando tratamos de colocar la función Cos como una propiedad de WorsheetFunction, esta no existe. Aparecen Cosh, Cot y Coth, pero no Cos.

Lo mismo ocurre con la función Sin, no aparece como un elemento de WorksheetFunction. Pero si aparece como una función de VBA.

Al ignorar que la función Cos no existe para worksheetFunction y colocar Cos de todas maneras, genera el error que se muestra a continuación.

Al hacer clic en depurar nos indica cual es la línea de código que está causando el error con un resaltado amarillo.

En conclusión para conocer si una función de Excel es utilizable en VBA lo mejor es utilizar el Examinador de Objetos y ver si en la lista de miembros de la propiedad worksheetFunction está incluida la función que deseamos utilizar. También podemos utilizar la facilidad que proporciona la opción de autocompletado (Intellisense).

El Examinador de Objetos se puede abrir haciendo clic en el icono que aparece en la barra de menú, o utilizando F2.

En el Examinador de Objetos se selecciona la clase worksheetFunction y en el panel de la derecha aparecen los miembros que pertenecen a WorksheetFunction. Si la función aparece listada, puede ser utilizada.

Para trabajar con Intellisense en las opciones de código deben estar seleccionada la opción de Lista de miembros automática

En Herramientas seleccionar Opciones, y luego en la pestaña Editor ir a las opciones de código. Verificar Lista de miembros automática.

Cuando se utiliza intellisense al declarar un objeto o propiedad al colocar el operador punto aparecerá automáticamente un listado de propiedades y métodos aplicables al objeto o propiedad que hemos escrito antes del punto.

Referencias

Etheridge, D. (2007). Excel 2007 Programming, Your visual blueprint for creating interactive spreadsheets. Indiana: Wiley .