Author Topic: What's the best approach to deploy Visio VBA code? (Read 15076 times)

Sergio Burgos · « **on:** March 26, 2013, 04:15:04 PM »

Hi,
I'm writing some VBA code in Visio to read/write information into a DB nad it's working really neat.
The issue I'll be facing when I start distributing this code through the user population is:

How to to keep the code up to date in each one of the documents that will be out there?

What is the best approach here?, can I convert the VBA code into an Add-In that runs with Visio?

I'll appreciate any guidance.

Thanks!

Yacine · « **Reply #1 on:** March 27, 2013, 01:05:37 AM »

An addin is certainly the more professional solution.
I prefer however to put the macros in a stencil. It is easy to maintain.

JohnGoldsmith · « **Reply #2 on:** March 27, 2013, 04:42:44 AM »

Yes, I agree with Yacine. It just depends on how widely you want to distribute your solution, whether code security is important to you, whether another language or framework things possible that are not available or simple via VBA. If your main concern is keeping a single code base then, as Yacine suggests, put all of your code in a stencil and then distribute that with a template. The template can be saved (including the workspace) with your stencil open and then your code just has to reference the drawing document. Here's a couple of links that might be useful:

DVS - Chapter 13, Packaging Stencils and Templates

DVS - Chapter 3, Creating Templates

Recent VisGuy discussion on 'Event handling by stencil'

Hope that helps.

Best regards

John

Sergio Burgos · « **Reply #3 on:** March 27, 2013, 12:00:06 PM »

Thanks a lot for the feedback!, this is exactly what I was looking for!

I'm just trying to figure out from the VisGuy response where to create the object for the Class Sink to catch the events from the document... any idea if I should create it when loading the stencil?

Thanks.

Yacine · « **Reply #4 on:** March 28, 2013, 02:19:01 AM »

The events need to be caught by the document itself (vsd). Thus you would implement the events handling in the template.
To make sure, that your users have always the last version, even when editing older files, you may want to offer an import function of old files in newly created files. So the files are always up to date.
Cheers, Yacine.

Sergio Burgos · « **Reply #5 on:** March 28, 2013, 12:44:51 PM »

Thanks!, I think I made it work yesterday, I used collections to create an Event Handler from the code in the stencil every time a document is open.
It was an EUREKA moment!!

Thanks everyone for your guiadance!!

Hey Ken · « **Reply #6 on:** March 29, 2013, 07:31:50 AM »

sburgos:

Could you please post the code? I have a situation which calls for a similar solution, and I'd love to see the details of how you approached it.

Thanks in advance,

- Ken

Sergio Burgos · « **Reply #7 on:** March 29, 2013, 03:25:50 PM »

I was trying to paste my code, but I have already customized it a lot and it would become confusing.
Instead, find below where I took the sample code from:

http://stackoverflow.com/questions/1083603/vba-using-withevents-on-userforms

The idea of the collection is suggested by Visio Guy in the article mentioned by John GoldSmith in this same Topic.

I'll be happy to help further if you need it.

Sergio Burgos

Sergio Burgos · « **Reply #8 on:** March 29, 2013, 03:44:29 PM »

I was reading my own post and thinking of missing information:
I created two Event Handlers based on the code sample from the above article.
The first one is an Event Handler for the Visio.Application that helps me to capture when a document is opened or created.
The second one is an Event Handler for the doc that is opened provided by the previous handler, this helps me to catch events for the opened/created document.

These are the two variables I define at my main module

Code

Public objAppEventHandler As clsApplicationEventSink
Public objDocCollection As Collection

Class for App Event Handler

Code

Private WithEvents m_visApp As Visio.Application

Private Sub Class_Initialize()
  Set m_visApp = Visio.Application
End Sub
Private Sub Class_Terminate()
  Set m_visApp = Nothing
End Sub

Private Sub m_visApp_DocumentOpened(ByVal doc As IVDocument)
    Dim objDocEventHandler As clsDocumentEventSink
    Set objDocEventHandler = New clsDocumentEventSink

    Set objDocEventHandler.objDoc = doc
    objDocCollection.Add objDocEventHandler
End Sub
Private Sub m_visApp_DocumentCreated(ByVal doc As IVDocument)
    Dim objDocEventHandler As clsDocumentEventSink
    Set objDocEventHandler = New clsDocumentEventSink

    Set objDocEventHandler.objDoc = doc
    objDocCollection.Add objDocEventHandler
