How to use ChatGPT.(Microsoft Excel)


Japanese version.

Introduction to using ChatGPT from Excel.

This procedure is only for applications (Desktop) because it uses VBA and macros.

If you want to use it in the cloud (Online), use Power Automate.


Get the ChatGPT API key (secret key) in beforehand.

Then paste the code below into a VBA project in an Excel workbook.

Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub ChatGPT()
  ' Setting.
  ' API key (secret key).
  Dim APIkey As String: APIkey = "sk-******"
  ' Specifies the number of times to wait. If this number is exceeded, the program times out and waits 10 seconds per wait.
  Dim tryMaxCount As Integer: tryMaxCount = 5
  ' Specify the cell of the message to be queried.
  Dim CellAsk As String: CellAsk = "Sheet1!A1"
  ' Specify the cell that will receive the results.
  Dim CellAnswer As String: CellAnswer = "Sheet1!A2"
  ' End setting.
  Dim httpReq As Object: Set httpReq = CreateObject("MSXML2.XMLHTTP")
  Dim httpReqBody As String: httpReqBody = ""
  With httpReq

        .Open "POST", "", False
        .setRequestHeader "Content-Type", "application/json"
        .setRequestHeader "Authorization", "Bearer " & APIkey

        httpReqBody = "{""model"":""text-davinci-003"", ""prompt"":""" & Range(CellAsk).Value & """, ""max_tokens"":2024, ""temperature"":0, ""top_p"":1}"
        .send httpReqBody
        Dim count As Integer: count = 0
        Do While .readyState <> 4
          If count < tryMaxCount Then
            Call Sleep(10000)
            Exit Do
          End If
          count = count + 1
        If .readyState = 4 Then
          Dim textStartIndex As Integer: textStartIndex = InStr(httpReq.responseText, "\n\n") + 4
          Dim textEndIndex As Integer: textEndIndex = InStr(textStartIndex, httpReq.responseText, """")
          Range(CellAnswer).Value = Replace(Mid(httpReq.responseText, textStartIndex, textEndIndex - textStartIndex), "\n", Chr(10))
          Range(CellAnswer).Value = "Could not retrieve (timeout)"
        End If
  End With

  Set httpReq = Nothing
  MsgBox ("End")
End Sub

Rewrite lines 6-15.

Change the red part of line 6 to the API key (secret key) you obtained.

Dim APIkey As String: APIkey = "API key (secret key)"

The larger the number in line 9, the longer to wait. When the server load is heavy, if this number is not large, the process may be terminated halfway through.

Dim tryMaxCount As Integer: tryMaxCount = 5

Line 12 specifies the cell that sets the contents of the query to ChatGPT.

Dim CellAsk As String: CellAsk = "Sheet1!A1"

Line 15 specifies the cell to set the response from ChatGPT.

Dim CellAnswer As String: CellAnswer = "Sheet1!A2"

After setting up, place a button or something on the worksheet to call ChatGPT.



ChatGPT Articles

Tips. Others.(Microsoft Excel)