Integrating Excel VBA with REST APIs: Fetch, Parse, and Automate Live Data Workflows

Introduction

You've got a dashboard that your VP checks every Monday morning. It pulls currency exchange rates, inventory levels from your ERP system, or maybe weather data that feeds a logistics model. Right now, someone — probably you — manually downloads a CSV, pastes it in, reformats the headers, and runs a macro to refresh the pivot tables. It takes 45 minutes, it's error-prone, and it makes you feel like a human ETL pipeline.

REST APIs exist to solve exactly this problem. Nearly every modern data system — financial data providers, CRMs, logistics platforms, government databases, and cloud services — exposes a REST API that lets you fetch structured, live data on demand. And Excel VBA, despite its age, has everything you need to make HTTP requests, parse JSON responses, and wire up fully automated workflows directly inside the workbooks your stakeholders already live in. This isn't a workaround; it's a legitimate integration pattern used in enterprise environments every day.

By the end of this lesson, you'll be able to build production-grade VBA routines that authenticate with REST APIs, handle paginated responses, parse complex JSON structures without third-party libraries, manage errors gracefully, and schedule automated refreshes — all from inside Excel. We're going to build this up from the ground up, touching every rough edge along the way.

What you'll learn:

• How to make authenticated HTTP GET and POST requests from VBA using MSXML2.XMLHTTP60 and WinHttp.WinHttpRequest.5.1
• How to parse JSON responses in native VBA without external dependencies, and when to use the ScriptControl or JsonConverter library approach instead
• How to handle pagination, rate limiting, and multi-call workflows for real API endpoints
• How to structure reusable, maintainable VBA API client modules with proper error handling
• How to automate refresh cycles using Application.OnTime and workbook events

---

Prerequisites

This lesson assumes you're comfortable with VBA — you write subroutines and functions regularly, you understand object variables, and you're not intimidated by the VBA IDE. You should know what a REST API is conceptually: HTTP verbs, endpoints, request headers, and JSON responses. You don't need to be a web developer, but you should have seen a JSON payload before and understand what a status code like 200 or 401 means. If you've used Power Query to pull API data, you already have the right mental model — we're just dropping one level lower for more control.

---

Understanding the HTTP Stack Available to VBA

Before writing a single line of code, you need to understand your options, because the wrong choice here causes subtle, maddening bugs that only appear in certain environments.

VBA on Windows has access to two primary COM objects for making HTTP requests:

MSXML2.XMLHTTP60 — Part of the Microsoft XML library, this object runs on the same thread as Excel. It's synchronous by default, meaning Excel freezes while the request is in flight. It respects the user's Internet Explorer proxy settings, which is enormously important in corporate environments where all traffic must route through a proxy server.

WinHttp.WinHttpRequest.5.1 — Part of the Windows HTTP Services library, this is a lower-level object that does not automatically inherit IE proxy settings. It's generally faster and more reliable for server-to-server type calls, but in a corporate network behind a proxy, you'll need to configure the proxy manually or your requests will silently fail.

There's also MSXML2.ServerXMLHTTP60, which behaves like WinHttp in that it bypasses IE proxy settings. This is sometimes the right choice in automated, unattended scenarios.

Here's how to choose:

Scenario	Recommended Object
Interactive workbook, corporate network	MSXML2.XMLHTTP60
Automated/scheduled, no proxy	WinHttp.WinHttpRequest.5.1
Automated, needs proxy config	MSXML2.ServerXMLHTTP60 with manual proxy
Need async behavior	MSXML2.XMLHTTP60 with OnReadyStateChange

For this lesson, we'll use MSXML2.XMLHTTP60 for interactive examples and show you how to swap in WinHttp where it matters.

Late Binding vs. Early Binding: You can declare these objects using early binding (Dim http As MSXML2.XMLHTTP60) after adding the reference via Tools → References, or late binding (Dim http As Object with CreateObject("MSXML2.XMLHTTP60")). Early binding gives you IntelliSense and slightly better performance. Late binding requires no reference and is more portable across machines. In production, use early binding during development and late binding for deployment — or just commit to early binding and document the required reference.

---

Building Your First API Request

Let's work with a real, publicly accessible API: the Open Exchange Rates API (free tier available) or, for zero-friction testing, https://api.exchangerate-api.com/v4/latest/USD, which requires no authentication. We'll also use https://jsonplaceholder.typicode.com for structured test payloads.

Here is the foundational pattern for a GET request:

