Category Archives: VBA add-ins

The strange case of VBA / VB6 editor prompting to repair Visual Studio (revisited 3rd time)

This is the nth time that I find this problem (and I am not the only one), so I am going to document in this 3rd post another cause for it, that happens on Window 10. The problem is that suddenly, the VBA editor of Office or VB6 prompts you to repair some version of Visual Studio. In this case, VB6 prompts to repair Visual Studio 2005 (that I have installed on my main development machine) but it can also happen with Visual Studio 2010, etc.:

vb6prompttorepair

I already talked about this back in 2012, where the root cause was the installation of Visual Studio from a removable hard disk (since then I always copy the setups of Visual Studio to the fixed hard drive before installing) and in 2014, where the cause was the deletion of partition D: of the hard disk (actually a variation of the previous cause). In both cases I was unable to fix the problem until I uninstalled all versions of Visual Studio and reinstalled them, which for a developer of extensions like me that need all Visual Studio versions to test is the most painful experience.

Since I adopted Windows 10 as my main OS, I have seen this problem twice, in 2015 and in 2016, and it has the same cause in both cases as I will explain, but different to the ones of 2014 and 2012. To diagnose this issue, you need to follow the procedure explained by Heath Stewart in his blog. So I started looking for events with ID 1001:

eventviewer1001

As you can see, the error doesn’t tell you exactly which is missing. The events with ID 1004 provide the actual cause:

eventviewer1004

So, the file C:\Windows\Microsoft.NET\Framework\v2.0.50727\RedistList\VSList.xml is missing. This is a file likely installed by Visual Studio inside a folder that belongs to the .NET Framework, not to Visual Studio. I would say this is a bad idea. And it is not the only .xml file installed by Visual Studio, there are a lot of them.

But which is the root cause that makes that a file suddenly disappears from that folder? It is the installation of the major updates of Windows 10: Update “1511” in November 2015 and “Anniversary Update” 1607 in October 2016. As you know, the next one announced a couple of days ago will be “Creators Update” in 2017 and the issue will reappear in 3D ;-). Each time that a major Windows 10 update is installed, the previous Windows installation is stored in a backup folder “Windows.old” (which includes the .NET Frameworks folders, and the .xml files in question). It seems that the Windows 10 update is smart to detect that you had .NET Framework 2.0 installed (it’s an optional feature of Windows 10, not installed by default) and it installs it for you, but not smart enough to detect the missing files. So, alas, the .xml files are not restored and cause this issue. In 2015 I still had the Windows.old backup folder and I was able to restore all the missing .xml files using BeyondCompare, the folder comparison utility that I love. Now in 2016 I deleted the Windows.old backup folder before I realized the issue, so I had to provide the .msi files to the prompt dialog, copying them to the hard disk before, of course!

I hope this helps you if you encounter this issue.

Another two strange cases of MZ-Tools 8.0 for VBA could not be loaded

It seems that the path to load a .NET-based add-in in the VBA editor of Office is full of mines. In the last couple of days I have discovered two new scenarios (apart from this and this) that can cause “<Add-In> could not be loaded”. I will document them here for future reference for me or other people:

This first one happens if you are not using a COM Shim, you are using .NET Framework 2.0 and the version of Office is 2000, 2002 (XP) or 2003. It is due to a lockback policy that “prevents the .NET Framework 2.0 common language runtime (CLR) from initializing when the .NET Framework 2.0 is hosted in either the Word process space or the Excel process space. The policy restriction limits Word and Excel from loading versions of the .NET Framework that are later than version 1.1. Therefore, the .NET Framework 2.0 assemblies cannot load”. It is documented in the Knowledge Base article You cannot load a .NET Framework 2.0 assembly from Visual Basic for Applications in Word 2003 and earlier versions, or in Excel 2003 and earlier versions and fortunately has a solution through a registry key HKEY_CLASSES_ROOT\Interface\ {000C0601-0000-0000-C000-000000000046} that can be created. I was aware of this problem since long time ago and my setup created that registry key if Office 2003 or 2002 was detected. However, I noticed that my add-in was failing for Office 2000 (yes, I have users running Office 2000 on old PCs) because I added support for Office 2000 later and I didn’t update the setup to check that version. This case is difficult to diagnose because the following script works perfectly (I mean, the problem only manifests loading the assembly in Office):

Dim o
Set o = CreateObject("MZTools8VBA")

The other problem was also hard to diagnose. It happened when using a COM Shim to load the CLR using code from the COM Shim wizards provided by Microsoft, which calls the CorBindToRuntimeEx function passing NULL to the first pwszVersion parameter. A NULL value means to load the highest CLR installed below CLR 4.0, which should be CLR 2.0. However, on Windows XP I got HRESULT 0x80131700. Fortunately I found on a comments of the post COM Shim CLR Loader Bug that the problem could be solved passing the specific version “v2.0.50727”, which certainly fixed the problem for me.

