lunes, 1 de agosto de 2016

Cómo optimizar la velocidad de nuestras macros en Excel (5 de 5)


Finalizamos con este 5º post nuestra serie de artículos "Cómo optimizar la velocidad de nuestras macros en Excel". Lo dedicaremos a 3 trucos que optimizarán nuestro código hacia otras formas más óptimas de VBA, ya sea por la velocidad o por la claridad en el código.




1) Seleccionar la última celda que contiene datos en un rango

El título de este primer punto hace referencia a una necesidad con la que nos solemos encontrar cuando escribimos código VBA. Esta necesidad la podemos cubrir de varias formas... pero unas son más óptimas que otras en cuanto a velocidad.

Por ejemplo, el siguiente código cumple con la función de encontrar la última celda que contiene datos en una lista que comienza en A1:

Sub UltimaCeldaConDatosConBucleFor()
    Range("A1").Select
    For i = 1 To 10
        If Range("A1").Offset(i, 0) <> vbNullString Then
            ActiveCell.Offset(1, 0).Select
        End If
    Next i
End Sub

Eso sí, tenemos que tener en cuenta que la celda que quedará seleccionada será la primera cuya celda siguiente esté vacía. Por lo tanto, si la lista tiene huecos, debemos contar con ellos:



También es posible seleccionar la última celda con datos de una lista mediante el código:

Sub UltimaCeldaConDatosConBucleDoUntil()
    Range("A1").Select
    Do Until ActiveCell.Offset(1, 0) = vbNullString
        ActiveCell.Offset(1, 0).Select
    Loop
End Sub


Pero el código más óptimo en cuanto a velocidad y longitud es el siguiente:

Sub UltimaCeldaConDatosDesdeArriba()
    Range("A1:A10").End(xlDown).Select
End Sub


Como vemos, utilizamos en este caso la propiedad End con el argumento xlDown (equivalente a utilizar con el teclado CTRL + MAYUS + Flecha abajo).

Con un código similar podemos salvar el inconveniente de los "huecos" en la lista. El truco está en empezar a "buscar" desde abajo (desde la última celda de Excel de una columna determinada):

Sub PrimeraCeldaConDatosDesdeAbajo()
'AVERIGUAMOS CUANTAS FILAS TIENE EXCEL (SEGÚN VERSIÓN, TENDRÁ MÁS O MENOS)

    UltimaFila = Range("A:A").Count'DESDE LA ÚLTIMA CELDA, BUSCAMOS LA PRIMERA CELDA QUE TIENE DATOS EN LA LISTA
     Range("A" & UltimaFila).End(xlUp).Select
End Sub


2) Utilizar la versión "solo texto" de algunas funciones 

Cuando queremos utilizar alguna de las funciones nativas que tiene VBA, si lo vamos a hacer exclusivamente con textos, podemos utilizar la versión óptima de la función.

Las siguientes funciones...

Left(), Mid(), Right(), Chr(), ChrW(), UCase(), LCase(), LTrim(), RTrim(), Trim(), Space(), String(), Format(), Hex(), Oct(),Str(), Error

... son más lentas que sus correspondientes versiones que sirven para utilizar exclusivamente con textos....

Left$(), Mid$(), Right$(), Chr$(), ChrW$(), UCase$(), LCase$(), LTrim$(), RTrim$(), Trim$(), Space$(), String$(), Format$(), Hex$(), Oct$(),Str$(), Error$

3) Definir constantes cuando hay textos o valores que se repiten mucho

Si hay textos o valores en nuestro código que se repiten mucho a lo largo de nuetro poryecto, podemos incluirlas en una constante. Las constantes almacenan valores que, como su nombre indica, permanecen iguales durante la ejecución de una aplicación. A diferencia de las variables, cuando se declara una constante se le debe asignar su valor en ese momento:

Const NombreDeConstante As TipoDato = ValorConstante

Por ejemplo, si sabemos que en nuestro código se repite constantemente dentro de los cuadros de mensaje (MsgBox) la frase: "¿Está seguro de continuar con el proceso?. Si no lo está, haga clic en el botón 'Cancelar'. Si desea continuar, haga clic en 'Aceptar'." , podemos incluirla en una variable mucho más corta que será gestionada mucho mejor por el usuario y por VBA:

Sub FuncionesDeTextoOptimas()
    Const PreguntaAceptar As String = "¿Está seguro de continuar con el proceso?. Si no lo está, haga clic en el botón 'Cancelar'. Si desea continuar, haga clic en 'Aceptar'."""
    MsgBox PreguntaAceptar
End Sub


Si sabemos que vamos a necesitar la constante en varios procedimientos del módulo, debemos declararla al principio del módulo, en la zona de declaraciones:

 

Si sabemos que vamos a necesitar la constante en cualquier módulo de la aplicación, debemos declararla al principio de algún módulo, en la zona de declaraciones y con la palabra Public delante:



José Manuel Pomares Medrano






miércoles, 6 de julio de 2016

Cómo optimizar la velocidad de nuestras macros en Excel (4 de 5)


Es turno del 4º capítulo de esta serie de 5 que iniciamos hace unos meses. En este caso traemos otras 3 formas de optimizar nuestras macros, sobre todo en cuanto a eficiencia en velocidad.


1) Rellenar rangos de celdas con datos modificados de otros rangos (utilización de bucles, arrays y variables de valor)

Imaginemos que necesitamos rellenar las celdas de un rango determinado con los datos que hay en otro rango. Esto, en principio, se podría hacer simplemente copiando y pegando el rango original en el rango destino. Incluso se podría hacer (si no necesitamos que se copien los formatos y los rangos tienen el mismo tamaño) igualando los rangos con el simple código RangoDestino = RangoOrigen. Ya vimos estos ejemplos en el punto nº 3 del post Cómo optimizar la velocidad de nuestras macros en Excel (4 de 5).

