Manipular fórmulas

Fórmulas en Excel

Existen centenares de fórmulas disponibles en Excel e incluso un usuario muy avanzado solo utilizará una mínima parte de ellas. Sin necesidad de ser un experto en fórmulas y funciones de Excel, he aquí un breve recordatorio de la pestaña Fórmulas de la cinta de opciones de Excel y de algunos de los errores de salida de las fórmulas con los que tendrá que lidiar en VBA.

Pestaña Fórmulas

En la cinta de opciones de Excel, las fórmulas tienen su propia pestaña dedicada.

La cinta de opciones tiene un grupo Biblioteca de funciones en el que se agrupan las fórmulas por categorías.

Puede introducir sus propias fórmulas directamente en las celdas o en la barra de fórmulas.

Las fórmulas comienzan con el signo = y utilizan tanto funciones de Excel como celdas o valores escritos «directamente».

=A1+B2 
=AÑO(C3) 
=TEXTO(AHORA(); "AAAA-MM") 

Ejemplo 1: diferentes tipos de fórmulas de Excel

Cada una de estas fórmulas proporciona un valor como salida, de diferentes tipos de datos, según los valores y funciones utilizadas (texto, valor numérico, fecha, booleano, etc.).

Fórmulas en VBA

Como descubrirá en las siguientes secciones, hay varias formas de escribir fórmulas en VBA. Dependiendo de su fluidez con la lengua de Shakespeare o de sus limitaciones empresariales, tendrá que usar uno u otro método.

Usar inicialmente la grabadora de macros

Para empezar, utilice la grabadora de macros para ver qué código predefinido se genera cuando se añaden fórmulas a las celdas.

Manipulaciones por realizar

He aquí los cinco pasos que se deben realizar:

1 - Rellenar las celdas

 En un libro nuevo o existente, introduzca los siguientes datos en las celdas A1 a C3:

2 - Ejecutar la grabadora de macros

 Como se vio en el capítulo La grabadora de macros, inicie la grabadora de macros y nombre la macro Macro1.

3 - Agregar las fórmulas

 En la celda D1, agregue la fórmula: =BUSCARV(1;A:C;3;FALSO)

 En la celda E1, agregue la fórmula: =BUSCARV("Martín Juárez";B:C;2;FALSO)

4 - Detener la grabadora de macros

 Detenga la grabadora de macros tal y como vio en el capítulo La grabadora de macros.

5 - Analizar el código generado automáticamente

 Acceda a la interfaz VBE; este es el código que debería ver en el módulo recién creado:

Sub Macro1() 
   Range("D1").Select 
   ActiveCell.FormulaR1C1 = "=VLOOKUP(1,C[-3]:C[-1],3,FALSE)" 
   Range("E1").Select 
   ActiveCell.FormulaR1C1 = "=VLOOKUP(""Martín Juárez"", 
C[-3]:C[-2],2,FALSE)" 
End Sub 

Ejemplo 2: código generado por la grabadora de macros

Análisis del código generado por la grabadora de macros

La primera línea de la macro consiste en seleccionar la celda D1, que a estas alturas ya conoce.

Range("D1").Select 

La segunda línea utiliza la celda activa (D1) para asignarle una fórmula a través de la propiedad FormulaR1C1, que verá con más detalle en la siguiente sección.

ActiveCell.FormulaR1C1 = "=VLOOKUP(1,C[-3]:C[-1],3,FALSE)" 

La fórmula se aplica directamente a nivel de una celda de tipo Range/Cells.

Puede constatar que la fórmula se pasa como una cadena, con el signo = como primer carácter, como lo que usted introdujo manualmente....

Usar sus propias funciones en las fórmulas de Excel

Otra posibilidad muy práctica con VBA es utilizar sus propias funciones directamente en las celdas de Excel.

Hacerlo es muy sencillo. Las funciones que desee utilizar en las fórmulas tendrán que escribirse en módulos (no en ThisWorkbook, en las hojas ni en los módulos de clase). El ámbito de las funciones también debe ser Public.

He aquí un ejemplo de una fórmula que devuelve el valor 1.

Public Function Devuelve1() As Integer 
   Devuelve1=1 
End Function 

Ejemplo 17: hacer que una función personalizada esté disponible en las fórmulas

Una vez escritas estas pocas líneas, al ir a una celda de Excel y escribir el nombre de la función, esta aparecerá en la lista de funciones disponibles, respetando las mayúsculas y minúsculas del nombre.

Cuando la fórmula se valide, el valor que devuelve la función (aquí 1) se mostrará directamente en la celda.

Esto también funciona si se cuenta con una función que requiere parámetros.

'Función que devuelve el cuadrado del valor pasado como parámetro 
Public Function RegresaElCuadrado(iValor As Integer) As Integer 
   RegresaElCuadrado = iValor * iValor 
End Function 

Ejemplo 18: función personalizada que requiere un parámetro

Bastará...

Usar fórmulas de Excel directamente en VBA - WorksheetFunction

Puede utilizar funciones directamente en VBA sin pasar por las fórmulas de Excel gracias a la propiedad WorksheetFunction del objeto Application.

Application.WorksheetFunction.NombreDeLaFunción(Arg1, [Arg2],...) 

Ejercicios

El objetivo de los ejercicios es utilizar fórmulas basadas en estos datos.