The strange case of System.InvalidCastException (“Unable to cast COM object of type ‘System.__ComObject’ to class type System.Windows.Forms.UserControl”) showing toolwindow

In my last post I explained a problem that can happen when a .NET-based add-in for the VBA/VB6 editor that uses a COM Shim tries to use any Common Language Runtime (CLR) that it’s installed on the target machine (CLR 2.0 or CLR 4.0), assuming that the add-in is built with .NET Framework 2.0/3.0/3.5 and therefore can use CLR 2.0 or CLR 4.0. This post is about other problem that can happen.

The ideal approach, that was the first that I implemented, is that the COM Shim uses at run-time the first detected CLR that it supports. In my case the logic was:

  • First it detects if CLR 2.0 is installed. If so, use it.
  • If not, it detects if CLR 4.0 is installed. If so, uses it.

This would be the best approach because at any given time, it uses the available(s) CLRs. The default CLRs installed by Windows are:

  • Windows XP: Neither CLR 2.0 nor CLR 4.0
  • Windows Vista, Windows 7: CLR 2.0
  • Windows 8, Windows 8.1, Windows 10: CLR 4.0

And life would be great if that were the whole picture. However, most add-ins need to create and show toolwindows. For a .NET-based add-in, this is somewhat tricky because toolwindows need ActiveX UserDocuments, and .NET doesn’t support UserDocuments. However, a UserControl can be used instead with some tricks as I explained in HOWTO: Create a toolwindow for the VBA editor of Office from an add-in with Visual Studio .NET. The UserControl, being .NET-based, needs to be registered as a COM component, and .NET-based COM components need to specify the required runtime version of the .NET Framework in the Windows registry, in the RuntimeVersion value inside the InProcServer32 key:

HKEY_CLASSES_ROOT\progid
   (default) = progId
HKEY_CLASSES_ROOT\progid\CLSID
   (default) = clsid
HKEY_CLASSES_ROOT\CLSID\{clsid}
   (default) = progid
HKEY_CLASSES_ROOT\CLSID\{clsid}\InProcServer32
   (default) = mscoree.dll
   Class = ClassName
   ThreadingModel = Both
   Assembly = Stringized_assembly_reference
   Codebase = path_of_private_assembly
   RuntimeVersion = version_of_the_runtime
...

toolwindowcontrolregistration

This registration of the usercontrol used by the toolwindow must be done by the installer at setup-time. And I used in the installer the same logic than in the COM Shim: first it detects CLR 2.0 and, if not available, then it detects CLR 4.0.

However, a couple of my users reported this exception when clicking a button that should show a toolwindow:

System.InvalidCastException: Unable to cast COM object of type ‘System.__ComObject’ to class type ‘System.Windows.Forms.UserControl’. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.

Toolwindows are created calling the CreateToolWindow function, that receives in the second parameter the ProgId of the ActiveX UserDocument that the function will create internally and return as output last parameter:

Windows.CreateToolWindow (AddInInst, ProgID, Caption, GuidPosition, DocObj) As Window

And that exception happened in a line of code that casts the usercontrol instance returned by the last output parameter (which should be a UserControl instance) to the System.Windows.Forms.UserControl type. Why?

It took me a while to discover the cause (the scenarios are so messy that often I need to go walking or swimming to get clarity thinking about the problem…). But at some point I realized that there were some scenarios where the runtime version of the usercontrol and the runtime version of the COM Shim would mismatch:

The first one is that you have Windows 10, which by default provides .NET Framework 4.0 (CLR 4.0) and not .NET Framework 2.0/3.0/3.5 (CLR 2.0). The user runs the setup of the add-in. The usercontrol is registered with the only runtime available (v4.0.30319). When the add-in is loaded the COM Shim uses the only runtime available, also v4.0.30319. So far so good. However, one day the user installs .NET Framework 2.0/3.0/3.5, which is an optional feature of Windows 10:

windowsoptionalfeatures

In this case, the usercontrol remains registered with runtime version v4.0.30319 (unless the user runs the installer again) but the COM Shim, that detects CLRs at runtime, not at setup time, would use CLR 2.0! (because its logic gives it preference over v4.0.30319). And this causes the System.InvalidCastException, because the CreateToolwindow returns an instance of a CLR 4.0 usercontrol. Since such thing cannot enter the CLR 2.0 of the COM Shim, a System.__ComObject wrapper is created, that cannot be cast to a UserControl type.

