1 Reply Latest reply on Mar 25, 2014 4:30 AM by Sam Pride

    .onTime in process book VBA

    bpolaski

      I have a trend that contains an embedded excel sheet which only updates when the trend is opened. The excel sheet just has some current value information gathered with Data Link.

       

      My goal is to have it update every 5 seconds and display this on the trend, I wanted to use:

       

       

       

      Application.onTime

       

       

       

      which works in excel but not in process book? (The method is not supported) Is there another way to do this?

        • Re: .onTime in process book VBA
          Sam Pride

          As you have found, ProcessBook does not support Application.OnTime.

           

          You can use the Windows API SetTimer and KillTimer functions to start and stop a timer. You need to declare the functions in a Module first:

           

           

           

          Processbook does not support the Application.OnTime event (Only Word and Excel do). You can still implement a timer through the Windows API. SetTimer and KillTimer are used to start and stop a timer:

           
          Declare Function SetTimer Lib "user32" _
                            (ByVal hwnd As Long, _
                            ByVal nIDEvent As Long, _
                            ByVal uElapse As Long, _
                            ByVal lpTimerFunc As Long) As Long
          
                   Declare Function KillTimer Lib "user32" _
                            (ByVal hwnd As Long, _
                            ByVal nIDEvent As Long) As Long
          

           

           

          Start the timer: 

           
          Dim lngTimerID As Long
          lngTimerID = SetTimer(0, 1, <time interval in milliseconds> , AddressOf TimerProc)
          

           TimerProc is the name of the function you want to call each time the timer "dings". So, just put in the sub name of your refresh code.

           

          Stopping the timer (before you close the display!!) is easy: 

           
          lngTimerID = KillTimer(0, lngTimerID)
          

           Another way to use the timer is within a loop (but is more suited to waiting or responding in a certain period of time, not necessarily "Do this every x seconds"):

           

           

           
          Dim Start As Double
          Dim SecondsToWait As Integer
          
          SecondsToWait = 30
          Start = Timer
          Do While Timer < Start + SecondsToWait
              DoEvents
          Loop
          Call TimerProc ' The procedure/Sub to call to do your work
          

           Another alternative (which may be simpler) is to put your refresh code within your excel spreadsheet. ProcessBook is an OLE Container and what you are seeing on your display is a cut-down version of Excel. I've not checked it, but it might be much easier to just setup a .onTIme event within your spreadsheet.