Best practices for Visio VBA code that also has to be run from Excel

[Visisthebest]

Am at the beginning of developing some custom cross-functional flowchart-similar diagram generating code that generates a custom diagram from a custom Excel data structure. (the Data Visualizer provides too little flexibility for what I'm trying to achieve unfortunately).

It is only about generating the diagram, no interaction and event catching in Visio.

The VBA code needs to open a new Visio document and page, drop shapes from a custom stencil where I want them dropped and connect the shapes as per how this is specified in the Excel sheets. Finding out how this works in Visio with the macro recorder has been quite easy (so far at least).

Developing the code in Visio is faster but I will need to move it over to Excel to allow Excel users to generate the diagram directly from Excel as well.

To make the code easier to move over, what are some best practices to follow to make this easier?

By the way, what a total relief Visio's support for Undo for changes made by VBA code instead of the user himself/herself is, this is quite the nightmare in Excel.

Your help and guidance are much appreciated thank you!

[Croc]

You can programmatically create a Visio document based on a template containing a macro and execute the desired macro. Or open an existing Visio document and run its macro.
In this case, all the functionality will be placed in the Visio template, and the macro call in Excel.
The call would be something like this:

Code Select Expand

Set vApp = CreateObject("Visio.Application")
Set vDoc = vApp.Documents.Open ("C:\dGC.vsd")
Call vDoc.CreateChart
vDoc.Save
vDoc.Close
vApp.Quit

[Visisthebest]

Thank you Croc can I also call a VBA function in a Visio template that has an Array() as argument, that would allow me to really efficiently transfer some data from Excel sheets to Visio. Hope this is also possible!

[Croc]

You can pass data through a variable.
Excel ThisWorkbook

Code Select Expand

Sub ttt()
    Set vApp = CreateObject("Visio.Application")
    Set vDoc = vApp.Documents.Open("C:\work\msg.vsdm")
    a = Application.ActiveSheet.Cells(1).CurrentRegion.Value
    vDoc.a = a
    Call vDoc.msg
    vDoc.Save
    vDoc.Close
    vApp.Quit
End Sub
Visio ThisDocument

Code Select Expand

Public a
Public Sub msg()
    If IsArray(a) Then
        MsgBox "Array( " & UBound(a, 1) & ", " & UBound(a, 2) & ")"
    Else
        MsgBox "No data"
    End If
End Sub

[Visisthebest]

Thank you that is no doubt much faster than performing hundreds of calls from Excel to Visio as well! I will try this approach.

[Visisthebest]

Sample code works well thank you Croc!

[Visisthebest]

Also works with VBA functions that I call in Visio from Excel that pass Arrays to and from Visio, really useful!

[Obsidian]

Here example of shapes drawing in Visio from Excel^ https://youtu.be/Gt340w9t-20
Document from video on GitHub: https://github.com/Obsidian-pb/VisioLessons/tree/master/Lessons/Draw%20shapes%20in%20Visio%20from%20Excel
Language is Russian, but you still can observe VBA code.