Link to home
Create AccountLog in
Avatar of OCM123
OCM123

asked on

Excel MsgBox Errors & VBA Scheduler

I have an issue whereby, on random occassions, a data feed fails into a spreadsheet, and my VBA code that is running throws out a RunTime Error 13. If i click on the End or Debug buttons, and then just reset the VBA, the scheduler runs again and updates my sheet on the next cycle.

The problem is, the random occassion is usually at say 04:34:50 in the morning, when I am asleep. So i am not here to click on the message-box.

What i am looking for is a way to ignore this code break, such that 30 seconds later, the code cycle simply runs again. The data feed is usualy unavailable for a matter of a few seconds, so simply running the process again makes everything work.

I hope I've explained that clearly, perhaps i need to clarify more ? thanks for help
Avatar of javaftper
javaftper
Flag of Afghanistan image

You could wrap your code with an error handler; on error you could then pause the code for say a minute then resume code execution.

Eg-
Sub() 
 On Error Goto ErrHandler:
    N = 1 / 0    ' cause an error
    '
    ' more code
    '
    Exit Sub 
    ErrHandler:
    ' error handling code
    Application.Wait (now() + timevalue("00:01:00")) 'wait 1 minute
    Resume Next
    End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2012