One of the goals for the new MZ-Tools 8.0 version was to provide a 100% error-free setup experience. While the setups for MZ-Tools 3.0 for VB6/VB5 worked always fine, the setup for MZ-Tools 3.0 for VBA suffered the following problems that caused many support requests along the years:
- The most common problem was: “I have downloaded and installed MZ-Tools 3.0 for VBA, but I don’t see it in my VBA editor”. Believe it or not, most of the time the cause for this was that the setup for MZ-Tools 3.0 for VB6 was downloaded and installed instead 🙁
- Another common problem was: “I lack admin rights on the computer of my company. Is there a way to install MZ-Tools 3.0 for VBA without admin rights?”. This is caused because MZ-Tools 3.0 for VBA uses machine-wide COM-registration, which requires admin rights.
- A variation of the previous one was: “The IT people installed MZ-Tools 3.0 for VBA on my machine, but I don’t see it with my user account”. This is caused because VB6/VB5/VBA add-ins are registered only for the user installing it (HKEY_CURRENT_USER registry hive), not for all users, a bad decision from Microsoft that even considers it a bug: BUG: Add-Ins Only Visible to the User Who Installs VB.
- Another problem was: “I have installed MZ-Tools 3.0 for VBA, but I am using Office 64-bit and it doesn’t appear in the VBA editor”. This is caused because MZ-Tools 3.0 for VBA is a 32-bit add-in and Office 64-bit requires 64-bit add-ins.
- The setup of MZ-Tools 3.0 for VBA could fail with “Unable to register DLL/OCX:RegSvr32 failed with exit code 0x5″ when using Office 2013. This is caused because that version of Office either doesn’t install the MSADDNDR.DLL file required by add-ins, or installed during its beta a version that broke backwards compatibility. See the Microsoft Knowledge Base article A custom add-in that uses interfaces in the Msaddndr.dll file does not work in Office 2013. While I solved this problem in the latest versions of the setup of MZ-Tools 3.0 for VBA supplying the correct version of that file in the setup and forcing its installation, older setups can fail.
So, how were these problems addressed in the new setups for MZ-Tools 8.0?
To prevent the problem #1 (downloaded MZ-Tools 8.0 for VB6 instead of MZ-Tools 8.0 for VBA, or vice versa), the setups require that you explicitly select the checkbox with the (only) component to install before enabling the “Next” button. Hopefully this last chance that forces to read what is being installing will prevent this problem:
The problem #2 (installation without admin rights) required to switch from machine-wide COM-registration (that uses the HKEY_LOCAL_MACHINE registry hive and therefore requires admin rights) to per-user COM-registration (that uses the HKEY_CURRENT_USER registry hive and doesn’t require admin rights). If you are interested in the technical details, see my article INFO: Registry entries to register an add-in for the VBA editor of Office for the current user without admin rights. MZ-Tools 8.0 for VB6 cannot use per-user COM-registration because vb6.exe is normally run “elevated” (with admin rights), and a process running with admin rights cannot “see” per-user COM-registration (for security reasons). So, the setup for MZ-Tools 8.0 for VB6 requires admin rights but that’s not a problem because you require them also to run VB6. On the other hand, Office apps don’t run with admin rights, so they can use COM add-ins that use per-user COM-registration (however, if they are run with admin rights for some reason, they would fail to load add-ins that use per-user COM-registration). Incidentally, since the setup of MZ-Tools 8.0 for VBA doesn’t require admin rights, the user that installs the add-in and the user that uses it is the same, and the problem #3 is avoided too.
The problem #4 (support for Office 64-bit) was solved switching to .NET, as explained in the first part of this series. As much as I wanted to use the latest versions 4.x of the .NET Framework, I stuck to .NET Framework 2.0 because that version is already installed on Windows 7 machines, so you don’t need to download and install it as a prerequisite.
In the early betas of MZ-Tools 8.0 for VBA, the setup tried to guess which version (32-bit or 64-bit) you had installed on your machine, or you had to make the choice:
That proved to be quite error-prone, because it is somewhat difficult to know programmatically which Office version number is installed and much more difficult which “bitness”. So, I simplified the setup and both versions (32-bit/64-bit) are installed always:
The files are exactly the same in both cases (because a .NET assembly compiled for “Any CPU” can behave as a 32-bit component or as a 64-bit component), only duplicated entries for 32-bit / 64-bit are created in the Windows registry for COM-registration and VBA add-in registration, but it doesn’t hurt to have registered an add-in for a host that doesn’t exist. Furthermore, if you replace Office 32-bit by Office 64-bit (or vice versa), MZ-Tools 8.0 for VBA will work without reinstallation.
Finally, the problem #5 caused by the MSADDNDR.DLL file was avoided completely because MZ-Tools 8.0 for VBA doesn’t require it, it has embedded the COM interfaces declared in that file.
With all these new approaches introduced in MZ-Tools 8.0 for VBA, so far the new setup experience is working fantastically well.