How to get COM Server for Excel written in VB.NET installed and registered in Automation Servers list?

Question

Versions

Excel 2007, Windows Vista, VB.NET, Visual Studio 2008 with .NET 3.5 sp2, MSI setup package.

What I am trying to do

I have an Excel UDF that is written in VB.NET. It is exposed as a COM Server because you cannot create Excel UDFs in .NET languages directly. Installation is a real pain because none of the installation settings seem to get it quite right; none of them gives you an installation package that puts the COM Server on the client machine with the server registered, the type library registered, and the component visible in the Excel 2007's list of Automation Servers.

What I've tried

Here are the installation settings for type libraries, with their defects apparent at compile-time and install-time:

vsdrfComSelfReg

• no warning during the compilation of the setup project
• Module xxx.tlb failed to register. HRESULT -2147024703
• Component's ProgID and GUID are set in registry, but the component does not appear in the list of Automation Servers

vsdrfDoNotregister

• no warning during compilation
• installation works but of course the TLB is not registered

vsdrfCOM

• Compile-time warning: WARNING: Unable to create registration information for file named 'xxx.tlb'
• type library is not registered during installation

The correct setting should be vsdrfCOM, as explained here:

Q. Can anyone please tell what does vsdrfCOM mean in a setup project of Visual Studio? It is available when I check the property "Register" among properties of added files in a Setup project.

A. It means that Visual Studio will extract COM registration data at build time and put it in the MSI file (mostly the MSI file's registry table, but also the class table). So when you install it your code doesn't need to self-register because the file gets copied to disk and the registry entries get created. It will also create type library registration by adding an entry to the MSI's TypeLib table.

Many of the difficulties appear to be Vista-specific. In particular, using the REGCAP utility to produce a .REG file from a .TLB file does not work in Vista. If not for that, perhaps this advice would be useful. Instead, it produces empty .REG files when it works at all.

I've tried all of the advice in this StackOverflow post. That post has a pretty good description of the technical problem:

The entries in the References dialog box come from the HKCR\TypeLib registry key, not from HKCR\CLSID. If your assembly does not show up in the References dialog but compiled DLL's can still use your COM assembly, it means the classes and interfaces were correctly registered for your assembly, but that the type library itself was not.

The question

Anyone have an idea of how to make the installation register the component and the type library? I don't have access to a Windows XP machine.

---

Elaboration on why this sucks

The .TLB is not necessary for any compiled code to call it. I've not tried deploying an Excel Automation add-in, as you are doing, but my guess is that the UDFs should load and run just fine.

It's not quite like that in Excel.

• The user opens a worksheet and tries to reference the UDF. It isn't found because the DLL is not loaded. FAIL
• The user goes to Home|Excel Options|Add-Ins|Excel Add-Ins+Go and the COM Server is not listed in the Add-Ins dialog. FAIL
• The user then presses Automation Servers to get a list of available automation servers. The DLL is not there. FAIL
• The user returns to the Add-Ins dialog and selects Browse, navigates to the installation directory, and selects either the DLL ("XXX is not a valid add-in") or the type library ("The file you selected does not contain a new Automation Server, or you do not have sufficient rights..."). FAIL

As far as I can tell, the user has to run regasm.exe from the command line to make the Excel UDF/COM server available. How would you feel about telling people to run regasm from the command line to install an add-in to Excel?

---

Edit 2009-10-04

Mike's comments and directions below are awesome. The key thing I did not know was that the setup program has a built-in registry editor for adding registry keys. Oh, and that the installation function with attribute ComRegisterFunctionAttribute was not being called by the Microsoft installer. I already had the directions about writing installer functions from the sources he cited.

Solution

I took a shot at deploying an automation add-in over the weekend. It turns out that it is enormously complicated (not a surprise to you!) and I could find absolutely no sources on the internet on how to do this properly. None.

There are sources that describe how to use RegAsm, but none how to correctly use a Setup Project to register an automation add-in, which is a little different from your standard COM add-in.

Fortunately, I was able to solve it. Here's what I found out:

If you read some of the articles on how to create and register your C# automation add-in, you'll see that you need to add a registry key named Programmable at HKEY\_CLASSES\_ROOT\CLSID\\{GUID}, where {GUID} is the GUID of your COM-visible class.

This is generally done by adding a pair of methods marked by the ComRegisterFunctionAttribute and the ComUnregisterFunctionAttribute. A good example of this comes from the article Writing Custom Excel Worksheet Functions in C# by Gabhan Berry:

// C#:

[ComRegisterFunctionAttribute]
public static void RegisterFunction(Type type) {
  Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type));
}

