lunes, 10 de febrero de 2014

VBA: Repetir una macro cada x tiempo.

Hace algún tiempo publiqué un par de entradas donde aplicabamos el método .OnTime, en una de ellas mostrábamos lo más esencial de este método (ejemplo1) y en el segundo algo más elaborado, donde completábamos un cronómetro (ejemplo2).
En la entrada de hoy, dando respuesta a un lector, mostraremos algo interesante (parecido al ejemplo del cronómetro), y es la forma en cómo poder repetir con una carencia de tiempo definida una macro o rutina.
La idea entonces es clara, queremos automatizar la repetición controlada de una macro cada x segundos, minutos u horas. Para ello crearemos tres procesos Sub:
1- macro que inicie todo el proceso, y que además controle el final de las repeticiones
2- macro que de por finalizado el método .OnTime
3- por supuesto, nuestra macro principal, la que queremos se ejecute cada x tiempo.


Estas son nuestras macros que añadiremos nuestro código asociándolo a un Módulo; para ello accederemos a la ventana de código del explorador del editor de VBA, donde insertaremos el siguiente código VBA:

'declaración de variables para todo el módulo
Dim tiempo As Date
Dim contador As Integer

Sub IniciaOnTime()
'controla la repetición y la cadencia
Dim allea As Integer
'repetición cada 2 segundos
tiempo = Now + TimeSerial(0, 0, 2)
'llama a esta misma macro en el tiempo estipulado
Application.OnTime tiempo, "IniciaOnTime"
'controlamos el final de la repetición
contador = contador + 1
'mientras el contador sea menor a 6 ejecutamos la rutina principal
'cuando lleguemos a 6 interrumpimos la repetición
If contador < 6 Then
    Run "MacroPrincipal"
Else
    Run "CancelaOnTime"
End If
End Sub

Sub CancelaOnTime()
'ejecuta el final de la repetición y reinicia el contador
    Application.OnTime tiempo, "IniciaOnTime", , False
    contador = 0
End Sub

Sub MacroPrincipal()
'el código de la rutina a repetir
'cualquier procedimiento....
With Range("B2")
    .Value = contador & " - " & Now
    Randomize
    allea = Int(20 * Rnd)
    If allea < 5 Then
        .Interior.Color = vbRed
        .Font.Color = vbYellow
    End If
    If allea >= 5 And allea < 10 Then
        .Interior.Color = vbGreen
        .Font.Color = vbBlack
    End If
    If allea >= 10 And allea < 20 Then
        .Interior.Color = vbBlue
        .Font.Color = vbWhite
    End If
End With
End Sub

Algo fundamental es incluir algún control que finalice las reiteraciones, ya que de lo contrario nunca finalizará y tendremos que pararlo manualmente entrnado en el editor. En el ejemplo el control se ha realizado con un 'contador' que ejecuta y para el método .Ontime.. cuando contador llegue a 6 se lanza la macro CancelaOntime. Otra manera, si nos interesa que la repetición esté operativa mientras el libro esté abierto es asociar el inicio y fin a un evento WorkBook_Open y otro WorkBook_Close. Este sería el resultado visible:

VBA: Repetir una macro cada x tiempo.

