INTRODUCCION:
Las funciones son un trozo de código inserto en el programa principal que recibe uno, muchos o ningún valor (parámetros o argumentos) y que a diferencia de las subrutinas devuelven un único valor, por esta razón se debe especificar( aunque no es obligatorio)de qué tipo de dato es dicho valor. Las funciones deben tener un nombre que las identifique y así poder ser llamadas por el programa principal. También nos permiten ampliar el listado de las funciones que ya vienen por defecto en Excel, estas funciones están asociadas a un libro o una hoja de Excel y las denominamos Funciones personalizadas, son muy útiles,entre otras cosas, cuando la fórmula que se requiere para resolver un problema usando las funciones propias de las hojas Excel resulta muy larga y complicada, haciendo casi imposible su comprensión.
Funciones personalizadas:
Empezaremos dando un ejemplo trivial de una función personalizada que podríamos resolver sin recurrir a ellas, este ejemplo es solo para mostrar como funcionan.
Supongamos que queremos multiplicar 2 números enteros (en la figura el entero 12 y el 2)que están en las celdas A1 y C1 y que el resultado se devuelva en la celda B3, que obviamente se resuelve con la fórmula =A1*C1 ubicada en la celda B3, mentalmente podemos decir que el resultado es 24
pero lo haremos con una función definida por nosotros programada con VBA que llamaremos MULTIPLICA()
Esta función ya esta disponible junto con las demás,(solamente en la hoja donde la definimos) como se puede ver en el recuadro rojo, también puede verse que su definición está entre Function y End Function
la usamos poniendo dos números como argumento, tal como puede verse
Ahora daremos un ejemplo de una función definida por el usuario que tiene más utilidad.
La ferretería EL BULON hace el 20% de descuento si las ventas superan las 100 unidades.
Esto se puede resolver con la función SI, como se puede ver en la siguiente figura
Resolveremos el mismo problema definiendo una función, que llamaremos DESCUENTO, con el siguiente código
ya definida la función DESCUENTO() la podemos aplicar
Se ve que el resultado es el mismo, pero si definimos la función DESCUENTO el proceso es mas rápido.
Como se habrá notado en el ejemplo anterior, está permitido omitir el tipo de dato, pero es una buena costumbre ponerlos, ya que nos puede ahorrar muchos problemas principalmente en la depuración del código en programas largos (En otros idiomas, como en C, esto es obligatorio) por surte VBA tiene la posibilidad de forzar el hecho de tener que poner obligatoriamente los tipos de datos con la instrucción Opción Éxplicit al principio de cada módulo y mejor aún, se puede configurar el editor de VBA para que se ponga automáticamente esta instrucción en todos los nuevos módulos, esto se hace yendo a Herramientas->Opciones->Solapa “Editor“ y en ella tildando la casilla “Requerir declaración de variables”, como se puede ver en la siguiente imagen
Ahora daré un ejemplo, que si bien puede resolverse con funciones (mas precisamente funciones matriciales) les puedo asegurar que la solución es muy complicada y es aquí donde se ve la verdadera utilidad de las funciones personalizadas. Se trata de la obtención de las iniciarles de un nombre completo.
Su código es el siguiente:
usaremos la funcion recien definida en la siguiente tabla
Llegando a este punto debemos hacer algunas aclaraciones del código
En primer lugar podemos ver que se usan las funciones de librería de VBA (funciones que ya están definidas), más precisamente las funciones de cadena Len(), Asc() y Mid(). Una cadena es una serie de caracteres tratados como una misma unidad, estos caracteres pueden ser letras, números o caracteres especiales, como pueden ser /, *, &, %, @ y otros muchos incluido el espacio. A todos estos caracteres les corresponde un código numérico llamado ASCII . Estas funciones sirven para el manejo de cadenas, hay otras que iremos analizando cuando sea necesario.
Función Len
Esta función nos devuelve el número de caracteres de una cadena, por lo tanto retorna un número entero y recibe un parámetro que es un tipo de dato string.
Su sintaxis es: Len([cadena de caracteres])
Ejemplo: Si Texto="hola como estas" Len(Texto) devuelve el valor 15.
Función Asc
Con la función Asc podemos obtener el código ASCII de un caracter
Función Mid
Extrae partes de una cadena y recibe 3 parámetros.
Sintaxis: Mid(cadena, inicio, longitud)
El parámetro cadena es la cadena a extraer caracteres.
inicio es el carácter desde donde se comienza la extracción.
longitud es la cantidad de caracteres devueltos a partir del carácter de inicio.
Dicho esto el código se puede entender mas claramente
1 Function INICIALES(Texto As String) As String
2 Dim strLong As Long, i As Long
3 Dim textTemp As String
5 strLong = Len(Texto)
7 For i = 1 To strLong
8 If Asc(Mid(Texto, i, 1)) >= 65 And Asc(Mid(Texto, i, 1)) <= 90 Then
9 textTemp = textTemp & Mid(Texto, i, 1)
9 End If
10 Next i
12 INICIALES = textTemp
14 End Function
Línea 1: se da el nombre a la función (INICIALES) y se define el nombre (Texto) y el tipo del parámetro que recibe.
Líneas 2 y 3: se declaran las variables que se van a usar.
Línea 5: se asigna a la variable strLong la longitud del string Texto, o sea que se cuentan cuantos caracteres hay en la celda que contiene el nombre
Línea 7: se entra en un ciclo for-Next que recorre el string Texto, o sea que comienza desde 1 y termina en strLong.
Línea 8: esta es la línea más importante. ya que en cada ciclo se evalúa cada carácter y determina si es una letra mayúscula (Asc>=65 o Asc<=90).
Línea 9: Si es mayúscula agrega el carácter a la variable strTemp.
Línea 12: Al terminar la rutina, la función INICIALES recibe el valor de strTemp que es donde se guardan las iniciales.
Para comprobar que funciona dejo el código para copiar y pegar
Function INICIALES(Texto As String) As String
Dim strLong As Long, i As Long
Dim textTemp As String
strLong = Len(Texto)
For i = 1 To strLong
If Asc(Mid(Texto, i, 1)) >= 65 And Asc(Mid(Texto, i, 1)) <= 90 Then
textTemp = textTemp & Mid(Texto, i, 1)
End If
Next i
INICIALES = textTemp
End Function
FUNCIONES DE HOJA( NATIVAS):
Para definir funciones personalizadas, a demás de las funciones propias de VBA , se pueden usar las llamadas funciones de hoja ( Work Sheet Functions en ingles) también llamadas funciones nativas, es decir las funciones que normalmente están disponibles en las hojas de Excel, como son , SUMA, BUSCARV, MAX, PROMEDIO, COINCIDIR, por nombras algunas. Para usarlas debemos anteponer al nombre de la función( que debe estar en ingles) la expresión Application.WorkSheetFunction.[nombre de la función](parámetros).Si queremos usar la función BUACARV, la expresión debe ser:
Application.WorkSheetFunction.VLookup(Valor a buscar, Matriz de búsqueda; Columna, Valor lógico)
Alternativamente:
WorkSheetFunction.VLookup() o Function.VLookup()
Como se puede ver los parámetros de la función son los mismos que los de las funciones nativas
Empezaremos dando un ejemplo en el que se usa la función DESREF que en ingles es Match. Como todos sabemos la función BUACARV solo puede traer valores que están en la misma fila del valor buscado pero en columnas a la derecha de este, el problema se podria resolver con un anidamiento de las funciones INDICE y COINCIDIR, nuestro objetivo es definir una función muy parecida a BUSCARV pero que busque hacia la izquierda, a esta función la llamaremos BUSCARVIZQ
Supongamos que tenemos la siguiente tabla
y queremos:
A) Introducir la tabla en una hoja se Excel
B) Que dado el código de producto se devuelva en dos celdas la herramienta y el precio de la misma
C) No emplear anidamiento de funciones
Como vemos el código del artículo se encuentra en la columna central, por lo tanto para responder debemos hacer una búsqueda a la izquierda y otra a la derecha, por lo tanto debemos recurrir a BUSCARV y a una UDF( función definida por el usuario) que como ya dijimos llamaremos BUSCARIZQ que definimos con el siguiente código:
Function BUSCARVIZQ(valor_a_buscar, matriz_de_busqueda As Range, indice_de_Col As Integer, ValorExacto As Boolean)
Dim NrFila As Long
NrFila = Application.WorksheetFunction.Match(valor_a_buscar, matriz_de_busqueda.Resize( , 1), ValorExacto)
BUSCARVIZQ = matriz_de_busqueda(NrFila, 1).Offset(0, indice_de_Col)
End Function
ya estamos en condiciones de itroducir la fórmula BUSCARIZQ con sus parámetros
que ubicamos en la celda D16 y BUSCARV
que ubicamos en la celda D15, haciendo esto podemos reponder al punto B) como puede verse en la figura
marcamos con un recuadro rojo la fórmula y el rango introducido, que como se ve es el mismo para ambas fórmulas, no así la columna que para BUSCARIZQ es -1
EXPLICACION DEL CODIGO
Function BUSCARVIZQ(valor_a_buscar, matriz_de_busqueda As Range, indice_de_Col As Integer, ValorExacto As Boolean)
se da el nombre a la función y los parámetros, proporcionando el tipo de dato. Notar que no se da el tipo de dato del parámetro valor_a_buscar ya que este puede ser una cadena de caracteres, un número o una combinación de ambos. Lo mismo podemos decir del parámetro que devuelve la función
Dim NrFila As Long
Se define la variable NrFila
NrFila = Application.WorksheetFunction.Match(valor_a_buscar, matriz_de_busqueda.Resize( , 1), ValorExacto)
Se asigna a NrFila lo que devuelve la funcion Match (COINCIDIR) donde se redimenciona la matriz_de_busqueda con Recize
matriz_de_busqueda.Resize( , 1)
esto se hace para que la matriz de búsqueda seleccionada, que es rectangular, se transforme en una matriz columna, ya que Match solo busca en rangos columna.
Veamos cono funciona:
Selection.Resize(12,4).Select
si el rango seleccionado inicialmente es C4:E12 , o sea 8 filas y 3 columnas, luego aplicar Resize el Rango queda con 12 filas y 4 columnas
en nuestro caso el rango seleccionado es 11 filas y 2 columnas, como a las filas las queremos dejar igual no se pone nada como primer parámetro y si,ademas, queremos que el rango final sea de una columna se pone( ,1) así, el rango seleccionado queda un rango columna
Se podría haber omitido el Resize seleccionando solo el rango central, esto se hizo para que los parámetros sean iguales a los de la función BUSCADV, salvo por el -1 de indice_de_Col
finalmente el código
BUSCARVIZQ = matriz_de_busqueda(NrFila, 1).Offset(0, indice_de_Col)
asigna a BUSCARVIZQ el valor que está en la celda activa matriz_de_busqueda(NrFila, 1) desplazada una celda a la izquierda(indice_de_Col ). Esto se logra con la PROPIEDAD Offset que seleciona celdas a cierta distancia de la celda activa
por ejemplo:
ActiveCell.Offset(-5,2).Select
selecciona la celda que se encuentra 5 filas hacia arriba y 2 columnas a la derecha de la celda activa.
de otra manera:
Sheets(1).Range("C2").Offset(2,4).Select
a partir de C2 selecciona 2 filas hacia abajo y 4 columnas a la derecha, es decir C2 pasa a ser G4(C2 = G4), tener en cuenta que 2 es fila y 4 columna.
como se ve Offset hace lo mismo que DESREF.
La programación VBA tiene sus propias funciones como Mid y Len, que ya se describieron, estas permiten trabajar con cadenas de caracteres, con números, fechas, horas, mostrar mensajes por pantalla y muchas otras que podemos listar por categorías, como sigue:
1-Funciones de conversión de tipos (Conversion)
2-Funciones Financieras (Financial)
3-Funciones de cadenas (Strings)
4-Funciones matemáticas (Math)
5-Funciones de comprobación (Information)
6-Funciones de Interacción(Interaction)
7-Funciones de Fecha y Hora (DateTime)
8-funciones del sistema (FileSystem)