Problems with VBA — and solutions

Severe Crash of Excel

Problem : During programming, it is possible to experience a severe crash and get a corrupted Excel File after the crash.

Solution : Restart from an older Excel file (from a previous commit) and reimport modified modules.

Unexplainable Compilation Error

Problem : At first run of automatic tests, the error message "undefined user type" appears

Solution 1 : Activate the reference Microsoft Visual Basic for applications Extensibility 5.3

Solution 2 : Run Prep again before Run

Impossible to delete a file with Kill

Problem : When using Dir to browse a folder tree, it is sometimes impossible to delete a file with Kill

Solution : Use the Dir command to browse another directory before applying the Kill command. When browsing a directory with Dir, this directory is frozen and it's impossible to modify its content.

Impossible to delete a file after git initialization during tests

Problem : When we create a local repository we use shell commands. Every command will create 2 processes that will be related with folders and don't allow tests to delete them, these processes are named "conhost.exe" and "cmd.exe".

Solution : Kill the processes with Shell("cmd.exe /k TASKKILL /IM cmd.exe")

Unit tests crash hen using Git

Problem : When Shell commands are included in VBA Unit Tests, these tests crashes with a VBA error

Workaround : Application.Wait (Now + TimeValue("0:00:01")) after the Shell Command call.

Double defined functions in VBAToolKit and VBAToolKit_DEV

Problem : We use VBAToolKit to manage the VBAToolKit_DEV project with contains the next version of VBAToolKit. We have to be sure to use the current version of each module for project management and the last version for project test.

Solution 1 : Does nothing ! By default the called function is the one located in the same module then in the same project than the caller.

Solution 2 : Fully qualify the called Functions with ProjectName.ModuleName.FunctionName. This solution implies to have different versions of certain modules for Test and for Delivery.

Impossible to remove a Module with VBComponents.Remove method

Problem : It's a known problem of VBA. The problem is when importing a module with the same name ; VBA creates a copy with a new name.

Workaround : If the module already exists before import, read the lines of code and replace the existing ones instead of import the file.

Insufficient Memory

Problem : A message "Mémoire insuffisante" could appear many times, it must be validated with OK and the program is still running.

Solution : This message may be due to Sub and Function names too long; make them smaller.

Error 91 when running automatic tests

Problem : A message "Error 91" could appear during automatic tests, especially when running automatic tests on only one Tester module using run("TesterClassName")

Solution 1 : This is due to the String used as parameter to run not corresponding to any Tester module. Double-check the name of the module and the String parameter of run for any typos. If there is any, fix them, then do prep and run again.

Solution 2 : You double-checked and there is no typo. This looks like a bug you will have to work around.

1. Slightly modify the name of the Tester module
2. Do prep.
3. Do run("slightyModifiedTesterClassName")
4. It shouldn't raise error 91.
5. Change back the name of the Tester module to its original and wanted value.
6. Do prep
7. Do run("TesterClassName")
8. It shouldn't raise error 91.

"Invalid use of property"

Problem : You get an "Invalid use of property error" when setting a variable.

Solution : You are actually not trying to set a variable, but a proper object. To set it properly, you have to use the 'Set' statement.