algunos consejos varios con las macros

En esta última semana he recibido un par de consultas sobre el manejo de registros con macros, siendo que en algunos aspectos son bastante coincidentes, lo que me llevó a pensar en esta entrada para indicar algunas "buenas prácticas" a la hora de llevar adelante este tipo de proyectos. 

Como digo en casi todos mis post, no existe una "única forma" de hacer las cosas al momento de programar, cada uno de nosotros piensa y razona de una manera distinta y ello siempre se ve plasmado en el código resultante. Pero aún así podemos tener en cuenta ciertos aspectos y aplicarlos.

la tabla de datos... fuente de eterna inspiración... y problemas

[+/-] Ver el resto / Ocultar

Empecemos.

Vi como algunos trabajos insertan una fila en la tabla. Hacen mas o menos esto:

Sub InsertarRegistro()

'este procedimiento copia 3 celdas de la hoja

'formulario y lleva esos datos a la hoja tabla:

Sheets("formulario").Select

Range("c2").Copy

Sheets("tabla").Select

Range("a2").Select

Selection.EntireRow.Insert

Range("a2").Select

Selection.Paste

Sheets("formulario").Select

Range("c3").Copy

Sheets("tabla").Select

Range("b2").Select

Selection.Paste

Sheets("formulario").Select

Range("c4").Copy

Sheets("tabla").Select

Range("d2").Select

Selection.Paste

End Sub

Si leemos línea x línea la cuestión no es complicada: copio una celda, voy hasta la hoja "tabla", inserto una fila, pego el dato... y repito la operación hasta completar todo. En este caso copié tres celdas, pero generalmente en los formularios son muchas mas (edad, cargo, situación, deuda, sueldo, estado civil, etc, etc, etc). O sea que al procedimiento de arriba deberíamos agregarle muchas líneas mas.

Cabe aclarar que no es una buena forma. Noten que estamos "yendo y viniendo" de una hoja a la otra, insertando, seleccionando, copiando, pegando... son demasiadas instrucciones innecesarias que consumen tiempo y recursos.

Primero: si quiero insertar una fila en otra hoja.... no es necesario desplazarme hasta ella, puede hacerlo desde cualquier parte, así:

Sheets("tabla").Range("a2").EntireRow.Insert

lo que traducido es: en el rango A2 de la hoja "tabla" insertá una fila completa. Listo, así de fácil evité unas cuantas líneas de código repetitivas y no "fui y vine", desplazándome entre hojas.

Ahora pasemos al tema de como enviar los datos desde "formulario" a "tabla".

Si lo pensamos un poco, el "copiar y pegar" solo se justifica si necesitamos llevar formatos (colores, fuentes, estilos, etc, etc), caso contrario, es mucho mas rápido indicarle a Excel que en la otra hoja coloque los valores:

With Sheets("tabla")

    .Range("a2") = Range("c3")

    .Range("b2") = Range("c4")

    .Range("c2") = Range("c5")

End With

Y a simple vista es mas sencillo: desde la hoja actual solo llevo los valores, sin necesidad de "moverme hasta allá". 

Pero si necesitamos si o si copiar y pegar... también podemos hacerlo sin desplazarnos:

With Sheets("tabla")

    Range("c3").Copy .Range("a2")

    Range("c4").Copy .Range("b2")

    Range("c5").Copy .Range("c2")

End With

Cada línea de código copia una celda de la hoja actual y la lleva al rango A2, B2 y C2 de tabla, respectivamente. Luego de utilizar el método Copy, dejo un espacio y le especifico a Excel en que lugar del libro quiero pegarla, sin tener la obligación de "irme hasta allá", desperdiciado tiempo y recursos.

En algunos casos, como por ejemplo ordenar la tabla, deberemos ir a esa hoja. Si la cuestión no nos brinda otra alternativa, tenemos a mano una propiedad que ayudará a acelerar las cosas y no consumir RAM o CPU en cuestiones menores: ScreenUpdating. ¿Que hace? Elimina lo que se llama el "refresco de pantalla", es decir, no muestra los cambios realizados hasta que restauro esa propiedad. ¿Es importante? Si, muy importante, por que ¿para que quiero mostrarle al usuario que voy y vengo entre distintas hojas? Aparte visualmente es desprolijo e internamente consume recursos.

