Macros Excel – haciéndolas disponibles para todos los cuadernos

Con Excel es relativamente fácil crear macros. Uno de los usos más corrientes es automatizar tareas. No hace falta ser un profesional del Vba (Visual Basic for Applications) para automatizar tareas sencillas con macros.
Así que supongamos que hemos desarrollado una macro para formar la fuente de la selección a negrita (bold) y el fondo de las celdas a gris.
La forma más fácil es utilizar la grabadora de macros.




El resultado es el siguiente



Cambiamos el nombre de la macro a N_gris, y eliminamos la primer línea de la macro, para que podamos aplicarla a cualquier rango que elijamos en la hoja



Sub N_gris()
'
' Macro1 Macro
' Macro grabada el 05/02/2007 por JLD
'

'
Selection.Font.Bold = True
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
End Sub


Para poder usarla con facilidad asociamos una combinación de teclas a la macro (Herramientas—Macros—Opciones)




Ahora tenemos una macro que nos permite aplicar los formatos pulsando Ctrl+Mayúsculas+N a cualquier rango que hayamos seleccionado.

Para usar esta macro en hojas de otros cuadernos podemos:

1 – Abrir el cuaderno donde grabamos la macro. De esta manera estará disponible para todos los cuadernos abiertos en la sesión.

2 – Guardar la macro en el cuaderno Personal.xls. Excel crea este cuaderno automáticamente la primera vez que grabamos una macro en él. Normalmente este cuaderno está oculto, pero las macros que contiene están disponibles en cada sesión de Excel.

Para crear Personal.xls, simplemente grabamos una macro en él (basta con activar la grabadora e inmediatamente terminar la grabación)




Una vez hecho esto, podemos ver en la ventanilla Proyectos el Personal.xls




Copiamos la macro a un módulo de Personal, y la macro estará disponible cada vez que iniciemos una sesión de Excel.


Supongamos ahora que queremos distribuir nuestra macro a otros usuarios. Una posibilidad es darles una copia del cuaderno con la macro. Estos usuarios podrán luego copiar la macro a su propio Personal.

Otra posibilidad, más eficiente, en guardar la macro como complemento (Add in) y guardarlo en una carpeta pública, de donde cada usuario autorizado podrá instalarlo.
Mostraremos esta técnica en la próxima nota.

Technorati Tags: MS Excel

Cálculo automático y manual en Excel

Hace un tiempo que me vengo prometiendo a mi mismo publicar una nota sobre velocidad de cálculo en Excel. La idea surgió, como todas las ideas que tengo en estos últimos tiempos, de los problemas con los que nos enfrentamos en mi empresa para implementar el nuevo sistema de información (Oracle Applications).
Para poner en marcha el nuevo sistema, debimos transferir grandes masas de datos del sistema anterior. En muchos casos los datos originales debieron pasar algún tipo de transformación. Por ejemplo, los números de catálogo pasaron de 9 cifras a un número variable cifras.
Excel suele ser la herramienta preferida para este tipo de transformaciones. Funciones como BUSCARV, INDICE y COINCIDIR fueron usadas con mucha intensidad.
Ahora imagínense una tabla de 16000 filas y 10 columnas. En una de las columnas ponemos una fórmula con BUSCARV con coincidencia exacta. Cada cambio que hace que Excel recalcule todas las fórmulas en la hoja, puede detener el trabajo por varios minutos. Esto puede suceder cuando usamos Autofiltro y cambiamos el criterio de filtrado.

Básicamente hay dos formas de enfrentarse con este problema:

1 – Cuando Excel comienza a recalcular hojas "pesadas", veremos en el ángulo inferior izquierdo de la hoja el porcentaje de avance del cálculo.




Apretamos ESC. Excel detiene el cálculo y pone un aviso que debemos completar el cálculo



2 – Pasar de cálculo automático a cálculo manual. Para hacer esto, usamos el menú Herramientas—Opciones y el diálogo que se abre vamos a la pestaña Calcular. Allí marcamos la opción Manual.




Hay que tomar en cuenta que este cambio afectará a todos los cuadernos abiertos en la sesión.

Para forzar el cálculo usamos F9 (para recalcular todos los cuadernos abiertos) o Shift+F9 para recalculas sólo el cuaderno activo.

Technorati Tags: MS Excel

Usos de Autofiltro – Copiar a rangos no continuos

Hace unos días atrás, Rob van Gelder plantea el siguiente problema en Daily Dose of Excel:

tenemos una tabla con líneas en blanco que separan distintos grupos de datos. Queremos agregar una columna que contiene una fórmula, pero al copiar la fórmula a todo el rango se producen resultado "error". Esta imagen ejemplifica el problema:



La solución más inmediata es seleccionar las celdas con resultados erróneos y borrar el contenido.
Pero supongamos que se trata de una tabla con 1000 filas. Evidentemente la tarea llevaría muchísimo tiempo (1).
La misión es crear una selección lo continua de celdas en una columna dónde copiaríamos la fórmula.
Rob van Gelder propone una macro como solución. Nosotros mostraremo como hacer esto con Autofiltro.

Partimos de la tabla original, sin fórmulas en la columna E. Si aplicamos Autofiltro sin más trámite, Excel mostrará solamente los valores hasta la primer fila en blanco.
Para "obligar" a Excel a considerar toda la tabla, empezamos por seleccionar todo el rango de las columnas A hasta E y luego aplicamos Autofiltro



Ahora filtramos la tabla de acuerdo a la columna B, "no vacías"



Excel oculta las filas vacías



Seleccionamos el rango relevante en la columna E y aplicamos la fórmula



Todo lo que nos queda por hacer es anular el autofiltro



Obtenemos el mismo resultado, sin aplicar macros.

(1) En realidad podríamos seleccionar de una vez todas las celdas con resultado #DIV/0! usando Ir A --Especial, como ya hemos mostrado, y borrar el contenido en una sola operación. Si usamos alguna fórmula que no divida por una celda vacía no recibiremos resultado #DIV/0 y el método mostrado en esta nota sería el ideal.

Technorati Tags: MS Excel