jpimbert/vbatoolkit

Toolkit for quality development with Excel VBA

VBAToolKit

VBAToolkit is an open-source framework aiming to ease the development of quality VBA code.
It is designed to be used as a tool on the developer's machine to create and manage a VBA project (only Excel at the moment).

Its features include :

Management of configurations
Currently, its main feature is to ease the management of different configurations of a VBA project. Mainly a configuration for delivery to the end user, and a configuration for development purpose. The second one contains test modules not included in the first one.

Allowing versioning
VBA modules are stored separately in text files, consistent versioning is made possible. Though, VBATookit does not integrate any versioning system ; using a third party software such as Git in the project directory is the way.

Team collaborative development Excel files can be regenerated from the text source code under version management. This facilitates the merge operations and thus the team collaborative development.

Facilitating Test Driven Development
VBAToolkit integrates the open-source unit-testing VBAUnit framework to encourage and facilitate Test Driven Development, an healthy development method.

Convenient interface
A toolbar in Excel and several automatic functions provide easy access to these features.

Further reading

---

VBAToolKit is developed by Skwal-Soft and JPI-Conseil

This work is licensed under the Apache License, version V2.0

vbatoolkit's Issues

File format : xla behaving strangely

When saving a workbook with the xlAddIn (18) file format, the wb.FileFormat property has the value 56 instead of 18.

Code to reproduce

Dim wb As Workbook
Set wb = vtkCreateExcelWorkbook()
wb.SaveAs "C:\Any\Path\Will\Do\xxxx.xla", FileFormat:=xlAddIn

Debug.Print "xla = " & xlAddIn
Debug.Print "actual = " & wb.FileFormat

vtkCloseAndKillWorkbook wb

Results are :

xla = 18
actual = 56

Notes

The behaviour for other file formats, such as :

• xlOpenXMLWorkbookMacroEnabled for xlsm files
• xlOpenXMLAddIn for xlam files
• xlExcel8 for xls files

is normal.

Protect the VBA code with password

VBAToolKit could protect the VBA code with the password you would like.
Add a new line with the optional password on MacroDEV_DEV.xls.
Add a new tag on MacroDEV.xml
Modify vtkRecreateConfiguration to integrate this option.

Differences in AddIns locations on different Windows installation

Sometimes AddIns are installed by default in <AppData>/Microsoft/AddIns and sometimes in <AppData>/Microsoft/Macros Complémentaires.
The relative path for references indicated in the References sheet should be relative to the default AddIn folder instead of App Data folder.

Trouble detaching VTK from Excel

Environment
WinXP Pro SP3
Excel 2007

Description
Two problems here :

• the VTK toolbar does not go away if we deactivate the add-in nor if we delete the add-in file.
• the VTK code is still visible if we deactivate the add-in.

How to reproduce

1. Put the add-in file in the folder %APPDATA%\Microsoft\AddIns
2. Open Excel, activate the add-in
3. Close and open Excel
   -> the VTK toolbar is both visible and functionnal (we can see the VBA code in the editor)
4. Deactivate the add-in
5. Close and open Excel
   -> the VTK toolbar is still both visible and functionnal (we can see the VBA code in the editor). It shouldn't.
