OpenAI API VBA 函数返回 #Value！但 MsgBox 显示响应

问：

我正在尝试将 OpenAI API 集成到 Excel 中。对 OpenAI 聊天完成的 http 请求工作正常，响应正常。当我用 MsgBox 显示它时，它看起来不错。

但是当在工作表中调用该函数时，返回值为 #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