[ComUnregisterFunctionAttribute]
public static void UnregisterFunction(Type type) {
  Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type), false);
}

private static string GetSubKeyName(Type type) {
  string s = @"CLSID\{" + type.GUID.ToString().ToUpper() + @"}\Programmable";
  return s;
}

Translated to VB.NET, this works out to:

'VB.NET:

<ComRegisterFunctionAttribute()> _
Public Shared Sub RegisterFunction(ByVal type As Type)
    Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type))
End Sub

<ComUnregisterFunctionAttribute()> _
Public Shared Sub UnregisterFunction(ByVal type As Type)
    Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type), false)
End Sub

Private Shared Function GetSubKeyName(ByVal type As Type) As String
    Dim s As String = ("CLSID\{" _
                + (type.GUID.ToString.ToUpper + "}\Programmable"))
    Return s
End Function

The method marked by the ComRegisterFunctionAttribute is automatically called by RegAsm when the assembly for this class is registered. The method marked by the ComUnregisterFunctionAttribute is automatically called by RegAsm when the assembly for this class is being unregistered via the /u switch.

The problem is that the ComRegisterFunctionAttribute and ComUnregisterFunctionAttribute are completely ignored when installing via a Visual Studio Setup Project.

This seems surprising at first, because the Visual Studio Setup Project runs RegAsm using the /regfile switch in order to generate a .REG file containing all of the required registry keys. It is this .REG file that is then utilized then the .MSI package is run at the client site.

From Build and Deploy a .NET COM Assembly by Phil Wilson:

How does Visual Studio work out the COM class registration entries? Well, if you have configured the Fusion Log Viewer (Fuslogvw.exe in the .NET 2.0 SDK) to record assembly loading, run it after the build of your setup and you'll notice that Regasm.exe actually runs during the build of your setup project. However, it doesn't perform any registration. What happens is that Visual Studio runs Regasm with the /regfile option to create a .reg file containing the registry entries required to get the information for step 1, and this .reg file is internally imported into the setup project. So if you want to see what class registration entries Visual Studio will create in the MSI setup, you can run Regasm yourself with the /regfile option

Upon running RegAsm myself using the /regfile switch, however, I noticed that the Programmable switch was not being included. I then put logging within my methods marked by the ComRegisterFunctionAttribute and ComUnregisterFunctionAttribute and found that they are both called when running RegAsm without the /regfile switch, but are not called when run with the /regfile switch, nor are they called when run via the .MSI package created by the Visual Studio Setup Project.

The help files for Regasm.exe confirm this (emphasis added):

You can use the /regfile option to generate a .reg file that contains the registry entries instead of making the changes directly to the registry. You can update the registry on a computer by importing the .reg file with the Registry Editor tool (Regedit.exe). Note that the .reg file does not contain any registry updates that can be made by user-defined register functions.

The solution, then, is to add the Programmable key ourselves. This can be done as follows:

1. Within the Setup Project, open up the Registry Editor. Create a new Key named CLSID under HKEY_CLASSES_ROOT by right-clicking on the HKEY_CLASSES_ROOT folder, then choosing 'New', and then 'Key'.
2. Under the CLSID key, add a new key named for your GUID, including the curly braces.
3. Under the new GUID key you added, add a key named Programmable. You don't need to put any value within this key; however, we do need to force it to be created. Therefore, right-click on the Programmable key and choose 'Properties Window'. Then change the AlwaysCreate property to True.

Once you've done this, you no longer need the methods marked with ComRegisterFunctionAttribute and ComUnregisterFunctionAttribute, but I would still leave them in for those occasions when you intall via RegAsm and not via the Setup Project.

At this point you are ready to deploy. Build your solution and then right click on your Setup Project and choose 'Build'. You can then use the created Setup.exe and .MSI files to deploy to a client machine.

Something else to consider, however, is that when adding the automation add-in via Excel's add-ins dialog box, an error message will be shown stating that "Mscoree.dll cannot be found, would you like to delete the add-in?" or something very similar. This error message can be ignored, and your add-in will run no matter what you answer, but it can be alarming to a client installing your add-in.

This situation, and the explanation of how to solve it, is well described in the article Writing user defined functions for Excel in .NET by Eric Carter.

