In this post, we will talk about how to run ChatGPT inside MS Excel. Popularity of ChatGPT is growing day by day. It has a variety of use cases ranging from healthcare to banking domain.
OpenAI released a public API a few days ago which makes it easy to embed ChatGPT in any web application or software. They have an official python package for the same, but no Excel add-in for MS Excel users. Since web API is available, we can use it in MS Excel as well using VBA code. Some of the beginners may not familiar with VBA but with Excel Macros. VBA is basically a programming language used to create Excel Macros to automate repetitive tasks.


Hit Download button to download working file
When you download excel workbook from the internet, Microsoft blocks macros from running and shows the source of this file is untrusted. In order to fix this error, follow the steps below.
Go to the folder where this macro file is located. Right-click the file and choose Properties from the menu. At the bottom of the General tab, select the Unblock checkbox under security option and then click OK.
- Easy to edit output (response) from ChatGPT in Excel.
- Simple to format output with various formatting options in Excel.
- Since Excel is used as a dashboarding tool, bringing ChatGPT inside it allows users to generate insights, making it easier to identify trends and make informed decisions.
- Easy to record and organize data in a structured format, making it easier to share it with team members
sk-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxBY
Once you are done with your sign-up, you will get 18 dollars grant to test APIs which will be expired by April 1,2023. Post that, you will be charged $0.002 / 1000 tokens. Tokens are basically words. Make sure not to share your API key with others. Otherwise, you will pay for other's usage.

You need to enter API key in cell F3. This is the same secret key which we obtained in the previous step.

Sub chatGPT() Dim request As Object Dim text, response, API, api_key, DisplayText As String Dim startPos As Long Dim rng As Range 'API Info API = "https://api.openai.com/v1/chat/completions" api_key = Trim(Range("F3").Value) If api_key = "" Then MsgBox "Error: API key is blank!" Exit Sub End If 'Input Text text = Replace(Range("B3").Value, Chr(34), Chr(39)) 'Create an HTTP request object Set request = CreateObject("MSXML2.XMLHTTP") With request .Open "POST", API, False .setRequestHeader "Content-Type", "application/json" .setRequestHeader "Authorization", "Bearer " & api_key .send "{""model"": ""gpt-3.5-turbo"", ""messages"": [{""content"":""" & text & """,""role"":""user""}]}" response = .responsetext End With 'Extract content Result = Split(response, """,""") For i = LBound(Result) To UBound(Result) If InStr(Result(i), "content") > 0 Then startPos = i Exit For End If Next i DisplayText = Mid(Result(startPos), InStr(Result(startPos), ":") + 2, InStr(Result(startPos), """},")) DisplayText = Mid(DisplayText, 1, InStr(DisplayText, """},") - 1) 'Put response in cell B4 Set rng = Range("B4:B2000") rng.Clear Range("B4").Value = DisplayText 'Split to multiple rows Call SplitTextToMultipleRows rng.WrapText = True 'Clean up the object Set request = Nothing End Sub Sub SplitTextToMultipleRows() Dim cell As Range Dim splitArr() As String Dim delimiter As String delimiter = "\n" Set cell = Range("B4") splitArr = Split(cell.Value, delimiter) For i = LBound(splitArr) To UBound(splitArr) cell.Offset(i, 0).Value = Replace(splitArr(i), "\""", Chr(34)) Next i End Sub
- Press Alt+F11 to open the VBA editor.
- Click Insert > Module to create a new module.
- In the module, copy and paste the VBA code mentioned in the previous section.
- Once you have entered the code, close the VBA editor.
- Run the macro by pressing Alt+F8 or by going to the Developer tab > Macros and select the macro you want to run.
To access GPT-4, you can replace gpt-3.5-turbo
with gpt-4
in the above VBA code. The OpenAI Team regularly updates their models and they have various versions of them but they make sure that users get the latest model version in gpt-4 or gpt-3.5-turbo
.
If you encounter any issue related to VBA references, try to add Microsoft XML, v 3.0. I am using Office 2019 in which the above code works without it. References I am using are as shown below. References can be added by opening VBA Editor and then Tools > References.

Once you are through with all the above steps, just enter your question in cell B3 and Hit ChatGPT button, response will start appearing in a few seconds.
Post a Comment