Return server error from function as string?

[Blue1971]

I have an Excel VBA function that takes a URL and returns a response.

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)?

 

[John_w]

Try this:

Public Function GetHTTP(ByVal url As String) As String

    On Error Resume Next
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", url, False: .send
        If Err.Number = 0 Then
            GetHTTP = StrConv(.responseBody, vbUnicode)
        Else
            GetHTTP = Err.Description
        End If
    End With
    On Error GoTo 0

End Function

 

[Blue1971]

Try this:

Public Function GetHTTP(ByVal url As String) As String

    On Error Resume Next
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", url, False: .send
        If Err.Number = 0 Then
            GetHTTP = StrConv(.responseBody, vbUnicode)
        Else
            GetHTTP = Err.Description
        End If
    End With
    On Error GoTo 0

End Function

Thanks. That works!