VBA: Funciones de la hoja de cálculo en VBA.

Dedicaré hoy unas líneas para aclarar algo más el uso de funciones en nuestras macros. Es sabido por todos nosotros que podemos generar nuestras propias funciones mediante los procedimientos Function, y que estas UDF (funciones personalizadas) pueden emplearse tanto en la hoja de cálculo como en otros procedimientos. Podemos ver un ejemplo de UDF en ver.
Este aspecto, por lo general suele estar bastante claro.

Sobre lo que voy a hablar en esta entrada es del uso de las funciones estándar de la hoja de cálculo en nuestros procedimientos... hablo de las funciones habituales como COINCIDIR, BUSCAR, PAGO, o cualquier otra de listado de más de 350 funciones existentes en Excel. E igualmente diferenciarlas de las funciones específicas de VBA.

Comenzaremos detallando cómo llamar a las funciones de la hoja de cálculo y la ventaja de usar dichas funciones. En Visual Basic las funciones de hoja de calculo de Excel pueden ejecutarse mediante el objeto WorksheetFunction.
Por ejemplo, para obtener el valor máximo de un rango de celdas, declararíamos una variable 'rng' como un objeto Range y, a continuación, lo estableceríamos como el rango A1:B13 de la hoja 'Hoja1'. Asignamos una segunda variable, 'resp', se asigna al resultado de aplicar la función Max a 'rng'.
Por último, el valor de respuesta se muestra en un cuadro de mensaje.

Sub funcionHojaCalculo()
    Dim rng As Range
    'definimos la variable rng como el rango A1:B13
    Set rng = Worksheets("Hoja1").Range("A1:B13")
    'asociamos una segunda variable 'resp'
    'como el valor máximo del rango anterior
    'empleando la función de hoja de cálculo MAX
    resp = Application.WorksheetFunction.Max(rng)
    'mostramos el resultado en un MsgBox
    MsgBox resp
End Sub

Es fácil identificar qué funciones de hoja de cálculo podemos emplear, ya que al escribir el objeto WorksheetFunction. aparecerá una etiqueta con un desplegable de todas las funciones...
Ojo por que como siempre en nuestro editor de VB, el nombre de las funciones aparecerán en su versión en inglés!!!.

Una ventaja de emplear estas funciones es que son fáciles de implementar en nuestros procedimientos, y con ellas evitamos ciertos desarrollos personalizados que sobrecargarían nuestras macros. En general, siempre que sea posible, es más óptimo emplear estas funciones que escribir algún código que realice la misma acción.

Otra manera de trabajar con nuestras funciones de Hoja de cálculo en nuestros procedimientos, es la de Insertar una función de hoja de cálculo en una celda.
Para insertar una función de hoja de cálculo en una celda, especificaremos la función como el valor de la propiedad Formula del objeto Range correspondiente.
Podemos ver un ejemplo en la siguiente entrada.
O el siguiente ejemplo, en el que la función ALEATORIO (que genera un número aleatorio - RAND en inglés) se asigna a la propiedad Formula del rango B1:D5 de la Hoja1 del libro activo.

Sub InsertarFormula()
    'insertamos en un rango de la hoja de cálculo
    'la función ALEATORIO - RAND en inglés
    Worksheets("Hoja1").Range("B1:D5").Formula = "=RAND()"
End Sub

Ojo por que como siempre en nuestro editor de VB, el nombre de las funciones aparecerán en su versión en inglés!!!.
Si queremos emplear la notacion en el lenguaje de nuestro sistema, emplearemos la propiedad .formulalocal en vez de .formula:

Sub InsertarFormula()
    'insertamos en un rango de la hoja de cálculo
    'la función ALEATORIO
    Worksheets("Hoja2").Range("B1:D5").FormulaLocal = "=ALEATORIO()"
End Sub

Nos queda por hablar de las funciones de VBA. Las funciones de Visual Basic no usan el calificador WorksheetFunction. Una peculiaridad de las funciones de VB es que puede tener el mismo nombre que una función de hoja de cálculo y, sin embargo, podrían dar otros resultados...
Veamos un ejemplo sencillo, donde hemos empleado algunas funciones de VBA (Rnd, Int, Abs o Sgn):

Sub FuncionVBA()
Dim aleatorio As Long
'definimos los valores del intervalo
sup = Application.Max(Range("A1:A10"))
inf = Application.Min(Range("A1:A10"))
'producimos un entero aleatorio en el intervalo dado
aleatorio = Int((sup - inf + 1) * Rnd + inf)
'mostramos el aleatorio en un MsgBox
MsgBox "1:= " & aleatorio

valor = Application.Average(sup, inf)
'con Sgn determino signo de la media
'y luego multiplico por la parte entera del valor absoluto dicha media
aleatorio2 = Sgn(valor) * Int(Abs(valor))
MsgBox "2:= " & aleatorio2
End Sub

