News:

Happy New Year!

Main Menu

ChatGPT in VBA

Started by Thomas Winkel, November 25, 2024, 09:39:52 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

Thomas Winkel

Attached an Excel document that I used to translate my GUI with ChatGPT.
Click "Translate Empty" to finish the missing translations.
The function can also be used as formula in a cell (see screenshot).
Of course this can also be used in Visio VBA.
I will deactivate the API key tomorrow.

You cannot view this attachment.

Thomas Winkel

Ok, you want a Visio Example?
Attached a Class generator using Visio, VBA & ChatGPT.
Right click, select the language and click "Generate" to create the code.

You cannot view this attachment.

Yacine

#2
Well yes, absolutely cool, but a somewhat unrealistic example.

How about this one? Right click on the shape and let the AI find keywords in the news and generate automatically a diagram.
It's just a stub. You could pass more than one keyword per prompt, define a region, a time span, ask for importance, etc.

Sub getNews(shp As Shape)
    Dim strKeywords As String
    Dim prompt As String, result As String
    Dim shp2 As Shape
    strKeywords = shp.Text
   
    prompt = "Get 5 keywords from the news, related to " & strKeywords & ". Separate them by space chars."
   
    Debug.Print prompt
    result = Ai.AskChatGpt(prompt)
   
    Dim aKeywords As Variant
    Dim vKeyword As Variant
    Dim x As Single, y As Single, w As Single, h As Single
    Dim i As Integer
    aKeywords = Split(result, " ")
    x = shp.Cells("PinX").ResultIU
    y = shp.Cells("PinY").ResultIU
    w = shp.Cells("width").ResultIU
    h = shp.Cells("Height").ResultIU
    For Each vKeyword In aKeywords
        Set shp2 = ActivePage.Drop(ActiveDocument.Masters("item"), x + w, y + i * 1.1 * h)
        shp2.Text = vKeyword
        shp.AutoConnect shp2, visAutoConnectDirNone
    Next vKeyword
    ActivePage.Layout
End Sub

You cannot view this attachment.

... and don't forget to protect your account against misuse.  ;)
Yacine

Thomas Winkel

Nice one 👍

Quote from: Yacine on November 27, 2024, 08:07:45 PMand don't forget to protect your account against misuse.  ;)
Only two downloads so far. I loaded €10 onto the profile four months ago and, despite frequent use, there are still €3 left. I like it when people post other ideas here, like you. I'll just leave the key open for a few more days :)

wapperdude

My apologies in advance.  Lately, I feel I've become the nattling Nellie of naive negativity.

So, this topic isn't at all what I expected.  Well, not sure what I expected, but this isn't it.  I'm sure it's me, though.  Anyway, I was kind of expecting VBA code to invoke chattyAI, send some text via input box, and in return receive answer suitable for display via Visio diagram.  Yacine comes close.  But, it seems like there is some other requirements that need be inplace before a user can just enter code in a vba input box and have chattyAI respond.  Thus, this topic really misses the mark for me.  I'm to unfamiliar to understand the basic core that must be inplace to enable all of this.  I guess that was what I was hoping to see.
Visio 2019 Pro

Yacine

I'm not really sure how to answer.
AIs take a prompt (a question) with a set of parameters. The prompt can come from an internet page where the user writes their question, or it can come from code—in this case, VBA. While using the web interface is often free of charge (likely to attract new users), requests via code are always subject to a fee.

In his example, Thomas demonstrates in the "AI" module how to send a request to ChatGPT 4o. You'll notice that he includes the connection key, which he paid for. For each request, the service deducts a small amount from his account.

The prompt is a human-readable text, and you could ask the user to write exactly that prompt. However, since we're in a graphical application here, it's logical to have a prepared prompt that can be enhanced automatically with parameters from your drawing. Thomas demonstrates this mechanism in different contexts:
• In the Excel example, he's passing a list of words to be translated as parameters.
• In the code generation example, he's configuring a module and asking for a corresponding implementation.
• In my example, I pass one keyword and ask the AI to return a set of related words from recent news.

We can say these are just finger exercises with no real use yet. But it's up to us to think about how AI could be utilized—both in VBA in general and in Visio in particular.

