Introducción a las funciones personalizadas en Excel con VB

Introducción a las funciones personalizadas en Excel con VB

Introducción

Programar sobre cualquier lenguaje es fascinante, en la mayoría de ellos, el limite esta más de parte del programador que del lenguaje, no tengo elementos para decir cual es mejor o peor, creo que cada uno tiene diferentes propósitos y que el bueno o malo, es el programador. Aprendí a programar de forma empírica, esto tiene sus pros y sus contras, como desventaja esta que se tarda un poco más de tiempo en aprender y como ventaja esta que te vuelves un muy buen investigador, estas, solo por nombrar algunas, se que tal vez tenga muchas deficiencias y muchos otros "vicios" de los cuales, tal vez, ni cuenta me dé, pero confío en que algún buen programador que tenga más experiencia que yo en la programación, me los hará notar, por lo pronto, es mi deseo que este camino que para mi ha sido de muchos años, sea mas corto y más leve para Ustedes. El Excel, fue de los primeros programas que use y en que empecé con el VBA.

Mi intención no es hacer un manual, sino tan solo una guía, pero mi espíritu pedagógico, además de que mi actividad es favorita es dar clases, empezaré como unos apuntes que se están convirtiendo en un pequeño manual, así que aprovecha, estudia, lee, investiga, lee, practica y lee, lee mucho...

Funciones

Las funciones personalizadas de Excel, trabajan igual que las incorporadas, con la diferencia de que estas las creamos nosotros y nos sirven para obtener valores que no nos devuelven las incorporadas o para unir en una sola, el resultado de varias funciones, su uso y manipulación es muy semejante a las macros, pero como sabes estas nos devuelven valores.

Escribiendo la primera función en Excel

Para obtener el mejor provecho de esta sección, estoy asumiendo que manejas con soltura el uso de las funciones incorporadas de Excel, como SUMA, MAX, BUSCARV, entre otras, recuerda que Excel tiene mas de 300 funciones incorporadas y que también has usado el Editor VBA, sino lo ha hecho, recomiendo leer y conocer la estructura de cualquier función, veamos cual es:

responder de la misma manera que las incorporadas, DEVOLVIENDO VALORES , esto me parece la parte más importante que algunos olvidan, ¿has visto que alguna función de Excel te de algún mensaje?, no, lo que hace, cuando no puede hacer resolver la operación, es darnos un error como

#¡NULO!, #¡DIV/0!, #¡VALOR!, #¡REF!, #¿NOMBRE?, #¡NÚM!, #N/A, pero nada más.

Entonces ya conocemos los siguiente términos, Función y Argumentos, ¿verdad?, otro punto importante es saber que una función puede o no llevar argumentos como la función ALEATORIO y AHORA, una función puede requerir argumentos de un Tipo y devolver valores de otro Tipo, como las funciones:

Función Tipo de ARGUMENTO requerido Tipo de VALOR devuelto

LARGO Texto Numero

VALOR Texto Numero

FILA Rango Numero

ESNUMERO Numero Booleano

Excel, dentro de sus formulas, maneja diferentes Tipos de datos, como fechas y números, ¿que otros TIPOS DE DATOS conoces que usen las funciones de Excel?, ¿que otros ejemplos de funciones, podrían dar, que devuelvan un tipo de dato diferente del de su o sus argumentos?.

Como habrás notado, el nombre es importante, el nombre de la función nos sirve para en primera instancia sepamos que hace o que valor o valores nos devuelve, entonces, una parte importante es saber QUE HARA NUESTRA FUNCION es decir, que trabajo realizara, cual es su finalidad.

Resumiendo:

• Conocer los términos FUNCION, ARGUMENTO, VALOR DEVUELTO y TIPO DE DATO

• Conocer la estructura de cualquier función

• Conocer que trabajo realiza una función

• TODA función devuelve un valor

• Una función puede o no, tener argumentos

• Una función puede devolver valores de diferente tipo del de sus argumentos

• Los argumentos se separan por "comas" o "punto y coma", depende de??.

Ahora que sabemos lo que necesitamos, hagamos nuestra primera función, como ya dijimos, lo primero es saber, que queremos que haga nuestra función, como ejemplo, haremos la súper difícil formula para calcular el Área de un triángulo:

• Entramos a Excel

• Entramos el Editor VBA

• Insertamos un nuevo Módulo y escribimos lo siguiente:

Option Explicit

Function AreaTriangulo(Base, Altura)

AreaTriangulo = (Base * Altura) / 2

End Function

