Archivos para julio, 2013

VBA: Maneras de acelerar nuestras macros de Excel.

Muchas veces nos hemos encontrado programando nuestras macros en Excel, y después de un tiempo incluyendo líneas y líneas de código, al ejecutarla, observamos como el tiempo de ejecución es extremadamente elevado, o muy lento (en ocasiones, incluso bloqueamos la aplicación).
En esta entrada contaré alguna de las buenas prácticas que agilizan y optimizan los tiempos de ejecución de nuestras macros… aunque a veces, ni yo mismo utilizo 😉

Mis 15 consejos, no necesariamente por orden de importancia, serían:
1. Usar la propiedad .ScreenUpdating al inicio y final de nuestra macro, para evitar la actualización de la pantalla cada vez que se realiza una acción sobre la hoja de cálculo:

view plainprint?

  1. Application.ScreenUpdating=False
  2. ‘… nuestro código VBA…
  3. Application.ScreenUpdating=True

2. Emplear la propiedad .Calculation para evitar el recálculo de las operaciones de nuestra hoja:

view plainprint?

  1. Application.Calculation=xlCalculationManual
  2. ‘… nuestro código VBA…
  3. Application.Calculation=xlCalculationAutomatic
  4. ‘o bien antes de cerrar el libro
  5. Application.CalculateBeforeSave=True

3. Cuando sea posible usar la instrucción WITH…END WITH:

view plainprint?

  1. With MyLabel
  2. .Height = 2000
  3. .Width = 2000
  4. .Caption = "Esta es mi etiqueta"
  5. End With

4. Liberar memoria cuando definamos variables con objeto:

view plainprint?

  1. Dim wsHoja as Worksheet
  2. ‘Definimos la variable objeto
  3. Set wsHoja = Hoja1
  4. ‘… nuestro código VBA…
  5. ‘Liberamos la memoria
  6. Set wsHoja = Nothing

5. Este no siempre es posible, pero cuando lo sea, mejor no emplear la instrucción IF… THEN…ELSE. Por ejemplo, en lugar de:

view plainprint?

  1. Dim booSiNo As Boolean
  2. Dim x As Long
  3. If x = 13 Then
  4. booSiNo = True
  5. Else
  6. booSiNo = False
  7. End If
  8. MsgBox booSiNo

podríamos usar:

view plainprint?

  1. Dim booSiNo As Boolean
  2. Dim x As Long
  3. booSiNo = (x = 13)
  4. MsgBox booSiNo

6. Hacer uso de las funciones estándar de Excel en VBA en lugar de contruir procesos que hagan lo mismo. Por ejemplo utilizar el siguiente código:

view plainprint?

  1. MiSuma=Application.WorksheetFunction.Sum(Range("B2:C10"))

en lugar de:

view plainprint?

  1. For Each celda In Range("B2:C10")
  2. MiSuma = MiSuma + celda.Value
  3. Next celda

7. Especificar/concretar lo máximo posible a la hora de definir variables, evitando las variables tipo Variant o Object. Por ejemplo:

view plainprint?

  1. Dim MiHoja As Worksheet
  2. ‘mucho mejor que…
  3. Dim MiHoja As Object
  4. ”””
  5. Dim filas As Long
  6. ‘mucho mejor que…
  7. Dim filas As Variant

8. No seleccionar un rango para trabajar sobre él, si no es estrictamente necesario:

view plainprint?

  1. Range("C13").Font.Bold = True
  2. ‘mucho mejor que…
  3. Range("C13").Select
  4. Selection.Font.Bold=True

9. Evitar Copiar y Pegar (Copy and Paste), empleando otras instrucciones similares, pero algo más eficientes. Por ejemplo, es mejor usar:

view plainprint?

  1. Range("A1:A13").Copy Destination:=Range("B1")
  2. ‘O si únicamente necesitamos los valores:
  3. Range("B1:B13").Value= Range("A1:A13").Value

10. Emplear los índices para los elementos de las colecciones, en lugar de los nombres que definen estos elementos. Por ejemplo:

view plainprint?

  1. Worksheets("Hoja3")
  2. ‘mejor usar
  3. Worksheets(3)

Ojo, por que por contra, estos índices pueden cambiar, lo que generaría problemas de identificación futuros…no hay nada perfecto, verdad?.

11. Otro caso muy frecuente, el uso de "" en lugar de la variable vbNullString, mucho más efectiva.
Mejor usar

