Tip para escribir macros eficientes en Excel

Hay muchas normas de buenas prácticas y tips para escribir macros eficientes. Una posibilidad raramente mencionada es usar en nuestros códigos métodos incorporados de Excel que podemos grabar con la grabadora de macros.

Un ejemplo puede ser la macro que propuse para importar fechas de un archivo .csv. La macro usa el loop For Each - Next para convertir fechas en formato mes/día/año (como en los Estados Unidos) al formato día/mes/año en uso en la mayoría de los países de habla hispana.

En lugar del código podemos usar la grabadora de macros para usar el método Texto en Columnas (Datos-Texto en Columnas) en nuestro código en lugar del loop For Each - Next. La ventaja inmediata de la grabadora de macros es que nos exime de tener que conocer la sintaxis del método. Además, como veremos más adelante, esta funcionalidad incorporada de Excel es mucho más eficiente.

No nos limitaremos a grabar la macro sino que eliminaremos las partes innecesarias; luego agregaremos variables para que nuestro código sea lo más flexible posible (al contrario del código que resulta de la grabadora de macros).

Supongamos que hemos importado un archivo .csv que contiene 400000 registros de fechas. Como el archivo fue originado en los Estados Unidos, tenemos que cambiar el formato de los datos, tal como explicamos en la nota mencionada.

Después de importar el archivo, activamos la grabadora de macros y usamos Texto en Columnas para transformar las fechas

El código resultante es el siguiente

Eliminamos la primer línea del código, suponiendo que el usuario activará la macro después de haber elegido el rango a convertir. También podemos eliminar las propiedades definidas por defecto, es decir, aquellas que no hemos cambiado (como norma, aquellas donde el valor de la propiedad es False)

Podemos dar un paso más adelante y permitir al usuario definir donde pegar el resultado (en el código de arriba Destination:=Range("A1")); además queremos verificar que el usuario haya elegido un rango que contengo por lo menos dos celdas

Ahora podemos verificar cuál es el código que corre más rápido: el que usa el loop For each - Next, de la nota mencionada, o éste basado en el método incorporado de Excel.

En mi máquina (Dell Latitude E5540 con procesador Intel Core i5-4300, 8 GB RAM, Excel 2010 64-bit), la macro que usa Texto en Columnas tomó 4.3 segundos en convertir las 400 mil fechas. La misma tarea con el loop For Each - Next tomó 180 segundos.

Conclusión: siempre considerar usar los métodos incorporados en Excel en nuestras macros.

Fechas de .csv a Excel - otras técnicas

En relación a la nota sobre los problemas que pueden surgir al importar fechas de un archivo texto .csv a Excel, dos lectores me sugieren técnicas que no fueron tratadas en el post. Javi sugiere usar Texto en Columnas (la opción más obvia que ignoré olímpicamente, mea culpa!) y Miguel (Power User en Español) sugiere usar la propiedad "locale" del Power Query.

Ambas técnicas merecen algo más que un comentario al pie del post, así que vamos a mostrar cómo usarlas.

Recordemos que el formato de fechas en los distintos países genera en potencia un problema al importar fechas de un archivo .csv a Excel. Al abrir directamente un archivo .csv, Excel "decide" que tipo de dato irá en cada celda. Todo lo que se vea como número será transformado en número (también si va precedido por uno o más ceros); todo lo que se vea como fecha será transformado en fecha. Todo lo demás será importado como texto.
Dado que el formato de fecha en los Estados Unidos (y en otras áreas del mundo) es mes/día/año mientras que la mayoría de los países hispanoparlantes y en Europa es día/mes/año, las fechas pueden ser importadas incorrectamente. Por ejemplo la fecha 10/06/2014 en los Estados Unidos es el 6 de octubre, mientras que en la Argentina es el diez de junio.

La técnica sugerida por Javi es usar Texto en Columnas y en el tercer paso elegir el formato MDA lo que transformará las fechas en forma correcta. Este video muestra la técnica



La técnica sugerida por Miguel es más avanzada y requiere que tengamos instalado el complemento Power Query (Excel 2010 en adelante).

Entre otras fuentes el Power Query permite importar datos también de archivos .csv. Una vez importados los datos al editor del Power Query podemos determinar el formato de fecha del archivo de origen (la propiedad "locale") para que estas sean interpretadas correctamente. Al cargar los datos a la hoja de Excel estos serán transformados correctamente.