Pero... ¿qué ocurre si los datos que deben ser rellenados en el rango destino tienen que sufrir alguna operación matemática o cualquier otra modificación, respecto de los datos originales?. En este caso, todo cambia, puesto que la modificación de cada dato dentro de un rango es un problema a solucionar y se puede hacer de varias formas. Algunas más rápidas que otras.

Veamos (con varias formas de resolución) un ejemplo de utilidad que copia los valores contenidos en un rango (desde H1 hasta H5000) y los traslada a otro rango (desde A1 hasta A5000) ... pero... cambiando cada valor multiplicándolo por si mismo

MEDIANTE UN BUCLE FOR... NEXT

Sub RellenarConBucle()
'SE DECLARA LA VARIABLE DEL CONTADOR
    Dim i As Integer
'SE COLOCA EN CADA CELDA-DESTINO EL VALOR DE CADA CELDA-ORIGEN, SEGÚN LA OPERACIÓN DESEADA
    For i = 1 To 5000
       CeldaActualOrigen = Cells(i, 8)
       Cells(i, 1) = CeldaActualOrigen * CeldaActualOrigen
    Next i
End Sub


Este método es el más lento de todos, puesto que el tiempo de ejecución (en mi ordenador) es de unos 0,300 sg. El motivo de este lapso de tiempo relativamente largo es que el código actúa directamente sobre celdas de Excel durante el bucle... y no sobre variables en memoria RAM. La ventaja es que hay menos código y más sencillo.

MEDIANTE UN ARRAY (arreglo o matriz)

Sub RellenarConArray()
'SE DECLARA LA VARIABLE PARA EL BUCLE, UN ARRAY Y LA VARIABLE OBJETO
    Dim i As Integer
    Dim miArray(5000, 1)
    Dim miRango As Range
    Set miRango = Range("H1").CurrentRegion
'SE COLOCAN, UNO A UNO Y SEGÚN LA OPERACIÓN DESEADA, LOS DATOS DEL RANGO EN UN ARRAY
    For i = 1 To 5000
        miArray(i, 1) = miRango(i, 1)
        miArray(i, 1) = miArray(i, 1) * miArray(i, 1)
    Next i
'SE TRASPASAN DE UNA VEZ LOS DATOS DESDE EL ARRAY HASTA EL RANGO
    Range("A1:A5000") = miArray
End Sub


Este método es el más formal y para algunos casos podría tener alguna ventaja, pero su ejecución es de unos 0,023 sg. (unas 13 veces más rápido que el método del bucle).

MEDIANTE UNA VARIABLE DE VALOR

Sub RellenarConVariable()
'SE DECLARAN LAS VARIABLES DEL CONTADOR Y RANGO ORIGEN (ESTA ÚLTIMA COMO VARIABLE DE VALOR, TIPO VARIANT)
    Dim i As Long
    Dim miRango As Variant
'SE COLOCAN DE UNA VEZ LOS DATOS DEL RANGO-ORIGEN EN UNA VARIABLE DE VALOR (NO DE OBJETO)
    miRango = Range("H1:H5000").Value
'SE MODIFICAN, SEGÚN LA OPERACIÓN DESEADA, LOS DATOS QUE ESTÁN EN LA VARIABLE DE VALOR (ESTARÁN EN FORMA MATRICIAL)
    For i = 1 To 5000
        miRango(i, 1) = miRango(i, 1) * miRango(i, 1)
    Next i 
'SE TRASPASAN DE UNA VEZ LOS DATOS DESDE LA VARIABLE HASTA EL RANGO-DESTINO
    Range("A1:A5000") = miRango
End Sub


Este método es el más rápido (unos 0,012 sg. en mi equipo. Es decir, casi 30 veces más rápido que el sistema que utiliza solo un bucle For Next y prácticamente el doble de rápido que el sistema que aboga por utilizar un Array), puesto que no se manejan Arrays ni dimensiones y en su lugar lo que se hace es:
  1. Colocar en una variable de valor declarada como tipo "Variant" (NO una variable de objeto) el rango de datos original. El resultado es que los datos se colocan en forma de matriz en la variable y el resultado es similar a la utilización de un Array (se puede hacer referencia a ellos mediante el índice de cada dimensión).
  2.  Una vez que los datos están en una variable (en la RAM) su modificación es mucho más rápida, por lo tanto es en este momento cuando nos disponemos a multiplicar por si mismo cada valor.
  3. Se traspasan, de una vez, todos los datos de la variable-matriz hasta el rango-destino.
2) Utilizar, cuando sea posible, el bucle For Each...Next para recorrer colecciones, en vez del bucle For...Next

El siguiente bucle (que rellena con un texto un rango de 500.000 celdas)...


Sub RecorrerConIndice()'SE DECLARAN LAS VARIABLES DEL ÍNDICE Y DEL RANGO A RELLENAR
    Dim i As Double
    Dim miRango As Range
    Set miRango = Range("A1:A500000")   
'SE RECORRE EL RANGO MEDIANTE EL ÍNDICE DE CADA CELDA
     For i = 1 To miRango.Count
        miRango(i, 1) = "Prueba 1"
    Next i
End Sub


... es casi un 4% más lento que el siguiente bucle...


Sub RecorrerColeccion()'SE DECLARAN LA VARIABLES PARA CADA CELDA DEL RANGO Y LA VARIABLE DEL RANGO A RELLENAR
    Dim miCelda As Range
    Dim miRango As Range
    Set miRango = Range("A1:A500000")
'SE RECORRE LA COLECCIÓN DE CELDA EN CELDA ASIGNANDO VALOR
     For Each miCelda In miRango
       miCelda = "Prueba 2"
    Next miCelda
End Sub


3) Utilizar el método SpecialCells para hacer referencia a celdas visibles o con una característica y/o valor concreto

