• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 671
  • Last Modified:

What is the syntax for reading a visio public variable in an Excel code module?

I use Excel vba procedures to open and manipulate visio drawings:

visFilePath = Application.GetSaveAsFilename(options_EndUser_OpenFile_DefaultPath,
       Set visioApp = New Visio.Application 'early binding
       Set visQDoc = visioApp.Documents.Open(visFilePath)
       Set pgs = visQDoc.Pages etc

my visio doc defines some Public visio variables which I need to read in Excel.

What is the syntax for reading a visio public variable in an Excel code module?

  • 7
  • 4
  • 2
2 Solutions
Hi Kelvin,

I'm not 100% sure this is even possible. I tried from one Visio document to another as a baby step, and it seems I needed to reference Document2's VBA project (Tools > References) in order for Document1 to be able to get at the VBA code.

With late-binding, you won't have this reference. It's like a dll isn't available in that case, unless there is some sort of way to CreateObject(,"VisDocument1's VBA stuff")
Excel seems to have a way of getting code from other Excel projects to run, but I can't find how to do it for Visio:


What kind of variables are you trying to get from Visio, and how many. Could you store values in the Document ShapeSheet instead? You can easily read those from Excel:

Sub TestAccessVisioConstants()

  '// Get a running instance of Visio:
  Dim visApp As Object
  Set visApp = GetObject(, "visio.application")
  Debug.Print visApp.Documents.Count
  '// Get the first document:
  Dim visDoc As Object
  Set visDoc = visApp.Documents(1)
  Debug.Print visDoc.Name
  Debug.Print visDoc.FullName

  '// Read info from the Document ShapeSheet. Note: YOU create
  '// the user cells yourself: User.MyTestValue1, User.SomeLength,
  '// User.SomeTextInfo.

  '// Get Document ShapeSheet info:
   Debug.Print visDoc.DocumentSheet.Cells("User.MyTestValue1").ResultIU

  '// Get some other value in millimeters:
  Const visMillimeters% = 70
  Debug.Print visDoc.DocumentSheet.Cells("User.SomeLength").Result(visMillimeters)

   '// Get a string value:
   Const visUnitsString% = 231
   Debug.Print visDoc.DocumentSheet.Cells("User.SomeTextInfo").Result(visUnitsSTring)

  Set visApp = Nothing
  Set visDoc = Nothing

End Sub

Open in new window

Kelvin4Author Commented:
Thanks, VisioGuy:
Having been convinced there WAS a direct means of doing this, I'm glad I stopped trying!

I think I'll take this route, which works ok for sending visio events to excel. No great machine speed is required...

1. IN VISIO:  When my Public variable pubVar changes, I'll also write it to an address in an excel worksheet (here, "Sheet1"):

    If xlAppFromVis Is Nothing Then
        Set xlAppFromVis = GetObject(dirPath & "myExcelfile.xlsm").Application
    End If

    xlAppFromVis.Application.Run "myExcelfile.xlsm'!Store_Value", pubVar, descriptor

2. Then IN EXCEL:

Function Store_Value(ByVal pubVar As String, descriptor As String)

    Sheet1.Range("A2").Value = pubVar
    Sheet1.Range("C2").Value = descriptor

    Call appropriate process to use pubVar
End function

The descriptor is useful. If there are several different variables being sent from visio to excel, the descriptor can drive a Case Selection to trigger the appropriate downstream processing.

Scott also has told me:
scottPosted on 2013-05-22 at 13:46:21ID: 39187328:

"... just answering your question about communicating in both directions between an instance of Visio and an instance of Excel, please take a look at Use Excel's hidden data store to share data across VBA projects." I've not actually used this, but he has a full EE article on this.."

Thanks for your useful warning.
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Kelvin4Author Commented:
Our messages crossed.
I think the difference between our schemes may be that the Application.run approach enables visio to drive excel to respond to a variable_change event in visio.

Happy to correspond further, but time to pay-up..

Kelvin4Author Commented:
Rapid response much appreciated; or I'd have spent hours trying to read the visio variable direct in excel.

Thanks Kelvin.
Hmm. Excel's hidden data store sounds interesting. Wish I had time to look into it.

I'm still not 100% convinced you can't do what you originally wanted with late binding, but who knows how long it would take us to figure it out!

I wonder if the stuff you are doing would be better served with a separate app that drove both Excel and Visio from a third point, instead of trying to mix all the code together? You could start Word and use it's VBA, haha! :) :) But seriously, when I do stuff with Visio and Excel, I usually have all of the code either in Visio or in Excel. Of course, what you are doing sounds much more complicated than the stuff I typically do.
Kelvin4Author Commented:
99% of the code is in excel, but the Visio interface is where the end-user will make his choices. So Visio events play a significant part.

Thanks for the follow-up; it helped me think.
Ok. But in Excel VBA, you can register/listen-for events in Visio.


Dim WithEvents m_visApp as Visio.Application

But your Excel code would need to know when Visio is started, or start Visio itself. It may be that you do not have this situation--maybe your users simply start Visio whenever, and Excel needs to know about it?
Kelvin4Author Commented:
Hi, Visio_Guy

Excel starts visio each time, so that connection is made at the start.

I have used Dim WithEvents m_visApp as Visio.Application in user defined classes to detect particular responses in dynamic Userforms, and also, a bit, in making nested collections as object classes for nested data sets.

I've no  experience in using Dim WithEvents m_visApp as Visio.Application for other Events .

I have, however, some tasks scheduled for the next few months, for which I would like some pointers.  Maybe these would use WithEvents m_visApp as Visio.Application, so much the better, because I need to collect data sets from connected shapes.

I need to construct an interface for the user to construct a branched tree of connected shapes to represent a parent-child heirachy of the User's choice. My User user does this manually  at present (dragging and dropping master shapes and master connections), which requires vba checking for loose connections, and unconnected shapes. Then vba gets get formal parent-child relationships for every shape, and that is the vital data I need.

What I'd like is to build a branched tree maker, where the author defines the connections and the vba does the connecting and arranging (and if possible FIXES connections to they wont break except by vba automation). I have a mechanism for automatically creating shapes with useful text on them, but no Automated Shape Connection routine.

Would you be interested in showing me some simple examples for me to play with?

It might be helpful to define the Q more clearly before hitting the EE 'Ask a new Question button'

Kelvin4Author Commented:
Re-branched diagrams: am now submitting new 'scoping' Question, for assessment of  practicality.

Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
Kelvin -- I just ran across this thread and thought I'd toss in my two cents -- it may or may not be of any value.

Chris mentioned Excel's hidden data store above -- I wrote an article about it a while back and provided some sample code. It's easy to use from Excel VBA; your challenge, I think, will be to have Visio VBA write back to the correct Excel instance, though I think that is solvable.

If the technique works, your Excel code would not need to read Visio variable values, instead Visio would write values to Excel's hidden data store, which Excel VBA can easily read.

In any event, take a look at this article if you haven't already.
Kelvin4Author Commented:
Thanks, Scott:
Your fame went before you - I was citing your article to visioGuy earlier in this thread!
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
Whoops --- missed that when I scanned the thread!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

  • 7
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now