The second scenario is that you have Windows 10, which by default provides .NET Framework 4.0 (CLR 4.0) but the user has installed .NET Framework 2.0/3.0/3.5 (CLR 2.0). The user runs the setup of the add-in. The usercontrol is registered with the runtime v2.0.50727 (because the logic gives it preference over v4.0.30319). When the add-in is loaded the COM Shim uses also the preferred runtime v2.0.50727 over v4.0.30319. So far so good. However, one day the user uninstalls the optional .NET Framework 2.0/3.0/3.5. In this case, the usercontrol remains registered with runtime version v2.0.50727 (unless the user runs the installer again) but the COM Shim, that detects CLRs at runtime, not at setup time, would use CLR 4.0 instead of CLR 2.0! This causes a different exception, though.

So I thought a different approach: rather than using whatever CLR is installed on the machine (with preference for CLR 2.0 over CLR 4.0), I use the default CLR that should be installed on each Windows version:

  • Windows XP: none. But the setup enforces that .NET Framework 2.0 must be installed.
  • Windows Vista, Windows 7: .Net Framework 2.0
  • Windows 8, Windows 8.1, Windows 10: .Net Framework 4.0

This approach makes the COM Shim immune to installation/uninstallation of additional .NET Framework installations (because default .NET Frameworks are OS components and cannot be uninstalled, only optional .NET Frameworks can be uninstalled).

And while this approach seemed fine, another two users reported the same System.InvalidCastException, both cases on Windows 10 using VB6.  More walking and swimming :-). The reason is that VB6 can be set to run in compatibility mode with Windows XP:

windowsxpcompatibility

What happens if a user is running Windows 7 and sets VB6 with compatibility mode with Windows XP? The setup was run on Windows 7, so the usercontrol is registered with runtime version v2.0.50727 (the default for Windows 7). When VB6 in compatibility mode loads the COM Shim, it thinks that it is running on Windows XP and uses the .NET Framework 2.0 that the setup required. So nothing bad happens.

But what happens if the user is running Windows 10 and sets VB6 with compatibility mode with Windows XP? The setup was run on Windows 10 (not in compatibility mode), so the usercontrol is registered with runtime version v4.0.30319 (the default for Windows 10). When VB6 in compatibility mode loads the COM Shim, it thinks that it is running on Windows XP and uses the .NET Framework 2.0 that the setup required. So a System.InvalidCastException happens when performing the cast operation due to the mix of runtime versions.

The best solution, that I hope that fixes this problem for good, is to make the COM Shim to read the registered runtime version of the usercontrol of the toolwindow from the Windows registry, and use the matching CLR. With this approach, it doesn’t matter what Windows version the COM Shim thinks it is running on, because it won’t use the default CLR of that Windows version, but the one that was determined at setup-time.

Update (September 12, 2016): it happens that there is a way to register a .NET component as a COM component indicating that it can work with CLR 2.0 and CLR 4.0. Instead of using the RuntimeVersion name mentioned above, you can use the SupportedRuntimeVersions name and provide a list of CLRs in order of preference:

HKEY_CLASSES_ROOT\progid
   (default) = progId
HKEY_CLASSES_ROOT\progid\CLSID
   (default) = clsid
HKEY_CLASSES_ROOT\CLSID\{clsid}
   (default) = progid
HKEY_CLASSES_ROOT\CLSID\{clsid}\InProcServer32
   (default) = mscoree.dll
   Class = ClassName
   ThreadingModel = Both
   Assembly = Stringized_assembly_reference
   Codebase = path_of_private_assembly
   SupportedRuntimeVersions = v2.0.50727;v4.0
...

See the post In-Proc SxS and Migration Quick Start. So, original approach for the COM Shim would work:

  • First it detects if CLR 2.0 is installed. If so, use it.
  • If not, it detects if CLR 4.0 is installed. If so, uses it.

The strange case of mscoree.dll and the CLRCreateInstance function

Since I started three months ago to use a COM Shim for my .NET-based MZ-Tools 8.0 for VBA/VB6 to achieve isolation and .NET CLR independence, nobody had reported a single problem. However, last week two different problems were reported. This post is about the first one. The error was that on Windows XP, MZ-Tools 8.0 for VB/VBA failed to load. This dreaded error message:

MZTools8CouldNotBeLoaded

Fortunately, the user reported that installing .NET Framework 4.0 the problem was solved, but why?

The error message is hiding the actual error code and description (Microsoft started to show that crucial piece of information when an add-in fails to load in Visual Studio .NET 2002). I was able to reproduce the problem so I didn’t need to contact the user for further information. An approach to know why an add-in is failing to load on any IDE is to use Process Monitor to capture disk and registry activity and then guess what is failing (some missing registry entry or file, etc.). Unfortunately the current version of Process Monitor doesn’t support Windows XP, and although I couldn’t find an earlier version to download, I found the Registry Monitor and File Monitor utilities (that later became unified in Process Monitor) and they worked on Windows XP.  However, I couldn’t guess the problem. I always forget that the easiest way to know the actual error is to create a VBScript with this code that creates a COM object with the ProgId of the add-in (typically “MyAddIn.Connect”):

