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
– Tim Williams

2020-06-25 22:43:48 +00:00
Commented Jun 25, 2020 at 22:43 — Tim Williams
– Tim Williams, Commented Jun 25, 2020 at 22:43

User1974 · Accepted Answer · 2020-06-26 17:10:16Z

1

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

answered Jun 26, 2020 at 17:10

User1974

3661 gold badge26 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Return server error from function as string?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related