VBA: Transforming XML Error Messages into VBA Errors (Raising or Throwing Errors)

This is trial code that I used to translate an error from a Yahoo web service into a COM ErrObject.

It’s not real XML parsing, but good enough for this purpose. IF an error message is sent, we extract the message and then use Err.Raise to throw an error.

Sub testRegex()
    Dim response As String
    response = "<?xml version=""1.0"" encoding=""UTF-8""?>:+" & vbCrLf & _
        "<Error xmlns=""urn:yahoo:api"">" & vbCrLf & _
        "   The following errors were detected:" & vbCrLf & _
        "        <Message>unable to parse location</Message>" & vbCrLf & _
        "</Error>" & vbCrLf & _
        "<!-- ws01.ydn.gq1.yahoo.com uncompressed/chunked Tue Aug 11 15:44:44 PDT 2009 -->"
    e = RegExMatch(response, "<Error xmlns=""urn:yahoo:api"">s*.*s*.*<Message>(.+)</Message>s*</Error>")
    Debug.Print e
    If (e <> "") Then
        Err.Raise 123, , e
    End If
End Sub

Note that we don’t create an instance of ErrObject (we don’t do a “Dim e as ErrObject”). You can’t instantiate one. There’s only a single Err object in the environment, and you reuse it. That’s why Err.Raise takes arguments, instead of allowing you to change the value of an Err.

The definition of RegExMatch is:

' Returns the first regular expression match object of comparing regular express test to source
Function RegExMatch(ByRef Source As String, _
                      ByRef test As String) As String
    Dim regex As Object
    Set regex = CreateObject("vbscript.regexp")
    
    Dim match As Object
    
    With regex
        .Pattern = test
        .Global = True
        .MultiLine = True
    End With
    
    Set match = regex.Execute(Source)
    If match.Count > 0 Then
        If match(0).SubMatches.Count > 0 Then
            RegExMatch = match(0).SubMatches(0)
        Else
            RegExMatch = ""
        End If
    Else
        RegExMatch = ""
    End If
End Function

Now you can use exception handling to deal with errors from the web service.

In this application, we really just want to mark the error and continue encoding more data.

Exception handling is nice because the function calls are nested a few levels deep. The looping is done up at a layer where we do a lot of SQL. The network communication is done within a network communication method, and there’s one class in-between. You want the error on the network side to affect the behavior of the loop up in the SQL-calling layer.

With exceptions, each layer just needs a little code to catch the error and re-throw it up to the caller. Eventually it will be caught by a a caller that will log the error, and continue processing.