Centralized macro management (VBA)

[mb-ing]

Hi everyone,

I created serveral macros via VBA.
Currently, they are located in each document (This Document), which is more than suboptimal for modification Management.

One possibility to accomplish a centralized macro managment is to generate Add-Ins.

Is there a pragmatically and handy way to transform VBA-macros easily to Add-Ins?

Another conceivable solution could be a small standardized  macro in each document, which is triggered by open Document.
This short code snippet only loads a Project (Modules, Classes) from the Server.

If I modify a VBA-macro or create a new one, everbody can use this, if he opens the document again

Is it possible to Import Modules, Classes, etc. via VBA-macro?

Has anybody another Suggestion?

Thank you in advance for your efforts and have a nice day.

Best regards
MB-Ing.

[Surrogate]

This is not part of the general functional of Visio. Try third party add-in - Super Utilities and Tools by Paul Herber

Macro Store
Keep all your Visio macros ready for use in any diagram, just paste from here into the Visio macro editor

Also we have topic

Thinking more globally, it probably could make sense for Microsoft to provide something like "Visio Store" for Visio where you could give/sell templates/shapes/addins/macroces (solutions) to the customers, using a "one-click" install.

The majority of users are not developers, they are in fact interested in functionality which is behind macroces and not in macroces themselves, so if this functionality can be wrapped as "application" which is represented as a "button" or "task pane" in Visio, that would be a good step forward.

I'm thinking of something like "Visio Apps", probably.

But not so many peoples intrested for it

[Surrogate]

Also Nikolay make VBA Import/Export Addin

This free Visio add-in helps to import and export VBA code from Visio to a folder.
The addin can exports and import the code from “ThisDocument”, forms (.frm), classes (.cls) and modules (.bas)

It may be handy if you want to track changes to the VBA code which is located in a template or a stencil. For example, you can compare it with previous versions, or put the exported folder under a source control, like GIT or TFS. So that you always know what was changed in comparison with the previous version.

And i start thread in Visio Suggestion Box - Local storage for macros, which available for all documents.
In suggestion box you can find idea by Chris Hopkins - Online store support for Visio shapes, templates, add-ons, add-ins
You can voted for this ideas, and share yours ideas too !

[mb-ing]

@Surrogate:
Thank you very much for both replies.
I voted for each issue 

All shown Topics by you could be possible approaches.
But they require an user-interaction, which can result in difficulties or might be forgotten

I must begin with a little background Information.

In our Company (1200 employees, 80 Engineeres in 5 Locations (Germany, China, USA, etc.) with R&D) we develope and design several different components.
Futhermore we have a advance developement for electrial devices. The colleagues in this department work with a Special ECAD-Application. But indeed this Software is very cost-intensive. However for this it is justified, because they have increased demands.

Moreover, we sometimes have the case, that a mechanical design engineer have to Show for example a control cabinet.

- How to wire components
- Where to Position relay, SPS, etc.
- ...
» A simple circuit diagram for assembly and the customer.

So he only have to Show the Status quo of mechanical design. It is not a electrical developement in the proper meaning of the word...
In former times we used AutoCAD for this issues. But it is not so Handy and comfortable compared to MS Visio.

Because of this it was my idea to introduce MS Visio for this matters.

As a member of the R&D-Management i have the honourable Task to to take all the preliminary steps

What I have prepared until now:

- Implement Visio-Files in our PLM-System (Documents can be created, released, versioned, pre-viewed, etc.)
- create autofilled title block and drawing Frame in Visio (DIN conform)
- manage stencils (with a vast amount of customized shapes) global
- distribute GUI via clientmanagement
- created Interface to CAD
- Part list can be created automatically in Visio and Export to ERP-System
- create several usefull macros

Tbd:

- Centralized Management of backroundpages
- Centralized Management of VBA-macros
- ....

[Hey Ken]

Mb-ing:

   Since you say you’re managing a vast amount of customized stencils, why not keep your VBA macros in there?

   I’m in a similar situation where I need to disseminate macros, custom shapes, and more, and keep them up to date with occasional updates, and I’ve found that using the stencil as a central repository is a fine solution.  So rather than having to update macros in a thousand ThisDocuments, there’s only one place.  I just distribute the new stencil and have the users copy it to the My Shapes folder that Visio builds for every user.

   For more detail, check out this earlier post: http://visguy.com/vgforum/index.php?topic=4710

   Good luck!

   - Ken

[wapperdude]

@Ken (and others):  I see the rationale for storing macs with stencils, but isn't that sort of a shape-centric approach?  What about macs that are more document universal and no necessarily associated with any set of shapes (stencils)?  Wouldn't a template file approach be more effective?  Seems you could have greater flexibiliy in terms of available macros plus multiple preset user configurations, all handled by a handful of templates.  Strikes me as still centralized, still simple & efficient updating.

Thoughts?  Comments?

Wapperdude

[Surrogate]

In former times we used AutoCAD for this issues. But it is not so Handy and comfortable compared to MS Visio.