End Sub

Class for Doc Event Handler

Code

Private WithEvents m_visDoc As Visio.Document

Public Sub New_VBA(ByRef visDoc As Visio.Document)
  Set m_visDoc = visDoc
End Sub

Private Sub Class_Initialize()
  Set m_visDoc = Nothing
End Sub
Private Sub Class_Terminate()
  Set m_visDoc = Nothing
End Sub

Public Property Set objDoc(ByVal doc As IVDocument)
    Set m_visDoc = doc
End Property

Private Sub m_visDoc_DocumentSaved(ByVal doc As IVDocument)
    MsgBox doc.Name
End Sub

I hope helps better.

Sergio Burgos

Hey Ken · « **Reply #9 on:** June 18, 2013, 03:12:48 PM »

Everyone:

   It’s taken me a while to find the time to follow up on all your suggestions, then wrestle with them to reach a solution that fits my goals. It’s been a very fruitful exercise, and I now have what I need and more. However, I did run into what appears to be an insurmountable problem in one scenario.

   My goal was to be able to deploy not only the initial masters and associated macros, but to keep them up to date without user intervention. Until I had encountered this thread, I had never considered using the stencil as the code repository—didn’t even know it was possible! But once I began thinking of the stencil as central, a whole bunch of possibilities opened up. Thanks for pointing me in the right direction.

   Rather than defining sinks, classes, imports, and templates, I started instead with the existing Document Opened event in the stencil itself, and built outward from there. Here’s an outline of how it works:

1. All actions are based on the existence of TheDoc!User.Flag in the active document.

2. When opening the stencil, check to see if the Flag exists.

   2a. If the Flag does NOT exist, then this document is not one that already utilizes the stencil’s functionality, so throw out a message box asking if the user wants to utilize it.

      2.a.1. If they DON’T want to, create the Flag and set it to zero.

      2.a.2. If they DO want to, create the Flag and set it to the stencil’s version number, then go about the various first-time initializations the stencil’s functionality requires.

   2b. If the Flag DOES exist, and if it’s set to zero, the user has previously declined to use the stencil’s functionality, so turn everything off such that the stencil’s existence becomes transparent to the user.

   2c. If the Flag DOES exist and it’s set to the current version number, the user has previously agreed and everything is up-to-date. Just turn on the stencil’s functionality and leave it at that.

   2d. If the Flag DOES exist and it’s NOT the current version number, perform whatever upgrade tasks are necessary to bring the user document up to speed, then set the Flag to the new version number.

3. Each shape has CALLTHIS in its EventDrop formula that invokes the necessary functionality in the stencil, and upgrades the shape if necessary. Of course if the stencil hasn’t been opened, CALLTHIS calls nothing, so the stencil shapes can be used freely anywhere, only without the stencil’s functionality.

   What I like about this approach is that I can just send out the new stencil and ask the users to simply open it; everything else is automatic. Each master and shape is similarly flagged with version numbers such that I can tell what upgrades are needed to existing shapes, if any. Using a template would require users to migrate existing drawings to the new template (and we all know how well trained users are when it comes to things like that!), but the auto-upgrade facility allows them to continue using their existing .vsd files. It also allows advanced users to create and maintain their own macros without worrying about the stencil getting in the way.

   All well and good, BUT… there is one problem. For the first-time user, I thought it would be easiest if they just double clicked on the stencil.vss and have it initialize a new, blank document complete with all stencil functionality enabled. No such luck.

   The stencil’s Document Opened event fires as expected, but that’s where all expectations went out the window. In no particular order, here’s how things went sour:

1. While it’s easy to dock a stencil to an existing document, I could not figure out how to dock a new document to an existing stencil. If you do this from the stencil…

Code

Set TheDocument = Application.Documents.AddEx("", , visAddDocked)

…it does not dock.

2. If you subsequently open a copy of the stencil as follows…

Code