view plainprint?

  1. If Range("A1").Value = vbNullString Then
  2. ‘…
  3. Else
  4. ‘…
  5. End If

en lugar de

view plainprint?

  1. If Range("A1").Value = "" Then
  2. ‘…
  3. Else
  4. ‘…
  5. End If

12. Otra forma muy útil cuando trabajamos con eventos sobre nuestras hojas o libros (o controles), desactivar los Events al inicio de nuestra programación y activarlos de nuevo al final:

view plainprint?

  1. Application.EnableEvents = False
  2. ‘… nuestro código VBA…
  3. Application.EnableEvents = True

13. Intentar reducir el número de líneas de código, empleando para ello la posibilidad de escribir en una misma línea usando el separador ‘:’ (dos puntos). Por ejemplo:

view plainprint?

  1. With Selection
  2. .WrapText = True: .ShrinkToFit = False
  3. End With
  4. ‘más rápido que…
  5. With Selection
  6. .WrapText = True
  7. .ShrinkToFit = False
  8. End With

14. Declarar las variables OLE directamente. Un ejemplo:

view plainprint?

  1. Dim xls As Excel.Application
  2. ‘mejor que…
  3. Dim xls As Object
  4. Set xls = CreateObject("Excel.Application")

15. Y uno más, el último pero no el menos importante (quizá sea el más utilizado). Cuando se haga necesario emplear bucles del tipo FOR, es más óptimo emplear FOR EACH…NEXT que FOR…NEXT cuando recorremos colecciones ‘indexadas’. Por ejemplo, es más rápido el primero que el segundo:

view plainprint?

  1. Dim wsHoja as Worksheet
  2. For Each wsHoja In Worksheets
  3. MsgBox wsHoja.Name
  4. Next wsHoja
  5. ‘más rápido que el siguiente..
  6. Dim i as Integer
  7. For i = 1 To Worksheets.Count
  8. MsgBox Worksheets(i).Name
  9. Next i

Por supuesto existen otras acciones que mejoran la eficiencia de nuestras macros, pero en algún punto hay que parar, y es que la optimización del VBA no tiene fin…

Anuncios

Excel

Publicado: 10 de julio de 2013 en Actualidad

VBA: Una función personalizada para mostrar el Número de página en una celda.

Hace bastante tiempo mostré cómo conseguir identificar el número de página impresa directamente en una celda de nuestra Hoja de cálculo; mediante funciones macro 4.0 de Excel conseguíamos identificar en una celda la numeración de la hoja correspondiente al área de impresión (ver).

Hoy veremos otra forma de obtener el mismo resultado, de una manera algo más sencilla, mediante una función personalizada en VBA.

La idea sigue siendo la misma, para una hoja con un área de impresión definida, con diferentes saltos de página insertados, mostrar en la misma hoja de cálculo, en las celdas correspondientes, cuál es el número de página de un total de ellas.
Partiremos de una hoja con diferentes saltos de página:

VBA: Una función personalizada para mostrar el Número de página en una celda.

Insertamos el siguiente código VBA de nuestro procedimiento Function en un módulo del Explorador de proyectos del Editor de VBA:

view plainprint?

  1. Function InfoPag()
  2. Dim iPags As Integer, iPag As Integer
  3. Dim iCols As Integer, iCol As Integer
  4. Dim lfilas As Long, lfila As Long
  5. Dim x As Long, y As Long
  6. Application.Volatile
  7. Dim direccion As Range
  8. ‘controlamos la dirección de la celda donde se encuentra la función
  9. Set direccion = Range(Application.Caller.Address)
  10. ‘contamos saltos de página (horizontales y verticales)
  11. With ActiveSheet
  12. lfilas = .HPageBreaks.Count
  13. iCols = .VPageBreaks.Count
  14. End With
  15. ‘calculamos el número total de páginas
  16. iPags = (lfilas + 1) * (iCols + 1)
  17. With ActiveSheet
  18. ‘recorremos columnas hasta que encontramos entre qué saltos verticales está.
  19. y = direccion.Column
  20. x = 0
  21. Do
  22. x = x + 1
  23. Loop Until x = iCols Or y < .VPageBreaks(x).Location.Column
  24. iCol = x
  25. If y >= .VPageBreaks(x).Location.Column Then
  26. iCol = iCol + 1
  27. End If
  28. ‘recorremos columnas hasta que encontramos entre qué saltos horizontales está.
  29. y = direccion.Row
  30. x = 0
  31. Do
  32. x = x + 1
  33. Loop Until x = lfilas Or y < .HPageBreaks(x).Location.Row
  34. lfila = x
  35. If y >= .HPageBreaks(x).Location.Row Then
  36. lfila = lfila + 1
  37. End If
  38. ‘Dirigimos y calculamos el número de pagina, según este la configuración de pagína
  39. ‘bien Hacia abajo y luego hacia la derecha
  40. ‘bien Hacia la derecha y luego abajo
  41. If .PageSetup.Order = xlDownThenOver Then
  42. iPag = (iCol – 1) * (lfilas + 1) + lfila
  43. Else
  44. iPag = (lfila – 1) * (iCols + 1) + iCol
  45. End If
  46. End With
  47. ‘devolvemos el valor a la hoja de cálculo
  48. InfoPag = iPag & " de " & iPags
  49. Set direccion = Nothing
  50. End Function

