GroqVBA : using Groq AI in Excel 2021 with VBA

GroqVBA has arrived ! Hey there! I just wrapped up an Excel worksheet (xlsm) that includes some simple macros. It lets you use the Groq Inference API directly in Excel, either as a regular worksheet function or by pressing the shortcut CTRL+G. Nothing too fancy—just some basic coding to serve as an example of using an LLM in VBA

I haven’t tested it on other machines yet, it is just meant to serve as example (apologetically).

Download Excel sheet

Open the worksheet, enter the Groq API key into cell C1 on the Main sheet and give it a try. Fingers crossed! It worked on my end, so hopefully, it will for you too!

Develop your own Excel Groq AI Worksheet functions with VBA

You can develop it yourself : you would need

• some coffee
• jsonConverter.bas : you can download it from GitHub, and import it in your VBA project
• …to activate “microsoft scripting runtime” as project reference in the VBA editor (to use the Dictionary object)
• a Groq AI API key

I supplied some code underneath, you can put that in a VBA module. Create a sheet Main, and put the Groq API key in cell C1. That is where the code picks up the api key. You can put a ‘system’ message in cell C20 of the Main sheet, the code will also pick it up.

Subs and functions

In Excel VBA we can use Subs and Functions. We can use a Public Function as a regular worksheet function. These functions cannot alter other cells. And the resulting values are recalculated. An AI LLM is stochastic, so the same question gets a slightly different answer each time.

A sub can do anything, but cannot be a worksheet function. It requires a shortkey or a command button or anything to trigger it. But the output is not recalculated, the output doesn’t change.

Usage of sub or function

The basic setup in our sheet : you can type a question story in cell C3 and in cell C4 type in the worksheet function =GroqVBA(C3). C4 will have the Groq API response.

But as said, that output is ‘recalculated’ now and then. We can also get a hardcopy : you can use any cell for questions and push CTRL+G. The cell under the question gets the answer. That doesn’t change anymore. CTRL+g is the shortkey I assigned to the sub/macro “GroqVBA_Sub”. You can make that assignment in (worksheet) : developers, macro’s.

Using the JSON API

Getting the actual response requires some basic coding. The API expects a JSON payload in the POST body. We set the basic Authorization header with the Api Key as Bearer token, and json as content type.

You can supply three ‘message’ inputs to the call to the Groq AI API : user, system and assistent. So that is also the input of our GroqVBA function. msgUser is required, the other two, system and assistant, are optional. Normally you would also supply a model but to keep it brief I picked a fixed model.

I coded the sheet and routine to pick up a msgSystem message from cell C20 in the Main sheet.

What is interesting about the system message : you can use it for extra basic instructions to the AI. You can instruct the AI (through the system message) that the AI is a an AI tech editor. That primes the AI to the general ‘neighborhood’. Then the AI knows from what point of view to work, what the general terms are, what the field is. The system message is your basic “setting” and you can do a lot more with it, like dictating for the format also of your responses. I also ordered it to respond short and in a quatrain.

This code undereath is the final code I made for the downloadable worksheet. If you make it yourself, you will have to assign the right shortkeys, but then it could/should work.

The code is composed of the main GroqVBA public function, two helper functions (ElaboratePrompt and EncodeURL), and three general utils for the spreadsheet (GroqVBA_Sub, CopyBeneath, QandA).

Option Explicit

Public Function GroqVBA(ByVal msgUser As String, Optional ByVal msgSystem As String = "", Optional ByVal msgAssistant As String = "") As String
    
    'no point in answering a missing question
    If msgUser = "" Then Exit Function

     'basic settings : Api key from C1, and the api_endpoint

    ' Set your Groq API credentials
    Dim api_key As String
    api_key = Worksheets("Main").Range("C1").Value

    If (CStr(api_key) = "") Then
        MsgBox "kindly get a Groq Api key"
        Exit Function
    End If
    
    ' Set your Groq API endpoint
    Dim api_endpoint As String
    api_endpoint = "https://api.groq.com/openai/v1/chat/completions"
    
    ' Create an XMLHTTP object for sending the request
    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP")
    
    ' Open the connection to the API
    'set header to JSON content
     'set Authorization to Bearer token (api key)
    http.Open "POST", api_endpoint, False
    http.setRequestHeader "Content-Type", "application/json"
    http.setRequestHeader "Authorization", "Bearer " & api_key
    
    ' Set the AI LLM model
    Dim model As String
    model = "llama-3.2-3b-preview"
    
     'get msgSystem from cell C20, if it has any content, otherwise do nothing, maybe it is supplied with the function
    If CStr(Worksheets("Main").Range("C20").Value) = "" Then
    Else
        msgSystem = CStr(Worksheets("Main").Range("C20").Value)
    End If
    
    ' Compose the API request body
     'ElaboratePrompt returns the correct JSON, I put it in a separate function to keep the main routine accessible
    Dim body As String
    body = ElaboratePrompt(URLEncode(msgUser), model, URLEncode(msgSystem), URLEncode(msgAssistant))
    
    ' Send the API request
    http.Send body
    
    ' Check if the API request was successful
    If http.Status = 200 Then
        ' Parse the API response
        Dim response As Object
        On Error GoTo ParseError ' Handle any potential parsing errors
        Set response = JsonConverter.ParseJson(http.responseText)
        
        ' Extract the completion content from choices[0]
        Dim Content As String
        Content = response("choices")(1)("message")("content")
        
        ' Return the content to the function caller (Excel cell)
        GroqVBA = Content
    Else
        ' Handle the API error
        GroqVBA = "Error: " & http.Status & " " & http.statusText
    End If

    ' Clean up
    Set http = Nothing
    Set response = Nothing
    Exit Function