Dim o
Set o = CreateObject("MZTools8VBA")

And executing that script shows the actual problem:

ProcedureEntryNotFound

ProcedureEntryNotFoundScript

So, the problem was that the CLRCreateInstance function (used by the COM Shim to load the Common Language Runtime, CLR) was not found in mscoree.dll. At this point I knew I needed another tool: depends.exe (Dependency Walker).

The CLRCreateInstance function was introduced in .NET Framework 4.0 to load the CLR 4.0. The CLR 2.0 used by .NET Frameworks 2.0, 3.0 and 3.5 is loaded with a different function. My COM Shim has this logic:

  • First it detects if .NET Framework 2.0 is installed. If so, it uses the old API to load CLR 2.0.
  • If not, it detects if .NET Framework 4.0 is installed. If so, it uses the new CLRCreateInstance API to load CLR 4.0.

Note: this logic that uses run-time detection of the CLR rather than setup-time detection is flawed by a different reason that caused the second reported problem (for another post).

Since my setup requires .NET Framework 2.0 on Windows XP, I knew that the CLRCreateInstance function was not called on Windows XP. However, since my COM Shim dll linked it at compile-time:

COMShimOld

the whole dll of the COM Shim failed to load if that function is missing in mscoree.dll (it seems that my knowledge of C/C++ linking is very limited, I thought I would get a runtime error only if the COM Shim tried to call that missing function…).

This explained why installing .NET Framework 4.0 solved the problem: even if the COM Shim still used CLR 2.0 (by the logic above), the mscoree.dll updated by .NET Framework 4.0 provided the missing CLRCreateInstance function and the COM Shim dll didn’t fail to load.

But a question remained that got me puzzled for several days: nobody had reported this problem on Windows 7, that lacks .NET Framework 4.0 (it installs only .NET Framework 3.5 / CLR 2.0), in these three months. And since my setup doesn’t require .NET Framework 4.0 on Windows 7, I was sure that not all users have .NET Framework 4.0 installed. I should have received tons of bug reports with the same problem. But not a single one… The first thing I did was to check the version of mscoree.dll of my virtual machines with Windows 7. First surprise: they showed 4.0.xxxx, although .NET Framework 4.0 was not installed:

mscoreeWindows7WithSP1

Then I used depends.exe to check that the mscoree.dll of Windows 7 certainly exports the CLRCreateInstance function:

mscoreeExportedFunctionsWindows7WithSP1

So, how is that Windows 7, lacking .NET Framework 4.0, has a mscoree.dll version 4.0.xxxx that provides the CLRCreateInstance method to load the CLR 4.0?

At this point I decided to start from scratch: I created a virtual machine with Windows 7 RTM, and suprise, surprise, mscoree.dll was version 2.0.xxxx:

mscoreeWindows7WithoutSP1

and certainly lacked the CLRCreateInstance method:

mscoreeExportedFunctionsWindows7WithoutSP1

And of course, installing VB6 or Office and MZ-Tools 8.0 reproduced the problem that happened on Windows XP.

It was only after I applied SP1 to Windows 7, that mscoree.dll was updated to version 4.0.xxxx. So, it seems that Microsoft decided to include the mscoree.dll version 4.0.xxxx of .NET Framework 4.0 in the service pack 1 of Windows 7, even if that service pack doesn’t include .NET Framework 4.0.

Of course the best solution is to use dynamic linking at run-time using GetProcAddress so that the COM Shim can be loaded even if the CLRCreateInstance function is missing:

COMShimNew

For the record, Windows 10 uses version 10.0.xxxx of mscoree.dll:

mscoreeWindows10

Case closed.

Achieving .NET CLR independence of .NET-based add-ins for the VBA editor of Office

Being quite a perfectionist, there was one issue in older versions of my MZ-Tools 8.0 add-in for VBA that was not right for me: it required .NET Framework 2.0, which is installed by default on Windows 7, but not on Windows 8, Windows 8.1 or Windows 10, it’s an optional feature:

NetFrameworkOptionalFeature

So, installing MZ-Tools 8.0 for VBA on Windows 8.0 or higher likely you would get this dialog:

NetFramework20Required

And then the user should download and install .NET Framework 2.0. Needless to say, this is mostly inconvenient and can hurt sales, so I thought it would be nice if MZ-Tools 8.0 for VBA could use whatever higher .NET Framework was installed on Windows (such as .NET Framework 4.0 on Windows 8 or higher).

