Bring ChatGPT to Excel using VBA

In this blogpost we will show how to bring ChatGPT into Excel and chat with the OpenAI's ChatGPT from the comfort of your excel sheet.

To do this we can use the following code

Sub GetChatGPTResponse()

Dim apikey As String

 apikey = "Your aPI Key" ''''''''''get you API key from openAI account and paste it here.

 Dim prompttext As String

prompttext = Home.Range("B9").Text   ''Change the cell reference for your question as per your needs

 Dim modelname As String

 modelname = "text-davinci-002"

   

Dim url As String

url = "https://api.openai.com/v1/engines/" & modelname & "/completions"

   

Dim xmlhttp As Object

Set xmlhttp = CreateObject("MSXML2.XMLHTTP")

   

Dim requestData As String

requestData = "{""prompt"": """ & prompttext & """, ""max_tokens"":1000, ""temperature"":0.5}"

   

xmlhttp.Open "POST", url, False

xmlhttp.setRequestHeader "Content-Type", "application/json"

xmlhttp.setRequestHeader "Authorization", "Bearer " & apikey

xmlhttp.send requestData

   

Dim responsetext As String

responsetext = xmlhttp.responsetext

 

Call Answer(responsetext)    

End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Function Answer(ByVal response As String)

Dim startposition As Integer

Dim stringlenth As Integer

startposition = InStr(1, response, """" & "text" & """" & ":" & """", vbTextCompare) + 8

stringlenth = InStr(1, response, """" & "," & """" & "index" & """" & ":", vbTextCompare) - startposition

Answer = Mid(response, startposition, stringlenth)

Dim Myarray() As String

Myarray() = Split(Answer, "\n\n")

Home.Range("B11:B1048575").ClearContents

Dim X As Long

For X = 0 To UBound(Myarray())

Home.Range("B" & 11 + X).Value = Replace(Replace(Myarray(X), "\", ""), "==", "=")

Next X

End Function

1. Create a new excel file. Got to the VBA Editor, create a new module and paste the above code into the module.

2. Provide promt/question for the ChatGPT in the input cell, which you have set up in the code.

3. You can call this subroutine on click of a button or you can also set this up on worksheet change event use it. Here I am calling it on change event.

Enjoy :)