ScreenUpdating soporta solo dos valores: True (activado) y False (desactivado). Entonces, y antes de ponerme a navegar entre las hojas, haría esto:

Application.ScreenUpdating = False

Sheets("tabla").select

Range("A1").select

Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:= _

xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

DataOption1:=xlSortNormal

Sheets("formulario").select

Application.ScreenUpdating = True

Explicación: desactivo el refresco de pantalla, voy a la hoja tabla, ordeno los datos, vuelvo a la hoja "formulario" y activo el refresco de pantalla. Si prueban esto en sus ordenadores, verán que en ningún momento se observa "pestañeo" alguno, si no que "todo se queda quieto", a pesar de que cambiamos de hoja, ordenamos y  volvemos a la hoja de origen.

¿Por que activo el refresco de pantalla al final? Por  que estoy trabajando sobre el objeto Application y cualquier cosa que realice sobre él se aplicará a todos los libros de Excel, no solo con el cual me encuentro trabajando. Y por ahí sería un incordio muy grande modificar cosas en un libro que al final terminen afectando a todos. ¿Se entiende la idea? Entonces: si modifico algo de Application (que en definitiva es Excel) tengo que volverlo a su estado natural al terminar la rutina o cerrar el libro activo.

El párrafo anterior me lleva a otro tema que incluye también a Application: los separadores decimales. En muchas ocasiones recibimos archivos que fueron realizados en una PC con una configuración regional determinada (cuando los lectores me hacen llegar sus proyectos, recibo archivos de España, Mexico, Colombia, Argentina, EEUU, Perú... ¡¡¡¡hasta tengo seguidores en Croacia, de donde proviene parte de mi familia!!!!), la cual en la mayoría de los casos no coincide con la configuración regional de mis equipos.

Con una sentencia muy simple puedo saber como viene la mano, y obrar en consecuencia:

MsgBox Application.DecimalSeparator

Lo que me retorna:

ahá.. eso es una "coma"

Entonces tengo a mano una poderosa herramienta para saber si usar puntos o comas dentro del libro al momento de insertar números.

Este ejemplo lo saco de la propia ayuda de Excel, modificando la configuración del mismo:

Sub ChangeSystemSeparators()

    Range("A1").Formula = "1,234,567.89"

    MsgBox "Los separadores del sistema serán cambiados"

    'Defino los separadores y aplico:

    Application.DecimalSeparator = "-"

    Application.ThousandsSeparator = "-"

    Application.UseSystemSeparators = False

End Sub

Y no olvidemos que debemos volver ese código atrás luego de trabajar. La forma mas sencilla sería almacenando los valores previos en variables:

DSeparator = Application.DecimalSeparator

TSeparator = Application.ThousandSeparator

'trabajo en todo lo que tengo que trabajar

'codigo

'codigo

'codigo

'y dejamos todo como estaba:

Application.DecimalSeparator = DSeparator

Application.ThousandSeparato = TSeparator

Si voy a trabajar con Rangos y quiero ser prolijo y ordenado (cosa que también se aplica a las funciones, no solo a las macros) es mucho mas cómodo, facilitando su mantenimiento y depuración, asignarle nombres a esos rangos. Volvamos al ejemplo de la hoja "formulario". Si en la misma pido legajo, apellido e importe de un agente, lo mejor será nombrar las celdas que contendrán esa info con un nombre descriptivo, que luego me permitirá una mejor lectura de lo realizado. Miren:

With Sheets("tabla")

  .Range("a2") = range("legajo")

  .Range("b2") = range("apellido")

  .Range("c2") = range("importe")

End With

Mucho mas claro y descriptivo. Si queremos aún acortar mas el código no olvidemos otra forma de escritura, mas breve:

With Sheets("tabla")

  .Range("a2") = [legajo]

  .Range("b2") = [apellido]

  .Range("c2") = [importe]

End With

Mas breve aún: podemos encerrar el nombre del rango entre llaves, evitando el range("lo_que_sea")

Procedo de igual forma al hablar del trabajo con hojas. Soy partidario de definir una variable y trabajar sobre ella en vez de referenciar directamente a la hoja en cuestión:

