VBA Source Code Management (SCM)

Started by paulv45, March 14, 2012, 02:43:53 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

paulv45

I am feeling the need to do a bit of source code management (SCM) for my VBA code.  It seems like to do this the basic requirement is "export all" and "import all" forms, modules, classes from/to a project and then some kind of version control.  Googling around just a little points to 2 solutions -- MS sourcesafe and "other."  For "other" the initial reads are CodeCleaner http://www.appspro.com/Utilities/CodeCleaner.htm for export/import and Git http://git-scm.com/ for management.

Does anyone have any words of wisdom in the VBA SCM area?

Thanks.

Paul


AndyW

CodeCleaner works with Excel etc, but last time I tried it didn't work with Visio (2003).

I just included some code in my application that will export all the modules, classes and forms to a specified directory.

As for SCM, I chose to setup Subversion on my own (local) web server and then use TortoiseSVN to interface with it via windows explorer.
Live life with an open mind

paulv45

Thanks for input Andy.
So for export do you brute force parse VBProjectData byte array or is there a more elegant access provided?

AndyW

Here is my export code, I just have this in a module in my application and run it when required.

Private Sub ExportVBAFiles()

    Dim pVBAProject As VBProject
    Dim vbComp As VBComponent  'VBA module, form, etc...
    Dim strSavePath As String
    Dim vsoDoc As Visio.Document
    'Dim vbRef As VBIDE.Reference
   
    For Each vsoDoc In Visio.Documents
       
      ' Get the VBA project
      ' If you want to export code for Normal instead, paste this macro into
      ' ThisDocument in the Normal VBA project and change the following line to:
      ' Set pVBAProject = ThisDocument.VBProject
 
      Set pVBAProject = vsoDoc.VBProject
 
      strSavePath = "C:\My Sources\" & pVBAProject.Name
           
      'For Each vbRef In pVBAProject.References
        'Debug.Print vbRef.description, vbRef.FullPath
      'Next
     
      ' Loop through all the components (modules, forms, etc) in the VBA project
     
      If pVBAProject.Protection = vbext_pp_none Then
     
        For Each vbComp In pVBAProject.VBComponents
     
          Select Case vbComp.Type
     
              Case vbext_ct_StdModule
                  vbComp.Export strSavePath & "\" & vbComp.Name & ".bas"
     
              Case vbext_ct_Document, vbext_ct_ClassModule
                  ' ThisDocument and class modules
                  Call vbComp.Export(strSavePath & "\" & vbComp.Name & ".cls")
     
              Case vbext_ct_MSForm
                  vbComp.Export strSavePath & "\" & vbComp.Name & ".frm"
     
              Case Else
                  vbComp.Export strSavePath & "\" & vbComp.Name
     
          End Select
   
        Next
     
        MsgBox "VBA files have been exported to: " & strSavePath
     
      Else
     
        MsgBox "Skipped locked project: " & pVBAProject.Name
     
      End If
   
    Next

End Sub
Live life with an open mind

paulv45

Thanks for sharing your code, Andy.
It points me to a world I have not yet had the opportunity to explore -- Visual Basic Extensibility Library.
Not sure why I have not discovered this before.
In googling around and looking at MSDN library I have found book references circa 1999 but nothing online (except embedded in Visual Studio but not independently discussed in a reference manual).
Do you have documentation you would recommend?
Am I missing something obvious?
Thanks.
Paul

AndyW

Hi Paul

No, I never found any good documentation for that. Just bits and pieces from googling.

Andy
Live life with an open mind

paulv45

Further digging around lead to the following discoveries.
Since I'm very much a newbie to this area any words of wisdom from others appreciated.

1.  The old way of doing VB source code management seems to be Visual Source Safe (VSS) but this is replaced by the new Team Foundation Server (TFS) for which there is a nice video:
http://channel9.msdn.com/shows/VS2010Launch/Clemri-Steyn-Team-Foundation-Server-for-Everyone/

2.  TFS (and VSS?) are designed to work nicely with Visual Studio, but are not really geared for VBA since TFS would work at the granularity of a project not at stuff within project.

3.  There does not appear to be a source code management system that ties directly to VBA so that you can check out/in etc from within the VBA IDE.  At least I have not discovered something that claims do to that.

4.  This leave us with some type of export/import of project info and some system like Subversion or Git to actually manage the code.

5.  Subversion and Git have advocates showing advantages and disadvantages, but I'm not smart enough yet in this area to sort this out.  I am marching down the Git route for the moment so can use GitHub for sharing and don't need to manage a server.

6. I was hoping to use CodeCleaner for the export/import operation but as Andy discovered it doesn't work with Visio and my experience supports that.  I think there are also 32-bit/64-bit issues, but not sure.  Anyway its:
http://www.appspro.com/Utilities/CodeCleaner.htm

7.  This leads to code like Andy posted for export/import operations.  It seem like the VB Extensibility Library is replaced in Office 2010 with Visual Basic Add-in Model.  Here is a reference for that.
http://msdn.microsoft.com/en-us/library/gg278951.aspx

Again, that's where I'm at at the moment -- any help appreciated.

Paul


AndyW

A good source for what you can do can be found here, http://cpearson.com/excel/vbe.aspx

I have moved my export code out of my vsd and created an add-in for the vba ide editor. It would be possible to extend this to handle importing classes/modules/forms etc into the project. The examples in the above link show copying between projects, modifying code etc. As I opten say, its software, everything is possible!

Andy
Live life with an open mind

Nikolay

Recently came across the same need, and created an add-in that can import/export VBA from Visio files.


http://unmanagedvisio.com/products/vba-importexport-addin/

download/install is quite straightforward, two buttons (import/export) visible on "Developer" tab, should work with Visio 2007-2016

Basically the code was inspired by the nice article on excel and CodeCleaner. Unfortunately the code in that article is in a form of a VBA macro, which may sometimes be not that handy  (you'll need to copy that over and over to every document where you want to use it), and right out of the box that code does not work for Visio. So this extension addresses these issues (you get the code import/export with one click), also it allows you to export code in "ThisDocument", and supports x64 version of Visio.

The extension is open source, the code is on github:
https://github.com/nbelyh/VisioImportExportVba

AndyW

Nicely done as a Visio addin instead of a VBA IDE addin, although as a VBA add-in you can use it fo Word, Excel etc.

You should check the VBA project protection, it if has been protected then not allow the import/export.

I also chose to have an export option to ignore the forms. If you are version controlling you will find the .frx is always changed, so can be useful to ignore forms if you know they haven't changed.

It may also be useful to export the list of references (VBA example below). I didn't chose to import those, but it is relevant if version controlling the code.


        Set ts = oFSO.CreateTextFile(strSavePath & "\References.dat", True)
       
        For Each vbRef In pVBAProject.References
       
            With vbRef
           
                If Not .BuiltIn Then
               
                    On Error Resume Next
                   
                    Call ts.Write(.Name)
                    Call ts.Write("|")
                    Call ts.Write(.Description)
                    Call ts.Write("|")
                    Call ts.Write(.Guid)
                    Call ts.Write("|")
                    Call ts.Write(.Major)
                    Call ts.Write("|")
                    Call ts.Write(.Minor)
                    Call ts.Write("|")
                    Call ts.Write(.FullPath)
                    Call ts.WriteLine
                   
                    On Error GoTo 0
                   
                End If
           
            End With
           
        Next
       
        Call ts.Close

Live life with an open mind

Visio Guy

#10
I actually have built a COM add-in for VBA IDE that exports all of the files either to a standard subdirectory of the Visio file's location, or to a dated subdirectory (vba-export-2016.03.09). So I always have the code in text format, in case the Visio file gets corrupted.

I've also built some snippet handling into it, but it is very rough. There's no installation, instead, I have to play with registry settings to get it to work every time I clean my machine or get a new PC. (win 32 or 64, Visio 32 or 64 - I still am not sure which settings are really the right ones)

The add-in has huge potential, but I just haven't had time to perfect it and share it with the world.

It even has right-click options for the Immediate Window for often-needed, and long, expressions like Visio.ActiveWindow.Selection(1), etc.

Also, parts of the add-in seem to stop working inexplicably. No idea why.

Another problem is determining if the VBA session is for Visio or Word or Excel. Not super-important, but it would help for having different families of snippets being automatically loaded.

Anyway, food for thought...

For articles, tips and free content, see the Visio Guy Website at http://www.visguy.com
Get my Visio Book! Using Microsoft Visio 2010

AndyW

If this of any help to others, I have uploaded by VBA add-ins for export/import and also one for resetting the tab order on forms.

https://github.com/andy-w/VBA-Add-in-Project-Export-Import

https://github.com/andy-w/VBA-Add-in-Tab-Index-Reset
Live life with an open mind

Surrogate

AndyW, how install your add-in in VBA IDE ???

AndyW

It is installed automatically when built. Other than that on other machines, should just need to register the dll
Live life with an open mind

Thomas Winkel

@Nikolay: Very cool, thanks for sharing!

I have the same export in VBA, but no import.
We have our stencils in SVN and the idea was to have the exports in order to track the code modifications.
In practice we do not use it because nobody makes an export before commit...
Maybe I should create a TortoiseSVN pre-commit hook that makes the export automatically before commit.

I imported your AddIn in VS2015 and I'm able to build it and it works.
But when I try to debug, an error message appears: "You cannot debug or run this project, because the required version of the Microsoft Office application is not installed."
I already changed to following references to my versions:
* Microsoft.Office.Inerop.Visio -> 14 (Visio 2010)
* Office -> 15 (Office 2013)

Could you give me a hint, please?

Regards,
Thomas