• Pruébala..., ¿como?, como cualquier función de Excel, que ya lo sabes, si usas el Asistente para funciones, veras una nueva categoría, al final de la lista que se llama, Definidas por el usuario, ahí encontraras nuestra función.

• Ahora, comparemos la formula tradicional con nuestra función:

Bueno, nuestra formula funciona, pero no esta optimizada, es decir, estamos cometiendo omisiones "técnicas", es decir, no estamos escribiendo nuestra función como lo mandan los

"cánones" de la programación y esto, si bien, no es obligatorio, es muy, muy conveniente hacerlo y más aun, saberlo, veamos cuales son esas omisiones...

Si no se especifica otra cosa, toda función es Pública, pero procura hacerlo explícitamente...

Public Function AreaTriangulo(Base, Altura) AreaTriangulo

End Function

No le estamos diciendo de que TIPO son nuestros argumentos, en nuestro ejemplo, nuestra función debe poder manejar números grandes y con decimales, por lo tanto el Tipo de dato más conveniente es el Single (busca en la ayuda, los tipos de datos que se puedes usar en el VBA y me dices cuales son), por lo tanto nuestra función quedaría así:

Public Function AreaTriangulo(Base As Single, Altura As Single) AreaTriangulo = (Base * Altura) / 2

End Function

Tampoco le estamos diciendo que tipo de valor es que nos devolverá nuestra función, es muy importante, escoger este tipo de dato muy bien, la recomendación es, debe ser lo suficientemente grande para contener el resultado de nuestra función, en la misma ayuda, encontraras cuando espacio ocupa (en memoria) y que valores pueden contener los diferentes tipos de datos, para nuestro caso, si estamos multiplicando un Single por un Single y aunque estemos dividiendo, el más conveniente es un Double, para que nuestra función quede así:

Public Function AreaTriangulo(Base As Single, Altura As Single) As Double

AreaTriangulo = (Base * Altura) / 2 End Function

Por ultimo, hay que agregarlo otra palabrita, que en otros ejemplos veremos a detalle, por lo pronto, solo te menciono que sirve para que los valores que tengan los argumentos, no se puedan modificar desde nuestra función y en la mayoría de los casos se usa, esta palabrita se llama ByVal y me gustaría que buscaras ayuda de esta palabra, ya sabes donde...

Public Function AreaTriangulo(ByVal Base As Single, ByVal Altura As Single) As Double

AreaTriangulo = (Base * Altura) / 2 End Function

Y ahora sí, estarás de acuerdo conmigo, que se ve mucho más bonita y presentable, pero sobre todo, esta eficientemente codificada. Para terminar, te mostrare una variante, que te servirá como ejemplo para la tareita que te dejare.

Public Function AreaTriangulo(ByVal Base As Single, ByVal Altura As Single) As Double

Dim Area As Double Area = Base * Altura Area = Area / 2 AreaTriangulo = Area End Function

Lo que te he querido decir con esta variante, es que "casi nunca", obtenemos el resultado de nuestra función en una línea, "casi siempre" se hace uso del proverbio que dice -divide y vencerás-, “Descartes”, puedes hacer uso de VARIABLES intermedias, observa muy bien esta línea

Dim Area As Double

Se encontrara con líneas como esta muy seguido, lo que le estamos diciendo al VBA es que estamos DECLARANDO UNA VARIABLE. Es "casi" igual que con los argumentos, solo que aquí usamos la palabra reservada (¿que es eso de palabra reservada?) Dim, pero para decirle el tipo de datos es igual As Tipo_Dato

Ahora si: allá por el año 500 a.C., vivia el señor Herón que no se conformo con la función que hicimos un poco más arriba, así que se puso a investigar como obtener el área, pero de cualquier triángulo con solo conocer la longitud de sus lados, para llegar a deducir la siguiente formula...

En donde a, b y c, son los lados del triángulo y S es el Semiperímetro o sea, la mitad del perímetro y hasta te voy a ayudar un poco más, en VBA para obtener la raíz cuadrada de un número, se usa la función Sqr (número), en donde número es el número del cual

queremos obtener la raíz cuadrada, adelante, empieza a codificarla que con gusto la revisaré...

Como siempre, pruébala, si tenemos el famoso triángulo 3,4,5 y le pasamos estos datos a nuestra primer función, Base = 4 y Altura = 3, el área obtenida sería 6, si le pasamos estos mismos datos, más el largo del tercer lado, a la función que escribas, te tiene que dar el mismo resultado, el poder de la segunda, como ya se menciono, esta en que se puede obtener el área de cualquier triángulo, tan solo conociendo la longitud de sus lados.