Before digging on how this can be done, some basics: a .NET Framework is composed of a Common Language Runtime (CLR), which contains the virtual machine for .NET, with the JIT compiler, garbage collector, etc. and a set of libraries. So:

.NET Framework = CLR + libraries

There are three kinds of .NET Frameworks:

  1. .NET Frameworks that provide their own, new, CLR. This has been the case for:
    • .NET Framework 1.0 (provides CLR 1.0)
    • .NET Framework 1.1 (provides CLR 1.1)
    • .NET Framework 2.0 (provides CLR 2.0)
    • .NET Framework 4.0 (provides CLR 4.0)
  2. .NET Frameworks that don’t provide a new CLR, they just provide new libraries. This has been the case for:
    • .NET Framework 3.0 (uses CLR 2.0 of .NET Framework 2.0)
    • .NET Framework 3.5 (uses CLR 2.0 of .NET Framework 2.0)
  3. .NET Frameworks that provide an update of an existing CLR (backwards compatible). This has been the case for:
    • .NET Framework 4.5, 4.5.1, 4.5.2, 4.6, etc. (all of them update the CLR 4.0).

A native process (such as an Office app) must load a CLR in order to load a managed (.NET) add-in. CLRs 1.0, 1.1 and 2.0 cannot coexist in the same process, that is, once of those CLRs is loaded, no other CLR version can be loaded in the same process. However, CLR 4.0 can coexist with CLR 2.0 in the same process.

Note: from this point, I will talk only about CLR 2.0 / CLR 4.0 (.NET Framework 2.0 or higher).

So, there are two aspects that an add-in for VBA must consider:

  • The lowest .NET Framework that it requires, which depends on the libraries used by the add-in. MZ-Tools 8.0 uses WinForms (not WPF), so it can work on .NET Framework 2.0 (it doesn’t require libraries from .NET Framework 3.x or 4.x).
  • The lowest CLR that it requires, which depends on the lowest .NET Framework that it requires. Since MZ-Tools 8.0 requires .NET Framework 2.0, the lowest CLR is 2.0.

Now the question is: given a native process that must load a .NET-based add-in, which CLR version does it use?.

An .exe process can have an .exe.config file that specifies the supported/required runtimes of the .NET Framework. Visual Studio uses such .config files but Office apps don’t, so they don’t care about CLR versions (they can use CLR 2.0, CLR 4.0, or even both at the same time). By no means an add-in should install an .exe.config file to modify the behavior of an Office application regarding supported/required CLRs, because it can break other add-ins.

However, there are still two approaches that an add-in written against .NET 2.0 (or .NET 3.x, which also uses CLR 2.0) can use to work with CLR 2.0 on systems that have it installed (such as Windows 7) and with CLR 4.0 on systems that don’t have CLR 2.0 installed but have CLR 4.0 installed (such as Windows 8, Windows 8.1 or Windows 10):

The first approach is to make the setup to detect which CLR versions are installed on the system:

  • If CLR 2.0 is detected, register the .NET assembly for COM Interop setting the Runtimeversion value of the InprocServer32 key to v2.0.50727, as it would be the usual since it’s an assembly compiled against .NET 2.0.
  • If CLR 2.0 is not detected and CLR 4.0 is detected, set the Runtimeversion value to  v4.0.30319. This forces the assembly to use .NET 4.0 even if it was compiled against .NET 2.0:

ComInteropRegistration

Note: to register the assembly for COM Interop, the setup cannot use regasm.exe, not even the version of .NET 4.0 (C:\Windows\Microsoft.NET\Framework\v4.0.30319\regasm.exe) because regasm.exe sets always the Runtimeversion value to the value of the CLR that the assembly was compiled against. Instead, the setup must create the registry entries for COM Interop directly (see the required entries here).

The second approach is to use a COM Shim, which I explained in Isolating .NET-based add-ins for the VBA editor with COM Shims. Since a COM Shim is a native C++ dll that loads the CLR to create a custom AppDomain and to load the assembly, it can decide which CLR version to load, depending on which CLRs are installed on the machine: if CLR 2.0 is installed, it loads it, and if CLR 2.0 is not installed but CLR 4.0 is installed, it loads CLR 4.0.

The strange case of MZ-Tools 8.0 crashing the VBA editor of Autodesk Inventor

Recently I got a bug report from a customer of my MZ-Tools 8.0 add-in for VBA stating that it crashed the VBA editor of Autodesk Inventor 2016. If he uninstalled MZ-Tools, the problem disappeared. This user has been using MZ-Tools 8.0 for months but now, suddenly, this problem appeared.

I downloaded the trial version of Autodesk Inventor 2017 and I was able to reproduce the problem. This was the first window of the crash:

AutodeskCrash

Clicking the View Report Details more info was shown:

AutodeskCrashDetails

