Using VBAToolKit

Quick Intro

A VBA Project managed with VBAToolKit is associated with a folder (see the project folder description). The name of this folder is the same as the project. Main subfolders and files of the project structure, assuming your project is named SampleProject are:

• Project\SampleProject_DEV.xlsm is the main Excel workbook in which you'll develop and test your VBA Modules, Classes and UserForms
• Delivery\SampleProject.xlsm (you might choose .xlam if you prefer) is the file to be delivered to the users and customers of your project (including yourself if you are also a user, not only a developer). This file is generated by VBAToolKit based on the project configuration
• Template subfolder contains files to be used as templates for tests or for Excel workbook generation (not yet implemented)

A project can manage several configurations. A configuration is mainly an Excel worksheet composed of VBA components (modules, classes, UserForms). By default, a project named SampleProject has two configurations named SampleProject (the configuration to deliver) and SampleProject_DEV (the configuration for development and tests).

General usage

The developer starts its work by creating a project from scratch. Then he defines and manages the project configurations and he creates new code modules in the project.
While he's working, the code modules are exported as text files in order to be managed under version control and collaborative development. The developer creates unit tests using VBAUnit and TDD methodology.
When the work is completed, the developer can commit its to the version control system (Git). He can regenerate an Excel workbook of a configuration to prepare the release and the delivery.
One may sometimes have to reactivate the VBAToolKit toolbar included in the VBA IDE.

Important
All VBAToolKit functions work on the Active workbook project.

Create a Project

Click on the Create Project icon of the VBAToolKit toolbar
The Create New Project window is displayed. You have to type in the Project Name and the folder path where the project folder structure will be created. A new folder with the same name as the project will be created in the given folder path.

When both fields are filled, the Create button is enabled on which you can click to create:

• the project folder structure
• a development configuration initialized with VBAUnit modules
• an empty delivery configuration
• an initialized Git repository (if Git is installed)

Recreate a Configuration

Click on the Recreate Configuration icon of the VBAToolKit toolbar
The Recreate Configuration dialog box is displayed. You have to select a configuration in the combo list and choose to recreate only the selected configuration or all the configurations but the one selected.

Be sure that the Excel workbooks for the configurations to recreate are not opened, then click on Create Configuration button.

Manage Configurations

The development configuration Excel workbook (Project\SampleProject_DEV.xlsm) contains a sheet names vtkConfigurations (the Configuration Sheet).

Each column starting with column B describes a configuration:

• The configuration name on the first line
• The Excel workbook path containing this configuration on the second line
• The VBA project name of this configuration on the third line (default is the configuration name), this information is also visible in the Title field of the Summary sheet of the property window of the Excel file
• An optional comment for this configuration on the fourth line; this information is visible in the Comment field of the Summary sheet of the property window of the Excel file
• A list of module source code pathes on the following lines

You can freely add, delete and modify configurations in the Configuration Sheet. Be careful to always have a development configuration with a standard name SampleProject_DEV and with a standard Excel workbook Project/SampleProject_DEV.xlsm; the Configuration Sheet is located in this configuration.

To be implemented later:

• Manage the type of configuration (Excel 2003, 2007, Add-In 2003, 2007)
• Use a specific Excel template to generate the configuration
• Convenient toolbar button and UserForm to manage configurations

Manage Modules

The development configuration Excel workbook (Project\SampleProject_DEV.xlsm) contains a sheet names vtkConfigurations (the Configuration Sheet).

Each row starting with row 5 describes a module:

• The module name on the first column
• The path of the source code module text file to be used with each configuration in the following columns

You can freely add, delete and modify modules in the Configuration Sheet. If you add a VBA component in the development or delivery Excel workbook, it will not be exported nor managed under version control unless declared in the Configuration Sheet.

You can define the module pathes exactly as you want; be sure to create the corresponding folder. The standard pathes are:

• - if the module doesn't belong to the configuration
• Source/ConfProd for modules used in the delivery configuration
• Source/ConfTest for modules only used in the development configuration (for test purpose)

The standard extensions for module source code text files are:

• .bas for a standard VBA module
• .cls for a VBA class module
• .frm for a VBA UserForm (a binary .frx file is also created)

