How VBA Talks to the Web

A request and a response, nothing more

Every web call has the same shape. You send a request: a method (GET, POST), a URL, some headers, sometimes a body. The server sends back a response: a status number, some headers, and a body of text. VBA has no Http keyword. Instead you create a Windows COM object that does the talking and read the answer out of its properties. Say the platform part plainly: these objects exist on Windows only. Excel for Mac has no supported HTTP object, and the usual workaround there is shelling out to curl.

Three objects, and which to pick

• MSXML2.XMLHTTP - simplest. It rides on the machine's Internet Explorer stack, inheriting proxy settings, cookies and credentials. Convenient behind a corporate proxy, but it has no timeout control, and it caches.
• MSXML2.ServerXMLHTTP.6.0 - the same interface plus a real setTimeouts and its own network stack. The sensible default for unattended code, and what the macro below uses.
• WinHttp.WinHttpRequest.5.1 - the most control: timeouts, proxy, redirect and certificate options. Reach for it when the other two misbehave.

Option Explicit

Sub WhichObjects()
    Dim a As Object
    Dim b As Object
    Dim c As Object

    Set a = CreateObject("MSXML2.XMLHTTP")               ' simple, no timeouts
    Set b = CreateObject("MSXML2.ServerXMLHTTP.6.0")     ' the sensible default
    Set c = CreateObject("WinHttp.WinHttpRequest.5.1")   ' the most control

    Debug.Print TypeName(a); " "; TypeName(b); " "; TypeName(c)
End Sub

A complete call you can run right now

This fetches today's CHF-to-EUR reference rate from frankfurter.app, a free public service that publishes European Central Bank rates. No key, no account, no sign-up. Paste it into a standard module (Alt+F11, Insert - Module) and press F5 in GetReferenceRate. With no network it tells you so and stops; it does not crash and it does not write a wrong number to the sheet.

Option Explicit

Sub GetReferenceRate()
    Dim http As Object
    Dim url As String
    Dim body As String
    Dim rate As Double
    Dim ws As Worksheet

#If Mac Then
    MsgBox "The HTTP objects used here are Windows components. " & _
           "Excel for Mac has no supported equivalent.", vbExclamation
    Exit Sub
#End If

    ' A public endpoint: European Central Bank rates, no key, no sign-up
    url = "https://api.frankfurter.app/latest?from=CHF&to=EUR"

    Set http = CreateObject("MSXML2.ServerXMLHTTP.6.0")

    On Error Resume Next
    http.setTimeouts 5000, 5000, 10000, 15000
    http.Open "GET", url, False
    http.setRequestHeader "Accept", "application/json"
    http.setRequestHeader "Cache-Control", "no-cache"
    http.send

    If Err.Number <> 0 Then
        MsgBox "No reply at all." & vbCrLf & _
               "VBA error " & Err.Number & ": " & Err.Description & vbCrLf & vbCrLf & _
               "No connection, a proxy in the way, or the host is blocked. " & _
               "This is a network problem, not a fault in the code.", _
               vbExclamation, "Request failed"
        Err.Clear
        Exit Sub
    End If
    On Error GoTo 0

    If http.Status <> 200 Then
        MsgBox "The server answered, but with status " & http.Status & " " & _
               http.statusText & ", so there is no rate to read.", _
               vbExclamation, "Unexpected status"
        Exit Sub
    End If

    body = http.responseText
    rate = NumberAfter(body, """EUR"":")

    If rate = 0 Then
        MsgBox "A reply arrived but held no EUR rate. First 300 characters:" & _
               vbCrLf & vbCrLf & Left$(body, 300), vbExclamation
        Exit Sub
    End If

    Set ws = DemoSheet("WebDemo")
    ws.Range("A1").Value = "1 CHF in EUR"
    ws.Range("B1").Value = rate
    ws.Range("A2").Value = "Fetched"
    ws.Range("B2").Value = Now
    ws.Range("B2").NumberFormat = "yyyy-mm-dd hh:mm"
    ws.Columns("A:B").AutoFit
    ws.Activate

    Debug.Print body

    MsgBox "1 CHF = " & rate & " EUR", vbInformation, "Done"
End Sub

Private Function NumberAfter(ByVal jsonText As String, ByVal marker As String) As Double
    Dim p As Long

    p = InStr(1, jsonText, marker, vbBinaryCompare)
    If p = 0 Then Exit Function

    ' Val stops at the first character that is not part of a number,
    ' and always reads "." as the decimal point, on every locale
    NumberAfter = Val(Mid$(jsonText, p + Len(marker)))
End Function

Private Function DemoSheet(ByVal sheetName As String) As Worksheet
    Dim ws As Worksheet

    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets(sheetName)
    On Error GoTo 0

    If ws Is Nothing Then
        Set ws = ThisWorkbook.Worksheets.Add( _
            After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        ws.Name = sheetName
    End If

    Set DemoSheet = ws
End Function

Reading it line by line

• setTimeouts 5000, 5000, 10000, 15000 - resolve, connect, send and receive, in milliseconds. Without them a dead host leaves Excel frozen with no way out but Task Manager.
• The False in .Open is the Async argument. It makes .send wait for the answer, which is what a report macro wants; asynchronous calls in VBA mean polling readyState in a DoEvents loop.
• On Error Resume Next around .send catches the offline case: when nothing answers, .send raises a VBA error, so the macro checks Err.Number, says so in words and exits.
• http.Status is checked separately, because a 404 or a 401 is still a completed round trip: no VBA error is raised and responseText contains the server's complaint instead of your data.
• NumberAfter is deliberately crude: it finds "EUR": and reads what follows. It uses Val, not CDbl, because Val always treats . as the decimal point - otherwise the rate becomes 106 on a German machine.
• The rate = 0 check covers a reply in an unexpected shape: you see the first 300 characters instead of a silent zero on the sheet.

The trap: you are being served yesterday's answer

MSXML2.XMLHTTP goes through Windows' WinINET cache. Call the same URL twice and the second call may be answered from disk without touching the network. You fix the server, re-run the macro, see the old value, and spend an hour debugging code that was already correct. ServerXMLHTTP has its own stack and does not do this, but a proxy in between still can. Two cheap defences:

' Belt and braces against a cached answer
http.setRequestHeader "Cache-Control", "no-cache"
http.setRequestHeader "Pragma", "no-cache"

' Or make every URL unique, which no cache can match
url = url & "&_=" & CLng(Timer * 1000)

How it goes on

The pattern above is a start, not a finished tool. The next lessons build a GET wrapper that reports failure properly, work through the status codes you will meet, and cover headers, POST bodies and where an API key belongs - a header, never the URL. Then comes an honest assessment of JSON in VBA, which has no built-in parser at all, followed by retries, rate limits and whole result sets on a sheet.