Los datos en el archivo .csv son los siguientes

El proceso con el Power Query puede verse en este video

Importar fechas de un archivo .csv a una hoja de Excel

Ya hemos tocado en el pasado los problemas que pueden surgir cuando abrimos en forma directa archivos texto .csv en hojas de Excel. Por "forma directa" me refiero a archivos .csv abiertos con un doble click o usando Abrir. Excel interpreta los datos de acuerdos a ciertas reglas y pueden producir cambios indeseados. Por ejemplo el texto "012345" que representa, digamos, un número de catálogo será transformado en "12345".

El problema es particularmente grave cuando los datos importados son fechas. Supongamos que recibimos un archivo .csv con una lista de fechas que nos envía una empresa de los Estados Unidos. En los Estados Unidos se usa el formato mes/día/año mientras que en la mayoría de los países hispanoparlantes el formato de fecha es día/mes/año. Al abrir el archivo .csv directamente, los valores que Excel no interpreta como fechas de acuerdo a las definiciones regionales serán transformados en texto.Veamos este ejemplo:

Podemos ver que algunas fechas están alineadas a la izquierda y otras a la derecha.  Los valores alineados a la derecha han sido importados como fechas (al ser números Excel los alinea a la derecha), mientras que los valores alineados a la izquierda son texto. Esto se debe que Excel no puede interpretar  esos valores como fecha siguiendo el formato regional día/mes/año (por ejemplo en la celda A2, donde el número de mes sería 23).

El valor en la celda A3 nos muestra el problema más grave que se puede generar cuando importamos archivos .csv con fechas. La fecha, siguiendo el formato de los Estados Unidos, es el 6 de Octubre pero Excel la ha transformado en el 10 de Junio.

Señalemos que los datos importados deben ser fechas, de manera que podamos realizar operaciones con ellos.

Podemos transformar los textos en fechas usando una fórmula como

=SI(ESTEXTO(A3),VALOR(EXTRAE(A3,4,2)&"/"&IZQUIERDA(A3,2)&"/"&DERECHA(A3,4)),VALOR(TEXTO(A3,"mm/dd/yyyy")))

Podemos ver que el valor de la celda A3 es transformado correctamente por la fórmula en 06/10/2012.

Otra solución es usar una macro para forzar la transformación. La ventaja de la macro consiste en que no debemos crear las fórmulas para cada hoja; podemos guardar la macro en el cuaderno Personal y usarla en cada hoja que necesitemos sin necesidad de cargarla con fórmulas.

El código básico de la macro es

Sub USDdate_to_EURdate()
    Dim rngcell As Range

    On Error Resume Next
    For Each rngcell In Selection
        rngcell = CDate(Format(rngcell, "mm/dd/yyyy"))
    Next rngcell
    On Error GoTo 0

End Sub

Esta macro reemplaza los valores en el rango seleccionado por fechas con formato (dd/mm/yyyy).

Este código más elaborado nos permite elegir el rango donde copiar los resultados

Sub USDdate_to_EURdate_2()
    Dim rngcell As Range
    Dim rngOrigin As Range, rngDest As Range
    Dim iX As Long

    'seleccionar el rango a transformar
    Set rngOrigin = Application.InputBox(prompt:="Seleccione el rango a transformar", _
                                        Title:="Rango a transformar", _
                                        Type:=8)

    'comprobar si el rango elegido es vertical/columna
    If rngOrigin.Columns.Count > 1 Then
        MsgBox "El rango seleccionado debe contener solo una columna", vbCritical, "Error en la seleccion"
        Exit Sub
    End If

    'seleccionar la primer celda del destino
    Set rngDest = Application.InputBox(prompt:="Seleccione la primer celda del rango del destino", _
                                        Title:="Destino", _
                                        Type:=8)
    'comprobar que se haya elegido una sola celda
    If rngDest.Count > 1 Then
        MsgBox "Debe seleccionar solo una celda", vbCritical, "Error en la seleccion"
        Exit Sub
    End If

    Application.ScreenUpdating = False
    On Error Resume Next 'en caso de haber celdas vacias o valores no validos en el rango elegido
    For iX = 0 To rngOrigin.Count - 1
        rngDest.Offset(iX, 0) = CDate(Format(rngOrigin(iX + 1), "mm/dd/yyyy"))
    Next iX
    On Error GoTo 0
    Application.ScreenUpdating = True
 
End Sub