Crear fórmulas Excel con macros independientemente del idioma o separador de argumentos, coma o punto y coma

Cuando queremos insertar fórmulas en Excel mediante macros, nos encontramos con 2 disyuntivas:

1. Usar el nombre de la función mdiante lenguaje vba, es decir, su parte en inglés que sea compatible con cualquier Excel en cualquier idioma.
2. Escribir la fórmula en nuestro propio lenguaje, cual sea que este fuera, adecuando el separador de argumentos: coma (,) o punto y coma(;), pero sólo compatible con otro Excel en nuestro idioma

Insertar fórmulas mediante vba aplicables a Excel en cualquier idioma

Supongamos que necesitamos insertar en una celda la función BUSCARV desde vba. Si esta macro se utilizará en otras versiones de Excel con diferentes idiomas al que usamos, lo mejor será usar los nombres en inglés.

Desde vba usamos la propiedad FORMULA del objeto RANGE:

Range("F6").Formula = "=VLOOKUP(E6,A1:B12,2,0)"

Insertar fórmulas mediante vba aplicables a nuestro idioma

La misma función BUSCARV la deseamos escribir desde vba en nuestro mismo idioma. Para eso debemos hace uso de la propiedad FORMULALOCAL del objeto RANGE.

Range("F6").FormulaLocal = "=BUSCARV(E6,A1:B12,2,0)"

El caso del separador de argumentos cuando es coma o punto y coma

El separador de listas o argumentos varía dependiendo de la configuración regional de nuestro sistema. Por ejemplo, es coma cuando la cofiguración es el Español y punyo y coma cuando la configuración en el inglés. Pero como no nos interesa escribir fórmulas para cada tipo de región, mejor hacemos uso de la propiedad INTERNATIONAL del objecto APPLICATION, para meter a una variable el separador que nos detecte Excel.

Para obtener el separador de argumentos configurado en el Panel de control usamos la siguiente instrucción:

Application.International(xlListSeparator)

Y si dejamos el separador como variable, las instruccones quedarían así:

'Obtener el separador de argumentos
Separador = Application.International(xlListSeparator)
'Para fórmula multi idioma
Range("F6").Formula = "=VLOOKUP(E6" & Separador & "A1:B12" & Separador & _
    "2" & Separador & "0)"
'Para fórmula en nuestro idioma
Range("F6").FormulaLocal = "=BUSCARV(E6" & Separador & "A1:B12" & Separador & _
    "2" & Separador & "0)"

Sumar la misma celda de varias hojas en Excel

Les dejo un truco sencillo que nos permite sumar una misma celda, pero de todos las hojas abiertas, no importando cuántas sean.

La forma más conocidas, pero laboriosa es separar por comas cada celda de cada hoja hasta llegar a la última, pero eso puede provocar que nos falte una celda y ver una fórmula muy larga, como la siguiente:

=SUMA(A1,Hoja2!A1,Hoja3!A1,Hoja4!A1,Hoja5!A1,Hoja6!A1,Hoja7!A1,Hoja8!A1,Hoja9!A1,Hoja10!A1,Hoja11!A1,Hoja12!A1)

Supongamos que queremos sumar todas las cantidades que se encuentran en la celda A1 de todas nuestras hojas, pero lo queremos hacer de manera rápida. Para eso la siguiente fórmula nos ayudará con el propósito, incluso la podemos copiar hacia cualquier dirección si queremos hacer la suma de otras celdas.

=SUMA(Hoja1:Hoja12!A1)

Evitar introducir datos repetidos con validación de datos

Excel cuenta con una herramienta muy funcional, la cual es, Validación de datos, que entre otras cosas nos permite validar la entrada de datos:

1. Rangos de números.
2. Fechas.
3. Horas.
4. Longitud de texto.
5. Ingreso de listas.
6. Funciones personalizadas.

La que nos atañe en este post es la de Función personalizadad. Lo que deseamos es que Excel no nos permita introducir datos repetidos, y que en su lugar nos envíe un aviso personalizado.

Cómo funciona ?

En este caso tenemos nuestro rango que será “A1:A10”. Elegimos todo el rango y nos vamos al menú/pestaña Datos > Validación. En el combo Permitir elegimos Personalizada e introducimos la siguiente fórmula:

=CONTAR.SI($A$1:$A$10,A1)=1

Posteriormente, dentro del mismo formulario elegimos la pestaña Mensaje de entrada y personalizamos nuestro mensaje.

La validación nos permite limitar los datos de ingreso

Al momento de ingresar algún dato repetido, Excel nos arrojará un mensaje previamente personalizado donde le indiquemos al usuario acerca de los datos repetidos.

Excel nos permite personalizar nuestros mensajes para hacer la labor más dinámica

Insertar fórmula desde código vba en Excel

Al momento de crear nuestras propias macros siempre es recomendable utilizar el grabador de macros. Sobretodo cuando no sabemos o no conocemos cómo realizar alguna función de Excel.

Una de esas funciones es insertar fórmulas y funciones. El grabador nos permite crear macros donde se inserten fórmulas, la diferencia es que la fórmula vendrá con la referencia R1C1 (filas y columnas) y la función estará en inglés. Por ejemplo:

Sub FormulaGrabada()
'
' Macro1 Macro
' Macro grabada el 11/08/2010 por Sergio A Campos H
'
'
    Range("C2").FormulaR1C1 = "=COUNTIF(R[-2]C[-2]:R[1]C[-2],""VENTA"")"
End Sub

Pero si lo que queremos es escribir nuestra propia macro y escribir nuestras fórmulas en nuestro propio lenguaje podemos utilizar la propiedad FormulaLocal. Por ejemplo:

Sub FormulaEnCelda()
'FormulaLocal nos pemite escribir nuestras funciones en nuestro propio lenguaje
'y de la misma manera que la escribiríamos dentro de una celda.
'
Range("C3").FormulaLocal = "=CONTAR.SI(A1:A4,""VENTA"")"
'
End Sub