Dim Hoja As Worksheet

Set Hoja = Sheets("tabla")

'borro una fila entera:

Hoja.Range("a2").EntireRow.Delete

Set Hoja = Nothing

El código de arriba, aparte, me permitirá tener acceso a la lista desplegable con todas las propiedades y métodos de la hoja, cosa que si trabajara con Sheets("tabla") no podría conseguir.

Otra cuestión un tanto mas compleja es el tema de la "modularización". Esta forma de trabajo es excelente y nos permite mantener el código mas ordenado y fácil de depurar y mantener. El tema es este: si tengo acciones que llevo a cabo en reiteradas ocasiones, lo mejor es crear un procedimiento (Sub o Function) que las realice y así evito tener que repetir código por todo el proyecto.

En varias partes de esta entrada he utilizado la sentencia que inserta una fila en la hoja "tabla". Lo mejor sería crear un Sub que lleve a cabo dicha tarea, para luego invocarlo:

Public Sub InsertarFilaEnTabla()

 Sheets("tabla").Range("A2").EntireRow.Insert

End Sub

Luego "llamamos" al Sub desde cualquier parte del proyecto y así nos evitamos repetir esa orden. Ahora imaginemos que nuestro trabajo nos requiere insertar la fila desde el rango A3, no desde A2 como lo venimos haciendo. Si trabajamos de la forma "tradicional", tendríamos que andar revisando todas las partes e ir modificando dicho rango. Pero como creamos un fragmento de código (Sub) que hace dicha tarea... solo debemos cambiar allí las cosas para que, a partir de ese momento, las filas se inserten en el tercer registro:

Public Sub InsertarFilaEnTabla()

 Sheets("tabla").Range("A3").EntireRow.Insert

End Sub

Y si aún quisiéramos  hacer mejor las cosas, deberíamos colocar un argumento. Supongamos que en determinadas hojas insertamos filas a partir del segundo registro, en otras a partir del tercero y en otra en la quinta posición. Bien podríamos crear 3 procedimientos, pero evitamos tal duplicidad de código con el uso de parámetros. Vean:

Public Sub InsertarFilaEnTabla(QueFila as Long)

 Sheets("tabla").Range("A" & QueFila).EntireRow.Insert

End Sub

Es decir: pasaré un argumento al llamar al Sub y la fila se colocará en donde le indique:

InsertarFilaEnTabla 2 'colocará la fila en A2

InsertarFilaEnTabla 3 'colocará la fila en A3

InsertarFilaEnTabla 5 'colocará la fila en A5

o bien

InsertarFilaEnTabla 65 'colocará la fila en A65

Y si incluyo que la hoja en cuestión también sea una variable:

Public Sub InsertarFilaEnTabla(QueHoja as String, QueFila as Long)

 Sheets(QueHoja).Range("A" & QueFila).EntireRow.Insert

End Sub

Y luego paso los argumentos necesarios cuando llamo al Sub:

InsertarFilaEnTabla "tabla", 2 'colocará la fila en A2 de la hoja "tabla"

InsertarFilaEnTabla "datos", 3 'colocará la fila en A3 de la hoja "datos"

InsertarFilaEnTabla "otra",  5  'colocará la fila en A5 de la hoja "otra"

¿Notan la diferencia entre la metodología utilizada? Es indiscutible que la última nos ahorrará mucho trabajo.

Si en algo tan simple se observan buenos resultados... imaginen en procesos mas complejos, en donde incluimos funciones y varios trabajos a realizar: las ventajas son enormes.

Bien, llegamos al fin. Como dije al principio, noté algunos "problemas" con el código que me remitieron y quise levantar esta entrada con el objetivo de optimizar un poco los proyectos y que nuestro trabajo sea así mas rápido, insumiendo menos recursos. No estamos frente a "la verdad absoluta", solo ante un par de prácticas que es conveniente tenerlas en cuenta, mas aún si nuestros proyectos tienen cierta  envergadura y complejidad a la hora de realizarlos y, posteriormente, mantenerlos.

Suerte, gracias por los mails que a diario recibo y cualquier duda me avisan.