Supongamos que tenemos un rango de filas filtradas (o con algunas de ellas ocultas) y necesitamos actuar sola y precisamente sobre las celdas que quedan visibles. Aunque este objetivo se puede conseguir con otros métodos distintos a la utilización del método SpecialCells (por ejemplo con Tablas de Excel), no siempre tendremos los datos en un formato así. Además, el método SpecialCells realiza otras acciones con otras constantes que presentaremos al final.

Veamos esta macro:

Sub SpecialCells()
'SE DECLARA UNA VARIABLE PARA EL RANGO A TRATAR
    Dim miRango As Range
    Set miRango = Range("A1:A10")
'SE COPIA AL PORTAPAPELES EL CONTENIDO DE LAS CELDAS QUE ESTÁN VISIBLES (EN UN RANGO FILTRADO, POR EJEMPLO)
    miRango.SpecialCells(xlCellTypeVisible).Copy
'SE PEGA EL CONTENIDO DE LAS CELDAS COPIADAS EN OTRO RANGO
    Range("C15").PasteSpecial xlPasteValues
End Sub


Como ya se ha comentado, lo que realiza este procedimiento es copiar el contenido de las celdas que quedan visibles en un rango que tiene aplicado un filtro (o que tiene algunas filas ocultas) y después pega el contenido en otro rango. Esto se hace gracias al método:

expresión.SpecialCells(Type, Value)

... que nos devuelve un rango de celdas que tienen unas características y un tipo valor determinado. Como podemos intuir, los argumentos Type y Valor (el segundo es opcional) sirven para determinar la característica de celda que queremos y el tipo de valor que contiene, respectivamente. Las constantes de las que disponemos para el primer argumento son:


Solo cuando el primer argumento es xlCellTypeConstants   o   xlCellTypeFormulas podemos utilizar el segundo argumento, que tiene las siguientes posibilidades:


Por ejemplo, si necesitamos seleccionar todas las celdas de un rango que contengan valores de texto, podemos utilizar la siguiente macro:

Sub SpecialCells()
'SE DECLARA UNA VARIABLE PARA EL RANGO A TRATAR
    Dim miRango As Range
    Set miRango = Range("A1:A10")
'SE SELECCIONAN LAS CELDAS QUE TIENEN CONSTANTES (NO FÓRMULAS) Y CUYO VALOR ES DE TIPO TEXTO (xlTextValues)
    miRango.SpecialCells(xlCellTypeConstants,
xlTextValues).Select
End Sub




José Manuel Pomares Medrano






viernes, 8 de abril de 2016

Cómo optimizar la velocidad de nuestras macros en Excel (3 de 5)


Vamos a abordar en este tercer artículo de la serie, otras 5 formas de optimizar nuestras macros en VBA para Excel.



1) Declarar variables con tipos de datos concretos

Al declarar una variable, debemos declarar las variables asignándole el tipo de dato que contendrá. Es decir, si necesito una variable llamada i para un bucle, debo asignarle un tipo de dato a la declaración. En este caso sería una variable con números enteros:

Sub Variable_i ()
   Dim i As Integer
   For i = 1 To 5
   ... mi código...
   Next
End Sub

Si hubiéramos declarado la variable tan solo escribiendo  Dim i    o si no la hemos declarado, VBA asignará por defecto el tipo de variable Variant, para la que será necesario reservar y utilizar 16 bytes en la memoria de nuestro equipo. En su lugar, agregando   As Integer  a la declaración, se reservarán y utilizarán tan solo 2 bytes en memoria. Esta utilización, repetida muchas veces (como puede ser el caso de un bucle) hará que nuestra macro pueda ejecutarse más lenta o más rápida, dependiendo de los bytes reservados en memoria para la variable.

Cuidado con declarar varias variables a la vez de esta forma...

Dim i, f, t, x as Integer

... puesto que así, solo hemos asignado un tipo de datos a la última variable de la lista. Para aprender más sobre cómo declarar variables, haga clic en el siguiente enlace: Declaración de variables en VBA para Excel (I)

2) Utilizar "Select Case" en vez de "IF... Then..."

En vez del código:

Sub MiMacro ()
   IF Range("A1") = 100 Then
       Range("A2") = 200
   End IF
   IF Range("A1") = 200 Then
       Range("A3") = 400
   End IF 
   IF Range("A1") = 300 Then
       Range("A4") = 600
   End IF
End Sub

Es más óptimo utilizar el siguiente código:

Sub MiMacro ()
   Dim MiValor As Integer
   MiValor = Range("A1")

   Select Case MiValor
      Case 100
         Range("A2") = 200
      Case 200
         Range("A3") = 400
      Case 300
         Range("A4") = 600
   End Select
End Sub

El motivo es que la instrucción Select Case solo evalúa hasta el caso en el que es verdadero y después termina de evaluar. Sin embargo, con IF...THEN... , VBA debe entrar a valorar todas las expresiones. No debería hacer falta decir que es conveniente situar en primera posición la opción que tiene más posibilidades de cumplirse.

3) Limpiar las variables objeto al final de cada macro

Si utilizamos variables de tipo objeto es conveniente "limpiarlas" al final de la macro asignándoles un valor "Nothing". Esto se puede ver aquí:

Sub MiMacro ()
   Dim MiRango As Range
   Set MiRango = Range("A1")
    ..... mi código ....
   Set MiRango = Nothing

End Sub

4) Escribir macros que están relacionadas entre si, en el mismo módulo

Si tenemos una macro que nos remite a otra y esta a su vez remite a otra.... sería conveniente que todas las macros que van a ejecutarse de forma sucesiva estuvieran en el mismo módulo. De esta forma, VBA no tendría que cargar en memoria varios módulos durante la ejecución.

De hecho, también es conveniente que durante el trabajo cotidiano con nuestra aplicación en Excel no permanezcan abiertos módulos del editor de Visual Basic. Por supuesto, también es mejor que ni siquiera el editor de VBA esté abierto.

5) Precalcular con variables todo lo que esté dentro de un bucle

En vez del siguiente código...