Después de haber recibido todas las pruebas que han hecho, aquí esta una de varias posibles soluciones, pruebe y verifique si esta correcta, si no te gusta podría tener otras variantes...

'Función que obtiene el área de cualquier triangulo, conociendo 'la longitud de sus lados, usando la formula de Herón

Public Function AreaTrianguloHeron(ByVal LadoA As Single, _ ByVal LadoB As Single, _

ByVal LadoC As Single) As Double Dim Semi As Double

Dim Area As Double

'Obtenemos el semiperimetro

Semi = (LadoA + LadoB + LadoC) / 2 'Obtenemos los productos

Area = Semi * (Semi - LadoA) * (Semi - LadoB) * (Semi - LadoC) 'Obtenemos la RAIZ CUADRADA

Area = Sqr(Area)

'Asignamos el valor obtenido al nombre de nuestra función AreaTrianguloHeron = Area

End Function

Debes probar las dos funciones, de modo que verifique que te de el mismo resultado, en la siguiente imagen, puedes observar los valores asignados a cada función, así mismo, en rojo, están las funciones que hemos creado...

Función números a letras

Un poco de historia; esta función es una de las más recurridas en la mayoría de los lenguajes de programación, a casi todos los programadores, en algún momento nos sirve o nos la solicitan, esta función hace la utilísima tarea de mostrar un valor numérico en su equivalente en texto o palabras.

En México, los números en letras los mostramos en el siguiente formato:

12,345.67 (DOCE MIL TRESCIENTOS CUARENTA Y CINCO PESOS 67/100 M.N.)

Option Explicit 'Argumentos:

'Numero = Valor que deseamos convertir en texto 'Moneda = es el nombre de la moneda a mostrar

'Fraccion_Letras = Verdadero para que la fraccion de la moneda ' tambien la convierta a letras

'Fraccion = Es el nombre de la fraccion de la moneda

'Texto_Inicial = Cualquier texto que quieras al principio del resultado 'Texto_Final = Cualquier texto que quieras al finla del resultado

'Estilo = Formato de salida ' 1 = MAYUSCULAS ' 2 = minusculas ' 3 = Tipo Titulo

'Los valores negativos los convierte a positivos

'El valor minimo en 0, el valor maximo es 9,999,999,999,999.99 Public Function Numeros_Letras(ByVal Numero As Double, _

ByVal Moneda As String, _

Optional Fraccion_Letras As Boolean = False, _ Optional Fraccion As String = "", _

Optional Texto_Inicial As String = "", _ Optional Texto_Final As String = "", _ Optional Estilo As Integer = 1) As String Dim strLetras As String

Dim NumTmp As String

Dim intFraccion As Integer

strLetras = Texto_Inicial

'Convertimos a positivo si es negativo Numero = Abs(Numero)

NumTmp = Format(Numero, "000000000000000.00") If Numero < 1 Then

strLetras = strLetras & "cero " & Plural(Moneda) & " "

Else

strLetras = strLetras & NumLet(Val(Left(NumTmp, 15))) If Val(NumTmp) = 1 Or Val(NumTmp) < 2 Then

strLetras = strLetras & Moneda & " "

ElseIf Val(Mid(NumTmp, 4, 12)) = 0 Or Val(Mid(NumTmp, 10, 6)) = 0 Then strLetras = strLetras & "de " & Plural(Moneda) & " "

Else

strLetras = strLetras & Plural(Moneda) & " "

End If End If

If Fraccion_Letras Then

intFraccion = Val(Right(NumTmp, 2)) Select Case intFraccion

Case 0

strLetras = strLetras & "con cero " & Plural(Fraccion) Case 1

strLetras = strLetras & "con un " & Fraccion Case Else

strLetras = strLetras & "con " & NumLet(Val(Right(NumTmp, 2))) &

Plural(Fraccion) End Select Else

strLetras = strLetras & Right(NumTmp, 2) End If

strLetras = strLetras & Texto_Final Select Case Estilo

Case 1

strLetras = StrConv(strLetras, vbUpperCase) Case 2

strLetras = StrConv(strLetras, vbLowerCase) Case 3

strLetras = StrConv(strLetras, vbProperCase) End Select

Numeros_Letras = strLetras

End Function

Dim co1 As Integer Dim co2 As Integer Dim pos As Integer Dim dig As Integer Dim cen As Integer Dim dec As Integer Dim uni As Integer Dim letra1 As String Dim letra2 As String Dim letra3 As String Dim Leyenda As String Dim TFNumero As String

NumTmp = Format(Numero, "000000000000000") 'Le da un formato fijo co1 = 1

pos = 1

TFNumero = ""

