Excel/VBA Error with .Net COM component

Recently, I have been asked to demonstrate how to package some good old Excel/VBA script into a COM component that allows the organization to reuse the code. Sharing bas file is problematic.

So I use VS2005 VB.Net to develop essentially a hello world COM component. It works fine on my machine. But when I tried to execute the following VBA code:

Sub Test()
  Dim x as MyComClass
  Set x = MyComClass     ' L1
  x.SayHi( "Paul" )
End Sub

on another machine with identical version of Excel 2000, the above code in line L1 fails with a HRESULT of 0x80070002. Strange I thought! It is complaining that it cannot find the assembly.

My initial reaction is to perform a RegAsm /codeBase. This still fails. Then I install it into the GAC and it still fails.

That's odd. Finally, firing up Process Explorer reveals the reason.

It turns out that some components are loaded before me that required CLR1 and since only one version of CLR is permitted per process, my demo COM component, a CLR2 assembly, is not compatible with CLR1. Hence it is reporting the correct error message - file not found as CLR1 cannot understand the CLR2 meta data.

Once this becomes obvious to me, the logical thing to do is to give Excel.exe its own config file which specify the supported runtime to be of version 2. This forces those CLR1 components to run in CLR2.

The morale of the story is that: if you can considering writing COM component in .Net, it is wise to write it in CLR1 because in this way, it can be pulled up to CLR2 automatically and you do not need an application configuration file to do that.