Sub MiMacro ()

   Dim i As Integer
   For i = 1 To 5000
     Cells (i,3) = Range("B7")*459
   Next

End Sub

...utilizar el siguiente código...

Sub MiMacro ()
   Dim i As Integer
   Dim Operacion as Double
   Operacion = Range("B7")*459
   For i = 1 To 5000
     Cells (i,3) = Operacion
   Next

End Sub

... en el que podemos ver que la operación que hay dentro del bucle la calculamos con anterioridad y depositamos el valor en una variable (Operacion). De esta forma, VBA no tiene que realizar 5000 veces el cálculo, sino que solo tiene que transferir el resultado de la memoria a la celda Cells(i,3).


José Manuel Pomares Medrano















jueves, 4 de febrero de 2016

Cómo optimizar la velocidad de nuestras macros en Excel (2 de 5)


Contiuamos con nuestra serie de artículos dedicados a la optimización de código Visual Basic for Applications para Excel. En este caso, serán también cinco formas que hacer que nuestras macros funcionen de forma mas fluida y óptima.


1) Utilizar bloques With para acortar el código

Si partimos del primer artículo de esta serie, en él podemos ver que al final habíamos reunido una buena serie de líneas de código que optimizaban la velocidad de ejecución de una macro. Esas líneas eran:

Sub MiMacro ()
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   Application.EnableEvents = False 
   Application.DisplayStatusBar = False
   ....mi código
   Application.Calculation = xlCalculationAutomatic
   Application.EnableEvents = True
   ActiveSheet.DsiplayPageBreaks = False
End sub ()

Podemos apreciar que de las siete líneas de código (siete, sin contar los protocolos de entrada y salida), seis comienzan con Application . Esto quiere decir que si utilizamos una forma de resumir todas esas palabras que se repiten, Visual Basic no tendrá que leerlas todas y se ejecutará más rápido. Esta forma es la siguiente:
Sub MiMacro ()
  
   With Application
      .ScreenUpdating = False
      .Calculation = xlCalculationManual
      .EnableEvents = False 
      .DisplayStatusBar = False
   ....mi código
      .Calculation = xlCalculationAutomatic
      .EnableEvents = True
      ActiveSheet.DsiplayPageBreaks = False
   En With

End sub ()

Donde podemos ver la estructura With.... End With que simplifica el código en buena medida. Imaginémonos un código donde lo que se repite es una cadena tan larga como...
Worksheets("Sheet1").Rectangles(1).Interior.Color = RGB(255, 255, 255)
Worksheets("Sheet1").Rectangles(1).Interior.Pattern = xlChecker
Worksheets("Sheet1").Rectangles(1).Interior.PatternColorIndex = 5

En este último caso, la optimización podría ser aún más espectacular:
With Worksheets("Sheet1").Rectangles(1).Interior
   .Color = RGB(255, 255, 255)
   .Pattern = xlChecker
   .PatternColorIndex = 5
End With
2) Evitar utilizar la propiedad Select (típica de la grabadora de macros)

La grabadora de macros es una herramienta estupenda para iniciarnos con el código VBA, pero hay que entender que es imposible que genere un código excelente, sobre todo si pensamos en que se limita a traducir acciones de la interfaz de usuario a código.

Una de las señas de identidad de la grabadora de macros es la utilización de la propiedad Select aplicada normalmente a rangos de celdas y otros objetos. Esta utilización repetida de Select es debidio a que la grabadora reproduce nuestras acciones.... y como usuarios, no tenemos más remedio que seleccionar todo para poder hacer algo con la celda, gráfico, imagen, etc. que queramos. Lo que ocurre es que, seleccionar todo aquello sobre lo que queremos actuar no es algo obligatorio en VBA. Al contrario, por medio de código podemos aplicar métodos y propiedades sobre los objetos sin la necesidad de seleccionarlos previamente.

El siguiente es un trozo de código típico de una macro grabada:

    Range("B2").Select
    Selection.ClearContents


...que podría optimizarse así:

    Range("B2").ClearContent

Hemos ahorrado una línea de código evitando decir a VBA que seleccione la celda y que después, con la selección efectuada, limpie (borre) el contenido. Esta situación la encontraremos en muchos casos. Siempre que veamos una combinación Select y después Selection, podemos hacer esta "reducción".

3) Copiar-pegar adecuadamente y cuando sea estrictamente necesario

Algunas de las acciones más utilizadas en macros son, precisamente, "Copiar" y "Pegar". Sobre ellas tenemos que hacer una distinción:
1) SI NO NECESITAMOS COPIAR Y PEGAR LOS FORMATOS Y FÓRMULAS DE LAS CELDAS:
En este caso, en vez de copiar y pegar valores, es más óptimo utilizar simplemente el signo igual. Por ejemplo, en vez de esta macro grabada...

Range("B2").Selec
Selection.Copy
Range("D2").Select
ActiveSheet.Paste

...utilizaremos este código escrito...

Range("D2") = Range("B2")

Como podemos imaginar, este código es muuuuucho más rápido (lo notaremos especialmente si esta operación se repite muchas veces en nuestra macro) y también mucho más claro a la hora de entenderlo.

También es posible copiar y pegar rangos que tengan más de una celda. Por ejemplo:

Range("D2:E4") = Range("B2:C4")

Aunque... POR SUPUESTO... los rangos deben tener un mismo tamaño.
Tampoco debemos olvidar que si el copiado - pegado se debe hacer en hojas distintas a la que está activa en el momento de ejecutar el código debemos hacer una referencia más completa a ellas. Por ejemplo:

Sheets("Hoja2").Range("D2:E4") = Sheets("Hoja3").Range("B2:C4")

El código de arriba se podría ejecutar teniendo activa cualquier hoja en el momento de ejecución del código.

2) SI NECESITAMOS COPIAR Y PEGAR LOS FORMATOS Y FÓRMULAS DE LAS CELDAS:
En vez del código...