So, it was an unhandled exception. The dmpuserinfo.xml contained the stack trace:

AutodeskCrashStackTrace

So, when MZ-Tools shows a modal window, Autodesk Inventor tries to set a “quick access toolbar gradient color” using a function that crashes the app. Initially I thought (and so it was reported) that the crash happened only when MZ-Tools was set to load on startup, but not when loaded manually using the Add-In Manager. However, I realized that it happened at any time that MZ-Tools tried to show a modal dialog.

What could be causing this after months of normal usage? My main suspect was the COM Shim that I introduced in build 8.0.0.77 (June 1) to provide proper isolation from other .NET-based add-ins for the VBA editor. And certainly the hypothesis was correct. When I ran MZ-Tools without the COM Shim, the problem didn’t happen. So, it seems that WinForms dialogs don’t play well when loaded from a different AppDomain. The several Office apps and versions don’t suffer this problem, but Autodesk Inventor does. Although I reported the problem to Autodesk, I guess that likely they won’t fix it, or anyway the existing released Autodesk Inventor 2016 and 2017 builds will persist with the problem. So, as a workaround, the new setup of MZ-Tools 8.0 for VBA will detect if Autodesk products are installed. In that case, the setup won’t register MZ-Tools using the COM Shim.

Isolating .NET-based add-ins for the VBA editor with COM Shims

Office 2010 introduced the 64-bit version, which supports only 64-bit COM add-ins. This meant that all add-ins created with VB6 for its VBA editor stopped to work, because they are 32-bit add-ins. The only possibilities to create 64-bit add-ins are using C++ 64-bit, or using .NET x64 and COM Interop. So, it is not a surprise that quite a few add-ins for the VBA editor are created now using .NET. I wrote the article  HOWTO: Create an add-in for the VBA editor (32-bit or 64-bit) of Office with Visual Studio .NET back in 2012 along with a few others referenced at the end of that article. One thing that many developers of add-ins for the VBA editor may not know is that by default all .NET-based add-ins are loaded in the same AppDomain (a .NET concept for isolation of assemblies), and therefore are not properly isolated. A couple of bad things that can happen are the following:

  1. “System.InvalidCastException: Unable to cast object of type ‘<Namespace of add-in 1>.VBProjectClass’ to type ‘<Namespace of add-in 2>._VBProject'”. This exception happens because two add-ins use different Interop Assemblies (IAs) for the “Microsoft Visual Basic for Applications Extensibility 5.3” COM reference. For example, my MZ-Tools add-in uses its own local Interop Assemblies, while others use their own Interop Assemblies or Primary Interop Assemblies (PIAs) provided by Microsoft. I have good reasons to use my own IAs, but I have tested that even two add-ins using PIAs can suffer the problem depending on the version of the PIAs or their locations. This problem happens because both add-ins are loaded in the same AppDomain, otherwise it wouldn’t happen.
  2. Since Office is a COM application, .NET-based add-ins use Runtime Callable Wrappers (RCWs). It is important that an add-in releases its count on RCWs when no longer used, calling Marshal.ReleaseComObject. For example, if a .NET add-in calls VBE.SelectedVBComponent.CodeModule.CodePane, it is leaking RCWs for VBComponent and CodeModule. How bad is this depends on the host, but suffice to say that for VB6 you can freeze the code editor. The article Office application does not quit after automation from Visual Studio .NET client explains the issue when automating Office but it applies also to using the extensibility model for VBA. The problem is that RCWs are created by AppDomain, so when an add-in needs a RCW, if another add-in in the same AppDomain already created one for the same COM object, the same RCW is reused by the second add-in. Apart from an add-in failing to decrease its usage count on a RCW, it can also happen than an add-in decreases usage counts not belonging to it, for example calling Marshal.FinalReleaseComObject.

So, how are both problems solved? The solution is well-known for developers of add-ins for Office applications using .NET since 2002 or 2003, through the use of a COM Shim. A COM Shim is an ActiveX dll, written in C++, that acts as a native add-in, and loads the Common Language Runtime (CLR) of .NET, creates an AppDomain, and loads the .NET-based add-in. Calls to the native C++ add-in (OnConnection, OnDisconnection, etc.) are passed to the managed (.NET) add-in.

If you are a developer of .NET-based add-ins for the VBA editor, the following are the MSDN articles that you need to read, understand and apply for your add-in to be a good citizen, running isolated from other add-ins in its own AppDomain. When reading the articles, replace “add-in for Office” or “Office extension” by “add-in for VBA editor”:

Isolating Office Extensions with the COM Shim Wizard
This article is relevant because it explains all about isolation and COM Shims.

Isolating Microsoft Office Extensions with the COM Shim Wizard Version 2.0 (VS 2005)
This article is relevant because it doesn’t use the Managed Aggregator introduced in next version 2.3. Add-ins don’t require “aggregation”, just “containment” is required.

