Author Topic: Best practices for Visio VBA code that also has to be run from Excel  (Read 222 times)

0 Members and 1 Guest are viewing this topic.

Visisthebest

  • Full Member
  • ***
  • Posts: 79
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!
« Last Edit: March 04, 2020, 09:16:10 AM by Visisthebest »

Croc

  • Full Member
  • ***
  • Posts: 220
    • Visioport
Re: Best practices for Visio VBA code that also has to be run from Excel
« Reply #1 on: March 04, 2020, 10:17:28 AM »
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
Set vApp = CreateObject("Visio.Application")
Set vDoc = vApp.Documents.Open ("C:\dGC.vsd")
Call vDoc.CreateChart
vDoc.Save
vDoc.Close
vApp.Quit

Visisthebest

  • Full Member
  • ***
  • Posts: 79
Re: Best practices for Visio VBA code that also has to be run from Excel
« Reply #2 on: March 05, 2020, 03:10:55 AM »
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

  • Full Member
  • ***
  • Posts: 220
    • Visioport
Re: Best practices for Visio VBA code that also has to be run from Excel
« Reply #3 on: March 05, 2020, 03:33:00 AM »
You can pass data through a variable.
Excel ThisWorkbook
Code
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
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

  • Full Member
  • ***
  • Posts: 79
Re: Best practices for Visio VBA code that also has to be run from Excel
« Reply #4 on: March 05, 2020, 03:51:00 AM »
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

  • Full Member
  • ***
  • Posts: 79
Re: Best practices for Visio VBA code that also has to be run from Excel
« Reply #5 on: March 05, 2020, 04:47:20 AM »
Sample code works well thank you Croc!

Visisthebest

  • Full Member
  • ***
  • Posts: 79
Re: Best practices for Visio VBA code that also has to be run from Excel
« Reply #6 on: March 05, 2020, 05:05:05 AM »
Also works with VBA functions that I call in Visio from Excel that pass Arrays to and from Visio, really useful!

Obsidian

  • Jr. Member
  • **
  • Posts: 12
    • AIGS GraFiS-Tactic
Re: Best practices for Visio VBA code that also has to be run from Excel
« Reply #7 on: March 22, 2020, 09:32:04 PM »
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.
« Last Edit: March 22, 2020, 09:36:16 PM by Obsidian »
And may be from the darkness something beautiful will rize