Range("B2").Selec
Selection.Copy
Range("D2").Select
ActiveSheet.Paste

...utilizaremos este código escrito...

Range("B2").Copy Destination:= Range("D2")

Como podemos ver, además de prescindir de la propiedad Select, también hemos aprovechado el parámetro Destination del método Copy. Con una sola línea tenemos un código equivalente a cuatro líneas. Eso sí, con esta forma de proceder no tenemos más remedio que pegar todo lo copiado y no podemos discriminar solo valores (por ejemplo).

También debemos recordar que si el rango de origen o el rango de destino no están en la hoja que VBA considera como activa en el momento de ejecutar el código, debemos hacer una referencia más completa a los rangos. Por ejemplo:
Sheets("Hoja2").Range("B2").Copy Destination:= Sheets("Hoja3").Range("D2")

4) Limpiar el portapapeles después de copiar

Si finalmente hemos tenido que utilizar el método Copy en nuestro código, sería conveniete que al final de la macro insertáramos la línea...

Application.CutCopyMode = False

... de esta forma limpiamos la memoria RAM ocupada por lo que se ha copiado. Con los métodos explicado en el apartado 3) de este artículo, Excel no utiliza el portapapeles como lugar de intercambio o "buffer", pero si hemos utilizado Copy, sí es conveniente vaciar el portapapeles, puesto que sí se ha utilizado.

5) Utilizar la función Lenb( ) o la constante vbNullString en vez de "" cuando nos referimos a celdas vacías

Cuando queremos provocar que una celda esté vacía. Lo podemos hacer mediante...
 Range("B2") = ""  (por ejemplo)
... pero hay una forma más rápida:
Range("B2") = vbNullString 
El motivo es que VBA procesa más rápido su constante vbNullString que la cadena vacía "". En otras palabras, con vbNullString, VBA considera que no existe ninguna cadena de texto, sin embargo, con "" considera que existe una cadena de texto vacía (no es lo mismo tener una caja vacía... que no tener ni siquiera la caja).

PARA COMPROBACIONES DE CADENAS VACÍAS:
Otro ejemplo de referencia a cadenas vacías es cuando queremos comparar. Por ejemplo, el siguiente código...

If Range("B2") <> ""
...es más lento que ....

If LenB(Range("B2")) <> 0

El motivo es que VBA procesa y compara más rápidamente solo el resultado de la función LenB (LARGOB) que toda la cadena de texto que pudiera haber en Range("B2").

La función de VBA LenB (LARGOB como equivalente en funciones Excel) es la "hermana" de la función de VBA Len (LARGO como equivalente en funciones Excel). Mientras que la primera devuelve el número de caracteres de una cadena de texto, la segunda devuelve el número de bytes usados para representarlos.

Por cierto, es más rápida la comparación <> (distinto que...) que la comparación > (mayor que...). El motivo es que en el primer caso, para VBA es más sencillo detectar si un valor es sencillamente distinto que otro, que calcular si un valor es mayor o menor que otro. Por lo tanto, si no es imprescindible comparar el valor, sino que es suficiente con saber si es o no igual, utilizaremos la comparación <>.



José Manuel Pomares Medrano






lunes, 1 de febrero de 2016

Cómo optimizar la velocidad de nuestras macros de Excel (1 de 5)


Comenzamos con este artículo una serie de cinco que tendrán como hilo conductor un tema muy demandado por usuarios intermedios y avanzados de Excel. Se trata de la optimización de la velocidad de ejecución de nuestras macros (código VBA).

 

Cuando un usuario ya ha aprendido a grabar sus propias macros, se enfrenta a un siguiente paso: personalizar el código de las macros grabadas. Esta personalización irá encaminada en un principio a acomodar los procesos que ejecuta el código a las necesidades del propio usuario, añadiendo líneas de código que ejecuten acciones que normalmente no es capaz de grabar la grabadora de macros de Excel.

Pero también hay otra vertiente relativa a la personalización de macros: la optimización de la velocidad. Es decir, intentar que el código se ejecute de forma más rápida. Esto no tendrá mucha importancia cuando nuestras macros son muy sencillas y cortas, pero si en ellas se reflejan multitud de procesos que requieren muchos recursos de nuestro ordenador (activar hojas distintas, copiar y pegar mútiples veces, abrir y cerrar otros libros, realizar cálculos complejos, aplicar muchos formatos, etc...), será necesario aplicar algo de ingenio y conocimiento de ciertas técnicas para que el código se ejecute de forma fluida y rápida.

Comenzamos en esta primera entrega de una serie de varios artículos, con cinco trucos o técnicas que harán nuestro código más rápido:

1) Evitar que todas las acciones de la macro se vayan representando en la pantalla

Si dejamos que cada acción que ejecuta una macro se refleje en la pantalla, nuestro equipo tendrá que aplicar muchos recursos y tiempo al "refresco" o actualización de la pantalla. Para evitar esto utilizaremos la propiedad ScreenUpdating aplicada al objeto Application (Excel) en una línea de código al principio de nuestro código:

Por ejemplo:

Sub MiMacro ()
   Application.ScreenUpdating = False
   ....mi código
End sub ()

Con ello, establecemos la capacidad de Excel de refresco de panatalla como "False" (No). Además, hay que decir que no es necesario volver a cambiar en la última línea de la macro el valor a "True" (Sí), debido a que automáticamente lo hace Visual Basic cuando la macro (o suscesión de macros que se llaman entre sí) termina o cuando se produce un error.

2) Evitar que Excel recalcule todas las celdas de nuestro libro cuando no es necesario

