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

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.
Kelvin

VisioGuy:
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..

Kelvin

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.

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.
Kelvin

Ok. But in Excel VBA, you can register/listen-for events in Visio.

Ie:

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?

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'

Thanks,
Kelvin

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

Kelvin

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.

Thanks, Scott:
Your fame went before you - I was citing your article to visioGuy earlier in this thread!
Cheers
Kelvin

Whoops --- missed that when I scanned the thread!