SourceTools.xla

• Download addin - 53.3 KB

Introduction

SourceTool is a utility-addin for:

1. Extracting and loading source code from and to Excel workbooks, templates and add-ins
2. Comparing source code between different versions of above
3. Committing, updating, reverting and comparing source code into a version control system different from VSS (currently only SVN through TortoiseSVN is supported)

Most of the functionality builds upon Rob Boveys famous CodeCleaner addin, which was abused as a "SourceStripper" here.

The various functions can be accessed in the VBA IDE from the toolbar "Source":

• The first button is used to open a project or a selected component of a project (common module, class module or userform). When opening, a folder selection dialog allows to choose the directory where to load the components from, saving is automatically done into a folder called <Excelfilename>.src in the same directory as the Excel file:

  

• The second button is used to save a project or a selected component of a project (common module, class module or user form).
• The third button allows to select a different version of the selected project (this only works for projects as a whole) and shows the differences using a pre-configured difference viewer (currently WinMerge 2.4.6).

After the separator, the rest of buttons are all used for version control:

• The fourth button invokes the tool to check for differences between the current copy (in memory) of the whole project or selected component with the base version in the repository.
• The fifth button updates the whole project or selected component from the repository, notifying any conflicts.
• The sixth button commits the whole project or selected component to the repository, asking for a log message before.
• Finally, the seventh button reverts the whole project or selected component from the repository.

Installation and Configuration

First you need to install tortoiseSVN and subversion (in case you don't connect to a net-based repository already).

Then you need to install WinMerge to use the diff utility.

Finally, installation is completed by simply copying SourceTools.xla into your XLStart folder. You also have to enable access to VB project in the menu: Tools/ Macro/ Security, tab "Trusted Sources", checkbox "Trust access to Visual Basic Project".

Configuration is done in module MGlobals using mainly the following six constants:

' SVN control commands, if needed, replace with other

Public Const COMMITCMD = "C:\Programme\TortoiseSVN\bin\TortoiseProc.exe
    /command:commit /notempfile /path:"
Public Const UPDATECMD = "C:\Programme\TortoiseSVN\bin\TortoiseProc.exe
    /command:update /rev /notempfile /path:"
Public Const REVERTCMD = "C:\Programme\TortoiseSVN\bin\TortoiseProc.exe
    /command:revert /notempfile /path:"
Public Const DIFFCMD = "C:\Programme\TortoiseSVN\bin\TortoiseProc.exe
    /command:diff /path:"
Public Const PATHCONCAT = "*"
Public Const DIFFERCMD = "C:\Programme\WinMerge\WinMergeU.exe "

However, I'm sure that further customization will be needed in case a different version control system is used.

Usage

The first thing to do is to save the sources of the project by clicking the second button. This should create a directory called <Workbook/AddinName>.src with all components of your project as text files in it (named *.bas for plain modules, *.cls for classes, *.frm/*.frx for userforms and *.xwk for the Workbook's and Worksheet's code). So essentially everything you would get when exporting your components individually.

Then you should import BOTH the Workbook/Addin AND the <Workbook/AddinName>.src folder into a repository of your choice (on Sourceforge.net or your private/local one (that's when you need to download SVN itself, in case you don't want/need a repository for yourself, forget the SVN download, Tortoise is enough).

After that, DELETE the Workbook/Addin AND the *.src folder (or better, move them into a safe place, if the following checkout fails). Once you have deleted these files/folder, then CHECKOUT the files you have just imported into the folder (or any other folder, but that means you're changing the location of your addin/workbook).

Bear in mind, this is ALL still done OUTSIDE the Sourcetool, using plain TortoiseSVN functionality.
Now you're ready to use the SourceTool: Simply edit your project in the way you were used to, once finished click the commit button on the sourcetool toolbar in the VBA-IDE (button six) and you'll be presented the Tortoise commit dialog, showing either:

1. All the components that changed since the last update (in case you selected the project node)
2. Just the component that you selected (if changed, if it's up to date, Tortoise will tell you so)

Updating your Project/single Component is done by pressing the fifth button, which downloads the latest version from the repository and imports the component file(s) into your project.

Known Issues/Limitations

If a generic (shell called) version client shall be used, further entry dialogs (e.g. for entering the log messages) have to be added.

Points of Interest

• The great version tool subversion and its cool Windows Explorer-based client tortoiseSVN, which I use now heavily for VBA source code versioning.
• Also see Rob Bovey's site with the CodeCleaner addin (now also available as a COM component) that forms the basis for the source code saving.

History

• 15/03/2007: Initial post to The Code Project
• 09/04/2007
  • Bug fix: Loading now works for whole project
  • Enhancement: Added Stephen Bullens' VBEOnKey to enable shortcuts
• 10/05/2007
  • Bug fix: Removed VBEOnKey again because of several severe side-effects (regular invocation of VBA code isn't too healthy...)
• 04/04/2008: Added Usage section to the article