6. Close Excel
7. Move or delete the add-in in %APPDATA%\Microsoft\AddIns
8. Open Excel
   -> the VTK toolbar is still visible, but not functionnal anymore (we can't see the VBA code in the editor)

GUI errors when recreating a configuration

1/ The UserForm for configuration recreation doesn't close after the user clicked on the recreation button, nor inform the user if the recreation was performed with or without error.
The simplest and proper behavior is to close silently after a successful recreation.

2/ The "Create Configuration" button is activated even when the configuration recreation is impossible because the corresponding workbook is open. The proper behavior is to activate the button only when a recreation is possible.

vtkCreateFolderPath not working on server paths

Currently, paths like \\SERVERNAME\Folder1 are not supported by the vtkCreateFolderPath function, and thus by vtkRecreateConfiguration.
They should.

Separation of Configuration name and Project Name

Now the configuration name is used as the project name when a configuration is recreated.
This behavior doesn't permit to have two different configurations with the same project name, for example for Excel 2007 (.xlam configuration) and Excel 2003 (.xla configuration).
The enhancement is add a property ConfigurationProjectName for each configuration in the configurations description file

vtkExportOneModuleTester not imported

When recreating a configuration, the vtkExportOneModuleTester Tester Class is not imported.

This module is defined without the ".cls" extension in the vtkConfigurations sheet of the Dev project.

Update GitHub language breakdown

Currently, the language breakdown of the repo indicates that the majority of the code is Visual Basic .Net. That's because the last change made to the repo was done before VBA was added to the Linguist project on GitHub (here).

Normally, any changes (even a space added to the README) would be enough to trigger a new analysis by Linguist of the languages in the repo. So, it would be nice to push a fictitious change so this project can appear correctly labelled as a VBA project when people search on GitHub.

I can always create a PR for this, but I don't think this is worth adding me as a contributor 😅

Disable events during automated operations

Events have to be disable during:

• configuration recreation, in case of event handlers existence on configuration being recreated
• unit tests

VBAToolKit reference is not activated in new projects

Incomplete Fix of Issue#5

To take into account at creation and recreation of projects (except for VBAToolKit itself)

To fix in the same time as the project recreation enhancement

Problems for the function resetTestFolder in vtkTestUtilities

When we want to remove a folder or file read only thanks to the DeleteFolder function. An error message is displayed: 5006 permission denied.
To resolve this problem, it is necessary to add to true Force parameter (DeleteFolder path, true).

Upgrading VBAToolKit for Excel 2013

I'm trying to use VBAToolKit for the latest version of Excel, 2013. One incompatibility is that there is a more recent version of XML, "Microsoft XML v6.0". In this version of XML, there is no longer a public data type "DomDocument", but there is a "DomDocument60". I assume that it would be straight-forward to replace "DomDocument" everywhere in the VBA code by "DomDocument60", but I was wondering if anyone has tried doing that.

Recreate Configuration crashes with V0.2 version of VBAToolKit for Excel 2003

The module vtkExcelUtilities was updated for Excel 2007 but not for Excel 2003. A sub is missing.

Detecting errors in vtkExportConfiguration

When is desired to save one or more configurations, and that it was given for example a bad path where given a bad name, an error is displayed.
However, we don't have a description of the problem.
the handler of the error doesn't provide a description of the error.

Normalization bug : " .count, " is not normalized but " .count. " is.

There is a bug in the lexical analyser used to normalize the source files.

" .count, " is not normalized but " .count. " is.

Create Project Isn't Working for Me

I am trying to use VBAToolKit, and it is failing at the very first step. When I try to create a new project, it seems to work (I see the created files) but I get an error message:

Error 1004 (application-defined or object-defined error) in procedure vtkCreateProject of Module Mainfunctions.

I'm not sure if it's relevant, but I'm using Excel 2013.

Toolbars Test doesn't work any more

No explanations. These tests crashes.

Add Project Properties management

When Excel file is recreated by importing modules, Title and Comments properties are set according to VBAToolKit project. This is not adequate for operational VBAToolKit.

This enhancement aims to add a Title and Comments in vtkConfiguration sheet for each configuration :

• Title, will be the project name by default (at project creation)
• Comments for the delivery version will be typed by the user in the CreateProjectForm
• Comments for the dev version will be the same suffixed by " for Dev purpose"
  Add functions in vtkConfiguration for set and get Title and Comments for each configuration.

Test Utility functions doesn't work from the XLAM

The following instruction doesn't work when included in a VBAToolKit_DEV module
Debug.Print VBAToolKit.vtkTestPath

Several problem :

• TestUtilities isn't imported in the Delivery configuration
• VBAToolKit reference isn't activated in VBAToolKit_DEV
• the tespath is determined relative to VBAToolKit, not VBAToolKit_DEV

These must be taken into account during new project creation, and recreation.

VBAToolKit remplace les chemins relatifs en chemins absolus

Lors de l'enregistrement de MacroDEV_DEV, VBAToolKit change les chemins relatifs en chemins absolus dans le xml

Improper Excel File Extension with Excel 2003

All configurations are described to cretae .xlsm or .xlam files under Excel 2007. When the same project is used with Excel 2003, the extensions would be be converted to .xls or .xla.

thisWorkbook not considered at project creation

When creating a new project, the thisWorkbook module is not exported to the source directory nor inserted in the module list in the configuration sheet of the _DEV Excel workbook.

Bug with Excel 2003

Some VBA Excel constants are not defined in Excel 2003, thus VBAToolKit doesn't compile in Excel 2003. And the default extension is .xls instead of .xlsm.