En general, en este último uso, habrá que tener especial cuidado en diferenciar propiedades de algún objeto, como por ejemplo, Max, Min o Average, de lo que son estrictamente hablando Funciones de VBA; ya que las primeras requieren la definición del objeto (normalmente Application), frente a las funciones que pueden ser usadas directamente.
Un listado (no completo) de las funciones de Visual Basic más empleadas sería:

FUNCIÓN  DESCRIPCIÓN
Abs Devuelve el valor absoluto de un número
Asc Obtiene el valor ASCII del primer caracter de una cadena de texto
CBool Convierte una expresión a su valor booleano
CByte Convierte una expresión al tipo de dato Byte
CCur Convierte una expresión al tipo de dato moneda (Currency)
CDate Convierte una expresión al tipo de dato fecha (Date)
CDbl Convierte una expresión al tipo de dato doble (Double)
CDec Convierte una expresión al tipo de dato decimal (Decimal)
Choose Selecciona un valor de una lista de argumentos
Chr Convierte un valor ANSI en valor de tipo texto
CInt Convierte una expresión en un dato de tipo entero (Integer)
CLng Convierte una expresión en un dato de tipo largo (Long)
CreateObject Crea un objeto de tipo OLE
CStr Convierte una expresión en un dato de tipo texto (String)
CurDir Devuelve la ruta actual
CVar Convierte una expresión en un dato de tipo variant (Variant)
Date Devuelve la fecha actual del sistema
DateAdd Agrega un intervalo de tiempo a una fecha especificada
DateDiff Obtiene la diferencia entre una fecha y un intervalo de tiempo especificado
DatePart Devuelve una parte específica de una fecha
DateSerial Convierte una fecha en un número serial
DateValue Convierte una cadena de texto en una fecha
Day Devuelve el día del mes de una fecha
Dir Devuelve el nombre de un archivo o directorio que concuerde con un patrón
EOF Devuelve verdadero si se ha llegado al final de un archivo
FileDateTime Devuelve la fecha y hora de la última modificación de un archivo
FileLen Devuelve el número de bytes en un archivo
FormatCurrency Devuelve un número como un texto con formato de moneda
FormatPercent Devuelve un número como un texto con formato de porcentaje
Hour Devuelve la hora de un valor de tiempo
IIf Devuelve un de dos partes, dependiendo de la evaluación de una expresión
InputBox Muestra un cuadro de diálogo que solicita la entrada del usuario
InStr Devuelve la posición de una cadena de texto dentro de otra cadena
InStrRev Devuelve la posición de una cadena de texto dentro de otra cadena pero empezando desde el final
Int Devuelve la parte entera de un número
IsDate Devuelve verdadero si la variable es una fecha
IsEmpty Devuelve verdadero si la variable está vacía
IsError Devuelve verdadero si la expresión es un valor de error
IsNull Devuelve verdadero si la expresión es un valor nulo
IsNumeric Devuelve verdadero si la variable es un valor numérico
Join Devuelve una cadena de texto creada al unir las cadenas contenidas en un arrreglo
LBound Devuelve un tipo Long que contiene el subíndice más pequeño disponible para la dimensión indicada de una matriz.
LCase Devuelve una cadena convertida en minúsculas
Left Devuelve un número específico de caracteres a la izquierda de una cadena
Len Devuelve la longitud de una cadena (en caracteres)
LTrim Elimina los espacios a la izquierda de una cadena
Mid Extrae un número específico de caracteres de una cadena de texto
Minute Devuelve el minuto de una dato de tiempo
Month Devuelve el mes de una fecha
MsgBox Despliega un cuadro de dialogo con un mensaje especificado
Now Devuelve la fecha y hora actual del sistema
Replace Reemplaza una cadena de texto con otra
Space Devuelve una cadena de texto con el número de espacios especidicados
Split Devuelve un arreglo formado for cadenas de texto que formaban una sola cadena
Str Devuelve la representación en texto de un número
Right Devuelve un número especificado de carecteres a la derecha de una cadena de texto
Rnd Devuelve un número aleatorio entre 0 y 1
Round Redondea un número a una cantidad específica de decimales
RTrim Elimina los espacios en blanco a la derecha de una cadena de texto
Second Devuelve los segundos de un dato de tiempo
StrComp Compara dos cadenas de texto
StrReverse Invierte el orden de los caracteres de una cadena
Time Devuelve el tiempo actual del sistema
Timer Devuelve el número de segundos desde la media noche
TimeValue Convierte una cadena de texto a un númer de serie de tiempo
Trim Elimina los espacios en blanco al inicio y final de una cadena de texto
TypeName Obtiene el nombre del tipo de dato de una variable
UBound Devuelve un tipo Long que contiene el mayor subíndice disponible para la dimensión indicada de una matriz.
UCase Convierte una cadena de texto en mayúsculas
Val Devuelve el número contenido en una cadena de texto
Weekday Devuelve un número que representa un día de la semana
WeekdayName Devuelve el nombre de un día de la semana
Year Obtiene el año de una fecha