Temporarily disable macro for 5 seconds and then have it continue

[eques247]

Hi,

I'm trying to figure out how to temporarily disable my macro for like 5 seconds and then have it continue running the rest of the script. I tried using the OnTime function (shown below), but it caused the following error message to pop up "Cannot run the macro 'C:\...\testbook.xlsm'!statuscheck'. The macro may not be available in this workbook or all macros may be disabled."

Sub test()
Dim i As Integer

For i = 1 To 3
Application.Calculate
Next i

Application.OnTime Now + TimeValue("00:00:05"), "statuscheck"

End sub

Sub statuscheck()

Msgbox "done"

End sub

Any help with this would be much appreciated. Thanks!

 

[JLGWhiz]

You don't need the time delay. Just insert the macro call into the code you are running at the point you want to run Statuscheck.

Sub t()
MsgBox "Macro is being called"
Statuscheck
MsgBox "Continue this macro"
End Sub

Just make sure that Statuscheck is in the standard code module 1.

 

[eques247]

Thanks for your reply. The reason why I need the time delay is that when the calculate command is run, it is supposed to start importing data from Bloomberg into my excel workbook. Unfortunately, the data only gets imported when no macro is running, which is why I need some kind of break function and then have it continue after a few seconds. With my current code, the msgbox "Done" is displayed prematurely before the data is actually fully imported

 

[JLGWhiz]

Thanks for your reply. The reason why I need the time delay is that when the calculate command is run, it is supposed to start importing data from Bloomberg into my excel workbook. Unfortunately, the data only gets imported when no macro is running, which is why I need some kind of break function and then have it continue after a few seconds. With my current code, the msgbox "Done" is displayed prematurely before the data is actually fully imported

You can try this snippet. It will do a 5 second pause and let other activity take place simultaneously. I would suggest putting it immediately after the macro call.

s = Timer + 5
Do While Timer < s
	DoEvents
Loop

If you need more or less time, you just change the 5 to whatever you need. You can use decimal places for tenths and hundredths of a second, if needed.

 

[JLGWhiz]

If you want to set it up like a function call.

Sub TimeDelay(amt As Double)
s = Timer + amt
Do While Timer < s
    DoEvents
Loop
End Sub

Then to use it in your code

Sub t()
Statuscheck
TimeDelay 5
MsgBox "OK"
End Sub

The delay code would go in the standard code module 1.

 

[Christiaan]

You can also use the following:

Application.Wait (Now + TimeValue("00:00:05")).

This forces your macro to wait 5 seconds, before it continues with the next line of code.

 

[eques247]

You can also use the following:

Application.Wait (Now + TimeValue("00:00:05")).

This forces your macro to wait 5 seconds, before it continues with the next line of code.

For some reason this didn't work either. I guess the data can only be imported when no macro is running which is quite counterintuitive. Are there any other options I could utilize? Like somehow exit the sub and then reenter it after a few seconds

 

[eques247]

You can try this snippet. It will do a 5 second pause and let other activity take place simultaneously. I would suggest putting it immediately after the macro call.

s = Timer + 5
Do While Timer < s
    DoEvents
Loop

If you need more or less time, you just change the 5 to whatever you need. You can use decimal places for tenths and hundredths of a second, if needed.

Thanks!! I'll try this out when I get back to my Bloomberg machine on Monday. Sadly, I do not have remote access to Bloomberg.

 

[eques247]

Hi all. I was able to figure it out and get it to work. My issue was that I was missing a special Bloomberg API function

Do while Left(Range("[COLOR=#0000cd]first cell in the data table[/COLOR]"),3) = "Ret"
     BloombergUI.RefreshAllStaticData
     DoEvents
Loop

Thanks everyone for your help!

 

[JLGWhiz]

Hi all. I was able to figure it out and get it to work. My issue was that I was missing a special Bloomberg API function

Do while Left(Range("[COLOR=#0000cd]first cell in the data table[/COLOR]"),3) = "Ret"
     BloombergUI.RefreshAllStaticData
     DoEvents
Loop

Thanks everyone for your help!

Thanks for the feedback,
Regards, JLG