How to Call the OpenAI API from Excel VBA

What You Need

• An OpenAI API account with billing enabled

  API usage is billed separately from a ChatGPT Plus or Team subscription.

• An API key stored outside the workbook

  Set a Windows user environment variable named OPENAI_API_KEY (Start → 'Edit environment variables for your account'), then restart Excel.

• Excel for Windows with macros enabled

  Save the file as .xlsm. The HTTP objects used here are Windows-only.

• A model name from OpenAI's current model list

  Models change regularly. Use a small, inexpensive model for row-by-row work and put its name in one constant so you can change it later.

The Complete VBA Code

Option Explicit

Private Const API_URL As String = "https://api.openai.com/v1/chat/completions"
Private Const MODEL As String = "gpt-4.1-mini"   ' change to a current model
Private Const SYSTEM_PROMPT As String = _
    "You are a concise assistant. Answer in one short sentence."

Public Function AskOpenAI(ByVal userText As String) As String
    Dim http As Object, body As String, key As String

    key = Environ("OPENAI_API_KEY")
    If Len(key) = 0 Then Err.Raise vbObjectError + 1, , "OPENAI_API_KEY not set"

    body = "{""model"":""" & MODEL & """," & _
           """temperature"":0.2," & _
           """messages"":[" & _
           "{""role"":""system"",""content"":""" & JsonEscape(SYSTEM_PROMPT) & """}," & _
           "{""role"":""user"",""content"":""" & JsonEscape(userText) & """}]}"

    Set http = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    http.setTimeouts 5000, 5000, 30000, 60000
    http.Open "POST", API_URL, False
    http.setRequestHeader "Content-Type", "application/json"
    http.setRequestHeader "Authorization", "Bearer " & key
    http.send body

    If http.Status <> 200 Then
        Err.Raise vbObjectError + http.Status, , _
            "HTTP " & http.Status & ": " & Left$(http.responseText, 300)
    End If

    AskOpenAI = ExtractContent(http.responseText)
End Function

Public Sub ProcessRows()
    Dim ws As Worksheet, lastRow As Long, r As Long
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    If MsgBox("Send " & lastRow - 1 & " rows to the API?", vbYesNo) <> vbYes Then Exit Sub

    For r = 2 To lastRow
        If Len(ws.Cells(r, "A").Value) > 0 And Len(ws.Cells(r, "B").Value) = 0 Then
            On Error Resume Next
            ws.Cells(r, "B").Value = AskOpenAI(CStr(ws.Cells(r, "A").Value))
            If Err.Number <> 0 Then ws.Cells(r, "C").Value = Err.Description
            Err.Clear
            On Error GoTo 0
            Application.StatusBar = "Row " & r & " of " & lastRow
            DoEvents
            Application.Wait Now + TimeSerial(0, 0, 1)   ' gentle rate limit
        End If
    Next r
    Application.StatusBar = False
End Sub

Private Function JsonEscape(ByVal s As String) As String
    s = Replace(s, "\", "\\")
    s = Replace(s, """", "\""")
    s = Replace(s, vbCrLf, "\n")
    s = Replace(s, vbCr, "\n")
    s = Replace(s, vbLf, "\n")
    s = Replace(s, vbTab, "\t")
    JsonEscape = s
End Function

' Minimal extraction of choices[0].message.content without a JSON library.
Private Function ExtractContent(ByVal json As String) As String
    Dim p As Long, q As Long, ch As String, out As String
    p = InStr(1, json, """content"":")
    If p = 0 Then ExtractContent = "": Exit Function
    p = InStr(p + 10, json, """") + 1
    q = p
    Do While q <= Len(json)
        ch = Mid$(json, q, 1)
        If ch = "\" Then
            Select Case Mid$(json, q + 1, 1)
                Case "n": out = out & vbLf
                Case "t": out = out & vbTab
                Case """": out = out & """"
                Case "\": out = out & "\"
                Case Else: out = out & Mid$(json, q + 1, 1)
            End Select
            q = q + 2
        ElseIf ch = """" Then
            Exit Do
        Else
            out = out & ch
            q = q + 1
        End If
    Loop
    ExtractContent = out
End Function

How the Code Works

Building the request

Reading the response

Errors per row, not per run

Practical Uses in Business Workbooks

Controlling Cost and Quality

• Test on 50 rows first

  Check the answers and the usage dashboard, then multiply before running 10,000 rows.

• Constrain the answer

  A fixed list of allowed categories or a strict format produces answers you can filter and pivot.

• Keep the temperature low

  0 to 0.3 for classification and extraction; higher only for creative writing.

• Do not send what you do not need

  Strip names, account numbers and other personal data from the text before sending it.

• Cache answers

  Skipping filled rows, as the code does, means you never pay twice for the same row.

When to Move Beyond a Macro

• More than one person needs to run it, so keys would be shared.

• The data includes personal, health or financial information.

• The process should run on new data automatically, not when someone clicks a button.

• You need logs of what was sent, what came back, and what it cost.