En muchas macros (la mayoría), se ejecutan acciones que provocan que Excel haga un recálculo de todas las fórmulas que pudiera haber en el archivo (siempre que Excel esté configurado con "Cálculo automático"). Entre otras, esas acciones que provocan un recálculos son:
  • Introducir o actualizar un dato (ya sea desde teclado o desde código VBA) en cualquier celda
  • Eliminar o insertar filas
  • Filtrar datos
  • Hacer doble clic entre los títulos de una hoja para autoajustar el ancho o alto de columnas o filas, respectivamente
  • Agregar, editar o eliminar un nombre de rango definido
  • Cambiar el nombre de una hoja
  • Cambiar la posición de una hoja (moverla)
  • Ocultar o mostrar filas (no se produce recálculo si ocultamos o mostramos columnas)
También hay que decir que Excel realiza un recálculo inteligente. Es decir, solo recalcula las celdas con fórmulas que están afectadas por un cambio.

Para conseguir que nuestras macros no provoquen recálculos sin necesidad de que los haya, debemos insertar al principio de nuestro código la línea: Application.Calculation = xlCalculationManual
En estas condiciones, hasta que no volvamos a escribir una línea que reestablezca la situación original (Application.Calculation = xlCalculationAutomatic), Excel no recalcula nada. Por ejemplo, quedaría así:

Sub MiMacro ()
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   ....mi código
   Application.Calculation = xlCalculationAutomatic
End sub ()

Es posible que entre la línea de código que establece el recálculo a manual y la línea de código que lo devuelve a automático necesitemos que Excel calcule una celda o rango de celdas determinado. En este caso, podemos forzar un recálculo particular de ese rango, evitando que el resto del libro se actualice. Para lograr esto tenemos dos métodos:

Range.Calculate
Este método calcula el rango al que hacemos referencia y también calcula todas las celdas con fórmulas que dependan de dicho rango (aunque no pertenezcan a él).

Range.CalculateRowMajorOrder
Este método calcula el rango al que hacemos referencia, pero no calcula ninguna celda más (por lo tanto, es más rápido).

Es importante resaltar que es MUY CONVENIENTE escribir la última línea de código mencionada anteiormente (Application.Calculation = xlCalculationAutomatic), puesto que si no lo hiciéramos, nuestro Excel permanecerá sin recálculo automático aunque cerremos el archivo y abramos una nueva sesión con otro.

3) Evitar que se ejecute el código VBA que hemos escrito en eventos de hoja o de libro

Es posible que tengamos código dentro de algunas hojas de nuestro libro o incluso en el propio libro de Excel (ThisWorkbook). Este código responderá a ciertos eventos (según hayamos elegido al escribirlo).

Por ejemplo, si en una hoja tenemos un determinado código que se ejecuta con el evento Worksheet_Change (cuando ocurre un cambio en alguna celda), quiere decir que el código entrará en funcionamiento cuando modifiquemos alguna celda de esa hoja... pero claro, cuando la modifiquemos introduciendo un dato "manualmente" Y TAMBIÉN si una macro genera dicha modificación.

Por lo tanto, se nos presenta la necesidad de evitar que el código asociado a eventos se ejecute cuando no es necesario. Para ello, podemos utilizar la propiedad EnableEvents del objeto Application. El código podría quedar así:

Sub MiMacro ()
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   Application.EnableEvents = False
   ....mi código
   Application.Calculation = xlCalculationAutomatic
   Application.EnableEvents = True
End sub ()

Vemos cómo la propiedad EnableEvents tiene dos valores posibles (False y True) debiendo utilizar un línea de código al final de la macro para reestablecer el valor a "True", puesto que si no se hiciera, los eventos dejarían de funcionar hasta que Excel se reiniciara.

4) Evitar que Excel muestre los saltos de página después de algunas acciones

Excel se ve obligado a mostrar los saltos de página después de las siguientes acciones:

  • Al mostrar una vista previa de impresión
  • Al hacer mostrar el cuadro de diálogo "Configuración de página"
  • Al modificar mediante VBA cualquier configuración de página
  • Al configurar la opción "Mostrar saltos de página" desde Archivo/Opciones/Avanzadas
Para evitar que se muestren estas líneas discontinuas que representan a los saltos o cambios de página en una hipotética impresión, podemos hacerlo manualmente, pero también mediante una macro insertando la línea de código:  ActiveSheet.DisplayPageBreaks = False   al final de la macro (para que "borre" los saltos de página. Un ejemplo sería este:

Sub MiMacro ()
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   Application.EnableEvents = False
   ....mi código
   Application.Calculation = xlCalculationAutomatic
   Application.EnableEvents = True
   ActiveSheet.DisplayPageBreaks = False
End sub ()

Después de que esta macro se ejecute ActiveSheet.DisplayPageBreaks = False deja de estar vigente y automáticamente, Excel mostrará los saltos de página la próxima vez que ocurra uno de los hechos que provocan la visualización de estas líneas. Por lo tanto, para evitar verlos, nuevamente, deberemos desactivarlos manualmente o bien con una nueva macro.

5) Evitar que Excel muestre la barra de estado

Tanto la anterior medida (ActiveSheet.DisplayPageBreaks = False) como la que vamos a comentar ahora, no son de las que más ahorro de tiempo generan, pero como dice el refrán: "Toda piedra hace pared".

Se trata de ocultar la barra de estado de Excel si no va a ser necesaria. Simplemente se hace introduciendo esta línea de código: Application.DisplayStatusBar = False


Quedaría así:

Sub MiMacro ()
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   Application.EnableEvents = False 
   Application.DisplayStatusBar = False
   ....mi código
   Application.Calculation = xlCalculationAutomatic
   Application.EnableEvents = True
   ActiveSheet.DisplayPageBreaks = False
End sub ()

