CODEWITHSUNDEEP
excelvbaemaileventsrtd
Simon Tonkin
Simon Tonkin

Reputation: 1

VBA Error Handling for Excel Errors/Dialog boxes

I have an Excel workbook that uses an RTD server to bring data in and I need to know when an error has occurred.

Usually the workbook stops working when Excel comes up with a dialogue box that says "The real-time server 'RTDServer.Class' is not responding. Would you like Microsoft Excel to attempt to restart the server?" The problem is the dialogue boxes stops everything - I want to control this so I can alert myself via email.

I have written some code to try and handle errors that are generated directly from excel. However, I am a little uncertain if this is going to work as most of the information is on Error Handling for VBA code.

The first thing I have tried to choose the correct event in this case I used SheetCalculate but thought I could use SheetChange or workbook save (as it saves automatically). There does not seem to be a dialogue box / error? Basically on Error it would send an email to myself. I am not sure whether it would capture these events/errors as they are fairly rare could happen once per week.

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

    On Error GoTo CleanFail'no error
    CleanExit:'no errorExit Sub
    CleanFail:
    Error_Handler Err.Number, Err.Source, Err.Description, Err.HelpFile, Err.HelpContext 
    Resume CleanExit

End Sub

The second procedure is basically called to handle the error and send the email.

Sub Error_Handler(Error_Num As Variant, Error_Source As Variant, Error_Description As Variant, Error_HelpFile As Variant, Error_HelpContext As Variant)
    Send Email_Alert
End Sub

Thanks for your help

Upvotes: 0

Views: 54

Answers (1)

CHill60
CHill60

Reputation: 1958

There is no event to capture, VBA On Error doesn't work like that. What you can do is write a wrapper for your RTD call wrappers-to-simplify-use - MS Lean. Be aware that not all errors may be communicated back to the VBA however

Upvotes: 0

Related Questions