Click here to Skip to main content
Click here to Skip to main content

SourceTools.xla

, 5 Apr 2008
Rate this:
Please Sign up or sign in to vote.
VBE IDE addin for comparing and versioning VBA using SVN or CVS

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":

Screenshot - clip_image002.jpg
  • 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:

    Screenshot - clip_image004.jpg

  • 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

License

This article, along with any associated source code and files, is licensed under The GNU General Public License (GPLv3)

About the Author

rkapl
Software Developer (Senior) Austrian Federal Financing Agency
Austria Austria
No Biography provided

Comments and Discussions

 
AnswerRe: Does any one know if there is a way to change VBIDE.VBComponent icons in VBE? PinmemberStevenLi29-Oct-08 2:36 
GeneralRe: Does any one know if there is a way to change VBIDE.VBComponent icons in VBE? Pinmemberrkapl29-Oct-08 14:05 
GeneralRe: Does any one know if there is a way to change VBIDE.VBComponent icons in VBE? PinmemberGISHY774-Nov-11 11:14 
QuestionButtons don't work PinmemberSebastian Paul8-Sep-08 23:47 
AnswerRe: Buttons don't work Pinmemberrkapl9-Sep-08 19:41 
GeneralRe: Buttons don't work PinmemberSebastian Paul9-Sep-08 23:37 
GeneralRe: Buttons don't work Pinmemberrkapl17-Sep-08 9:58 
QuestionDoes this work with office 2007? & what svn host path does it use? PinmemberBugMeNot ACCOUNT31-Mar-08 21:37 
I can't get it to work in office 2007. What path does it use to store the files? Where does it go in the repository?
 
Does it automatically create the required Directory structures? or do we have to import and create the structure in the SVN repo first?
 

GeneralRe: Does this work with office 2007? & what svn host path does it use? Pinmemberrkapl1-Apr-08 12:57 
GeneralRe: Does this work with office 2007? & what svn host path does it use? PinmemberBugMeNot ACCOUNT1-Apr-08 19:21 
GeneralGetting an error "couldnt determine path of project: path not found" error msg PinmemberPilotac24-Sep-07 11:42 
GeneralRe: Getting an error "couldnt determine path of project: path not found" error msg Pinmemberrkapl25-Sep-07 10:43 
GeneralRe: Getting an error "couldnt determine path of project: path not found" error msg PinmemberPilotac25-Sep-07 11:44 
GeneralRe: Getting an error "couldnt determine path of project: path not found" error msg Pinmemberrkapl25-Sep-07 12:51 
Questionuse with sourcegear Vault? Pinmemberlex30013-Aug-07 23:51 
AnswerRe: use with sourcegear Vault? Pinmemberrkapl4-Aug-07 10:33 
GeneralRe: use with sourcegear Vault? Pinmemberlex30014-Aug-07 12:00 
GeneralRe: use with sourcegear Vault? Pinmemberlex30014-Aug-07 12:08 
GeneralRe: use with sourcegear Vault? Pinmemberrkapl4-Aug-07 13:18 
QuestionHow do I get the toolbar? PinmemberBobo32128-Mar-07 3:06 
AnswerRe: How do I get the toolbar? Pinmemberrkapl28-Mar-07 9:27 
GeneralRe: How do I get the toolbar? PinmemberBobo32128-Mar-07 22:28 
GeneralRe: How do I get the toolbar? Pinmemberrkapl2-Apr-07 11:30 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140709.1 | Last Updated 5 Apr 2008
Article Copyright 2007 by rkapl
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid