OpenAI API VBA function returns #Value! but MsgBox displays response

I am trying to integrate the OpenAI API into Excel. The http request to OpenAI chat completion works correctly and the response is OK. When I display it with a MsgBox, it looks fine.

But when the function is called in a sheet, the returned value is #VALUE!

Public Function GPT(InputPrompt) As String
  'Define variables
  Dim request As Object
  Dim text, response, API, api_key, DisplayText, GPTModel As String
  Dim GPTTemp As Double
  Dim startPos As Long
  Dim rng As Range
  Dim httpRequest As Object
  Dim countArray As Variant

 
  'API Info
  API = "https://api.openai.com/v1/chat/completions"
  api_key = Trim(Range("API_Key").value)
  'Note: for future upgrades, please replace with GPT-4 etc
  GPTModel = Range("Model_Number").value
  
  If api_key = "" Then 'API key is missing!
    MsgBox "Error: API cannot be blank! Please go to 'Configuration' tab and enter a valid OpenAI API key", vbExclamation, "GPT for Excel"
    frmStatus.Hide
    Exit Function
  End If
    
  'Clean input text and make JSON safe
  text = CleanInput(InputPrompt)
  
  'Create request object
  Set httpRequest = CreateObject("MSXML2.XMLHTTP")
  'Set httpRequest = New MSXML2.XMLHTTP60
  
  'Get temp from Config panel
  GPTTemp = Range("GPT_temperature").value
  
  'Assemble request body
  Dim requestBody As String
  requestBody = "{""model"": ""gpt-4"", ""messages"": [{""role"": ""user"", ""content"": """ & text & """}], ""temperature"": 0.7}"

  With httpRequest
     .Open "POST", API, False
     .setRequestHeader "Content-Type", "application/json"
     .setRequestHeader "Authorization", "Bearer " & api_key
     .send (requestBody)
  End With

  If httpRequest.Status = 200 Then 'Successfully called OpenAI API
   response = httpRequest.responseText
   'Get usage info from response object
   countArr = ExtractUsageInfo(response)
    
   startPos = InStr(response, """content"":") + Len("""content"":") + 2
   endPos = InStr(startPos, response, "},")
   DisplayText = Trim(Mid(response, startPos, endPos - startPos))
   DisplayText = Mid(DisplayText, 1, Len(DisplayText) - 2)
   DisplayText = CleanOutput(DisplayText)
    
   Set request = Nothing
   If FillActive = False Then frmStatus.Hide
   MsgBox (GPT)
   GPT = DisplayText
   MsgBox (GPT)
   If Range("Log_Data").value = "Yes" Then
    Call UpdateLogFile(countArr)
   End If
  Exit Function

End Function

Solution

You are limited as to what you can do in a custom worksheet function. While I can't find an explicit reference to making web or API calls in the MS Docs my experience of worksheet functions leads me to not be surprised that this doesn't work.

An alternative would be to add a Button or Shape to your worksheet then associated that button with a Sub such as

Sub ButtonClicked()
    Dim Response As String, InputPrompt As String
    InputPrompt = CStr(ThisWorkbook.Sheets("Sheet1").Range("A1").Value)
    Response = GPT(InputPrompt)
    ThisWorkbook.Sheets("Sheet1").Range("A2").Value = Response
End Sub

Which uses your existing GPT function to get a response for the text in cell A1 and loads it into cell A2 (both in 'Sheet1' in this case ... obviously you can change the cells and worksheet as needed).