El resultado es el esperado, obtenemos en nuestras celdas la numeración de página respecto del total de páginas dentro del área de impresión.
Ojo, la función está definida bajo el supuesto que existe al menos un salto vertical y uno horizontal!!!. En caso contrario devolvería un error.
Para controlar esta situación habría que condicionar el código para el caso que .HPageBreaks.Count y.VPageBreaks.Count fueran cero…

EXCEl

Publicado: 10 de julio de 2013 en Actualidad

VBA: ejecutar una macro de Excel de un Libro cerrado – el programador de tareas de Windows.

En una entrada del blog anterior (ver) vimos cómo podíamos configurar o programar una macro para que se ejecutara en un momento determinado, pero con la condición que el Libro de trabajo que la contenía estuviera abierto.
En la entrada de hoy veremos cómo empleando una herramienta de Windows (el Programador de tareas), tendremos la posibilidad de programar la apertura de nuestro Libro de trabajo automáticamente, y por tanto, permitir la ejecución de una macro de Excel cuando así lo necesitemos. En definitiva salvaremos así el escollo o limitación que suponía deber tener un Libro abierto para forzar una macro.

Por otra parte, igualmente importante, está el asunto de la Seguridad de las macros y la habilitación automática de macros o contenidos, lo que quedó explicado en la entrada:

Se trata, por tanto, de trabajar en diferentes frentes:
1. Programador de tareas de Windows, y
2. Definir las Ubicaciones de confianza (que permita saltar el mensaje de seguridad de macros)

Nos centramos en los pasos a seguir para configurar el programador de tareas de Windows.
Lo primero desde el botón de Inicio de Windows buscaremos el Equipo sobre el que haremos clic derecho y Administrar:

VBA: ejecutar una macro de Excel de un Libro cerrado - el programador de tareas de Windows.
haz clic en la imagen

En la ventana que se abrirá de Administración de equipos buscaremos en los menús de la izquierda la opción de Herramientas del sistema > Programador de tareas:

VBA: ejecutar una macro de Excel de un Libro cerrado - el programador de tareas de Windows.
haz clic en la imagen

Como la tarea a programar es bastante sencilla (abrir un Libro de Excel), nos fijaremos en las Acciones que encontramos a la derecha, y presionaremos Crear tarea básica que abre la siguiente ventana delAsistente para crear tareas básicas, completaremos el Nombre de la tarea (y la descripción si queremos):

VBA: ejecutar una macro de Excel de un Libro cerrado - el programador de tareas de Windows.
haz clic en la imagen

El siguiente paso del Asistente es programar el Desencadenador de la tarea, esto es, indicar cuándo queremos que se ejecute la tarea. Podemos optar por que se programe diariamente, semanalmente, mensualmente, etc. Seleccionaremos la que nos interese:

VBA: ejecutar una macro de Excel de un Libro cerrado - el programador de tareas de Windows.
haz clic en la imagen

El siguiente paso del Asistente sirve para configurar el momento:

VBA: ejecutar una macro de Excel de un Libro cerrado - el programador de tareas de Windows.
haz clic en la imagen

En este caso indicamos cuando comienza la tarea y a qué hora queremos se ejecute diariamente (fue nuestra elección).

Otro paso es el de indicar qué acción va a realizar la tarea, podemos elegir entre tres opciones, pero en nuestro caso nos quedaremos con la primera: Iniciar un programa.

VBA: ejecutar una macro de Excel de un Libro cerrado - el programador de tareas de Windows.
haz clic en la imagen

Ya casi acabamos…
Indicamos dónde está el programa (Libro de Excel) a iniciar:

