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

题意：“OpenAI API VBA 函数返回 #Value!，但 MsgBox 显示响应”


题目背景：

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.
“我正在实验将 OpenAI API 集成到 Excel 中。对 OpenAI 谈天完成的 HTTP 哀求正常工作，响应也正常。当我使用 MsgBox 显示它时，它看起来很好。”

But when the function is called in a sheet, the returned value is #VALUE!
“但当在工作表中调用该函数时，返回的值是 #VALUE!”

1. Public Function GPT(InputPrompt) As String
2.   'Define variables
3.   Dim request As Object
4.   Dim text, response, API, api_key, DisplayText, GPTModel As String
5.   Dim GPTTemp As Double
6.   Dim startPos As Long
7.   Dim rng As Range
8.   Dim httpRequest As Object
9.   Dim countArray As Variant
12.   'API Info
13.   API = "https://api.openai.com/v1/chat/completions"
14.   api_key = Trim(Range("API_Key").value)
15.   'Note: for future upgrades, please replace with GPT-4 etc
16.   GPTModel = Range("Model_Number").value
17.   
18.   If api_key = "" Then 'API key is missing!
19.     MsgBox "Error: API cannot be blank! Please go to 'Configuration' tab and enter a valid OpenAI API key", vbExclamation, "GPT for Excel"
20.     frmStatus.Hide
21.     Exit Function
22.   End If
23.    
24.   'Clean input text and make JSON safe
25.   text = CleanInput(InputPrompt)
26.   
27.   'Create request object
28.   Set httpRequest = CreateObject("MSXML2.XMLHTTP")
29.   'Set httpRequest = New MSXML2.XMLHTTP60
30.   
31.   'Get temp from Config panel
32.   GPTTemp = Range("GPT_temperature").value
33.   
34.   'Assemble request body
35.   Dim requestBody As String
36.   requestBody = "{""model"": ""gpt-4"", ""messages"": [{""role"": ""user"", ""content"": """ & text & """}], ""temperature"": 0.7}"
38.   With httpRequest
39.      .Open "POST", API, False
40.      .setRequestHeader "Content-Type", "application/json"
41.      .setRequestHeader "Authorization", "Bearer " & api_key
42.      .send (requestBody)
43.   End With
45.   If httpRequest.Status = 200 Then 'Successfully called OpenAI API
46.    response = httpRequest.responseText
47.    'Get usage info from response object
48.    countArr = ExtractUsageInfo(response)
49.    
50.    startPos = InStr(response, """content"":") + Len("""content"":") + 2
51.    endPos = InStr(startPos, response, "},")
52.    DisplayText = Trim(Mid(response, startPos, endPos - startPos))
53.    DisplayText = Mid(DisplayText, 1, Len(DisplayText) - 2)
54.    DisplayText = CleanOutput(DisplayText)
55.    
56.    Set request = Nothing
57.    If FillActive = False Then frmStatus.Hide
58.    MsgBox (GPT)
59.    GPT = DisplayText
60.    MsgBox (GPT)
61.    If Range("Log_Data").value = "Yes" Then
62.     Call UpdateLogFile(countArr)
63.    End If
64.   Exit Function
66. End Function

复制代码

题目解决：

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.
“在自界说工作表函数中，你的操作是有限的。虽然我在微软文档中找不到明确提到进行 Web 或 API 调用的内容，但根据我对工作表函数的履历，这种方法不起作用并不令人不测。”

An alternative would be to add a Button or Shape to your worksheet then associated that button with a Sub such as
“另一种方法是在你的工作表中添加一个按钮或形状，然后将该按钮与一个 `Sub` 关联，例如：”

1. Sub ButtonClicked()
2.     Dim Response As String, InputPrompt As String
3.     InputPrompt = CStr(ThisWorkbook.Sheets("Sheet1").Range("A1").Value)
4.     Response = GPT(InputPrompt)
5.     ThisWorkbook.Sheets("Sheet1").Range("A2").Value = Response
6. 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).
“这个方法使用你现有的 `GPT` 函数来获取单元格 A1 中的文本的响应，并将其加载到单元格 A2 中（在本例中，这两个单元格都在 'Sheet1' 中……显然，你可以根据须要更改单元格和工作表）。”

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。