Let me share a few thoughts:
Diagram Generation
We could use AI to explore a topic and generate a map or diagram of related subjects. For example, I was upset by the limited range of news I get in Germany. News agencies always present a narrow set of catchy topics, but you never get a real overview of what's happening globally. So, I asked the AI specifically for important news that might not be covered in German media. Voilà, I got news from forgotten regions—South America, Africa, and others. Now I can use Visio to set up my own Newspaper.

Alternatively, you might want to explore a particular knowledge area, get a basic overview, and then expand on the AI's responses as needed—all while displaying the information in a diagram.

Another idea could be a daily dashboard including news, anniversaries, weather forecasts, commuting times to the office, etc.

Drawing Tools
While the previous example might seem more like a toy, let's consider how AI could assist us with drawings.

How about using Visio for planning and resource allocation? Imagine organizing a manufacturing hall: you provide a shop floor, the machine footprints, and then ask the AI to optimize the arrangement of machines in the space. 

You could generalize this idea into a positioning tool.

For instance, consider giving it the geometry of a flat, along with the requirements for a living room, kitchen, bedrooms, and appliances. The AI could suggest the best placement of these elements according to specific rules.

Yacine

Thomas Winkel

The basic idea is simple:
Sub AiUsage()
    Dim prompt As String
    Dim result As String
   
    ' Code to build the prompt
   
    result = Ai.AskChatGpt(prompt)
   
    ' Code to process the result
End Sub

And here in action:
You cannot view this attachment.

Thomas Winkel

And here is another example in Excel only using the AskChatGpt() formula:
You cannot view this attachment.

I find this particularly interesting because non-programmers can also use it.

Btw. I couldn't get it to admit he didn't know something.

Paul Herber

I really think it would be a good idea to start a new discussion area for AI related stuff. Anyone agree or disagree?
Electronic and Electrical engineering, business and software stencils for Visio -

https://www.paulherber.co.uk/

Nikolay

@Paul
I would say yes, if this thread reaches at least 3 pages.
3 is a magic number

wapperdude

#10
Thanks @Yacine, Thomas. 

I am familiar with asking AI questions from a website input box.  Well, at least the My version from Browser.  That's also the depth of my interactive experience.  Hadn't given it much thought beyond that.  So, when this topic showed up, now that I think about it more, I was expecting to see the steps, with example, to engage AI via VBA, to do some research, and then source the info back to VBA and construct an appropriate diagram.  Nothing elaborate, as I know aids Visio skills are limited.

Since this is new ground, then, for those like me, the steps/process of doing this haven't quite clicked.  Haven't had that "Aha!" moment.  Knowing that using code has a cost is important.  I would think issues wrt to security, authorized access are important.  Like someone using bot code to overwhelm an AI site.

Anyway, thanks for updates.,

@Paul, Nikolay:  Yeah.  I agree.  If this topic grows, new discussion board would be appropriate.
Visio 2019 Pro

Thomas Winkel

Quote from: Paul Herber on November 28, 2024, 12:12:34 PMI really think it would be a good idea to start a new discussion area for AI related stuff. Anyone agree or disagree?

Is this topic about AI, Programming & Code, or User-submitted Stuff?
Something of each, I guess.
Since most of the users here are probably not programmers, I would still think Programming & Code is the best choice.
We have so many sub-forums where the last post is years old. I would rather think about a general restructuring than adding more.

Yacine

#12
Just a quick note from a (Visio) muse passing by,
A custom Visio assistant, a mega one:

https://chatgpt.com/share/676fd6a2-37b8-8008-b5f1-ed1be08265b0

The smartshape would delete itself or stay on the page (for more instructions) depending on its setting.
If to stay, one would need to make it invisible to the applied code, by means of a "if not condition then ..."

The smartshape would reside in a stencil. The stencil provides the macros to connect to the AI and do the other procesing stuff. Drag the master on the page, type in your request and let the magic happen.

Applications usually offer this kind of assistance from their very core. Here - thanks to Visio's power - we can provide it as a custom solution.

A tool for both Newbies or lazy Visionistas.
Yacine

Nikolay

#13
I would simplify that to just a sidebar with AI prompt, similar to other apps :)
No need for stencils or shapes or macros or vba

And yes, there are already some built!
You can check out mermaid for example, it will draw and modify diagrams for you from a prompt.

Thomas Winkel