VBA: ejecutar una macro de Excel de un Libro cerrado - el programador de tareas de Windows.
haz clic en la imagen

El paso final nos muestra un resumen de la configuración anterior:

VBA: ejecutar una macro de Excel de un Libro cerrado - el programador de tareas de Windows.
haz clic en la imagen

Hemos acabado, nuestra tarea ha quedado programada. A partir de hoy, todos los días a a las 18:00 se abrirá el libro de Excel indicado. Además si el Libro contiene macros y una de estas macro está configurada para ejecutarse en un tiempo concreto, lo hará…

Para modificar o eliminar una tarea ya creada, accederemos a la Adminsitración de equipos (igual que al inicio de la entrada) y buscaremos la Biblioteca de tareas… entre ellas aparecerá la nuestra. Sobre ella podremos editarla, eliminarla, deshabilitarla, etc…

VBA: ejecutar una macro de Excel de un Libro cerrado - el programador de tareas de Windows.

EXCEL

Publicado: 10 de julio de 2013 en Actualidad

Los Estilos de celda y la herramienta Subtotales en Excel.

Aunque sin duda la herramienta Subtotal no es mi favorita, hoy explicaré cómo asociar a las filasdonde se encuentren los Subtotales en el esquema generado un formato o estilo de celda definido.
En definitiva se trata de formatear los diferentes niveles de Subtotales añadidos. Podemos ver en la imagen el resultado final:

Los Estilos de celda y la herramienta Subtotales en Excel.

Lo interesante de esta acción que aprenderemos hoy, es que la herramienta Subtotal asigna una categorización (de diferentes niveles) a los sucesivos subtotales añadidos.. aunque con una pequeña desventaja, y es que se lo asigna a toda la fila donde exista dicho subtotal!!!.

Veamos los pasos. Lo primero será acceder a la venta diálogo Configuración de Esquema, para ello navegamos por la Ficha Datos > grupo Esquema > flecha inferior derecha (abre/muestra cuadro diálogo):

Los Estilos de celda y la herramienta Subtotales en Excel.

En la ventana de Configuración marcaremos la opción Estilos automáticos:

Los Estilos de celda y la herramienta Subtotales en Excel.

Tras Aceptar o Aplicar estilos, ya podemos agregar dos subtotales a nuestra base de datos, uno para cada cambio en el campo ‘Código’ y otro más para cada cambio en el campo ‘Comercial’, y en este orden comentado.
El primer Subtotal tendría esta configuración:

Los Estilos de celda y la herramienta Subtotales en Excel.

Y el segundo:

Los Estilos de celda y la herramienta Subtotales en Excel.

El resultado de los subtotales aplicados sería el siguiente:

Los Estilos de celda y la herramienta Subtotales en Excel.

Una vez conseguido nuestro Esquema ya podemos aplicar los Estilos de celda, para lo cual nos iremos a la ficha Inicio > grupo Estilos > botón Estilos de celda, y esto es lo que vemos al desplegar dicho botón:

Los Estilos de celda y la herramienta Subtotales en Excel.

Nos fijaremos especialmente en los tres estilos creados ‘NivelFila_1’, ‘NivelFila_2’ y ‘NivelFila_3’, generados automáticamente al adicionar niveles en nuestro Esquema (empleando la herramienta Subtotal).
Ahora viene lo sencillo, ya que presionando botón derecho del ratón sobre dichos estilos, podremos modificar los formatos de cada uno de ellos…. aplicándose éstos a las filas de los subtotales:

Los Estilos de celda y la herramienta Subtotales en Excel.

En la ventana siguiente es donde aplicaremos los formatos deseados (formato número, alineación, fuente, bordes, relleno, etc.):

Los Estilos de celda y la herramienta Subtotales en Excel.

Repetiremos la operación para cada nivel de fila que deseemos editar… hasta conseguir el aspecto de la primera imagen de esta entrada.

Excel

Publicado: 10 de julio de 2013 en Actualidad

VBA: Diferencias entre las propiedades .Formula, .Value y .Text del objeto Range.

Han sido varias las ocasiones en que algún alumno me ha preguntado sobre las semejanzas o diferencias entres las propiedades del objeto Range: .Formula, .Value y .Tex; en apariencia muy similares y muchas veces con resultados idénticos.
Las diferencias son escasas y sólo salen a relucir cuando existen ciertas condiciones. Hoy aprenderemos a trabajar con éstas.