La barra de estado permanecerá oculta para la sesión de Excel que tenemos abierta (para cualquier libro que haya abierto), pero volverá a visualizarse al reiniciar una sesión de Excel. Esto quiere decir que si deseamos que siempre esté oculta cuando un determinado libro esté abierto, debemos escribir el código en el evento "Open" (estableciendo el valor como "False".

José Manuel Pomares Medrano





sábado, 16 de enero de 2016

Cómo personalizar el menú del botón derecho de Excel con VBA


Todos conocemos (y además usamos con mayor o menor frecuencia) el menú contextual que obtenemos al hacer clic en una celda de Excel. En la versión 2016, tiene el siguiente aspecto:



En este post, vamos a plantearnos modificar este menú contextual cuando trabajamos con una hoja de cálculo determinada. Concretamente queremos que, dentro de esa hoja, al hacer clic con el botón derecho en las celdas A1 y C1, el menú contextual lo veamos personalizado con unos comandos que nosotros decidiremos y que, evidentemente, ejecutarán las acciones que nosotros queramos.

Para ello, no tenemos más remedio que escribir algo de código Visual Basic for Applications. Así pues, lo primero que debemos hacer es presionar la combinación de teclas ALT+F11 para que se ejecute el editor de VBA.

Escribimos el código que permite que, al hacer clic con el botón derecho del ratón, el menú contextual esté personalizado

Una vez dentro del editor de VBA, nos fijaremos en la parte superior izquierda del editor, en el panel "Explorador de proyectos". Ahí podremos ver los archivos (proyectos para VBA) de Excel que están abiertos y debemos identificar el nuestro y expandir el árbol de objetos si es que no lo está.



Como vemos arriba, dentro de "Microsoft Excel Objetos" vemos la Hoja1 que será nuestra hoja objetivo a la hora de personalizar el menú contextual del botón derecho. Debemos hacer doble clic en nuestra hoja objetivo y veremos como a la derecha tenemos una ventana en blanco. En ella debemos realizar dos selecciones en la parte superior:

 

Podemos borrar todo lo que vemos en la parte inferior de la imagen anterior:

Private Sub WorkSheet_SelectionChange(ByVal Target As Range)

End Sub

... y para que nuestro código se ejecute al de hacer clic con el botón derecho del ratón, entre la línea que comienza por Private Sub WorkSheet_BeforeRightClick (ByVal Target.... y la línea End Sub escribimos el siguiente código:


Dim NuevoComando As CommandBarButton

'LIMPIAMOS TODOS LOS COMANDOS Y REESTABLECEMOS ORIGINALES DE EXCEL

For Each AntiguoComando In CommandBars("cell").Controls
AntiguoComando.Delete
Next

CommandBars("Cell").Reset

'AÑADIMOS NUESTRO PRIMER COMANDO

If Not Intersect(Target, Range("A1")) Is Nothing Then
     Set NuevoComando = CommandBars("cell").Controls.Add
     With NuevoComando
         .Caption = "
Ejercutar macro 1"
         .OnAction = "MiMacro1"
         .BeginGroup = True
     End With
End If

'AÑADIMOS NUESTRO SEGUNDO COMANDO

If Not Intersect(Target, Range("C1")) Is Nothing Then
     Set NuevoComando = CommandBars("cell").Controls.Add
     With NuevoComando
         .Caption = "
Ejercutar macro 2"
         .OnAction = "MiMacro2"
         .BeginGroup = True
     End With
End If


Lo que hemos hecho hasta ahora es:

1) Activar la ventana de código y seleccionar el evento "BeforeRightClick" para escribir dentro de los protocolos de entrada y de salida que aparecen automáticamente (Sub.... y End Sub).

2) En el código que escribimos:
  • Escribir el código que declara una variable objeto (NuevoComando) que representará el nuevo comando que se añade posteriormente
  • Escribir la parte de código que limpia todos los comandos que pudiera haber anteriormente y volver a poner los comandos que por defecto muestra Excel al hacer clic con el botón derecho
  • Escribir la parte de código que agrega nuestro primer comando. Este código se inserta dentro de una estructura If... End If que condiciona la agregación del comando al hecho de hacer clic en el "Target" que hemos definido (A1)

  • Escribir la parte de código que agrega nuestro segundo comando. Este código se inserta dentro de una estructura If... End If que condiciona la agregación del comando al hecho de hacer clic en el "Target" que hemos definido (C1)

A estas alturas, lo que ocurre es que, si dejáramos esto así, al salir de nuestra hoja o al salir de Excel, esta configuración del menú del botón derecho del ratón permanecería para siempre en nuestro Excel... y eso no es, muy probablemente, lo que queremos. Así pues, debemos volver a seleccionar en la parte superior lo siguiente (teniendo el cursor en la línea siguiente a la última del código que hay hasta el momento. Es decir, después de "End Sub"):

 