Public Function MakeGetRequest(ByVal url As String) As String
    Dim http As Object
    Dim response As String
    
    Set http = CreateObject("MSXML2.XMLHTTP60")
    
    On Error GoTo HttpError
    
    http.Open "GET", url, False  ' False = synchronous
    http.setRequestHeader "Content-Type", "application/json"
    http.setRequestHeader "Accept", "application/json"
    http.setRequestHeader "User-Agent", "ExcelVBA/1.0"
    
    http.Send
    
    If http.Status = 200 Then
        response = http.responseText
    Else
        Err.Raise vbObjectError + 1001, "MakeGetRequest", _
            "HTTP Error " & http.Status & ": " & http.statusText
    End If
    
    MakeGetRequest = response
    Set http = Nothing
    Exit Function

HttpError:
    Set http = Nothing
    MakeGetRequest = ""
    Debug.Print "Error in MakeGetRequest: " & Err.Description
    Err.Raise Err.Number, Err.Source, Err.Description
End Function

Notice a few things. We're not returning the raw http object — we're returning the response body as a string. This is intentional: it separates the transport layer from the parsing layer, which you'll appreciate when you're debugging at 10 PM. We also raise an error for non-200 status codes rather than silently returning an empty string. Swallowing HTTP errors is one of the most common mistakes in VBA API work, and it makes failures nearly impossible to diagnose.

The User-Agent header deserves a mention. Some APIs reject requests without a User-Agent or with the default MSXML agent string. Setting a descriptive one is good practice and occasionally necessary.

Let's call this and see it work:

Public Sub TestExchangeRates()
    Dim jsonResponse As String
    Dim url As String
    
    url = "https://api.exchangerate-api.com/v4/latest/USD"
    jsonResponse = MakeGetRequest(url)
    
    Debug.Print Left(jsonResponse, 500)  ' Print first 500 chars to Immediate Window
End Sub

Run this and you should see something like:

{"provider":"https://www.exchangerate-api.com","WARNING_UPGRADE_TO_V6":"...","terms":"...","base":"USD","date":"2024-11-15","time_last_updated":1731628800,"rates":{"AED":3.6725,"AFN":67.65,"ALL":92.67,"AMD":386.85,"ANG":1.79,...}}

You've made your first live API call from Excel VBA. Now the real work begins — turning that string into usable data.

---

Parsing JSON in Native VBA: The Ugly Truth and the Right Solutions

This is where most VBA API tutorials fall apart. JSON parsing in native VBA is genuinely painful, and understanding why helps you make better decisions about your approach.

VBA has no built-in JSON parser. Your options are:

1. Manual string parsing — Fragile, unmaintainable, breaks on edge cases. Avoid for anything beyond the simplest key-value pairs.
2. ScriptControl (JScript engine) — Use the built-in JavaScript engine to evaluate JSON. Elegant but limited to 32-bit Excel and deprecated in some environments.
3. VBA-JSON (JsonConverter by Tim Hall) — Open source, single .bas module import, handles complex nested structures. This is the pragmatic choice for most professional use.
4. Dictionary + custom parser — Viable for specific known structures, teaches you the internals.

The ScriptControl Approach (32-bit Excel Only)

Public Function ParseJsonWithScript(ByVal jsonString As String) As Object
    Dim sc As Object
    Set sc = CreateObject("ScriptControl")
    sc.Language = "JScript"
    
    sc.Eval "var data = " & jsonString
    Set ParseJsonWithScript = sc.CodeObject
End Function

This works beautifully in 32-bit Office but throws "ActiveX component can't create object" in 64-bit Excel. Since most enterprise Excel deployments are now 64-bit, this approach has a shrinking window of applicability. Don't build production systems on it.

Using VBA-JSON: The Professional Choice

Download the single file JsonConverter.bas from Tim Hall's GitHub repository (github.com/VBA-tools/VBA-JSON). In the VBA IDE, go to File → Import File and select the .bas file. Also add a reference to "Microsoft Scripting Runtime" (Tools → References) for the Dictionary object support.

Once imported, you parse JSON like this:

Public Sub ParseExchangeRates()
    Dim jsonString As String
    Dim parsed As Object
    Dim rates As Object
    Dim currencyCode As Variant
    
    jsonString = MakeGetRequest("https://api.exchangerate-api.com/v4/latest/USD")
    
    ' JsonConverter.ParseJson returns a Dictionary or Collection
    Set parsed = JsonConverter.ParseJson(jsonString)
    
    ' Access top-level keys like a Dictionary
    Debug.Print "Base currency: " & parsed("base")
    Debug.Print "Last updated: " & parsed("date")
    
    ' The "rates" key contains a nested Dictionary
    Set rates = parsed("rates")
    
    ' Iterate all currency codes
    For Each currencyCode In rates.Keys
        Debug.Print currencyCode & ": " & rates(currencyCode)
    Next currencyCode
End Sub

