Excel中，创建一个公式来调用ChatGPT API并返回结果

在Office 365的Excel中，虽然有了Copilot，目前我们还无法使用。不过可以用自定义Formula来调用ChatGPT来实现。本文是演示使用VBA创建一个自定义函数 AI()调用OpenAI API，并返回API的响应。

步骤1：启用开发工具

打开Excel，点击“文件” > “选项”。
选择“自定义功能区”，然后在右侧勾选“开发工具”选项。
点击“确定”启用开发工具。

步骤2：创建VBA脚本

在Excel中，点击“开发工具”选项卡，然后点击“Visual Basic”。
在VBA编辑器中，点击“插入” > “模块”，插入一个新模块。
将以下代码粘贴到Module1中：

'================================================

' 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