#14
Below the VBA functions for Claude and ChatGPT (almost identical).

For local AI:
* Install LM Studio
* In LM Studio:
  * Download and open a model (use a small one for the start -> phi-3.5-mini-instruct)
  * Start Server in Developer Tab
* Change the AskChatGpt function as follows:
Const API_KEY As String = ""
Const API_ENDPOINT As String = "http://127.0.0.1:1234/v1/chat/completions"
Const MODEL As String = "phi-3.5-mini-instruct"

Public Function AskClaude(prompt As String) As String
    Const API_KEY As String = "### Your-API-Key ###"
    Const API_ENDPOINT As String = "https://api.anthropic.com/v1/messages"
    Const MODEL As String = "claude-3-5-sonnet-latest"
    Const MAX_TOKENS As String = "1024"
    Const TEMPERATURE As String = "0.0"
   
    Dim httpRequest As Object
    Dim jsonRequest As String
    Dim jsonResponse As String
    Dim jsonObject As Object
   
    On Error GoTo ErrorHandler
   
    If (API_KEY = "### Your-API-Key ###") Then
        AskClaude = "Enter your API Key in VBA code."
        Exit Function
    End If
   
    jsonRequest = "{""model"":""" & MODEL & """,""max_tokens"":" & MAX_TOKENS & ",""temperature"":" & TEMPERATURE & ",""messages"":[{""role"":""user"",""content"":""" & prompt & """}]}"
   
    Set httpRequest = CreateObject("MSXML2.XMLHTTP")
    With httpRequest
        .Open "POST", API_ENDPOINT, False
        .setRequestHeader "Content-Type", "application/json"
        .setRequestHeader "x-api-key", API_KEY
        .setRequestHeader "anthropic-version", "2023-06-01"
        .send jsonRequest
    End With
   
    jsonResponse = httpRequest.responseText
    Set jsonObject = JsonConverter.ParseJson(jsonResponse)
    AskClaude = jsonObject("content")(1)("text")
   
    Exit Function
   
ErrorHandler:
    AskClaude = "Error"
End Function

Public Function AskChatGpt(prompt As String) As String
    Const API_KEY As String = "### Your-API-Key ###"
    Const API_ENDPOINT As String = "https://api.openai.com/v1/chat/completions"
    Const MODEL As String = "gpt-4o"
    Const MAX_TOKENS As String = "1024"
    Const TEMPERATURE As String = "0.0"

    Dim httpRequest As Object
    Dim jsonRequest As String
    Dim jsonResponse As String
    Dim jsonObject As Object
   
    On Error GoTo ErrorHandler
   
    If (API_KEY = "### Your-API-Key ###") Then
        AskChatGpt = "Enter your API Key in VBA code."
        Exit Function
    End If
   
    jsonRequest = "{""model"":""" & MODEL & """,""max_tokens"":" & MAX_TOKENS & ",""temperature"":" & TEMPERATURE & ",""messages"":[{""role"":""user"",""content"":""" & prompt & """}]}"
   
    Set httpRequest = CreateObject("MSXML2.XMLHTTP")
    With httpRequest
        .Open "POST", API_ENDPOINT, False
        .setRequestHeader "Content-Type", "application/json"
        .setRequestHeader "Authorization", "Bearer " & API_KEY
        .send jsonRequest
    End With
   
    jsonResponse = httpRequest.responseText
    Set jsonObject = JsonConverter.ParseJson(jsonResponse)
    AskChatGpt = jsonObject("choices")(1)("message")("content")
    Exit Function
   
ErrorHandler:
    AskChatGpt = "Error"
End Function

Browser ID: smf (possibly_robot)
Templates: 4: index (default), Display (default), GenericControls (default), GenericControls (default).
Sub templates: 6: init, html_above, body_above, main, body_below, html_below.
Language files: 4: index+Modifications.english (default), Post.english (default), Editor.english (default), Drafts.english (default).
Style sheets: 4: index.css, attachments.css, jquery.sceditor.css, responsive.css.
Hooks called: 436 (show)
Files included: 32 - 1207KB. (show)
Memory used: 1317KB.
Tokens: post-login.
Cache hits: 15: 0.00171s for 26,551 bytes (show)
Cache misses: 5: (show)
Queries used: 18.

[Show Queries]