'Para extraer tres digitos cada vez Do While co1 <= 5

co2 = 1

Do While co2 <= 3

'Extrae un digito cada vez de izquierda a derecha dig = Val(Mid(NumTmp, pos, 1))

Select Case co2 Case 1: cen = dig Case 2: dec = dig Case 3: uni = dig End Select

co2 = co2 + 1 pos = pos + 1 Loop

letra3 = Centena(uni, dec, cen) letra2 = Decena(uni, dec)

letra1 = Unidad(uni, dec)

Select Case co1 Case 1

If cen + dec + uni = 1 Then Leyenda = "billon "

ElseIf cen + dec + uni > 1 Then Leyenda = "billones "

End If Case 2

If cen + dec + uni >= 1 And Val(Mid(NumTmp, 7, 3)) = 0 Then Leyenda = "mil millones "

ElseIf cen + dec + uni >= 1 Then Leyenda = "mil "

End If Case 3

If cen + dec = 0 And uni = 1 Then Leyenda = "millon "

ElseIf cen > 0 Or dec > 0 Or uni > 1 Then Leyenda = "millones "

End If Case 4

If cen + dec + uni >= 1 Then

Leyenda = "mil "

End If Case 5

If cen + dec + uni >= 1 Then Leyenda = ""

End If End Select

co1 = co1 + 1

TFNumero = TFNumero + letra3 + letra2 + letra1 + Leyenda

Leyenda = ""

letra1 = ""

letra2 = ""

letra3 = ""

Loop

NumLet = TFNumero

End Function

Private Function Centena(ByVal uni As Integer, ByVal dec As Integer, _ ByVal cen As Integer) As String

Dim cTexto As String

Select Case cen Case 1

If dec + uni = 0 Then cTexto = "cien "

Else

cTexto = "ciento "

End If

Case 2: cTexto = "doscientos "

Case 3: cTexto = "trescientos "

Case 4: cTexto = "cuatrocientos "

Case 5: cTexto = "quinientos "

Case 6: cTexto = "seiscientos "

Case 7: cTexto = "setecientos "

Case 8: cTexto = "ochocientos "

Case 9: cTexto = "novecientos "

Case Else: cTexto = ""

End Select

Centena = cTexto

End Function

Private Function Decena(ByVal uni As Integer, ByVal dec As Integer) As String Dim cTexto As String

Select Case dec Case 1:

Select Case uni

Case 0: cTexto = "diez "

Case 1: cTexto = "once "

Case 4: cTexto = "catorce "

Case 5: cTexto = "quince "

Case 6 To 9: cTexto = "dieci"

End Select Case 2:

If uni = 0 Then cTexto = "veinte "

ElseIf uni > 0 Then cTexto = "veinti"

End If

Case 3: cTexto = "treinta "

Case 4: cTexto = "cuarenta "

Case 5: cTexto = "cincuenta "

Case 6: cTexto = "sesenta "

Case 7: cTexto = "setenta "

Case 8: cTexto = "ochenta "

Case 9: cTexto = "noventa "

Case Else: cTexto = ""

End Select

If uni > 0 And dec > 2 Then cTexto = cTexto + "y "

Decena = cTexto

End Function

Private Function Unidad(ByVal uni As Integer, ByVal dec As Integer) As String Dim cTexto As String

If dec <> 1 Then Select Case uni

Case 1: cTexto = "un "

Case 2: cTexto = "dos "

Case 3: cTexto = "tres "

Case 4: cTexto = "cuatro "

Case 5: cTexto = "cinco "

End Select End If

Select Case uni

Case 6: cTexto = "seis "

Case 7: cTexto = "siete "

Case 8: cTexto = "ocho "

Case 9: cTexto = "nueve "

End Select

Unidad = cTexto End Function

'Funcion que convierte al plural el argumento pasado

Private Function Plural(ByVal Palabra As String) As String Dim pos As Integer

Dim strPal As String

If Len(Trim(Palabra)) > 0 Then

pos = InStr(1, "aeiou", Right(Palabra, 1), vbTextCompare)

If pos > 0 Then

strPal = Palabra & "s"

Else

strPal = Palabra & "es"

End If End If

Plural = strPal

End Function

'***************************************************************

'Nota: la funcion StrConv solo funciona en Excel2000 en adelante 'si es tu caso, usa el siguiente codigo

TFNumero = TFNumero & Leyenda1 Select Case Estilo

Case 1

TFNumero = UCase(TFNumero) Case 2

TFNumero = LCase(TFNumero) Case Else

TFNumero = Application.WorksheetFunction.Proper(TFNumero) End Select