Make a VBA macro run automatically/constantly

Question

I'm having some trouble with my VBA code. I have some real time data linked to Bloomberg and I would like to get a pop-up window every time the value of a cell is higher than a specific threshold. To do that, I need to find a way to constantly run my macro to check if my condition is verified .. but I don't know how to do that .. my current VBA code is:

Private Sub Worksheet_Activate()  
Dim i As Long  
For i = 2 To 99  
    If Cells(i, 11).Value > 25000 Then  
       MsgBox "Last trade on " & Cells(i, 1).Value & " is higher than 25 000"  
    End If  
Next i  
End Sub

If you could help me with this code, this would be great ! Thanks in advance

By searching for `ms excel timer` you will find something like this http://stackoverflow.com/questions/2319683/vba-macro-on-timer-style-to-run-code-every-set-number-of-seconds-i-e-120-secon — BitAccesser, May 22 '16 at 01:59

teckchai lim · Accepted Answer · 2016-06-02T13:28:29.537

1

You can try something like this:

Private Sub Worksheet_Activate()  
  Dim i As Long  
  Do while (true)
    For i = 2 To 99  
      If Cells(i, 11).Value > 25000 Then  
        MsgBox "Last trade on " & Cells(i, 1).Value & " is higher than 25 000"  
      End If  
    Next i
    Application.Wait (Now + TimeValue("0:00:10")) 'wait 10 seconds
  Loop
End Sub

EDIT: to make it stop appearing if it the same, use the cells (like 10 columns away) to store the current value and compare.

Private Sub Worksheet_Activate()  
  Dim i As Long  
  Do while (true)
    For i = 2 To 99  
      If Cells(i, 11).Value > 25000 Then  
        If Cells(i, 11).Value <> Cells(i, 1).ValueThen
          MsgBox "xxxxx"
          Cells(i, 11).Value = Cells(i, 1).Value 'setting value
        End If
      End If  
    Next i
    Application.Wait (Now + TimeValue("0:00:10")) 'wait 10 seconds
  Loop
End Sub

edited Jun 02 '16 at 13:28

answered May 22 '16 at 02:47

teckchai lim

51
5

Thank you, I'll try it tomorrow when markets open :) – user6366207 May 22 '16 at 15:06
I just tried it but the loop makes Excel jams all the time and I no longer can click on a cell .. so I replaced Application.Wait by Application.OnTime and I deleted the "Do While .. Loop" it works perfectly. Thanks again ! :) – user6366207 May 23 '16 at 16:14
Hi guys, I have one more issue .. the VBA code works perfectly and it runs every 10 sec .. however, the pop-up window appears many times (every 10 sec) if the information is the same every 10 sec and will stop only if the condition is no longer verified .. Is there a way to get the pop-up window to appear only the first time the condition is met ? If the condition is still verified after 1 minute for instance, I don't want to see the pop-up window @teckchai lim @ BitAccesser – user6366207 May 31 '16 at 21:44
1

Store the cells that meet the conditions (in an array or a cell in the row of the sheet (maybe a timestamp) for example) and check if they are stored before displaying the msgbox. When should the msgbox appear again? The next day? The next hour? – BitAccesser Jun 04 '16 at 02:16

Make a VBA macro run automatically/constantly

1 Answers1