But in AutoCAD you can use external references!

[Hey Ken]

Wapperdude:

    Personally, I don’t like to store macros with documents or templates.  What happens if the macros need updating?  Or a certain shape’s properties or other aspects need changing?  Or page- or document-level changes?  You’d need to change N documents,  where N can be an unmanageable number.  But when macros live with the stencil, you send one file and that’s that.  And when you open it, it can automatically update whatever needs updating, including document-centric, page-centric, and shape-centric items.  Details about how I accomplish that are here, and it works very well.  So far...  ;- )

    I love the stencil-based approach.  Works great for me, but your mileage may vary.

    - Ken

[wapperdude]

@Hey Ken:  I see where that makes sense.  My initial thought was a template file would transport everything at once...including macros.  These new macros would replace the existing macros.  Seemed like a more efficient methodology.  But, once new template received, guess there is no mechanism to flush the new macros into place, so not a practical solution.  My concern was possibility that not all macros would be on a single stencil, so potential for sending multiple stencils, and need to have multiple stencils open to get access to all macros.

Anyway, thanks for detailed description.

Wapperdude

[mb-ing]

Hi everyone,

thank you all very much for your replies.
I will check them in the coming time...

What I tried now:

- Implement in template a Routine macro: Import all modules and classes from Server

-------------------------------------------------------------------------------------------------

Private Sub Document_DocumentOpened(ByVal doc As Document)
 
    Call ImportVBAComponents
 
End Sub

-------------------------------------------------------------------------------------------------

Private Sub ImportVBAComponents()
'   Datum       | Name              | Version   | Status
'-----------------------------------------------------------
'   2017.04.09  | MB-Ing.           | A         | In Arbeit
'-----------------------------------------------------------
'
' Anmerkungen siehe Änderungshistorie
'
'Funktion:
'Importiert alle im Ordner "Path" enthaltene *.CLS & *.BAS -Dateien in aktives VB-Projekt
'
'Besondere Anmerkungen
'Benötigt eine Referenz auf Microsoft Scripting Runtime & Microsoft Visual Basic for Applications Extensibility 5.3!
'» Projekteigenschaft wird von der Vorlage vererbt
' VBA-Editor: Extras > Verweise

Dim oDoc As Visio.Document

Dim vbProj As VBIDE.VBProject
Dim cmpComponents As VBIDE.VBComponents

Dim objFSO As Scripting.FileSystemObject

Dim Path As String

Set oDoc = ActiveDocument
Set vbProj = ActiveDocument.VBProject

Set cmpComponents = vbProj.VBComponents
Set objFSO = New Scripting.FileSystemObject
Path = "C:\temp\VBA_TMP\"                                       'Pfad zu Ordner

If Dir(Path, vbDirectory) = "" Then
    MsgBox "Kein Ordner " & Path & " vorhanden!"
    Exit Sub
   
End If

If objFSO.GetFolder(Path).Files.Count = 0 Then

    MsgBox "Keine Datein für den Import im Ordner enthalten!"
    Exit Sub
   
End If

For Each objFile In objFSO.GetFolder(Path).Files

If objFSO.GetExtensionName(objFile.Name) = "bas" Or objFSO.GetExtensionName(objFile.Name) = "cls" Then

    cmpComponents.Import objFile

End If

Next

End Sub

-------------------------------------------------------------------------------------------------

Furthermore a local macro for me to Export from development enviroment:

-------------------------------------------------------------------------------------------------
Public Sub ExportAllVBAComponents()
'   Datum       | Name              | Version   | Status
'-----------------------------------------------------------
'   2017.04.08  | MB-Ing.           | A         | In Arbeit
'-----------------------------------------------------------
'
' Anmerkungen siehe Änderungshistorie
'
'Funktion:
'Exportiert alle im aktiven VB-Projekt enthaltenen Module & Klassen in den Ordner "Path"
     
Dim objMyProj As VBProject
Dim objVBComp As VBComponent
Dim bFolderExists As Boolean
Dim Path As String

Path = "C:\temp\VBA_TMP\"                                                   'Pfad zu Ordner
bFolderExists = True
Set objMyProj = Application.Vbe.ActiveVBProject

If Dir(Path, vbDirectory) = "" Then
    bFolderExists = False
   
End If

If bFolderExists = False Then

    MkDir (Path)
   
End If

For Each objVBComp In objMyProj.VBComponents

    If objVBComp.Type = vbext_ct_StdModule Then
        objVBComp.Export Path & objVBComp.Name & ".bas"
    End If
   
    If objVBComp.Type = vbext_ct_ClassModule Then
        objVBComp.Export Path & objVBComp.Name & ".cls"
    End If
   
Next
     
End Sub
-------------------------------------------------------------------------------------------------

In future i will check from ADSystemInfo, in which AD-Group the user is located.

So i can decide which macros are shown to the current user....

What do you think about this?

Thank you in advance for your efforts and have a nice day.

Best regards
MB-Ing.

[mamendez]

Consider building a custom tab and ribbon with your macros as a addin.