54 comentarios:

  1. Oye una duda, para una condicionante se puede hacer una macro entre cierto intervalo de tiempo??
    te explico mejor, necesito una macro que me verifique la hora y si esta entre 6 y 6:30 y también entre 18 y 18:30 me deje ejecutar otra macro y si no solo me salga un mensaje!!
    es que solo lo he podido hacer pero con una hora, no con un intervalo de tiempo
    me podrías ayudar!!!

    ResponderEliminar
    Respuestas
    1. Hola Luis, que tal estás!?
      podrías emplear un condicional IF..THEN, algo así:
      IF Now()>=timeserial(6,0,0) and Now()<=timeserial(6,30,0) or Now()>=timeserial(18,0,0) and Now()<=timeserial(18,30,0) THEN
      ....

      combinándolo con OnTime.
      Saludos

      Eliminar
  2. hola, mi consulta es simple, tengo un excel que se conecta a una base de datos lo que necesito es actualizar este cada hora, es simple ejecutar el comando actualizar de la macro cada una hora a partir de que se abre la planilla, lo que no logro decodificar en que parte tengo que cambiar tu codigo para que haga esto y en que parte tengo que poner el cogido de mi macro para que lo ejecute?

    ResponderEliminar
    Respuestas
    1. Hola,
      si l oque quieres es actualizar la conexión, lo más sencillo es que entres en las propiedades de la Conexión y configures la actualización en el intervalo que desees.. basta con eso, y es mucho más sencillo que meterte en líos de macros recurrentes.
      Saludos

      Eliminar
    2. muchas gracias no sabia eso y estaba tratando de resolverlo con una macro
      al parecer es bastante simple. voy a intentarlo y te comento

      Eliminar
    3. solo me resta un detalle y es aqui donde necesito generar un timer que cada 1 hora me actualise, tengo dos tablas dinamicas que se rellenan de las hojas de datos que traigo de la coneccion, lo que necesito es hacer es ejecutar el comando ActiveWorkbook.RefreshAll cada una hora para que en dichas tablas se refleje el resultado de actualizar y asi pueda seguir con otras tareas sin recurri a hacer el trabajo manualmente como lo hago hasta ahora con un boton actualizar que tiene este comando en su codigo

      Eliminar
    4. Una forma sencilla sería añadir en la ventana de código de la hoja donde se encuentre la Conexión de datos el código:
      Private Sub Worksheet_Calculate()
      Sheets("Tabla dinámica").PivotTables("Tabla dinámica 1").RefreshTable
      ' o bien ActiveWorkbook.RefreshAll
      End Sub

      Este método es mejor que usar un temporizador recurrente que está empleando recursos constantemente.
      Saludos

      Eliminar
    5. gracias ismael por la respuesta, entiendo lo de poner el comando, lo que no me queda claro es como hago para que al iniciar mis tarear yo abro el libro de excel y hago que se ejecute el comando cada 1 hora desde que comienzo a trabajar, asi de esta manera mientras estoy con otras tareas se ejecuta solo y cuando vuelvo a ver la planilla esta actualizada y la tabla con los datos cargados.
      yo tomo tu ejemplo y estoy tratando de adaptarlo pero es mas simple con esto que me decis lo que no termino de entender es como logro hacer que ser repita la accion cada una hora.

      Eliminar
    6. Hola,
      subiré una explicación al blog
      ;-)
      Saludos

      Eliminar
    7. gracias ismael, después me dejas el link así lo reviso.

      Eliminar
    8. ismael hice lo siguiente....
      Private Sub Workbook_Open()
      Application.OnTime TimeValue("09:00:00"), "RECARGAR"
      Application.OnTime TimeValue("10:00:00"), "RECARGAR"
      .....
      Application.OnTime TimeValue("18:45:00"), "RECARGAR"
      Application.OnTime TimeValue("19:10:00"), "RECARGAR"
      End Sub
      Private Sub Workbook_close()

      End Sub

      Sub RECARGAR()
      Dim hora As String
      ActiveWorkbook.RefreshAll
      hora = Time
      MsgBox ("ACTUALIZACION COMPLETA " & hora)
      End Sub

      asi al menos funciona pero tengo una duda en el cierre del archivo debo detener el Application.OnTime o se ejecuta en cada horario y se detiene solo.

      Eliminar
    9. Hola,
      revisa lo expuesto en este post... no es necesario marcar las horas concretas en las que lanzar el procedimiento RECARGAR.
      En todo caso, si lo haces así no hace falta hacer nada más.. se jecuta en esas horas y ya está, puesto que no existe bucle.
      Si estás atento el día 20 de octubre, publicaré el caso resuelto.
      Slds

      Eliminar
    10. GRACIAS ISMAEL POR LA ASISTENCIA ESTARE ATENTO. SALUDOS

      Eliminar
  3. Hola, tengo una macro para hacer que un archivo en Excel se abra a una hora específica y que ejecute otra macro que contiene ese archivo. Utilizo el siguiente código y me funciona perfectamente. La duda me surge porque no quiero se active siempre a la misma hora y cada vez que quiero cambiar la hora tengo que editar el código e introducirlo manualmente. ¿Existe alguna manera de hacerlo mediante un cuadro de dialogo tipo InputBox?
    He hecho una prueba con un InputBox (abajo del todo podéis verlo) pero no me funciona. No me da ningún tipo de error pero cuando llega la hora indicada no hace nada, sé que algo está mal pero no sé el que.
    Gracias!
    -------------------------------
    Sub Cronometro()
    'Orden para iniciar un cronómetro y cuando llegue a la hora indicada, abrir un archivo Excel, la orden Call sirve para llamar a otra macro
    EarlTime = Now + TimeSerial(0, 0, 1)
    Application.OnTime EarlTime, " Cronometro "
    If Time = CDate("8:25:00") Then Workbooks.Open "C:\Documents\PDH\macros_V4.xlsm": Call Ejecutar_Macro_Excel
    End Sub
    --------------------------------------
    Sub Ejecutar_Macro_Excel()
    'Orden para ejecutar una macro en un archivo Excel abierto
    Windows("macros_V4.xlsm").Activate
    Application.Run "'macros_V4.xlsm'!FECHA_HORA"
    End Sub

    Prueba fallida con InputBox
    Sub Dialogo()
    Dim Val As String
    Val = InputBox("A qué hora te vas", "Ejemplo", "", 1200, 1400): Call CronometroII
    End Sub
    -----------------------------------
    Sub CronometroII ()
    EarlTime = Now + TimeSerial(0, 0, 1)
    Application.OnTime EarlTime, " CronometroII "
    If Time = "& Val" Then Workbooks.Open " C:\Documents\PDH\macros_V4.xlsm": Call Ejecutar_Macro_Excel
    End Sub

    ResponderEliminar
    Respuestas
    1. DIFICILMENTE ACTUALIZE LAS VARIABLES, ya que el excel no modifica el codigo de visual luego de la apertura, cualquier cambio que hagas en el codigo pos ejecucion del archivo, deberas cerrar el archivo de excel para que recargue el codigo nuevamente. de echo en mis prubeas me pasaba que modificaba el codigo con el archivo abierto y no corria el nuevo codigo hasta tanto no cerraba el archivo y lo volvia a abrir.

      Eliminar
    2. Hola Daniel,
      si pretendes abrir un fichero Excel tienes varias alternativas...
      obviamente desde algún fichero Excel ya abierto, o bien incluso desde las tareas de windows:
      http://excelforo.blogspot.com.es/2013/06/vba-ejecutar-una-macro-de-excel-de-un.html
      No existe, como indica el lector anterior, la forma de modificar un código de una macro desde otra macro...
      la alternativa del inputbox requeriría, obviamente la acción humana sobre ella.

      En cuanto a tu cuestión del inputbox, tendrías que declara como publica la variable, para que se pueda emplear en cualquier otro procedimiento (hazlo en las primera líneas de la ventana de código NO dentro de los procedimientos!

      Saludos

      Eliminar
  4. por otra parte la idea de crear variables que se modifiquen no estan mal solo que deberias asignarle el valor para trabajar al iniciar el archivo en el evento de carga del archivo...creo que ahi si lo asignara y correra luego.

    ResponderEliminar
  5. Tengo una macro que me elimina las filas en donde hay datos repetidos de un columna especifica, pero deseo que se ejecute cada un segundo automáticamente sin que yo oprima un botón ni un comando nada, simplemente que después de que yo abra el archivo este me ejecute dicha macro por si sola cada 1 segundo.

    este es el código de la macro:

    Sub repetidos()

    Range("A2").Select
    Do While Not IsEmpty(ActiveCell)
    x = WorksheetFunction.CountIf(Range("A:A"), ActiveCell)
    If x > 1 Then
    ActiveCell.EntireRow.Delete
    Else
    ActiveCell.Offset(1, 0).Select
    End If
    Loop
    Range("A1").Select
    End Sub

    ResponderEliminar
    Respuestas
    1. Hola,
      en principio solo deberías seguir los pasos indicados en este mismo post, empleando el método ontime..
      Sub IniciaOnTime()
      tiempo = Now + TimeSerial(0, 0, 1)
      Application.OnTime tiempo, "IniciaOnTime"
      Run "repetidos"
      End Sub

      No olvides cerrar el proceso OnTime!

      saludos

      Eliminar
  6. Esta muy bueno , pero no se si se puede aplicar a mi caso.

    En realidad lo que quiero hacer es que el archivos actualice todas las tablas (incluyendo los orígenes de datos externos) cada 20 mins, sin necesidad que el archivo este abierto.

    Alguien me podría ayudar con este caso o un ejemplo parecido.

    Mil gracias.

    ResponderEliminar
    Respuestas
    1. Hola,
      ¿sin el archivo abierto?, pues salvo que sobre ese libro tengas vínculos de terceros libros, no comprendo el sentido...
      En todo caso, si quieres forzar la actualización tendrás que abrir el fichero, actualizar, y si es el caso, luego cerrarlo...

      Para esto tendrás que tener un libro de Excel abierto o bien lanzar el proceso desde las tareas de windows:
      http://excelforo.blogspot.com.es/2013/06/vba-ejecutar-una-macro-de-excel-de-un.html

      Saludos

      Eliminar
  7. hola tengo un problema hago correr la macro y funciona de maravilla el problema es que si abro otro libro esta falla y me bota error 9 :(

    ResponderEliminar
    Respuestas
    1. Hola,
      al abrir varios libros y activar el 'otro' puede ser que entre en confusión e intente lanzar y ejecutar acciones concretas que no tengan sentido en ese segundo libro (y sí lo tenían en el primero).
      La solución es añadir a todas las líneas que lo necesiten la especificación del Workbook donde trabajar
      Workbook("Libro1").Range(...
      tambiénn en el Run
      etc...

      Saludos

      Eliminar
    2. muy buena camarada funciona de maravilla muchas gracias por la atención :)

      Eliminar
  8. perdón pero me equivoque la solución fue usando la aplicación de window().active ya con eso jalo nomas lo dejo trabajando en segundo plano y ya no afecta a los oros libros

    ResponderEliminar
  9. Ismael:

    Tengo una macro que genera correos automáticos en función a ciertas variables, el punto es que necesito una macro que ejecute esta otra (la de los correos) una vez al día, pero no los sábados ni los domingos. Detalle importante es que el excel permanece siempre abierto (por lo de los eventos generadores). Estare eternamente agradecido si me puedes ayudar.
    Saludos cordiales

    ResponderEliminar
    Respuestas
    1. Hola,
      podría ser de este estilo (integrándolo con lo expuesto en el post):

      Sub Prueba()
      If Weekday(Now, vbMonday) < 6 Then
      Application.OnTime TimeValue("13:54:10"), "MacroPrincipal"
      End If
      End Sub

      Saludos

      Eliminar
  10. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  11. Amigo, y como hago para que repita la macro cada DÉCIMA DE SEGUNDO.

    ResponderEliminar
    Respuestas
    1. Hola Kario,
      las décimas de segundo se podrían poner como 00:00:00.1
      aunque me parece una locura ejecutar algo (y seguramente bloquear el resto de acciones) cada décima de segundo
      Saludos

      Eliminar
    2. Este comentario ha sido eliminado por el autor.

      Eliminar
    3. jaja, es para hacer mover objetos 2D Y 3D en Excel desde VBA.

      Eliminar
  12. Hola a ver si me puedes ayudar te explico tengo la celda c3 que es el resultado de búsqueda de un cuadro de búsqueda y me gustaría poder pegarlo a través de dos botones
    1 en la celda que yo seleccione como activa y al presionar el botón la pegue
    2 en la última celda en blanco de la columna c al yo presionar el botón
    Gracias de antemano

    ResponderEliminar
    Respuestas
    1. Worksheer("registro").range("C4").copy
      Range.("activeCell").select
      Selection.pastespecial paste:=xlpastevalues
      End sub
      Lo que me falla es la celda de destino que quiero que sea la que yo elija

      Eliminar
    2. Que sea aleatoria la celda activa
      A ver si me puedes ayudar

      Eliminar
    3. Worksheets("registro").Range("C4").Copy
      ActiveCell.PasteSpecial Paste:=xlPasteValues

      Saludos

      Eliminar
    4. y para que lo pegue directo en la presión primera celda de la columna c en blanco al yo presionar otro botón
      De antemano perdona por tantas molestias pero en esto soy novato total

      Eliminar
    5. Probé un código pero al pegar por ejemplo yo voy por la celda c1000 lo pegaba en la c1001 hasta hay todo bien pero al pegarlo el libro se ponía a la celda c7 que era la primera del libro En vez de quedarse en la celda que he pegado se subía al principio

      Eliminar
    6. Esa que me distes va bien gracias

      Eliminar
    7. No encontré nada que me similar en el enlace

      Eliminar
    8. ;-)
      revisa bien los diferentes ejemplos del blog.. una vez tengas claro lo que necesitas encontrarás la respuesta

      Saludos y suerte!

      Eliminar
    9. Los revise todos el problema es que yo no sé de esto siempre que e echo algo a sido copiado de otro pero como no sea el mismo y cambie algo ya no se seguir si no fui capaz de resolver la que me mando usted y solo tenía una variable imagínese esa imposible

      Eliminar
    10. Ya lo conseguí gracias

      Eliminar
  13. alguien me puede crear solo quiero tener un cod cambiante constante de 6 numeros nada mas pero que se cambie cada3 segundos ejemplo: "0154254" nada mas

    ResponderEliminar
    Respuestas
    1. Hola Nestor,
      que tal estás?, un placer saludare igualmente
      Emplea lo expuesto en el post:
      Application.OnTime
      con la cadencia que indicas de 3 segundos
      para obtener el valor de 6 dígitos, entiendo que puede seer cualquier valor de hasta seis dígitos, emplea la función:
      aleatorio.entre(0;999999)
      Saludos cordiales

      Eliminar
  14. hola una consulta podrías por favor explicarme como hago para que el timer o el Ontime o un wait se demore menos de un segundo,

    ResponderEliminar
    Respuestas
    1. Hola,
      puedes usar segundos con decimales, lo que representarán centésimas de segundo
      Saludos cordiales!

      Eliminar
  15. Hola, a ver si alguien me puede ayudar!

    Tengo una hoja de excel con unos datos asignados a personas. Podría ser de esta forma:
    Columna A: NOMBRE; Persona 1; Persona 2; Persona 1
    Columna B: Nº REF; nº de referencia 1; nº de referencia 2; nº de referencia 3
    Columna C: DATOSc; Dato 1c; Dato 2c; Dato 3c
    Columna D: DATOSd; Dato 1d; Dato 2d; Dato 3d

    Lo que necesito es que CADA LUNES envíe un correo a la persona que tenga vacío algo en la columna C o D, de tal forma que en el correo mencione en qué columna faltan datos y el nº de referencia asignado a esa fila(Columna B).

    Muchas gracias por anticipado.

    ResponderEliminar
    Respuestas
    1. El correo podría ser así:

      Para: correo de Persona 1
      Asunto: Completar datos

      Cuerpo:
      Hola Persona 1,

      Faltan por rellenar los datos siguientes:
      nº de referencia 1
      - DATOSc

      nº de referencia 2
      - DATOSc
      - DATOSd

      Un saludo.

      Muchas gracias de nuevo

      Eliminar
    2. Hola Juanxo,
      echa un vistazo a
      http://excelforo.blogspot.com.es/2015/08/vba-enviar-un-email-desde-excel.html

      si lo combinas con la macro de este post, seguro que llegas a buen puerto

      Saludos

      Eliminar
  16. Hola Ismael, tengo una macro para ejecutarse a ciertas horas, yo quiero que la ejecute "todos los días", sin embargo parece que cuendo llega al final se para y tengo que volver a lanzarla manual, hay alguna forma que siga de manera indeterminada o hasta que cierre excel?

    Private Sub Workbook_Open()



    If Weekday(Now, vbMonday) < 7 Then

    Application.OnTime TimeValue("08:00:00"), "Calcular_Ayer"
    Application.OnTime TimeValue("09:59:00"), "Calcular"
    Application.OnTime TimeValue("11:29:00"), "Calcular"
    Application.OnTime TimeValue("12:29:00"), "Calcular"
    Application.OnTime TimeValue("13:29:00"), "Calcular"
    Application.OnTime TimeValue("15:00:00"), "Calcular"
    Application.OnTime TimeValue("16:59:00"), "Calcular"
    Application.OnTime TimeValue("18:00:00"), "Calcular"
    Application.OnTime TimeValue("19:30:00"), "Calcular"
    Application.OnTime TimeValue("21:00:00"), "Calcular"
    Application.OnTime TimeValue("22:00:00"), "Calcular"

    End If



    End Sub

    ResponderEliminar
    Respuestas
    1. Hola!
      no basta con incluirla en el evento Open,
      tienes que montar un cruce de macros que reiteren mientras el libro esté abierto....
      Echa un vistazo al post más arriba y verás como se combinan hasta tres macros para conseguir el 'movimiento eterno'

      Slds

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.