' Created by ChatGPT-4o on 2024-08-24.
' Modified by Vincent Meng.
'================================================
Function AI(arg As String) As String
Dim apiKey As String
Dim url As String
Dim http As Object
Dim jsonBody As String
Dim response As String
Dim startPos As Long
Dim endPos As Long

On Error GoTo ErrorHandler ' Enable error handling

' Your OpenAI API key
apiKey = "XXXXX" ' Replace it with your actual OpenAI API Key

' OpenAI API endpoint for chat completion
url = "https://api.openai.com/v1/chat/completions"

' Create the JSON body for the API request
jsonBody = "{""model"": ""gpt-4o-mini"", ""messages"": [{""role"": ""user"", ""content"": """ & arg & """}], ""max_tokens"": 1000}"

' Create HTTP object
Set http = CreateObject("MSXML2.ServerXMLHTTP.6.0")

With http
.Open "POST", url, False
.setRequestHeader "Content-Type", "application/json"
.setRequestHeader "Authorization", "Bearer " & apiKey

On Error GoTo HTTPErrorHandler ' Enable error handling for HTTP request
.send jsonBody
response = .responseText
On Error GoTo 0 ' Disable error handling for HTTP request
End With

' Simple string parsing: Find the "content" field and extract its value
startPos = InStr(response, """content"": """) + Len("""content"": """)
If startPos > 0 Then
endPos = InStr(startPos, response, """")
If endPos > startPos Then
AI = Replace(Mid(response, startPos, endPos - startPos), "\n", vbCrLf)
Else
AI = "Error: Could not parse the response."
End If
Else
AI = "Error: 'content' field not found in the response."
End If

Exit Function

ErrorHandler:
AI = "Error: " & Err.Description ' Catch general errors and return error message
Exit Function

HTTPErrorHandler:
AI = "HTTP Error: Unable to connect to the API."
Exit Function
End Function

步驟3:使用自定義函數

  1. 回到Excel工作表。
  2. 在單元格中輸入公式 =AI(A1)(假設 A1 單元格中包含你要發(fā)送給ChatGPT的文本)。
  3. 按 Enter 鍵,VBA腳本將調用API并返回結果。

通過上述步驟,你可以在Excel中創(chuàng)建一個自定義的?AI()?函數,用來調用ChatGPT的API,并將結果直接返回到Excel單元格中。

?注意事項

?安全提示

將API Key嵌入VBA代碼中時,請注意安全性,避免泄露敏感信息。如果是多人共享的Excel文件,建議使用更安全的方式存儲API Key。

文章轉自微信公眾號@SAPHANA

上一篇:

12條技巧,打造出超高性能的接口API

下一篇:

如何在你的Excel中接入ChatGPT這個人工智能函數?
#你可能也喜歡這些API文章!

我們有何不同?

API服務商零注冊

多API并行試用

數據驅動選型,提升決策效率

查看全部API→
??

熱門場景實測,選對API

#AI文本生成大模型API

對比大模型API的內容創(chuàng)意新穎性、情感共鳴力、商業(yè)轉化潛力

25個渠道
一鍵對比試用API 限時免費

#AI深度推理大模型API

對比大模型API的邏輯推理準確性、分析深度、可視化建議合理性

10個渠道
一鍵對比試用API 限時免費