miércoles, 5 de julio de 2017

La función SI de Excel



Me atrevería a decir que la función SI es la más utilizada en el ámbito de Excel. Esta función es capaz de realizar una comprobación (o prueba lógica) en nuestra hoja de cálculo y, dependiendo del resultado, nos devolverá algo (un valor, un texto o el resultado de una fórmula).


Para utilizar esta función, necesitamos cumplimentar sus tres argumentos separándolos mediante punto y coma. La estructura es la siguiente:


Por ejemplo, si necesitamos comprobar si el valor de una celda supera o iguala el valor de otra celda, podemos utilizar la función así:


POSIBILIDADES

Prueba lógica
En el espacio donde hay que escribir el primer argumento (la prueba lógica) necesitamos que se obtenga una respuesta VERDADERO o FALSO. Esta respuesta se puede obtener con una comparación (como podemos ver en la imagen de ejemplo anterior) con los operadores >, <, =, etc. Pero no es la única forma, sino que podemos utilizar, por ejemplo, otra función que nos devuelva cualquiera de los dos valores requeridos. Vemos un ejemplo de ello a continuación:


Observamos cómo la función ESERROR comprueba si en H19 hay un error y en caso de ser cierto, nos devuelve VERDADERO y... eso es precisamente lo que necesita la función SI en su primer argumento para decidir si tiene que devolver un 0 (según el ejemplo) o bien el propio valor de H19.

Valor si falso, Valor si verdadero
En los argumentos segundo y tercero tenemos que decir a la función SI qué es lo que queremos que nos devuelva en el caso de que el resultado de la prueba lógica sea VERDADERO O FALSO. Para ello, podemos escribir un valor directamente (número, texto, fecha, etc.) o podemos hacer referencia al valor o resultado de otra celda mediante su referencia. Ejemplos de todo esto también los tenemos en la última imagen donde aparece la función =SI(ESERROR(H19);0;H19).

Hay que recordar que, en el caso de que queramos obtener un texto, este debe estar escrito entre comillas. Un ejemplo de ello lo tenemos en la imagen siguiente:


Incluso en cualquiera de estos argumentos podemos utilizar una segunda función SI anidada para que su resultado sea devuelto por la función principal (la que contiene o anida a la segunda). Un ejemplo de esto lo tenemos en la función que vemos en la siguiente imagen:


El objetivo de anidar la función SI de la imagen anterior es realizar una nueva prueba lógica en el caso de que la primera sea fallida (sea FALSO). De esta forma conseguimos 3 posibles respuestas:
  • Si la prueba lógica de la función SI principal da como resultado VERDADERO, obtenemos un 2 en la imagen del ejemplo...
  • ... en caso contrario, la función SI anidada entra en funcionamiento determinando si se devuelve:
    •  un 1....
    • ...o un 0 (cero)


José Manuel Pomares Medrano










domingo, 30 de abril de 2017

Agrupar tablas dinámicas por número de semana


Cualquier usuario de Excel que utilice frecuentemente tablas dinámcias sabe que es posible agrupar la información que estas ofrecen por periodos (minutos, horas, días, meses, trimestres, años).

Aunque de forma indirecta, también podemos agrupar los datos de una tabla dinámica por el nº de semana correspondiente. Esto es algo que, según he comprobado con mi experienccia al impartir clases de Excel a empresa, es más frecuente de lo que podamos pensar y... este será el tema de este post.


Como decíamos, es posible realizar este tipo de agrupación de forma indirecta. Es decir, creando previamente en la tabla de datos original un campo (columna) cuyas celdas contengan una función que nos calcule el número de semana.

Dicho gráficamente, tenemos que conseguir que el campo FECHA de nuestra tabla...


...tenga un campo compañero con el número de semana calculado....


Esto se puede conseguir si introducimos en cada celda de la tabla la fórmula...


... donde podemos ver concatenados mediante el símbolo ampersand (&):
  • La palabra "Semana " (con espacio al final y entre comillas)
  • La función NUM.DE.SEMANA que tiene dos argumentos separados por punto y coma:
    • En el primero de ellos debemos hacer referencia a la celda que tiene la fecha (la celda de la columna FECHA).
    • En el segundo argumento debemos escribir un código que determinará el sistema de numeración de semana que deseamos. Por ejmplo, el Sistema 2 que se ajusta a la norma ISO europea necesita el código 21 (como vemos en la imagen anterior). Si deseamos otro sistema de numeración de semana, para conocerlo debemos acudir a la página de ayuda de la función NUM.DE.SEMANA de Microsoft.

Una vez llegados aquí, en nuestra tabla dinámica solo tenemos que:
  • Actualizar (Alt+F5), para que el campo esté disponible en la lista de campos
  • Insertar el campo "SEMANA" (arrastrándolo, normalmente al cuadrante "Filas")


José Manuel Pomares Medrano








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