En medio de las instrucciones que aparecen automáticamente (y que podemos ver en la imagen anterior, debemos escribir:

CommandBars("Cell").Reset


Escribimos el código de las macros que se ejecutarán al seleccionar los comandos personalizados de nuestro menú contextual

De nuevo en el explorador de proyectos debemos asegurarnos de que hay un módulo disponible para poder escribir el código de nuestras macros a ejecutar.

En la imagen siguiente vemos que ya está el Módulo 1:



Pero si no tuviéramos ningún módulo, lo podríamos añadir desde el menú "Insertar" / Módulo:



Cuando ya lo tengamos, hacemos doble clic sobre él y a la derecha aparecerá una ventana para escribir código, donde tendremos que escribir lo siguiente:

Sub MiMacro1()
     MsgBox "Se ha ejecutado mi macro 1"
End Sub

Sub MiMacro2()
    MsgBox "Se ha ejecutado mi macro 2"
End Sub

(Veremos que aparece una línea divisoria automáticamente)


Este código corresponde a dos macros de prueba que muestran un cuadro de mensaje para comprobar que se han ejecutado. Evidentemente, el usuario sustiruirá estas macros por las que realmente necesite ejecutar.

Ahora, ya estamos en disposición de comprobar el correcto funcionamiento de todo lo realizado, desde Excel. Es decir, haciendo clic con el botón derecho en cualquiera de las celdas que definimos al principio (A1 y C1):





Otras opciones

VER SOLO NUESTROS COMANDOS, NO LOS DE EXCEL

En al caso de que no deseemos que aparezcan los comandos propios de Excel y tan solo queremos ver nuestro comando, tenemos que quitar del código escrito dentro del evento "BeforeRightClick" la línea:

CommandBars("Cell").Reset

¡¡¡Insistimos!!! hay que eliminar la línea que hemos escrito dentro del evento  "BeforeRightClick"  ... ¡¡NO eliminar esa misma línea que también escribimos dentro del evento "Worksheet_Deactivate"!!

El resultado de eliminar esa línea es el siguiente:

 

Donde vemos que en el menú contextual aparece solo el comando que activa nuestra macro.

CON UN CLIC EN CUALQUIER CELDA, VER TODOS NUESTROS COMANDOS A LA VEZ SIEMPRE PARA PODER ELEGIR LA ACCIÓN:

En este caso, sea la celda que sea, al hacer clic con el botón derecho, veremos todos los comandos a la vez para poder elegir el que necesitemos. Para conseguirlo, debemos eliminar en el código las estructuras condicionales que comienzan por If y terminan por End If: 
 
 

 Siendo el resultado el siguiente:


 
 
Para finalizar, no hay que olvidar que el archivo debe ser guardado como... "Libro de Excel habilitado para macros (*.xlsm).  En el archivo que es posible descargar a continuación, podemos ver (incluso copiar) todo el código necesario, además de comprobar su funcionamiento:


https://www.dropbox.com/s/zaqyqqhptls1690/Personalizar%20bot%C3%B3n%20derecho.xlsm?dl=0

El espacio de Mac

En cuanto a Excel 2011 para Mac, no hay diferencias en el código a escribir. Tan solo podemos remarcar las diferencias que hay a la hora de activar el editor de VBA. Concretamente, debemos activar la ficha Programador (Menú Excel / Preferencias / Cinta... y activar la casilla "Programador".


Después de aceptar, veremos la nueva pestaña en la cinta y activándola podremos acceder al editor de VBA:



José Manuel Pomares Medrano









martes, 24 de noviembre de 2015

Cómo imprimir correctamente con Excel en 3 pasos




 ¿A quién no le ha pasado alguna vez que, tras hacer clic en el botón "Imprimir" ha visto a parecer un cuadro de diálogo en la pantalla que dice...


... es entonces cuando nos llevamos las manos a la cabeza y pensamos ¡¡¡¿¿¿ 121 páginas van a imprimirse???!!!

Si ocurre esto y verdaderamente no queríamos imprimir 121 páginas, quizá lo que ocurra es que no hemos configurado bien la impresión de nuestra hoja de Excel. Si queremos imprimir en Microsoft Excel necesitamos configurar algunos aspectos clave para que el programa conozca, al menos, tres cosas importantes:
  1. ¿Qué celdas queremos imprimir ?
  2. ¿Qué queremos que aparezca en cada hoja impresa?
  3. ¿Qué márgenes necesitamos en cada hoja impresa?
Por lo tanto, esta cuestión se convierte en vital pero, a la vez, muy fácil de solucionar. Tan solo tenemos que cumplir los siguientes 3 pasos antes de hacer clic en el botón "Imprimir".

PASO 1: Establecer el área de impresión

El primer paso consiste en "decirle" a Excel dónde está el rango de celdas que queremos imprimir. Tenemos que tener en cuenta que en Excel hay más de 16.000 columnas y más de 1.048.000 filas que dan lugar a millones de celdas. ¿Por qué iba Excel a saber qué celdas, entre todos los millones existentes, son las que nosotros queremos imprimir?.

Así las cosas, el primer paso hacia la impresión es "Establecer el área de impresión". Esto se hace desde la ficha "Diseño de página":


Una vez hecho esto y, aunque parezca que no ha ocurrido nada, Excel ya sabe qué es lo que queremos imprimir.

PASO 2: Establecer la escala

Establecer la escala significa decirle a Excel cómo queremos repartir toda la zona o área de impresión en páginas. Excel nos ofrece 3 opciones preconfiguradas (en las versiones de Excel 2010, 2013 y 2016 se encuentran en la ficha Archivo / Imprimir) que se ajustarán a nuestras necesidades en la mayoría de los casos (además de "Sin escalado"):
  • Ajustar hoja en una página: sirve para que el área de impresión establecida en el paso 1 aparezca íntegramente en una página. Tenemos que tener en cuenta que si el área de impresión es muy extensa, cuando Excel obliga a comprimirla en una sola página, es posible que se vea muy pequeña
  • Ajustar todas las columnas en una página: sirve para que el ancho del área de impresión se ajuste al ancho de una página, haya las columnas que haya. También debemos tener en cuenta que si hay muchas columnas es posible que el tamaño se reduzca damasiado. Una primera solución a intentar sería cambiar la orientación del papel de "vertical" a "horizontal". En cualquier caso, esta es la solución que mejores resultados suele tener.
  • Ajustar todas las filas en una página: de forma similar a la primera de las opciones comentadas, todo el contenido del área de impresión se ajusta y comprime para que todas las filas estén visibles en una sola página... aunque haya columnas que deban imprimirse en otras páginas.



PASO 3: Establecer los márgenes adecuados

Como última configuraciòn recomendable está la configuración de los márgenes. Es decir, el espacio que hay entre el texto y el borde de la página impresa.

La configuración de los márgenes podemos conseguirla por varios caminos. Pero uno de los más directos es desde la ficha "Diseño de página" y después desplegando el comando "Márgenes"...



 ... tan solo queda elegir la opción deseada.

Está claro que hay más configuraciones posibles (y en algunos casos necesarias) como el tamaño del papel y la orientación (vertical u horizontal), pero las 3 descritas en este artículo son las más importantes que no debemos obviar nunca si queremos que nuestras impresiones cumplan con unos mínimos.

José Manuel Pomares Medrano