How to Integrate GPT-4o into Excel Without Any Add-On

6 min readAug 9, 2024

After the success of my previous guide on integrating GPT-4o into Google Sheets, I received numerous requests to create a similar tutorial for Excel. In this article, I’ll show you how to integrate GPT-4o into Excel using VBA (Visual Basic for Applications) without the need for any add-ons. This integration will allow you to utilize the powerful GPT-4o model directly within your Excel spreadsheets.

Important: Please note that this implementation is Windows-only, as it relies on the Microsoft Scripting Runtime. Unfortunately, this means it will not work on Mac or other non-Windows platforms.

1. Get Your OpenAI API Key

First, you’ll need an API key from OpenAI. If you don’t have one, sign up at OpenAI and get your API key. You can generate and manage your API keys at OpenAI API Keys. Ensure that you keep this key secure and do not share it publicly. Note that to use GPT-4o, you should ensure your API account is topped up, as these models may require a higher quota or a specific subscription plan. You can top up your account at OpenAI Billing Overview.

2. Open Excel and Access the VBA Editor

Open Excel and save your file as XLSM. Then, navigate to the workbook where you want to integrate the GPT-4o functionality.

Using the Shortcut: Press Alt + F11 to quickly open the VBA editor.
Using the Ribbon: Alternatively, you can access the VBA editor through the Ribbon by following these steps:

Click on the Developer tab in the Ribbon. If you don’t see the Developer tab, you may need to enable it. Go to File > Options > Customize Ribbon, and check the box next to "Developer" in the right column.
Once the Developer tab is visible, click on Visual Basic in the Code group to open the VBA editor.

This will take you to the Visual Basic for Applications (VBA) editor, where you’ll write the script to connect Excel with the OpenAI API.

3. Enabling Microsoft Scripting Runtime (Windows Only)

You’ll need to ensure that the Microsoft Scripting Runtime is enabled in Excel. Here’s how you can do that:

Go to References: In the VBA editor, go to Tools > References.
Enable Microsoft Scripting Runtime: In the References dialog box, scroll down and check the box next to “Microsoft Scripting Runtime.” Click OK to enable it.

This step is crucial as the integration relies on the Scripting Runtime to handle HTTP requests and JSON parsing.

4. Write the VBA Script

In the VBA editor, go to Insert > Module to create a new module. Delete any existing code in the module and paste the following script. This script defines a custom function to call the GPT-4o API and handle the response.