The problem is that the default value for the InprocServer32 key is simply mscorree.dll, which is sufficient for .NET to find it, but causes Excel to complain. The solution is to make sure that the default value for the InprocServer32 key includes the full path to your system directory. For example, on 32 bit windows, it should read C:\Windows\system32\mscoree.dll. This path needs to vary, however, depending on the system it is installed on. So this path should not be hard-coded.

Eric Carter handles this by modifying the methods marked by the ComRegisterFunctionAttribute and ComUnregisterFunctionAttribute to be the following:

// C#: 

[ComRegisterFunctionAttribute]
public static void RegisterFunction(Type type)
{

  Registry.ClassesRoot.CreateSubKey(
    GetSubKeyName(type, "Programmable"));
  RegistryKey key = Registry.ClassesRoot.OpenSubKey(
    GetSubKeyName(type, "InprocServer32"), true);
  key.SetValue("",
    System.Environment.SystemDirectory + @"\mscoree.dll",
    RegistryValueKind.String);
}

[ComUnregisterFunctionAttribute]
public static void UnregisterFunction(Type type)
{

  Registry.ClassesRoot.DeleteSubKey(
    GetSubKeyName(type, "Programmable"), false);
}

private static string GetSubKeyName(Type type,
  string subKeyName)
{
  System.Text.StringBuilder s =
    new System.Text.StringBuilder();
  s.Append(@"CLSID\{");
  s.Append(type.GUID.ToString().ToUpper());
  s.Append(@"}\");
  s.Append(subKeyName);
  return s.ToString();
}  

Translated to VB.NET, this is equivalent to:

'VB.NET:

<ComRegisterFunctionAttribute()> _
Public Shared Sub RegisterFunction(ByVal type As Type)
    Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type, "Programmable"))
    Dim key As RegistryKey = Registry.ClassesRoot.OpenSubKey(GetSubKeyName(type, "InprocServer32"), true)
    key.SetValue("", (System.Environment.SystemDirectory + "\mscoree.dll"), RegistryValueKind.String)
End Sub

<ComUnregisterFunctionAttribute()> _
Public Shared Sub UnregisterFunction(ByVal type As Type)
    Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type, "Programmable"), false)
End Sub

Private Shared Function GetSubKeyName(ByVal type As Type, ByVal subKeyName As String) As String
    Dim s As System.Text.StringBuilder = New System.Text.StringBuilder
    s.Append ("CLSID\{")
    s.Append(type.GUID.ToString.ToUpper)
    s.Append ("}\")
    s.Append (subKeyName)
    Return s.ToString
End Function

This works, but has the same exact problem where the assembly is properly registered when running RegAsm on the local machine, but fails when attempting to use this within a Visual Studio Setup Project.

The solution, again, is to add our own registry keys. This time, however, we'll have to create a default value that makes use of the [SystemFolder] property, which is equivalent to the System.Environment.SystemDirectory call used within Eric Carter's code, above.

To do this, add a Key named InprocServer32 under your CLSID\\{GUID} key that we created previously. Then right-click on the new InprocServer32 key and choose 'New' then 'String Value'. The result will be a new Value named New Value #1, but you will be in edit mode allowing you to re-name it. What you want to do here is delete all the characters and then hit enter. By deleting all the characters from the name, you are creating a default value and the icon for the registry value will be automatically renamed "(Default)". Then right-click on this Default Value icon and choose 'Properties Window'. Within the properties window, set the Value property to "[SystemFolder]mscoree.dll" (without the quotes).

You can then right-click on your Setup Project and choose 'Build' and then you are ready to deploy.

There is just one last thing to worry about. If you are installing to Excel 2007 or above, the foregoing will work 100%. If you are installing on Excel 2003 or below, however, you will need to include the following:

FIX: Add-ins, smart documents, or smart tags that you create by using Microsoft Visual Studio 2005 do not run in Office

A detailed explaination of how to deploy it is given by Divo here.

If you do not apply this fix, everything will register correctly, and you can even add your automation add-in successfully -- everything seems fine -- but your worksheet functions will fail and you'll still get #NAME? errors as a result. (But, again, you don't need this for Excel 2007 and above.)

So, in the end, the TLB does not matter. In all my testing I used RegAsm witout the /TLB switch and did not include any TLB when registering via the Setup Project. So I had no trouble doing this from Vista, which has issues when attempting to add a TLB file to a Setup Project.

I hope this helps, Hugh, and hopefully anyone else who might stumble onto this thread in the future...

Mike