{"id":12736,"date":"2024-10-13T16:48:40","date_gmt":"2024-10-13T14:48:40","guid":{"rendered":"https:\/\/www.juust.org\/?p=12736"},"modified":"2024-10-13T16:48:45","modified_gmt":"2024-10-13T14:48:45","slug":"groqvba-groq-ai-in-excel-vba","status":"publish","type":"post","link":"https:\/\/www.juust.org\/index.php\/groqvba-groq-ai-in-excel-vba\/2024\/10\/","title":{"rendered":"GroqVBA : using Groq AI in Excel 2021 with VBA"},"content":{"rendered":"\n<p>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\u2014just some basic coding to serve as an example of using an LLM in VBA<\/p>\n\n\n\n<p>I haven&#8217;t tested it on other machines yet, it is just meant to serve as example (apologetically).<\/p>\n\n\n\n<div class=\"wp-block-rank-math-toc-block\" id=\"rank-math-toc\"><h2>Table of Contents<\/h2><nav><ul><li><a href=\"#download-excel-sheet\">Download Excel sheet <\/a><\/li><li><a href=\"#develop-your-own-excel-groq-ai-worksheet-functions-with-vba\">Develop your own Excel Groq AI Worksheet functions with VBA <\/a><\/li><li><a href=\"#subs-and-functions\">Subs and functions<\/a><\/li><li><a href=\"#usage\">Usage of sub or function<\/a><\/li><li><a href=\"#using-the-json-api\">Using the JSON API <\/a><\/li><li><a href=\"#conclusion\">Conclusion<\/a><\/li><\/ul><\/nav><\/div>\n\n\n\n<p><\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"download-excel-sheet\">Download Excel sheet <\/h3>\n\n\n\n<div class=\"wp-block-columns is-layout-flex wp-container-core-columns-is-layout-28f84493 wp-block-columns-is-layout-flex\">\n<div class=\"wp-block-column is-layout-flow wp-block-column-is-layout-flow\">\n<p>Here you can download the Excel Worksheet, it is XLSM (an Excel spreadsheet with macro&#8217;s), which are most likely blocked on your system. <\/p>\n\n\n\n<p>You can change that in the Windows File Explorer : find the downloaded file and edit the file properties, at the bottom tell it &#8216;no blocking&#8217;.<\/p>\n<\/div>\n\n\n\n<div class=\"wp-block-column is-layout-flow wp-block-column-is-layout-flow\">\n<div class=\"wp-block-file aligncenter\"><a id=\"wp-block-file--media-0b7816d2-ced7-445b-8f14-0e103cd7c36f\" href=\"https:\/\/www.juust.org\/wp-content\/uploads\/2024\/10\/GroqVBA.zip\">GroqVBA<\/a><a href=\"https:\/\/www.juust.org\/wp-content\/uploads\/2024\/10\/GroqVBA.zip\" class=\"wp-block-file__button wp-element-button\" download aria-describedby=\"wp-block-file--media-0b7816d2-ced7-445b-8f14-0e103cd7c36f\">Download<\/a><\/div>\n<\/div>\n<\/div>\n\n\n\n<p><\/p>\n\n\n\n<p>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!<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"develop-your-own-excel-groq-ai-worksheet-functions-with-vba\">Develop your own Excel Groq AI Worksheet functions with VBA <\/h3>\n\n\n\n<p>You can develop it yourself : you would need<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>some coffee<\/li>\n\n\n\n<li>jsonConverter.bas : you can download it from <a href=\"https:\/\/github.com\/VBA-tools\/VBA-JSON\" target=\"_blank\" rel=\"noopener\">GitHub<\/a>, and import it in your VBA project<\/li>\n\n\n\n<li>&#8230;to activate &#8220;microsoft scripting runtime&#8221; as project reference in the VBA editor (to use the Dictionary object)<\/li>\n\n\n\n<li>a <a href=\"https:\/\/groq.com\/\" target=\"_blank\" rel=\"noopener\">Groq AI<\/a> API key <\/li>\n<\/ul>\n\n\n\n<p>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 &#8216;system&#8217; message in cell C20 of the Main sheet, the code will also pick it up.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"subs-and-functions\">Subs and functions<\/h3>\n\n\n\n<p>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. <\/p>\n\n\n\n<p>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&#8217;t change.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"usage\">Usage of sub or function<\/h3>\n\n\n\n<p>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. <\/p>\n\n\n\n<p>But as said, that output is &#8216;recalculated&#8217; 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&#8217;t change anymore. CTRL+g is the shortkey I assigned to the sub\/macro &#8220;GroqVBA_Sub&#8221;. You can make that assignment in (<strong>worksheet) : developers, macro&#8217;s<\/strong>. <\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"using-the-json-api\">Using the JSON API <\/h3>\n\n\n\n<p>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.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/www.juust.org\/wp-content\/uploads\/2024\/10\/afbeelding-4.png\"><img fetchpriority=\"high\" decoding=\"async\" width=\"1024\" height=\"407\" src=\"https:\/\/www.juust.org\/wp-content\/uploads\/2024\/10\/afbeelding-4-1024x407.png\" alt=\"Groq AI in VBA\" class=\"wp-image-12747\" srcset=\"https:\/\/www.juust.org\/wp-content\/uploads\/2024\/10\/afbeelding-4-1024x407.png 1024w, https:\/\/www.juust.org\/wp-content\/uploads\/2024\/10\/afbeelding-4-300x119.png 300w, https:\/\/www.juust.org\/wp-content\/uploads\/2024\/10\/afbeelding-4-768x305.png 768w, https:\/\/www.juust.org\/wp-content\/uploads\/2024\/10\/afbeelding-4.png 1291w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a><\/figure>\n\n\n\n<p>You can supply three &#8216;message&#8217; 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.<\/p>\n\n\n\n<p>I coded the sheet and routine to pick up a msgSystem message from cell C20 in the Main sheet. <\/p>\n\n\n\n<p>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 &#8216;neighborhood&#8217;. 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 &#8220;setting&#8221; 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. <\/p>\n\n\n\n<p>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. <\/p>\n\n\n\n<p>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).<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Option Explicit\n\nPublic Function GroqVBA(ByVal msgUser As String, Optional ByVal msgSystem As String = \"\", Optional ByVal msgAssistant As String = \"\") As String\n    \n    'no point in answering a missing question\n    If msgUser = \"\" Then Exit Function\n\n     'basic settings : Api key from C1, and the api_endpoint\n\n    ' Set your Groq API credentials\n    Dim api_key As String\n    api_key = Worksheets(\"Main\").Range(\"C1\").Value\n\n    If (CStr(api_key) = \"\") Then\n        MsgBox \"kindly get a Groq Api key\"\n        Exit Function\n    End If\n    \n    ' Set your Groq API endpoint\n    Dim api_endpoint As String\n    api_endpoint = \"https:\/\/api.groq.com\/openai\/v1\/chat\/completions\"\n    \n    ' Create an XMLHTTP object for sending the request\n    Dim http As Object\n    Set http = CreateObject(\"MSXML2.XMLHTTP\")\n    \n    ' Open the connection to the API\n    'set header to JSON content\n     'set Authorization to Bearer token (api key)\n    http.Open \"POST\", api_endpoint, False\n    http.setRequestHeader \"Content-Type\", \"application\/json\"\n    http.setRequestHeader \"Authorization\", \"Bearer \" &amp; api_key\n    \n    ' Set the AI LLM model\n    Dim model As String\n    model = \"llama-3.2-3b-preview\"\n    \n     'get msgSystem from cell C20, if it has any content, otherwise do nothing, maybe it is supplied with the function\n    If CStr(Worksheets(\"Main\").Range(\"C20\").Value) = \"\" Then\n    Else\n        msgSystem = CStr(Worksheets(\"Main\").Range(\"C20\").Value)\n    End If\n    \n    ' Compose the API request body\n     'ElaboratePrompt returns the correct JSON, I put it in a separate function to keep the main routine accessible\n    Dim body As String\n    body = ElaboratePrompt(URLEncode(msgUser), model, URLEncode(msgSystem), URLEncode(msgAssistant))\n    \n    ' Send the API request\n    http.Send body\n    \n    ' Check if the API request was successful\n    If http.Status = 200 Then\n        ' Parse the API response\n        Dim response As Object\n        On Error GoTo ParseError ' Handle any potential parsing errors\n        Set response = JsonConverter.ParseJson(http.responseText)\n        \n        ' Extract the completion content from choices&#91;0]\n        Dim Content As String\n        Content = response(\"choices\")(1)(\"message\")(\"content\")\n        \n        ' Return the content to the function caller (Excel cell)\n        GroqVBA = Content\n    Else\n        ' Handle the API error\n        GroqVBA = \"Error: \" &amp; http.Status &amp; \" \" &amp; http.statusText\n    End If\n\n    ' Clean up\n    Set http = Nothing\n    Set response = Nothing\n    Exit Function\n\nParseError:\n    GroqVBA = \"Error parsing response: \" &amp; Err.Description\n    Debug.Print \"Error parsing JSON response: \" &amp; Err.Description\n    Resume Next\nEnd Function\n\n\nFunction ElaboratePrompt(ByVal PromptInput As String, ByVal model As String, Optional ByVal msgSystem As String = \"\", Optional ByVal msgAssistant As String = \"\") As String\n    ' Construct the API request body with optional system and assistant messages\n    Dim body As String\n    body = \"{\"\"messages\"\": &#91;{\"\"role\"\": \"\"user\"\", \"\"content\"\": \"\"\" &amp; PromptInput &amp; \"\"\"}\"\n    \n    ' Add system message if provided\n    If msgSystem &lt;> \"\" Then\n        body = body &amp; \", {\"\"role\"\": \"\"system\"\", \"\"content\"\": \"\"\" &amp; msgSystem &amp; \"\"\"}\"\n    End If\n    \n    ' Add assistant message if provided\n    If msgAssistant &lt;> \"\" Then\n        body = body &amp; \", {\"\"role\"\": \"\"assistant\"\", \"\"content\"\": \"\"\" &amp; msgAssistant &amp; \"\"\"}\"\n    End If\n    \n    ' Close the messages array and add the model\n    body = body &amp; \"], \"\"model\"\": \"\"\" &amp; model &amp; \"\"\"}\"\n    \n    ' Return the constructed body\n    ElaboratePrompt = body\nEnd Function\n\n' Function to encode the URL to handle special characters\nFunction URLEncode(ByVal str As String) As String\n    Dim i As Long\n    Dim char As String\n    Dim encodedStr As String\n    encodedStr = \"\"\n    \n    For i = 1 To Len(str)\n        char = Mid(str, i, 1)\n        Select Case char\n            Case \"0\" To \"9\", \"A\" To \"Z\", \"a\" To \"z\", \"-\", \"_\", \".\", \"~\"\n                encodedStr = encodedStr &amp; char\n            Case Else\n                encodedStr = encodedStr &amp; \"%\" &amp; Right(\"0\" &amp; Hex(Asc(char)), 2)\n        End Select\n    Next i\n    \n    URLEncode = encodedStr\nEnd Function\n\n\nSub GroqVBA_Sub()\n    'puts a Groq response to a question in an active cell one cell beneath it\n    'bound to shortkey CTRL+g in my sheet\n    \n    'no question, no answer\n    If CStr(ActiveCell.Value) = \"\" Then Exit Sub\n   \n    'Fill the cell under the current active cell\n    'with the answer to the current cell content as question\n   \n    ActiveCell.Offset(1, 0).Value = GroqVBA(CStr(ActiveCell.Value))        ' Writes content to the next column\nEnd Sub\n\nSub CopyBeneath()\n    'copy the value of a cell one column down\n    'to hardcopy the GroqVBA worksheet function return, in my case\n    'in my sheet bound to shortkey CTRL+SHIFT+G\n    ActiveCell.Offset(1, 0).Value = ActiveCell.Value\nEnd Sub\n\n\nSub QandA()\n    'put GroqVBA output in E4 based on contents of E3\n    If ActiveSheet.Range(\"E3:E3\").Value = \"\" Then\n        MsgBox \"put a question in E3, get the answer in E4\"\n        Exit Sub\n    End If\n    \n    ActiveSheet.Range(\"E4:E4\").Value = GroqVBA(ActiveSheet.Range(\"E3:E3\").Value)\nEnd Sub\n<\/code><\/pre>\n\n\n\n<p>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. <\/p>\n\n\n\n<p>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.<\/p>\n\n\n\n<p>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. <\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"conclusion\">Conclusion<\/h3>\n\n\n\n<p>I sincerely hope this helps someone :) if it doesn&#8217;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.<\/p>\n\n\n\n<p><\/p>\n\n\n\n<p><\/p>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>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\u2014just some basic coding to serve as an example of using [&hellip;]<\/p>\n","protected":false},"author":5796,"featured_media":12748,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_sitemap_exclude":false,"_sitemap_priority":"","_sitemap_frequency":"","site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"set","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[500,484,4],"tags":[483],"class_list":["post-12736","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-vba","category-ai","category-juust","tag-ai"],"_links":{"self":[{"href":"https:\/\/www.juust.org\/index.php\/wp-json\/wp\/v2\/posts\/12736","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.juust.org\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.juust.org\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.juust.org\/index.php\/wp-json\/wp\/v2\/users\/5796"}],"replies":[{"embeddable":true,"href":"https:\/\/www.juust.org\/index.php\/wp-json\/wp\/v2\/comments?post=12736"}],"version-history":[{"count":4,"href":"https:\/\/www.juust.org\/index.php\/wp-json\/wp\/v2\/posts\/12736\/revisions"}],"predecessor-version":[{"id":12751,"href":"https:\/\/www.juust.org\/index.php\/wp-json\/wp\/v2\/posts\/12736\/revisions\/12751"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.juust.org\/index.php\/wp-json\/wp\/v2\/media\/12748"}],"wp:attachment":[{"href":"https:\/\/www.juust.org\/index.php\/wp-json\/wp\/v2\/media?parent=12736"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.juust.org\/index.php\/wp-json\/wp\/v2\/categories?post=12736"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.juust.org\/index.php\/wp-json\/wp\/v2\/tags?post=12736"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}