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
• Project\SampleProject.xml contains the configuration description of the project in a convenient way to be managed with git. The configuration description is duplicated in the Project\SampleProject_DEV.xlsm Excel workbook which is easier to modify for the developer than the XML file
• 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
• Templates subfolder contains files to be used as templates for tests or for Excel workbook generation

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 Browse an XML configuration file in the XML Configuration File field then choose at least one configuration to recreate. When both are done, the Create Configuration button is enabled on which you can click to recreate the configuration Excel workbooks.

Be sure that the Excel workbooks for the configurations to recreate are not opened before clicking on Create Configuration button.

All configurations are recreated according to the configuration parameters read in the XML Configuration File:

• from a fresh new Excel workbook, or from a Template
• with Project Name, Title, and Comment initialized
• the VBA code can be protected by an optional password
• saved on the proper path relative to the root path of the project
• all these parameters are duplicated in the vtkConfigurations and vtkReferences sheet of the Development configuration Excel workbook. These sheets are regenerated when the development configuration Excel workbook is recreated.

The XML configuration file is named like the project and is located in the Project folder (Project\SampleProject.xml) near the development configuration Excel workbook.

Manage Configurations

The development configuration Excel workbook (Project\SampleProject_DEV.xlsm) contains a sheet named vtkConfigurations (the Configuration Sheet). The content of this sheet is automatically saved in the XML Configuration File.

Each column starting with column B describes a configuration:

• The configuration name on the first line
• The Excel workbook path (relative to the project path) containing this configuration on the second line. Instead of .xls or .xlsm extension, you can write .<xls> and VBAToolKit will choose the proper extension according to the current Excel version you are running. Similarly, you can write .<xla> instead of .xla or .xlam.
• The Excel workbook template path (relative to the project path) for recreating this configuration (default is to create a new workbook from scratch) on the third line
• The VBA project name of this configuration on the fourth 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 fifth line; this information is visible in the Comment field of the Summary sheet of the property window of the Excel file
• An optional VBA code password on the sixth line
• 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 path; the Configuration Sheet is located in this configuration.

To be implemented later:

• 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). The content of this sheet is automatically saved in the XML Configuration File.

Each row starting with row 7 describes a module:

• The module name on the first column
• The path (relative to the project 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). The content of this sheet is automatically saved in the XML Configuration File.

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 Application Data 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

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.