Function GPT4o(prompt As String, cellValue As String) As String
    Dim apiKey As String
    apiKey = "YOUR_OPENAI_API_KEY" ' Replace with your OpenAI API key
    
    Dim url As String
    url = "https://api.openai.com/v1/chat/completions"
    
    ' Combine prompt and cell value
    Dim combinedPrompt As String
    combinedPrompt = prompt & " " & cellValue
    
    ' Create the JSON request
    Dim jsonRequest As String
    jsonRequest = "{""model"":""gpt-4o"",""messages"":[{""role"":""user"",""content"":""" & combinedPrompt & """}],""temperature"":0.7}"
    
    ' Create an HTTP request object
    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP")
    
    ' Make the HTTP POST request
    With http
        .Open "POST", url, False
        .setRequestHeader "Content-Type", "application/json"
        .setRequestHeader "Authorization", "Bearer " & apiKey
        .send jsonRequest
    End With
    
    ' Get the response as a string
    Dim jsonResponse As String
    jsonResponse = http.responseText
    
    ' Parse the JSON response
    Dim parsedJSON As Object
    Set parsedJSON = ParseJSON(jsonResponse)
    
    ' Extract the content from the parsed JSON
     Dim extractedContent As String
     extractedContent = parsedJSON("obj.choices(0).message.content")

    ' Replace \n with actual line breaks
    extractedContent = Replace(extractedContent, "\n", Chr(10))
    
    GPT4o = extractedContent

End Function

Replace 'YOUR_OPENAI_API_KEY' with your actual OpenAI API key.

Replace gpt-4o with gpt-4o-mini if you want to use GPT-4o-mini.

5. Add the JSON Parser Script

To parse the JSON response from the OpenAI API, you’ll need a JSON parser. Here’s the code you can use, which I originally found in Daniel Ferry’s Medium article. Go to Insert > Module to create a new module. Delete any existing code in the module and paste the following script.

'-------------------------------------------------------------------
' VBA JSON Parser
'-------------------------------------------------------------------
Option Explicit
Private p&, token, dic
Function ParseJSON(json$, Optional key$ = "obj") As Object
    p = 1
    token = Tokenize(json)
    Set dic = CreateObject("Scripting.Dictionary")
    If token(p) = "{" Then ParseObj key Else ParseArr key
    Set ParseJSON = dic
End Function
Function ParseObj(key$)
    Do: p = p + 1
        Select Case token(p)
            Case "]"
            Case "[":  ParseArr key
            Case "{"
                       If token(p + 1) = "}" Then
                           p = p + 1
                           dic.Add key, "null"
                       Else
                           ParseObj key
                       End If
                
            Case "}":  key = ReducePath(key): Exit Do
            Case ":":  key = key & "." & token(p - 1)
            Case ",":  key = ReducePath(key)
            Case Else: If token(p + 1) <> ":" Then dic.Add key, token(p)
        End Select
    Loop
End Function
Function ParseArr(key$)
    Dim e&
    Do: p = p + 1
        Select Case token(p)
            Case "}"
            Case "{":  ParseObj key & ArrayID(e)
            Case "[":  ParseArr key
            Case "]":  Exit Do
            Case ":":  key = key & ArrayID(e)
            Case ",":  e = e + 1
            Case Else: dic.Add key & ArrayID(e), token(p)
        End Select
    Loop
End Function
'-------------------------------------------------------------------
' Support Functions
'-------------------------------------------------------------------
Function Tokenize(s$)
    Const Pattern = """(([^""\\]|\\.)*)""|[+\-]?(?:0|[1-9]\d*)(?:\.\d*)?(?:[eE][+\-]?\d+)?|\w+|[^\s""']+?"
    Tokenize = RExtract(s, Pattern, True)
End Function
Function RExtract(s$, Pattern, Optional bGroup1Bias As Boolean, Optional bGlobal As Boolean = True)
  Dim c&, m, n, v
  With CreateObject("vbscript.regexp")
    .Global = bGlobal
    .MultiLine = False
    .IgnoreCase = True
    .Pattern = Pattern
    If .TEST(s) Then
      Set m = .Execute(s)
      ReDim v(1 To m.Count)
      For Each n In m
        c = c + 1
        v(c) = n.Value
        If bGroup1Bias Then If Len(n.submatches(0)) Or n.Value = """""" Then v(c) = n.submatches(0)
      Next
    End If
  End With
  RExtract = v
End Function
Function ArrayID$(e)
    ArrayID = "(" & e & ")"
End Function
Function ReducePath$(key$)
    If InStr(key, ".") Then ReducePath = Left(key, InStrRev(key, ".") - 1) Else ReducePath = key
End Function
Function ListPaths(dic)
    Dim s$, v
    For Each v In dic
        s = s & v & " --> " & dic(v) & vbLf
    Next
    Debug.Print s
End Function
Function GetFilteredValues(dic, match)
    Dim c&, i&, v, w
    v = dic.Keys
    ReDim w(1 To dic.Count)
    For i = 0 To UBound(v)
        If v(i) Like match Then
            c = c + 1
            w(c) = dic(v(i))
        End If
    Next
    ReDim Preserve w(1 To c)
    GetFilteredValues = w
End Function
Function GetFilteredTable(dic, cols)
    Dim c&, i&, j&, v, w, z
    v = dic.Keys
    z = GetFilteredValues(dic, cols(0))
    ReDim w(1 To UBound(z), 1 To UBound(cols) + 1)
    For j = 1 To UBound(cols) + 1
         z = GetFilteredValues(dic, cols(j - 1))
         For i = 1 To UBound(z)
            w(i, j) = z(i)
         Next
    Next
    GetFilteredTable = w
End Function
Function OpenTextFile$(f)
    With CreateObject("ADODB.Stream")
        .Charset = "utf-8"
        .Open
        .LoadFromFile f
        OpenTextFile = .ReadText
    End With
End Function

6. Save the VBA Script and the Excel file

Press Ctrl + S to save your script. You can now close the VBA editor. Also, you should save your Excel file as XLSM since it now includes macros.

6. Use the Custom Function in Excel

Go back to your Excel workbook. You can now use the custom function =GPT4o("your additional prompt here", A1) in any cell. For example:

=GPT4o("Tell me a joke about", A1)

Assuming cell A1 contains “cats”, the combined prompt will be “Tell me a joke about cats”. The GPT-4o API will process this prompt and return the result directly in the cell.

Benefits of This Integration

Cost-Effective: This solution doesn’t require any paid add-ons. You only pay for your usage of the OpenAI API based on your plan.
Ease of Use: You can easily integrate powerful AI capabilities into your spreadsheets without needing advanced technical knowledge.
Customization: You can modify the script to adjust parameters such as temperature and model selection, tailoring the responses to your needs.

Conclusion

Integrating GPT-4o with Excel is a powerful way to leverage AI directly within your workflows without needing expensive add-ons. By following the steps outlined in this guide, you can set up a custom Excel function that calls the GPT-4o API and returns responses directly in your spreadsheet.

This setup allows for a wide range of applications, from generating creative content to automating complex data analysis tasks. Start exploring the possibilities of AI in your spreadsheets today!

If you have any questions or run into issues, feel free to leave a comment below. Happy coding!