To be implemented later:

• Convenient toolbar button and UserForm to manage modules

Manage References

The development configuration Excel workbook (Project\SampleProject_DEV.xlsm) contains a sheet names vtkReferences (the Reference Sheet).

Each row starting with row 2 describes a reference:

• The reference name on the first column
• The reference GUID on the second column, if relevant
• The Reference Path (absolute, or relative to the standard Microsoft Add-In users's folder) only if the reference has no GUID
• A X in the following columns if the reference must be added to the corresponding configuration

the standard Microsoft Add-In users's folder is Application Data\Microsoft\AddIns in the user's home folder.

To add references proceed as follow:

• Add the references to the development configuration Excel workbook (Project\SampleProject_DEV.xlsm) using the command References of the Tools menu of the VBA Editor
• Save this workbook; this forces the Reference Sheet update
• The new references are now added to the Reference Sheet; just add a Uppercase X in the column of the configuration you want to use the références

To be implemented later:

• Convenient toolbar button and UserForm to manage references

Export Modules

This feature is automatic for the Excel workbook development configuration. Each time you save your work, for example with Ctrl-S, the modified VBA component are exported according to the pathes in the Configuration Sheet.
This feature needs an event handler BeforeSave added to the ThisWorkbook VBA class module of the development Excel workbook. This class module is specific to the development configuration, so it is exported to the Source/ConfTest standard path. Be careful, if your delivery configuration needs a ThisWorkbook class module, it will be different and must be exported in the Source/ConfProd standard path.

To be implemented later:

• Detection of non managed modules when the workbook is saved or default log of the non managed modules with blank path in all configurations
• Detection of deleted modules and warning to the user (configuration sheet update on request)
• Convenient toolbar button and UserForm to re-export all modules of any configuration

Create Tests

In Test Driven Development methodology, the developer duty starts with coding automatic Unit Tests, using VBAUnit for VBA projects.

The project subfolder Tests is dedicated to receive files needed for or created during tests. A good practice is to prepare the Tests subfolder in the SetUp method of each tester class, and clear this subfolder in the Teardown method. The developer may use the following convenient functions included in VBAToolkit:

• VBAToolKit.vtkTestPath which gives the testPath of the current active project TO BE CONFIRMED, and tested
• VBAToolKit.getTestFileFromTemplate which copies a file from the Templates folder to the Tests folder, and optionally open the file as an Excel Workbook
• VBAToolKit.getTestFolderFromTemplate which copies a folder including its content from the Templates folder to the Tests folder
• VBAToolKit.resetTestFolder which clear the Tests folder, included in the teardown method of the TesterTemplate.cls VBAUnit class module

Generate a Configuration

To prepare and test a release, you'll have to generate the delivery configuration. Type in the following command in the immediate window of the VBA IDE. Replace SampleProject with the name of your project:

vtkRecreateConfiguration projectName :="SampleProject",configurationName:="SampleProject"

Except the template ones, the Excel files are not subject to version control. You'll have to regenerate these files after a checkout or after a merge. Use the same command as above, just replace the configuration name with the proper one. The development Excel workbook is a special case:

• change the name of development Excel workbook to, for example, Project/SampleProject2_DEV.xlsm
• open this workbook and the VAB IDE and change the project name for SampleProject2_DEV

Then type in the following command in the immediate window

 vtkRecreateConfiguration projectName :="SampleProject2",configurationName:="SampleProject_DEV"

To be implemented later:

• Convenient toolbar button and UserForm to re-generate any configuration or all configurations

Reactive the VBAToolKit toolbar

When a developer works on its project with the VBA IDE, its code may crash (yes, sometimes your code may crash while developing it!) and VBA has to be reset. Such a reset deactivates the VBAToolKit toolbar located in the VBA IDE.
The VBAToolKit toolbar is duplicated in Excel from which it may also be used (in the Add-Ins Ribbon in Office 2007).
You can also reactivate the VBAToolKit toolbar in VBA IDE by clicking the reactivate button .

Hidden Capabilities

Some capabilities are presently reachable only by hacking ways. More on the Hidden Capabilities page.