Click here to Skip to main content
15,867,686 members
Articles / Programming Languages / VBScript
Article

SourceTools.xla

Rate me:
Please Sign up or sign in to vote.
4.47/5 (13 votes)
5 Apr 2008GPL34 min read 228.9K   3.4K   43   56
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:

VB.NET
' 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)


Written By
Software Developer (Senior)
Austria Austria
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
Generalvery cool Pin
bonkey220-Aug-09 7:48
bonkey220-Aug-09 7:48 
QuestionUsing keyword expansion Pin
The Genius One14-Jun-09 12:14
The Genius One14-Jun-09 12:14 
AnswerRe: Using keyword expansion Pin
rkapl22-Jun-09 15:42
rkapl22-Jun-09 15:42 
QuestionUpdates Pin
pablleaf8-Jun-09 4:35
pablleaf8-Jun-09 4:35 
AnswerRe: Updates Pin
rkapl22-Jun-09 15:45
rkapl22-Jun-09 15:45 
Question.frx files Pin
Andreas_198322-Jan-09 6:02
Andreas_198322-Jan-09 6:02 
AnswerRe: .frx files Pin
rkapl22-Jan-09 10:35
rkapl22-Jan-09 10:35 
QuestionIs there a tutorial that explain the usage in more details? Pin
kaifu12-Jan-09 9:35
kaifu12-Jan-09 9:35 
From what I have seen while working with this code, this code will be a tremendous help in updating my excel spreasheets. However, I still couldn't figure out how to compare my existing spreadsheets and the previous versions. Help! please!
AnswerRe: Is there a tutorial that explain the usage in more details? Pin
rkapl13-Jan-09 10:35
rkapl13-Jan-09 10:35 
QuestionDoes any one know if there is a way to change VBIDE.VBComponent icons in VBE? [modified] Pin
StevenLi17-Oct-08 11:24
StevenLi17-Oct-08 11:24 
AnswerRe: Does any one know if there is a way to change VBIDE.VBComponent icons in VBE? Pin
StevenLi29-Oct-08 2:36
StevenLi29-Oct-08 2:36 
GeneralRe: Does any one know if there is a way to change VBIDE.VBComponent icons in VBE? Pin
rkapl29-Oct-08 14:05
rkapl29-Oct-08 14:05 
GeneralRe: Does any one know if there is a way to change VBIDE.VBComponent icons in VBE? Pin
GISHY774-Nov-11 11:14
GISHY774-Nov-11 11:14 
QuestionButtons don't work Pin
Sebastian Paul8-Sep-08 23:47
Sebastian Paul8-Sep-08 23:47 
AnswerRe: Buttons don't work Pin
rkapl9-Sep-08 19:41
rkapl9-Sep-08 19:41 
GeneralRe: Buttons don't work Pin
Sebastian Paul9-Sep-08 23:37
Sebastian Paul9-Sep-08 23:37 
GeneralRe: Buttons don't work Pin
rkapl17-Sep-08 9:58
rkapl17-Sep-08 9:58 
QuestionDoes this work with office 2007? & what svn host path does it use? Pin
BugMeNot ACCOUNT31-Mar-08 21:37
BugMeNot ACCOUNT31-Mar-08 21:37 
GeneralRe: Does this work with office 2007? & what svn host path does it use? Pin
rkapl1-Apr-08 12:57
rkapl1-Apr-08 12:57 
GeneralRe: Does this work with office 2007? & what svn host path does it use? Pin
BugMeNot ACCOUNT1-Apr-08 19:21
BugMeNot ACCOUNT1-Apr-08 19:21 
GeneralGetting an error "couldnt determine path of project: path not found" error msg Pin
Pilotac24-Sep-07 11:42
Pilotac24-Sep-07 11:42 
GeneralRe: Getting an error "couldnt determine path of project: path not found" error msg Pin
rkapl25-Sep-07 10:43
rkapl25-Sep-07 10:43 
GeneralRe: Getting an error "couldnt determine path of project: path not found" error msg Pin
Pilotac25-Sep-07 11:44
Pilotac25-Sep-07 11:44 
GeneralRe: Getting an error "couldnt determine path of project: path not found" error msg Pin
rkapl25-Sep-07 12:51
rkapl25-Sep-07 12:51 
Questionuse with sourcegear Vault? Pin
lex30013-Aug-07 23:51
lex30013-Aug-07 23:51 

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

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