JSON objects become VBA Dictionary objects. JSON arrays become VBA Collection objects. Nested structures work as you'd expect — you just chain the key lookups. This is clean, readable, and handles the full JSON spec including Unicode escape sequences, which manual parsing gets wrong almost every time.

Writing Rates to the Worksheet

Let's make this actually useful:

Public Sub RefreshExchangeRates()
    Dim ws As Worksheet
    Dim jsonString As String
    Dim parsed As Object
    Dim rates As Object
    Dim currencyCode As Variant
    Dim targetCurrencies As Variant
    Dim i As Integer
    
    ' Define which currencies we care about
    targetCurrencies = Array("EUR", "GBP", "JPY", "CAD", "AUD", "CHF", "CNY", "INR")
    
    Set ws = ThisWorkbook.Sheets("Exchange Rates")
    
    ' Clear old data (but preserve headers)
    ws.Range("A2:B100").ClearContents
    
    jsonString = MakeGetRequest("https://api.exchangerate-api.com/v4/latest/USD")
    
    If jsonString = "" Then
        MsgBox "Failed to fetch exchange rates. Check your connection.", vbCritical
        Exit Sub
    End If
    
    Set parsed = JsonConverter.ParseJson(jsonString)
    Set rates = parsed("rates")
    
    ' Write headers
    ws.Cells(1, 1).Value = "Currency"
    ws.Cells(1, 2).Value = "Rate (vs USD)"
    ws.Cells(1, 3).Value = "Last Updated"
    
    i = 2
    For Each currencyCode In targetCurrencies
        If rates.Exists(currencyCode) Then
            ws.Cells(i, 1).Value = currencyCode
            ws.Cells(i, 2).Value = rates(currencyCode)
            ws.Cells(i, 3).Value = parsed("date")
            i = i + 1
        End If
    Next currencyCode
    
    ws.Columns("A:C").AutoFit
    
    Debug.Print "Rates refreshed at " & Now()
End Sub

Important: Notice we check rates.Exists(currencyCode) before accessing the value. Trying to access a key that doesn't exist in a VBA Dictionary raises error 457. Get in the habit of always checking .Exists() on dynamic dictionary lookups.

---

Authentication: Bearer Tokens, API Keys, and OAuth Patterns

Most real-world APIs require authentication. Let's cover the patterns you'll actually encounter.

API Key in Header (Most Common)

Many APIs — Alpha Vantage, OpenWeatherMap, Airtable, and hundreds of others — authenticate via a key in a request header:

Public Function MakeAuthenticatedRequest(ByVal url As String, _
                                          ByVal apiKey As String) As String
    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP60")
    
    http.Open "GET", url, False
    http.setRequestHeader "Content-Type", "application/json"
    http.setRequestHeader "Accept", "application/json"
    http.setRequestHeader "Authorization", "Bearer " & apiKey
    ' Some APIs use a custom header name:
    ' http.setRequestHeader "X-API-Key", apiKey
    ' http.setRequestHeader "apikey", apiKey
    
    http.Send
    
    If http.Status = 200 Then
        MakeAuthenticatedRequest = http.responseText
    Else
        Err.Raise vbObjectError + 1001, "MakeAuthenticatedRequest", _
            "HTTP " & http.Status & " - " & http.responseText
    End If
    
    Set http = Nothing
End Function

Storing API Keys Safely in VBA

Never hardcode API keys in your VBA source code. If the workbook is shared, emailed, or stored in a shared drive, your credentials are exposed. Here are three progressively better approaches:

Approach 1: Named Range as Config Store the key in a hidden, password-protected sheet:

Function GetApiKey(keyName As String) As String
    Dim configSheet As Worksheet
    Set configSheet = ThisWorkbook.Sheets("_Config")  ' Hidden sheet
    
    Dim keyRange As Range
    Set keyRange = configSheet.Columns(1).Find(keyName, LookAt:=xlWhole)
    
    If Not keyRange Is Nothing Then
        GetApiKey = keyRange.Offset(0, 1).Value
    Else
        Err.Raise vbObjectError + 2001, "GetApiKey", "Key not found: " & keyName
    End If
End Function

Approach 2: Windows Registry

