Return server error from function as string?

Question

I have an Excel VBA function that takes a URL and returns a response (source: getHTTP with VBA?).

response = GetHTTP(.ListColumns(colNameURL).DataBodyRange(n).Value)

...

Public Function GetHTTP(ByVal url As String) As String

  With CreateObject("MSXML2.XMLHTTP")
    .Open "GET", url, False: .Send
    GetHTTP = StrConv(.responseBody, vbUnicode)
  End With

End Function

The function works as expected.

However, if there is a problem with the URL (like the server being down), then the VBA editor pops up an error dialog.

Run-time error '-2147467259 (80004005)': Uspecified error

Instead of popping up a dialog, I would like the function to return the error message as a string.

Is there a way to return the server error as a string (without doing the HTTP call more than once)?

What I've tried:

I can get the function to return a pre-defined error message (from a constant).

Public Const InvalidURL = "Problem with URL or Server"


Public Function GetHTTP(ByVal url As String) As String

On Error GoTo ConnectionError:

    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", url, False: .Send
        GetHTTP = VBA.StrConv(.responseBody, vbUnicode)
    End With

On Error GoTo 0

Exit Function

ConnectionError:
GetHTTP = InvalidURL

End Function

But that's not quite what I'm looking for. I want to return the true runtime error message (example: the error from the server).

You need to wrap an error handler around the `Open()` call – Tim Williams Jun 25 '20 at 22:43 — Tim Williams, Jun 25 '20 at 22:43

score 1 · Answer 1 · answered Jun 26 '20 at 17:10

It was pretty simple:

GetHTTP = "Problem with URL or server: " & Err.Number & " " & Err.Description

Here's the full function:

Public Function GetHTTP(ByVal url As String) As String

On Error GoTo ConnectionError:

    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", url, False: .Send
        GetHTTP = VBA.StrConv(.responseBody, vbUnicode)
    End With

On Error GoTo 0

Exit Function

ConnectionError:
GetHTTP = "Problem with URL or server: " & Err.Number & " " & Err.Description

End Function

Return server error from function as string?

1 Answers1

Linked