Comenzaremos con la propiedad .Formula, empleada habitualmente para introducir fórmulas en nuestras hojas de cálculo, por ejemplo:
Range("C1").Formula = "=SUM(A1:B1)"

o bien en su forma local:
Range("C2").FormulaLocal = "=SUMA(A2:B2)"

Esta propiedad .Formula nos devuelve o establece un valor de tipo Variant que representa la fórmula del objeto en notación de estilo A1 y en el lenguaje de la macro (.FormulaLocal hace lo mismo en el lenguaje de nuestra instalación).
Las condiciones en las que esta propiedad funciona serían:

  1. Si la celda contiene una constante, esta propiedad la devolverá. Si está vacía, devolverá una cadena vacía. Si la celda contiene una fórmula, la propiedad .Formula devolverá la fórmula en forma de cadena, con el mismo formato en que se presentaría en la barra de fórmulas (incluido el signo igual).
  2. Si se define el valor o la fórmula de una celda como una fecha, Excel comprueba si dicha celda ya tiene uno de los formatos numéricos de fecha u hora; de lo contrario, cambia el formato numérico al formato numérico de fecha corta predeterminado.
  3. Si se define la fórmula para un rango de varias celdas, se rellenan todas las celdas del rango con la fórmula.

Claro que si nos detenemos un segundo y ejecutamos la propiedad .Value como sigue:
Range("C3").Value = "=SUM(A3:B3)"
el resultado es el mismo… aunque las condiciones de uso son algo diferentes.

Lo probamos todo. En un módulo del Editor de VBA añadiremos las líneas de nuestra macro:

view plainprint?

  1. Sub pruebas()
  2. Range("C1").Formula = "=SUM(A1:B1)"
  3. Range("C2").FormulaLocal = "=SUMA(A2:B2)"
  4. ”””””””””””””””””’
  5. Range("C3").Value = "=SUM(A3:B3)"
  6. End Sub

Lo vemos en la imagen en modo Mostrar fórmula:

VBA: Diferencias entre las propiedades .Formula, .Value y .Text del objeto Range.

Y si obtenemos lo mismo por qué y para qué existen estas propiedades???… pues por que nuestras macros, según lean una u otra podrían devolvernos ‘cosas’ diferentes’ (un valor ya calculado o bien una fórmula). Veámoslo con un sencillo ejemplo, donde aplicaremos ambas propiedades.

VBA: Diferencias entre las propiedades .Formula, .Value y .Text del objeto Range.

La propiedad .Formula devolverá, como sabemos, una fórmula si es tal cosa lo que hemos indicado, pero si no es así, es decir, si no contine fórmula, obtendremos un .Value o Valor.
Por ejemplo, sobre los datos de la imagen anterior, para la celda D3, al indicar la propiedad .Formula y .Value conseguiremos precisamente esa fórmula incorporada a la celda:
Range("D3").Formula = "=(C3-B3)/B3"
Sin embargo, si aplicamos la propiedad .Value conseguiremos el valor calculado, el valor puro, sin fórmula (y sin formato).
Range("D3").Value

Verifiquémoslo. En un módulo del Editor de VBA añadiremos las líneas de nuestra macro:

view plainprint?

  1. Sub Prueba2()
  2. ‘incluimos una fórmula en D3
  3. Range("D3").Formula = "=(C3-B3)/B3"
  4. ”””””””””””””””””’
  5. Set c = Range("D3")
  6. ‘damos estilo de celda porcentual
  7. c.Style = "Percent"
  8. MsgBox c.Formula ‘mostramos el valor de la celda con la propiedad .Formula
  9. MsgBox c.Value ‘mostramos el valor de la celda con la propiedad .Formula
  10. End Sub

Al ejecutar nuestra macro Prueba2 rápidamente se observan las diferencias…

Por si fuera poco, tenemos alguna otra propiedad ‘en discordia’: la propiedad .Text, la cual devuelve en forma literal lo que aparece en la celda (además si el contenido de la celda es numérico lo obtendremos incluyendo el formato.
En el ejemplo anterior, si añadireamos a nuestra macro una última línea
MsgBox c.Text
nos mostraría el valor calculado con su formato porcentual.
Esta es una propiedad sólo de Lectura!!.

La conclusión final, podríamos decir que la diferencia principal entre estas propiedades, es la lectura que hace de ellas nuestra MAcro, y no tanto a la hora de escribir en nuestra hoja de cálculo.

Leer este artículo: http://pijamasurf.com/2012/12/7-razones-para-aborrecer-mcdonalds/

Enviado desde mi iPhone