Function GetApiKeyFromRegistry(keyName As String) As String
    Dim wsh As Object
    Set wsh = CreateObject("WScript.Shell")
    On Error Resume Next
    GetApiKeyFromRegistry = wsh.RegRead("HKCU\Software\WickedSmartData\" & keyName)
    On Error GoTo 0
    Set wsh = Nothing
End Function

Approach 3: Environment Variables (best for shared/automated environments)

Function GetApiKeyFromEnv(envVarName As String) As String
    GetApiKeyFromEnv = Environ(envVarName)
End Function

Set environment variables at the OS level. They're not visible in the workbook file at all.

POST Requests with JSON Body

For APIs that require POST — creating records, submitting data, triggering jobs — you need to send a JSON body:

Public Function MakePostRequest(ByVal url As String, _
                                 ByVal jsonBody As String, _
                                 ByVal apiKey As String) As String
    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP60")
    
    http.Open "POST", url, False
    http.setRequestHeader "Content-Type", "application/json"
    http.setRequestHeader "Accept", "application/json"
    http.setRequestHeader "Authorization", "Bearer " & apiKey
    
    ' Send converts String to bytes; encoding matters here
    http.Send jsonBody
    
    ' Accept 200 and 201 (Created) as success
    If http.Status = 200 Or http.Status = 201 Then
        MakePostRequest = http.responseText
    Else
        Err.Raise vbObjectError + 1001, "MakePostRequest", _
            "HTTP " & http.Status & ": " & http.responseText
    End If
    
    Set http = Nothing
End Function

Building the JSON body as a string manually works for simple structures:

Dim payload As String
payload = "{""symbol"": ""AAPL"", ""quantity"": 100, ""order_type"": ""market""}"

For complex nested structures, use VBA-JSON's ConvertToJson function:

Dim orderDict As Object
Set orderDict = CreateObject("Scripting.Dictionary")
orderDict.Add "symbol", "AAPL"
orderDict.Add "quantity", 100
orderDict.Add "order_type", "market"
orderDict.Add "timestamp", Format(Now(), "yyyy-mm-ddThh:mm:ss") & "Z"

Dim payload As String
payload = JsonConverter.ConvertToJson(orderDict)
' payload = {"symbol":"AAPL","quantity":100,"order_type":"market","timestamp":"2024-11-15T14:30:00Z"}

---

Handling Pagination: Real APIs Return More Data Than Fits in One Response

This is where VBA API integrations most commonly break down in production. You fetch 100 records and think you have everything, but the API quietly returned only the first page. A week later someone notices the data is wrong.

Most REST APIs signal pagination through one of three mechanisms:

1. Offset/Limit Parameters (most common)

GET /api/v1/transactions?limit=100&offset=0
GET /api/v1/transactions?limit=100&offset=100
GET /api/v1/transactions?limit=100&offset=200

2. Page Number Parameters

GET /api/v1/transactions?page=1&per_page=100
GET /api/v1/transactions?page=2&per_page=100

3. Cursor-Based Pagination (GitHub, Twitter/X, modern APIs) The response includes a next_cursor or next_page_token that you pass in the next request.

Here's a robust paginated fetch that handles offset/limit pagination and collects all records:

Public Function FetchAllTransactions(ByVal baseUrl As String, _
                                      ByVal apiKey As String) As Collection
    Dim allRecords As Collection
    Set allRecords = New Collection
    
    Dim pageSize As Integer
    Dim offset As Long
    Dim hasMore As Boolean
    Dim jsonString As String
    Dim parsed As Object
    Dim pageData As Object
    Dim record As Object
    Dim totalFetched As Long
    Dim requestCount As Integer
    
    pageSize = 100
    offset = 0
    hasMore = True
    totalFetched = 0
    requestCount = 0
    
    Const MAX_REQUESTS As Integer = 50  ' Safety ceiling: 5,000 records max
    
    Do While hasMore And requestCount < MAX_REQUESTS
        Dim pageUrl As String
        pageUrl = baseUrl & "?limit=" & pageSize & "&offset=" & offset
        
        jsonString = MakeAuthenticatedRequest(pageUrl, apiKey)
        
        If jsonString = "" Then
            Err.Raise vbObjectError + 3001, "FetchAllTransactions", _
                "Empty response at offset " & offset
        End If
        
        Set parsed = JsonConverter.ParseJson(jsonString)
        
        ' Most APIs wrap results in a key like "data", "results", or "items"
        Set pageData = parsed("data")
        
        If pageData.Count = 0 Then
            hasMore = False
        Else
            Dim item As Variant
            For Each item In pageData
                allRecords.Add item
            Next item
            
            totalFetched = totalFetched + pageData.Count
            offset = offset + pageSize
            requestCount = requestCount + 1
            
            ' Check if this page was smaller than the page size (last page)
            If pageData.Count < pageSize Then
                hasMore = False
            End If
            
            ' Respect rate limits: pause between requests
            ' Many APIs allow 1 req/second on free tiers
            Application.Wait (Now + TimeValue("0:00:01"))
        End If
        
        Debug.Print "Fetched " & totalFetched & " records so far..."
        DoEvents  ' Keep Excel responsive during long fetches
    Loop
    
    Debug.Print "Total records fetched: " & totalFetched & " in " & requestCount & " requests"
    Set FetchAllTransactions = allRecords
End Function

The Application.Wait call between requests is critical. Rate limiting is real — most free API tiers cap at 60 requests per minute or fewer. Hammering the API without delays will get your key suspended. The DoEvents call keeps Excel's UI thread alive so the user can still cancel if needed.

A word on cursor-based pagination: For cursor-based APIs, the loop structure is similar, but instead of incrementing an offset, you extract the next cursor from the response and stop when the cursor is null or missing:

Dim nextCursor As String
nextCursor = ""
' In the loop:
If parsed.Exists("next_cursor") And parsed("next_cursor") <> "" Then
    nextCursor = parsed("next_cursor")
    pageUrl = baseUrl & "?cursor=" & nextCursor
Else
    hasMore = False
End If

---

Building a Reusable API Client Module

Ad-hoc spaghetti code that makes API calls inline in your business logic routines is a maintenance nightmare. As your integration grows, you want a dedicated module that encapsulates all the HTTP mechanics. Here's a production-grade structure:

' =============================================================================
' Module: modApiClient
' Purpose: Centralized HTTP client for REST API operations
' Author: [Your Name]
' Dependencies: VBA-JSON (JsonConverter), Microsoft Scripting Runtime
' =============================================================================
Option Explicit

' --- Configuration Constants ---
Private Const BASE_URL As String = "https://api.yourservice.com/v2"
Private Const REQUEST_TIMEOUT_MS As Long = 30000  ' 30 seconds
Private Const RETRY_ATTEMPTS As Integer = 3
Private Const RETRY_DELAY_SECONDS As Integer = 2

' --- Core HTTP Engine ---
Private Function ExecuteRequest(ByVal method As String, _
                                 ByVal endpoint As String, _
                                 Optional ByVal body As String = "", _
                                 Optional ByVal apiKey As String = "") As String
    Dim http As Object
    Dim attempt As Integer
    Dim lastError As String
    
    Set http = CreateObject("MSXML2.XMLHTTP60")
    
    ' Set timeout (WinHttp uses SetTimeouts; XMLHTTP uses a different approach)
    ' For XMLHTTP, we manage timeout via Application.Wait externally
    
    For attempt = 1 To RETRY_ATTEMPTS
        On Error Resume Next
        
        http.Open method, BASE_URL & endpoint, False
        http.setRequestHeader "Content-Type", "application/json; charset=utf-8"
        http.setRequestHeader "Accept", "application/json"
        http.setRequestHeader "User-Agent", "ExcelVBA-Client/2.0"
        
        If apiKey <> "" Then
            http.setRequestHeader "Authorization", "Bearer " & apiKey
        End If
        
        If method = "POST" Or method = "PUT" Or method = "PATCH" Then
            http.Send body
        Else
            http.Send
        End If
        
        Dim sendError As Long
        sendError = Err.Number
        On Error GoTo 0
        
        If sendError <> 0 Then
            ' Network-level error (no connection, DNS failure, etc.)
            lastError = "Network error on attempt " & attempt
            If attempt < RETRY_ATTEMPTS Then
                Application.Wait (Now + TimeValue("0:00:0" & RETRY_DELAY_SECONDS))
            End If
        ElseIf http.Status >= 500 Then
            ' Server error — retry
            lastError = "Server error " & http.Status & " on attempt " & attempt
            If attempt < RETRY_ATTEMPTS Then
                Application.Wait (Now + TimeValue("0:00:0" & RETRY_DELAY_SECONDS))
            End If
        ElseIf http.Status = 429 Then
            ' Rate limited — back off longer
            lastError = "Rate limited on attempt " & attempt
            Dim retryAfter As Integer
            retryAfter = 10  ' Default: wait 10 seconds
            On Error Resume Next
            retryAfter = CInt(http.getResponseHeader("Retry-After"))
            On Error GoTo 0
            Application.Wait (Now + TimeValue("0:00:" & Format(retryAfter, "00")))
        ElseIf http.Status >= 200 And http.Status < 300 Then
            ' Success
            ExecuteRequest = http.responseText
            Set http = Nothing
            Exit Function
        Else
            ' Client error (400, 401, 403, 404) — don't retry
            Err.Raise vbObjectError + http.Status, "ExecuteRequest", _
                "HTTP " & http.Status & ": " & http.responseText
            Set http = Nothing
            Exit Function
        End If
    Next attempt
    
    ' All retries exhausted
    Set http = Nothing
    Err.Raise vbObjectError + 3000, "ExecuteRequest", _
        "All " & RETRY_ATTEMPTS & " attempts failed. Last: " & lastError
End Function

' --- Public API Methods ---
Public Function ApiGet(ByVal endpoint As String, _
                        Optional ByVal apiKey As String = "") As Object
    Dim response As String
    response = ExecuteRequest("GET", endpoint, , apiKey)
    Set ApiGet = JsonConverter.ParseJson(response)
End Function

Public Function ApiPost(ByVal endpoint As String, _
                         ByVal payload As Object, _
                         Optional ByVal apiKey As String = "") As Object
    Dim body As String
    body = JsonConverter.ConvertToJson(payload)
    Dim response As String
    response = ExecuteRequest("POST", endpoint, body, apiKey)
    Set ApiPost = JsonConverter.ParseJson(response)
End Function

Public Function ApiDelete(ByVal endpoint As String, _
                           Optional ByVal apiKey As String = "") As Boolean
    Dim response As String
    response = ExecuteRequest("DELETE", endpoint, , apiKey)
    ApiDelete = True  ' If we got here without error, it succeeded
End Function

This module gives you automatic retry with exponential-ish backoff, rate limit handling via Retry-After headers, clean separation of transport and parsing, and public methods that return already-parsed objects. Your business logic routines call ApiGet("/transactions") and get a Dictionary back — they never touch HTTP.

---

Automating Refresh: Scheduled and Event-Driven Updates

You've got the data flow working. Now let's make it run automatically.

Application.OnTime for Scheduled Refreshes

Application.OnTime schedules a macro to run at a specific time. Combine it with a self-rescheduling pattern to create a polling loop:

' In a standard module:
Public refreshJobTime As Date  ' Track the scheduled time so we can cancel it

Public Sub ScheduleRefresh()
    Const REFRESH_INTERVAL_MINUTES As Integer = 15
    
    refreshJobTime = Now + TimeValue("0:" & REFRESH_INTERVAL_MINUTES & ":00")
    Application.OnTime refreshJobTime, "RunRefreshAndReschedule"
    
    Debug.Print "Next refresh scheduled for: " & refreshJobTime
End Sub

Public Sub RunRefreshAndReschedule()
    On Error GoTo RefreshError
    
    ' Run the actual data refresh
    Call RefreshExchangeRates
    Call RefreshInventoryData
    
    ' Update status cell
    ThisWorkbook.Sheets("Dashboard").Range("B1").Value = _
        "Last refreshed: " & Format(Now(), "yyyy-mm-dd hh:mm:ss")
    
    ' Reschedule
    Call ScheduleRefresh
    Exit Sub

RefreshError:
    ' Log error but still reschedule — don't let one failure stop the automation
    Debug.Print "Refresh error at " & Now() & ": " & Err.Description
    
    ' Write error to log sheet
    LogError "Scheduled Refresh", Err.Description
    
    ' Reschedule anyway
    Call ScheduleRefresh
End Sub

Public Sub CancelRefresh()
    On Error Resume Next  ' In case the job isn't scheduled
    Application.OnTime refreshJobTime, "RunRefreshAndReschedule", , False
    On Error GoTo 0
    Debug.Print "Refresh cancelled."
End Sub

Wire ScheduleRefresh to the Workbook_Open event and CancelRefresh to Workbook_BeforeClose:

' In ThisWorkbook:
Private Sub Workbook_Open()
    Call ScheduleRefresh
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call CancelRefresh
End Sub

Critical caveat: Application.OnTime only fires while Excel is open and the workbook is active. If the user closes the workbook or Excel crashes, the schedule is gone. For truly unattended scheduling (overnight batch jobs), you need Windows Task Scheduler calling a VBScript or PowerShell that opens the workbook, runs the macro, and closes it. Application.OnTime is for "refresh every 15 minutes while someone is working with this file" — not for "run at 3 AM."

Workbook Event-Driven Refresh

For dashboards where users navigate between sheets, you can trigger a refresh when they activate the data sheet:

' In the Sheet module for your dashboard sheet:
Private Sub Worksheet_Activate()
    ' Only refresh if data is stale (older than 5 minutes)
    Dim lastRefresh As Date
    Dim lastRefreshCell As Range
    
    Set lastRefreshCell = Me.Range("B1")
    
    If lastRefreshCell.Value = "" Then
        Call RefreshExchangeRates
        Exit Sub
    End If
    
    On Error Resume Next
    lastRefresh = CDate(Replace(lastRefreshCell.Value, "Last refreshed: ", ""))
    On Error GoTo 0
    
    If lastRefresh = 0 Or DateDiff("n", lastRefresh, Now()) > 5 Then
        Call RefreshExchangeRates
    End If
End Sub

---

Building a Complete Real-World Example: GitHub Repository Dashboard

Let's put it all together. We'll build a dashboard that pulls data from the GitHub API — a real, freely accessible API with straightforward authentication — showing you repository stats, open issues, and recent commits for a list of repos you define.

' ============================================================
' Complete GitHub Dashboard Refresh
' Requires: VBA-JSON imported, GitHub Personal Access Token
' ============================================================
Public Sub RefreshGitHubDashboard()
    Dim ws As Worksheet
    Dim apiKey As String
    Dim repos As Variant
    Dim repoName As Variant
    Dim row As Integer
    Dim parsed As Object
    Dim url As String
    
    ' Get token from hidden config sheet
    apiKey = GetApiKey("GITHUB_TOKEN")
    
    ' Define repos to monitor (owner/repo format)
    repos = Array( _
        "pandas-dev/pandas", _
        "numpy/numpy", _
        "scikit-learn/scikit-learn", _
        "microsoft/vscode" _
    )
    
    Set ws = ThisWorkbook.Sheets("GitHub Dashboard")
    
    ' Write headers
    With ws
        .Range("A1:G1").Value = Array( _
            "Repository", "Stars", "Forks", "Open Issues", _
            "Last Push", "Language", "Description")
        .Range("A1:G1").Font.Bold = True
        .Range("A2:G100").ClearContents
    End With
    
    row = 2
    For Each repoName In repos
        url = "https://api.github.com/repos/" & repoName
        
        On Error GoTo RepoError
        
        Dim jsonStr As String
        jsonStr = MakeAuthenticatedRequest(url, apiKey)
        Set parsed = JsonConverter.ParseJson(jsonStr)
        
        With ws
            .Cells(row, 1).Value = repoName
            .Cells(row, 2).Value = parsed("stargazers_count")
            .Cells(row, 3).Value = parsed("forks_count")
            .Cells(row, 4).Value = parsed("open_issues_count")
            
            ' GitHub returns ISO 8601 dates like "2024-11-15T12:30:00Z"
            Dim pushDate As String
            pushDate = parsed("pushed_at")
            ' Convert to Excel date: strip the T and Z
            .Cells(row, 5).Value = CDate(Replace(Replace(pushDate, "T", " "), "Z", ""))
            .Cells(row, 5).NumberFormat = "yyyy-mm-dd hh:mm"
            
            ' Handle nullable fields
            If Not IsNull(parsed("language")) And parsed("language") <> "" Then
                .Cells(row, 6).Value = parsed("language")
            Else
                .Cells(row, 6).Value = "Multiple"
            End If
            
            .Cells(row, 7).Value = Left(parsed("description"), 100)
        End With
        
        row = row + 1
        
        ' GitHub API: 60 req/hour unauthenticated, 5000/hour authenticated
        ' Still good practice to be gentle
        Application.Wait (Now + TimeValue("0:00:01"))
        DoEvents
        
        GoTo NextRepo
        
RepoError:
        ws.Cells(row, 1).Value = repoName
        ws.Cells(row, 2).Value = "ERROR: " & Err.Description
        row = row + 1
        Err.Clear
        Resume NextRepo

NextRepo:
    Next repoName
    
    ws.Columns("A:G").AutoFit
    ws.Range("B2:D" & row - 1).NumberFormat = "#,##0"
    
    ' Sort by stars descending
    ws.Sort.SortFields.Clear
    ws.Sort.SortFields.Add Key:=ws.Range("B2:B" & row - 1), _
        Order:=xlDescending
    ws.Sort.SetRange ws.Range("A1:G" & row - 1)
    ws.Sort.Header = xlYes
    ws.Sort.Apply
    
    MsgBox "GitHub Dashboard refreshed! " & (row - 2) & " repos loaded.", vbInformation
End Sub

Notice how we handle nullable fields (the description can be null for some repos), parse ISO 8601 dates, handle per-row errors without stopping the whole process, and apply formatting after the data is written. These aren't edge cases — they're the normal texture of real API integration work.

---

Hands-On Exercise

Build an automated currency conversion dashboard using the following requirements:

Setup: Create a workbook with two sheets: "Config" and "FX Dashboard."

Part 1 — Data Fetch (30 minutes) Using https://api.exchangerate-api.com/v4/latest/USD (no auth required), write a FetchFXRates() function that:

1. Makes the GET request using MSXML2.XMLHTTP60
2. Parses the response with VBA-JSON
3. Returns a Dictionary of all exchange rates

Part 2 — Dashboard Population (20 minutes) Write PopulateFXDashboard() that:

1. Reads a list of currency codes from column A of the "Config" sheet (put 10-15 currencies there yourself)
2. Fetches live rates
3. Writes each currency, its rate vs USD, and the inverse rate (USD per 1 unit of currency) to the "FX Dashboard" sheet
4. Applies conditional formatting — green if rate improved vs yesterday's value in column D, red if it worsened

Part 3 — Automation (20 minutes)

1. Add a "Refresh" button on the dashboard sheet that calls your populate routine
2. Wire Workbook_Open to call ScheduleRefresh for a 10-minute polling interval
3. Store yesterday's rates in column D when you write today's rates, so tomorrow's run can compare

Stretch goal: Add a POST call to https://jsonplaceholder.typicode.com/posts that sends a JSON summary of your highest and lowest rate, and display the response in a "Last Sync" cell on the dashboard.

---

Common Mistakes & Troubleshooting

"The remote server returned an error: (407) Proxy Authentication Required" You're on a corporate network with a proxy. Switch from MSXML2.XMLHTTP60 to WinHttp.WinHttpRequest.5.1 and configure the proxy:

http.SetProxy 2, "proxy.yourcompany.com:8080"
http.SetCredentials "domain\username", "password", 0

Or stick with MSXML2.XMLHTTP60 — it respects IE proxy settings automatically, which usually means it works without any extra configuration.

"Run-time error '91': Object variable or With block variable not set" when accessing parsed JSON The key you're trying to access doesn't exist. Use parsed.Exists("key") before accessing it, or wrap in On Error Resume Next. Also check: did the API return an error response (a JSON object with an "error" key) instead of the data you expected? Print http.Status and http.responseText before parsing to verify what actually came back.

JSON parsing silently returns wrong values for numbers VBA's Dictionary stores everything as Variant. A number like 3.6725 might come back as a String in some edge cases depending on locale settings. Use CDbl() or CLng() explicitly when writing numeric API values to cells.

Charset/encoding issues — garbled characters in responses API responses with non-ASCII characters (currency symbols, names with accents) can get mangled. Use http.responseBody (a byte array) instead of http.responseText, and decode it explicitly:

Dim stream As Object
Set stream = CreateObject("ADODB.Stream")
stream.Open
stream.Type = 1  ' Binary
stream.Write http.responseBody
stream.Position = 0
stream.Type = 2  ' Text
stream.Charset = "UTF-8"
Dim properText As String
properText = stream.ReadText
stream.Close

Application.OnTime macro not firing Check that macro security settings allow macros to run without prompts when the workbook opens. If the VBA project is unprotected and macros are enabled, OnTime should work. Also verify the macro name string matches exactly, including module name if needed: "modRefresh.RunRefreshAndReschedule".

Rate limit errors appearing inconsistently You're making multiple calls in rapid succession from different subroutines. Centralize all HTTP calls through your modApiClient module and add a global request throttle using a module-level timestamp:

Private lastRequestTime As Date
Private Const MIN_REQUEST_INTERVAL_MS As Long = 1000

Private Sub ThrottleRequest()
    Dim elapsed As Long
    elapsed = (Now - lastRequestTime) * 86400000  ' Convert to ms
    If elapsed < MIN_REQUEST_INTERVAL_MS Then
        Application.Wait (Now + (MIN_REQUEST_INTERVAL_MS - elapsed) / 86400000)
    End If
    lastRequestTime = Now
End Sub

Call ThrottleRequest at the start of ExecuteRequest.

---

Summary & Next Steps

You now have the complete toolkit for production REST API integration in Excel VBA. You understand the difference between HTTP client objects and when to use each one. You can make authenticated GET and POST requests, parse complex JSON responses, handle pagination correctly, manage rate limits gracefully, and wire up scheduled automation — all in a maintainable, modular structure.

The patterns we've built here — the centralized API client module, the retry logic, the safe key storage, the pagination loop — are not VBA-specific ideas. They're software engineering fundamentals that happen to be implemented in VBA. That means the skills transfer directly to Python, JavaScript, or any other language you might use in the future.

Where to go from here:

• Webhooks and inbound data: Most of this lesson covered VBA as an API client (outbound calls). Explore using a local Flask server or Azure Functions as a webhook receiver that writes data to a shared location Excel can read.
• Power Query as a complement: For scheduled, credential-managed, transformation-heavy API calls, Power Query's Web.Contents function is worth learning. Use VBA for interactive, event-driven, or write-back scenarios; use Power Query for batch read scenarios.
• Error telemetry: Log all API errors (timestamps, endpoints, status codes, response bodies) to a dedicated log sheet or a database. When things break at 3 AM in an unattended automation, that log is your only forensic evidence.
• OAuth 2.0 flows: APIs like Google Sheets, Salesforce, and Microsoft Graph require OAuth, which involves redirect URIs and token refresh flows. This is significantly more complex in VBA but doable using the Shell command to open the auth URL in a browser and a local HTTP listener to capture the callback token.
• Unit testing your VBA: The Rubberduck VBA IDE extension brings unit testing to VBA, which lets you write tests for your JSON parsing and data transformation logic independent of live API calls.

The spreadsheet your VP checks on Monday morning doesn't have to be a manual ritual. It can be a live, self-maintaining data product — and you now know exactly how to build it.