Capture the Text of a VBA Error Mesage

[ruve1k]

How can I capture the entire text of a VBA error message and save it into a string variable?

Note that I would like to capture the error message not only when it is a native VBA error message but also when it is a SQL error message that gets kicked through to VBA. (E.g. when the VBA connects to a SQL Server database and executes a number of SQL commands against the database.)

 

[tusharm]

One way...

    On Error Resume Next
    'do whatever
    If Err.Number <> 0 Then MsgBox Err.Description
    On Error GoTo 0

I've used the above with ADO and DAO.

Some (all?) Windows APIs trap any error themselves and then return an appropriate error code. See the documentation for the specific API.

 

[ruve1k]

Thanks! It worked well.
I did the following:

1. I used

   On Error Goto myErrorHandler

   to trap the error.
2. Then I saved the error description into a string variable using the err.description property as you suggested and later wrote the error description to a SQL log table
3. Then I experimneted with two options for throwing the error message:

• Natively throwing the VBA error using [FONT=arial, helvetica]Err.Raise(number[, source, description, helpfile, helpcontext])[/FONT]
• Throwing a pseudo-error using a VBA message box

Thanks again for your help.