Isolating Microsoft Office Extensions with the COM Shim Wizard Version 2.3 (VS 2008)
This article is relevant because it uses “aggregation” instead of “containment”, and you need to realize that add-ins don’t require “aggregation” and you can remove with some adjustments the ManagedAggregator project and its output assembly.

COM Shim Wizards for VS 2010 (2.3.2.0)
This article is relevant because it’s the latest available COM Shim wizard with bug fixes, yet using “aggregation”. I used this and then removed “aggregation” to use “containment” of version 2.0.

Taking COM Shim Wizards to 64-bit
This article is relevant because it explains how to port the COM Shim to 64-bit.

There are two ways of knowing which AppDomain an add-in is loaded into:

Using Process Explorer, you can use the .NET Assemblies tab of the properties window of a process. For example, this image shows that MZ-Tools is loaded in its own MZToolsAppDomain using CLR 4.0:

AppDomainCLR40

For some reason that I don’t know, Process Explorer doesn’t show AppDomains for CLR 2.0:

AppDomainCLR20

The other way is calling from the own add-in System.AppDomain.CurrentDomain and showing its FriendlyName property. This image shows the MZ-Tools uses its own MZToolsAppDomain for CLR 2.0 (that Process Explorer didn’t show):

IsolatedAppDomain

If not using the COM Shim, the AppDomain is the DefaultDomain:

DefaultAppDomain

The strange case of MZ-Tools 8.0 for VBA could not be loaded

Yesterday I got this error when loading my add-in MZ-Tools 8.0 for VBA:

MZTools8VBACouldNotBeLoaded

I hate that error. The VBA and VB6 IDEs hide valuable information about why an add-in couldn’t be loaded. In Visual Studio you get at least a COM error number (the error message is almost always <Unknown error> for COM errors):

MZTools8VSCouldNotBeLoaded

And you have this article of mine explaining most causes given an error number: HOWTO: Troubleshooting Visual Studio and Office add-ins.

But for VB6/VBA, you are clueless. I have got that error many times because my MZ-Tools add-in is supported in several IDEs using different approaches for registration for the end user (using a setup) and for myself during development/debugging (using PowerShell scripts for registration/unregistration).

For the end user (using a setup):

  • MZ-Tools 8.0 for VBA uses per-user COM-registration, not requiring admin rights to install, which is important for many Office users in corporate environments. Per-user COM registration works because Office doesn’t run with admin rights by default (an executable running elevated can not see per-user COM-registration, for security reasons).
  • MZ-Tools 8.0 for VB6/VB5 uses machine-wide COM-registration, requiring admin rights to install, because per-user COM-registration wouldn’t work since VB6 runs elevated by default (to be able to register ActiveX Dlls when compiling).

For development/debugging (using PowerShell scripts):

  • MZ-Tools 8.0 for VB6/VB5 uses machine-wide COM-registration, because of the reason explained above. That means that I must run Visual Studio always elevated, so it can launch vb6.exe for debugging without annoying prompts for elevation of Visual Studio with a restart.
  • MZ-Tools 8.0 for VBA uses also machine-wide COM-registration, because since I run Visual Studio always elevated, Office runs always elevated during debugging, and therefore per-user COM-registration wouldn’t work (remember, a process running elevated cannot see per-user COM-registration).

All this means that any error using the wrong registration or running Visual Studio, VB6 or Office with the wrong elevation (or lack of), and I get the dreaded “MZ-Tools 8.0 could not be loaded”.

In those cases, I need to use Process Monitor (see HOWTO: Using the Process Monitor (ProcMon) tool to diagnose Visual Studio add-ins problems) to try to guess which registry entry or file is causing a problem. In this case, I got this:

PathNotFound

So, the dll of the add-in was not found. But, wait a minute: it is looking for it in the path “C:\Users\<user>\AppData\Local\MZTools Software\MZTools8VBA\”, which is used for the end user (setup), not in the path used during development. I went to the Control Panel, Program and Features, and MZ-Tools 8.0 was not installed!. So, somehow the last uninstallation of the setup failed silently, removing the file but leaving the COM-registration in the Windows registry. How could that be? I test the setups and PowerShell scripts exhaustively when created/modified and they were working correctly for weeks since the last changes.