Set TheStencil = Application.Documents.OpenEx(CurDir & "\" & Doc.Name, visOpenRW + visOpenDocked + visOpenCopy + visAddStencil)

…it DOES dock to the new document, and even asks if you want to enable macros. But no matter what I did, I could not get the stencil copy to execute any macro at all, not even its own Document Opened event.

3. I forgot to mention that the stencil initially opens up in a small window. If you maximize it using the user32 function ShowWindow, it maximizes to a window larger than the monitor and smothers the command bar at the bottom. Adding insult to injury, alt-tab does not select the next application’s window; Visio is always on top. Worse yet, Visio’s maximize box at the top right is grayed out, so you can’t manually maximize it, nor can you X out of the document. All you can do is minimize or close the entire Visio window, which is a handy necessity because the stencil window sits on top of everything, thereby precluding access to all other open applications. I could not devise a way to overcome any of these problems.

   In the end I threw in the towel and added a test to see if the stencil is the only open document. If it is, I put up a message box saying you have to have a document open first, THEN open the stencil. Sheesh. How unprofessional. But at least it is a workaround.

   Any ideas where I might have gone wrong? I’m running 32-bit Visio professional 2010 under Windows 8. A test stencil is attached. There’s a Stop in the Document Opened event because sometimes if you just let it run, Visio crashes. What a mess!

   Thanks,

   - Ken

Jumpy · « **Reply #10 on:** June 19, 2013, 01:54:11 AM »

Quote from: Hey Ken on June 18, 2013, 03:12:48 PM

But no matter what I did, I could not get the stencil copy to execute any macro at all, not even its own Document Opened event.

I discovered a similiar problem some time ago. From drawing A I opened via Code drawing B or template B (doesn't matter).
The Document Opened / Created event of B didn't fire, I guess because it was opened via code.
I have no solution for that yet.

vojo · « **Reply #11 on:** June 19, 2013, 01:22:33 PM »

would not the following work

User.date_time = date || time or use NOW
user.code = runmacro + depends (user.date_time)

Idea would be that when you open doc, the user.date+time gets updated to current.
Since macro launch is dependent on date_time, wouldnt it fire to launch the macro?

Hey Ken · « **Reply #12 on:** July 15, 2013, 03:34:42 PM »

Quote from: vojo on June 19, 2013, 01:22:33 PM

would not the following work

User.date_time = date || time or use NOW
user.code = runmacro + depends (user.date_time)

Idea would be that when you open doc, the user.date+time gets updated to current.
Since macro launch is dependent on date_time, wouldnt it fire to launch the macro?

Vojo:

   An interesting suggestion, which I finally had a chance to play around with. And it worked, sort of, but there was one problem that renders it unusable: NOW() only updates once a minute, and the macro will not fire until the minute changes. So you could be waiting up to 59 seconds for the first macro to fire. Bad. I tried modifying the target cell programmatically multiple times to try to fool it into triggering, but it didn’t matter. It has to wait for NOW to change. I could not identify a better triggering function, nor did it matter if I used a User cell in the document sheet or the page sheet.

   Separately, I also stumbled into another problem with the idea of opening the stencil first: The stencil copy I OpenEx above is NOT saved with the workspace unless I explicitly do a SaveAs on it first. That means the poor user will end up having two copies of the stencil lying around. Messy.

   Bottom line: I’m sticking with the path I’ve been following, namely, open the document first, then open the stencil, and display an error if the user does in in the wrong order. And the approach is working well… so far. My next challenge will be to perform a version update.

   - Ken

Visio Guy

Author Topic: What's the best approach to deploy Visio VBA code? (Read 15076 times)

Sergio Burgos

What's the best approach to deploy Visio VBA code?

Yacine

Re: What's the best approach to deploy Visio VBA code?

JohnGoldsmith

Re: What's the best approach to deploy Visio VBA code?

Sergio Burgos

Re: What's the best approach to deploy Visio VBA code?

Yacine

Re: What's the best approach to deploy Visio VBA code?

Sergio Burgos

Re: What's the best approach to deploy Visio VBA code?

Hey Ken

Re: What's the best approach to deploy Visio VBA code?

Sergio Burgos

Re: What's the best approach to deploy Visio VBA code?

Sergio Burgos

Re: What's the best approach to deploy Visio VBA code?

Hey Ken

Re: What's the best approach to deploy Visio VBA code?

Jumpy

Re: What's the best approach to deploy Visio VBA code?

vojo

Re: What's the best approach to deploy Visio VBA code?

Hey Ken

Re: What's the best approach to deploy Visio VBA code?