Connecting Excel VBA to Azure OpenAI

Question

I'm trying to connect Excel (via VBA) to our Azure OpenAI instance. Since there isn't an OpenAI library, I'm trying to manually set things like api_type, api_version, model, etc manually without much luck. Has anyone successfully accomplished this?

Here's my code with private info redacted.

Function myAI(thePrompt As String)
    Dim MAX_TOKENS As String
    Dim TEMPERATURE As String
    Dim MODEL As String
    Dim apiEndpoint
    Dim apiKey
    Dim response As String

    apiEndpoint = <redacted>
    apiKey = <redacted>
    MAX_TOKENS = 800
    TEMPERATURE = 0
    MODEL = <redacted>
    
    Dim requestBody As String
    requestBody = "{" & _
        """model"": " & MODEL & "," & _
        """prompt"": """ & thePrompt & """," & _
        """max_tokens"": " & MAX_TOKENS & "," & _
        """temperature"": " & TEMPERATURE & _
        "}"
    Set httpObj = CreateObject("WinHttp.WinHttpRequest.5.1")
    httpObj.SetTimeouts 90000, 90000, 90000, 90000
    httpObj.Option(4) = 13056
    httpObj.Open "POST", apiEndpoint, False
    httpObj.setRequestHeader "Content-type", "application/json"
    httpObj.setRequestHeader "Authorization", "Bearer " & apiKey
    httpObj.Send requestBody
    myAI = httpObj.responseText
End Function

The api key is used like this: `httpObj.setRequestHeader "api-key", apiKey` — Tim Williams, Aug 18 '23 at 18:52
You're not passing any messages in your call to the API? See: https://learn.microsoft.com/en-us/azure/ai-services/openai/reference#chat-completions — Tim Williams, Aug 18 '23 at 18:54

Tim Williams · Answer 1 · 2023-08-18T21:07:46.860

EDIT: reworked to use the VBA-JSON library to construct the request and parse the response.
https://github.com/VBA-tools/VBA-JSON

Option Explicit

Sub Tester()
    Debug.Print myAI("What is the population of the USA?")
End Sub


Function myAI(thePrompt As String)
    Const API_KEY As String = "xxxxxxxxxxxxxxxxxxxxxxxxxxx"
    Const API_ENDPOINT As String = "https://contoso.openai.azure.com/openai/deployments/" & _
                                "gpt4/chat/completions?api-version=2023-03-15-preview"
    
    Dim maxTokens As Long, temperature As Double
    Dim httpObj As Object, dictReq As New Scripting.Dictionary
    Dim messages As New Collection, requestBody As String, response As Object

    maxTokens = 800
    temperature = 0
    
    messages.Add Message("system", "You are a helpful personal assistant")
    messages.Add Message("user", thePrompt)
    dictReq.Add "messages", messages
    dictReq.Add "max_tokens", maxTokens
    dictReq.Add "temperature", temperature
    requestBody = JsonConverter.ConvertToJson(dictReq, 2)
    Debug.Print requestBody
    
    Set httpObj = CreateObject("MSXML2.XMLHTTP")
    httpObj.Open "POST", API_ENDPOINT, False
    httpObj.setRequestHeader "Content-Type", "application/json"
    httpObj.setRequestHeader "api-key", API_KEY
    httpObj.send requestBody
    
    Debug.Print httpObj.responseText
    Set response = JsonConverter.ParseJson(httpObj.responseText)
    
    If response.Exists("choices") Then
        myAI = response("choices")(1)("message")("content")
    Else
        myAI = "no response!"
    End If
    
End Function

'helper function: create a `message` object for the request json
Function Message(role As String, prompt As String) As Scripting.Dictionary
    Set Message = CreateObject("scripting.dictionary")
    Message.Add "role", role
    Message.Add "content", prompt
End Function

If this resolved your problem, please flag as "Accepted", to help anyone coming along later with a similar question. — Tim Williams, Aug 22 '23 at 15:46

Connecting Excel VBA to Azure OpenAI

1 Answers1