Then, suddenly, I remembered something. I am using Windows 10, and I always use the “Control Panel”, “Program and Features” item to uninstall the software, because I like colors and I am not a fan of the new bland “Settings” > “System” > “Apps and features” user interface of Windows 10. But the day before, I used the “Apps and features” to uninstall MZ-Tools 8.0 for VBA, maybe because I guess that the Control Panel will be removed at some point since it seems a remaining duplicated thing. So, after some manual cleaning of registry entries, I tried again to install and uninstall using the setup (using “Settings” > “System” > “Apps and features”) and then to register for development using the PowerShell script and the problem was reproduced. One try again and I realized the cause:

  • If you have a setup that doesn’t require admin rights to install, the  “Control Panel”, “Program and Features” doesn’t require admin rights to uninstall either, which is correct.
  • However, the “Settings” > “System” > “Apps and features” prompts for admin rights to uninstall even if the setup was installed without admin rights, which seems incorrect to me.

It happens that some users of MZ-Tools 8.0 for VBA run Office with admin rights (I don’t know really why they want that), and therefore if MZ-Tools 8.0 for VBA runs with per-user COM-registration, it doesn’t work. So I changed the setup of MZ-Tools 8.0 for VBA some months ago to behave as follows:

  • By default, the setup runs without admin rights. In that case, the setup registers the add-in using per-user COM-registration.
  • If you run Office with admin rights, you can force the setup to use machine-wide COM-registration (so it works) running it with admin rights (using the “Run as administrator” context menu). The setup detects that it is running with admin rights and uses machine-wide COM-registration instead of the default per-user COM-registration. Conversely, the uninstaller detects if it is running with admin rights and in that case removes machine-wide COM-registration; otherwise, it removes per-user COM-registration.

And then it happens that on Windows 10, if you use the “Settings” > “System” > “Apps and features” to uninstall, the uninstaller is elevated to use admin rights (even if it was installed without admin rights) and it removes the non-existent machine-wide COM-registration, leaving intact the existing per-user COM-registration that was created when installed without admin rights. When the PowerShell script registers the add-in using machine-wide COM-registration, two COM-registrations are in place. If I run Office with admin rights, the machine-wide COM-registration is used and it works. But if I run Office without admin rights, the per-user COM-registration that shouldn’t be there takes precedence, and points to a file that was uninstalled, so the error.

Now I have to modify the uninstaller to remove also the per-user COM-registration even if running with admin rights.

Announcing unified MZ-Tools 8.0 for Visual Studio, VBA (64-bit/32-bit), VB6 and VB5

It has been a long time since my last post here, and for a good reason. Since I released MZ-Tools 8.0 for Visual Studio 2015 last July 1 (converted from add-in to package), I have been very busy the last months to achieve a new huge milestone:

Announcing unified MZ-Tools 8.0 for Visual Studio, VBA (64-bit/32-bit), VB6 and VB5

From the extensibility point of view, MZ-Tools 8.0 is a unique piece in the world, AFAIK: it provides the same feature set for four different IDEs (Visual Studio, VBA editor, Visual Basic 6.0 and Visual Basic 5.0) with different extensibility APIs reusing the same code at binary level:

MZ-Tools 8.0 for Visual Studio (as package):
MZTools8VSPackage

MZ-Tools 8.0 for Visual Studio (as add-in):
MZTools8VSAddIn

MZ-Tools 8.0 for VBA:
MZTools8VBA

MZ-Tools 8.0 for VB6:
MZTools8VB6

MZ-Tools 8.0 for VB5:
MZTools8VB5

As you can see in the images, in all cases there is the same big MZTools8PlugIn.dll file, which contains the feature set, and smaller host adapter dlls that “plug” the features into the IDEs. If you are interested, I outlined the strategy to achieve this in this other post.

Calling Marshall.ReleaseComObject

If you ever develop COM add-ins for the VBA editor (or for some Office application), you may find that sometimes you need to call Marshall.ReleaseComObject (when you get strange errors that disappear when calling Marshall.ReleaseComObject) and most of the time (by default) you don’t have to (when you get an InvalidComObjectException). The issue is a bit tricky because of a number of factors, such as:

– The RCWs are reused and cached per-AppDomain

– An AppDomain can contain more than one .NET add-in

– RCWs have a reference counter, and the COM-object that the RCW wraps has another reference counter.

– There can be COM singletons involved.

– Etc.

Here you have some useful links to get a better knowledge:

Marshal.ReleaseComObject Method
http://msdn.microsoft.com/en-us/library/system.runtime.interopservices.marshal.releasecomobject.aspx

Marshal.ReleaseComObject Considered Dangerous
http://blogs.msdn.com/b/visualstudio/archive/2010/03/01/marshal-releasecomobject-considered-dangerous.aspx

The mapping between interface pointers and runtime callable wrappers (RCWs)
http://blogs.msdn.com/b/mbend/archive/2007/04/18/the-mapping-between-interface-pointers-and-runtime-callable-wrappers-rcws.aspx

Chapter 2: Basics of Office Interoperability (Part 2 of 3)
http://msdn.microsoft.com/en-us/library/office/aa679807%28v=office.11%29.aspx