How to script ChatGPT in Excel with VBA

Step #1: Install GPT for Excel add-on

To install GPT for Excel add-in, follow these steps:

1. Visit the GPT for Excel Word addin-in installation page.
2. Click Open in Excel to start the integration process.

   

3. Confirm any permissions to proceed with installing the Excel GPT extension.
4. To open the sidebar, click GPT for Excel Word in the Home tab. If you don't see it, go to Home > Add-ins > My add-ins > GPT for Excel Word.

   

Chat GPT is now connected to Microsoft Excel and can be used directly from any of your workbooks. Available models include GPT-4o, GPT-4o mini, o1, OpenAI Assistants, Claude 3.5 Sonnet and many more.

The installation details are also outlined in the following ChatGPT for Excel guide page.

Step #2: Test the ChatGPT integration in Excel

The GPT for Excel add-in offers several functions:

• GPT(): The simplest function for prompting in Excel cells
• GPT_TRANSLATE(): A specific function for translation
• GPT_CLASSIFY(): Another function for text content classification
• ...

You can find the complete list of available functions on this page.

To integrate GPT for Excel formulas with VBA, we’ll use a simple example featuring the GPT() function. The same process applies to the other functions.

Follow these steps:

1. Open a new Excel file
2. Save the file with the .xlsm extension to enable macros

   

3. Open your saved Excel file.
4. Launch the GPT for Excel extension.
5. In Sheet1, type the following formula into cell A1:

=GPT("hello")

You should see a response generated by GPT in cell A1, such as:

You have now successfully used a GPT function inside Excel.

Now, let’s say you need to trigger this function through VBA. This will be the focus of the next section: "Connect your GPT function in VBA."

Step #3: Prompt ChatGPT with an Excel macro using VBA

Simple insertion of a GPT formula

1. Click on the “Developer” tab of your Excel file (if you don’t see this tab, refer to the instructions on this Microsoft page to enable it).

   

2. Click on Visual basic in the Developer tab to open the VBA editor

   

3. On the left side of the screen in the Project Explorer, right-click on 'Sheet1' and select Insert > Module

   

4. In the blank module screen that appears, type the following code:

   Sub WriteGPTFormula()
   'Insert the GPT function into cell A2 of Sheet1
   Sheets("Sheet1").Range("A2").Formula = "=GPT(""Hello"")"
   End Sub

5. Click on the Save button to save your VBA code.
6. Then, click the Run Sub/UserForm button (green triangle) in the toolbar to execute the macro.

   

You should see another response to your "Hello" in cell A2, such as:

If you prefer referring to cells instead of ranges, you can alternatively use the following code:

Sub WriteGPTFormula()
Sheets("Sheet1").Cells(2, 1).FormulaLocal = "=GPT(" & """Hello""" & ")"
End Sub

This will give you the same result as the first code.

Using the result of a GPT function in VBA

Initial test

Now, let’s say you want to save the result of the GPT("Hello") prompt and display it in a MsgBox. You might write code like this:

Sub WriteGPTFormula()
Sheets("Sheet1").Cells(1, 1).FormulaLocal = "=GPT(" & """Hello""" & ")"
X = Sheets("Sheet1").Cells(1, 1).Value
MsgBox X
End Sub

However, you will encounter an error message: "Subscript out of range". This happens because the GPT function needs time to process the prompt, but the ongoing VBA code doesn’t wait for it to complete, causing a conflict.

Trying to bypass this issue by combining Application.CalculationState and DoEvents will not resolve the problem. The A1 cell will continue to display a #BUSY! message. For example:

Sub WriteGPTFormula()
Sheets("Sheet1").Cells(1, 1).FormulaLocal = "=GPT(" & """Hello""" & ")"
Do While Application.CalculationState <> xlDone
DoEvents
Loop
X = Sheets("Sheet1").Cells(1, 1).Value
MsgBox X
End Sub

The issue here is that the GPT() function cannot complete its execution as long as DoEvents or Application.Calculate is actively running.

Solution

To resolve this issue, we need to give the GPT() function time to execute without blocking it. For instance, we can wait for 3 to 5 seconds before checking if the function has completed. If the function has finished, we can retrieve the result. Otherwise, we schedule another check.

This requires two separate VBA procedures:

1. Sub WriteGPTFormula() - Purpose: Initiates the process by inserting the =GPT(“hello”) formula into cell A1 and schedules the first check to retrieve the result.

2. Sub CheckGPTResult() - Purpose: Checks if the function GPT() has completed and retrieves the result from cell A1. If the result is not yet available, it reschedules itself to check again after a delay

Full code:

//
Sub WriteGPTFormula()
' Write the formula =GPT(“Hello”) in cell A1
Sheets("Sheet1").Cells(1, 1).FormulaLocal = "=GPT(" & """Hello""" & ")"
' Schedule the first check in 5 seconds
Application.OnTime Now + TimeValue("00:00:03"), "CheckGPTResult"
End Sub

Sub CheckGPTResult()
Dim X As Variant
Dim NextCheck As Date
' Retrieve the value from cell A1
X = Sheets("Sheet1").Range("A1").Value
' Check if the cell contains an error or is empty
If IsError(X) Or X = "" Then
' If the cell is still calculating or empty, schedule another check
NextCheck = Now + TimeValue("00:00:03") ' Check again in 5 seconds
Application.OnTime NextCheck, "CheckGPTResult"
Else
' The cell contains a value, display the result
MsgBox X
End If
End Sub

//

Conclusion

In conclusion, integrating Chat GPT for Excel with VBA opens up powerful possibilities for automating advanced and repetitive tasks in Excel. By combining the flexibility of VBA with the AI-driven capabilities of ChatGPT, you can create dynamic workflows that go beyond standard Excel functionalities. Whether it's applying consistent formulas across multiple sheets, leveraging AI outputs for advanced operations, or connecting Excel with other Microsoft tools, this integration will help you work way faster.

The step-by-step guide provided demonstrates how to install the GPT for Excel add-in, test its functionality, and use VBA to trigger GPT formulas, handle their outputs, and manage execution delays effectively. By following these instructions, users can incorporate AI into their Excel macros and unlock new levels of efficiency in data management and analysis.