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.
The first and foremost step is to sign up using this link – platform.openai.com. You can do it easily through your existing Google or Microsoft account. Once done, you need to get secret API key to use API. It will look like this. Copy your API key for future reference. 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.
The program below fetch responses of ChatGPT using API. It also cleans the response from ChatGPT and put it in structured format so that formatting gets maintained. This lets users edit response easily and easy to reuse in any other document or spreadsheet.
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
You don’t need to perform the following steps if you use my workbook. 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.
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.
Read MoreListenData