ParseError:
    GroqVBA = "Error parsing response: " & Err.Description
    Debug.Print "Error parsing JSON response: " & Err.Description
    Resume Next
End Function


Function ElaboratePrompt(ByVal PromptInput As String, ByVal model As String, Optional ByVal msgSystem As String = "", Optional ByVal msgAssistant As String = "") As String
    ' Construct the API request body with optional system and assistant messages
    Dim body As String
    body = "{""messages"": [{""role"": ""user"", ""content"": """ & PromptInput & """}"
    
    ' Add system message if provided
    If msgSystem <> "" Then
        body = body & ", {""role"": ""system"", ""content"": """ & msgSystem & """}"
    End If
    
    ' Add assistant message if provided
    If msgAssistant <> "" Then
        body = body & ", {""role"": ""assistant"", ""content"": """ & msgAssistant & """}"
    End If
    
    ' Close the messages array and add the model
    body = body & "], ""model"": """ & model & """}"
    
    ' Return the constructed body
    ElaboratePrompt = body
End Function

' Function to encode the URL to handle special characters
Function URLEncode(ByVal str As String) As String
    Dim i As Long
    Dim char As String
    Dim encodedStr As String
    encodedStr = ""
    
    For i = 1 To Len(str)
        char = Mid(str, i, 1)
        Select Case char
            Case "0" To "9", "A" To "Z", "a" To "z", "-", "_", ".", "~"
                encodedStr = encodedStr & char
            Case Else
                encodedStr = encodedStr & "%" & Right("0" & Hex(Asc(char)), 2)
        End Select
    Next i
    
    URLEncode = encodedStr
End Function


Sub GroqVBA_Sub()
    'puts a Groq response to a question in an active cell one cell beneath it
    'bound to shortkey CTRL+g in my sheet
    
    'no question, no answer
    If CStr(ActiveCell.Value) = "" Then Exit Sub
   
    'Fill the cell under the current active cell
    'with the answer to the current cell content as question
   
    ActiveCell.Offset(1, 0).Value = GroqVBA(CStr(ActiveCell.Value))        ' Writes content to the next column
End Sub

Sub CopyBeneath()
    'copy the value of a cell one column down
    'to hardcopy the GroqVBA worksheet function return, in my case
    'in my sheet bound to shortkey CTRL+SHIFT+G
    ActiveCell.Offset(1, 0).Value = ActiveCell.Value
End Sub


Sub QandA()
    'put GroqVBA output in E4 based on contents of E3
    If ActiveSheet.Range("E3:E3").Value = "" Then
        MsgBox "put a question in E3, get the answer in E4"
        Exit Sub
    End If
    
    ActiveSheet.Range("E4:E4").Value = GroqVBA(ActiveSheet.Range("E3:E3").Value)
End Sub

You can put your question in any cell, suppose F13, and type in another cell =GroqVBA(F13) and get the response. Edit the question and you get a new response. As said, that response is recalculated.

CopyBeneath copies the output in the active cell to a text in the cell underneath the active cell. Thus you can save one output you want to save from the worksheet function, by copying the value to a cell beneath it.

QandA shows how you can create a basic chat with a command button that fires the QandA sub. The QandA sub takes the contents of E3 as question and prints the response in E4.

Conclusion

I sincerely hope this helps someone :) if it doesn’t work, let me know. I might be able to fix it. Maybe you have some good ideas or feature requests, in that case, I am all ears.