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.
Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he has worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and Human Resource.
Post a Comment