HOWTO: Generate Interop assemblies to create an add-in for the VBA editor (32-bit or 64-bit) of Office with Visual Studio .NET.

Author:	Carlos J. Quintero (Microsoft MVP)	Applies to:	Microsoft Visual Studio 2005
Date:	August 2012		Microsoft Visual Studio 2008
			Microsoft Visual Studio 2010
			Microsoft Visual Studio 2012

Introduction

Traditionally add-ins for the 32-bit VBA editor of Microsoft Office were created with Visual Basic 6.0, which can generate 32-bit COM (ActiveX) DLLs. However, the VBA editor of the 64-bit version of Microsoft Office 2010 only supports 64-bit COM add-ins, so it is not possible to create add-ins for that version using Visual Basic 6.0.

It is possible to create an add-in for the 64-bit VBA editor of Office (and for the 32-bit VBA editor) using Visual Studio .NET and the .NET Framework, since .NET dlls can be 32-bit or 64-bit and both can be registered for COM-Interop. The approach is the following (as explained in the article HOWTO: Create an add-in for the VBA editor (32-bit or 64-bit) of Office with Visual Studio .NET):

• Create a .NET Class Library (C# or VB.NET).
• Reference the required extensibility assemblies.
• Register the .NET assembly DLL for COM Interop.
• Register the .NET assembly DLL as add-in for the VBA editor in the Windows registry.

This article explains how to get the required extensibility assemblies to be referenced by the Visual Studio .NET project.

More information

The COM type libraries used by add-ins of the VBA editor of Microsoft Office are the following:

• Microsoft Add-In Designer ("MSADDNDR.DLL")
• OLE Automation ("stdole2.tlb")
• Microsoft Office <version> Object Library ("mso.dll")
• Microsoft Visual Basic for Applications Extensibility 5.3 ("vbe6ext.olb")

Since those are COM (ActiveX) type libraries, to reference them from a .NET Class Library project Interop assemblies are required. An Interop assembly is a .NET assembly that allows a .NET application or library to access a COM (ActiveX) type library (acting as a wrapper).

There are two kind of interop assemblies:

• Interop assemblies generated by anyone using the tlbimp.exe tool of the .NET Framework.
• Primary Interop Assemblies (PIA) generated (and signed with a strong name) by the manufacturer of the COM type library and installed in the Global Assembly Cache (GAC).

Microsoft has provided PIAs for some Office type libraries:

• Microsoft Office 2002 (XP) PIAs
• Microsoft Office 2003 PIAs
• Microsoft Office 2007 PIAs
• Microsoft Office 2010 PIAs

But the approach used here is to generate private Interop assemblies for all those COM type libraries. This is because if the PIAs are not on the target machine, the setup would have to install them in the Global Assembly Cache (GAC) and that requires admin rights, while private interop assemblies can reside on the same folder than the add-in dll, and that folder can be a per-user folder which doesn't required admin rights.

Note: if you use .NET Framework 4.0 or higher, you can embed interop typess and deploying the interop assemblies would not be required. However, if the user doesn't have .NET Framework 4.0 your setup would have to install it. Using .NET Framework 2.0 is much more likely that this step of the setup is not required.

To generate the Interop Assemblies you can use the .bat script below. You will need the to adjust the following names before executing the script:

• The path to the tlbimp.exe tool. The script uses "C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\tlbimp.exe"ot;
• The path to the COM type libraries.
• The path and file name of the .snk of your company to generate strong-named assemblies. The scripts uses "C:\mycompany.snk". Interop assemblies are required to have a strong name because when registering the add-in dll for COM Interop with the regasm.exe tool and the /codebase parameter,  this parameter requires a dll with a strong name, and to generate a dll with a strong name all the references must have a strong name.
• The name of the output assemblies. The script uses the "MyCompany.Interop" prefix.
• The version of the output assemblies, if you want something different to 1.0.0.0.

Note 1: the ^ character at the end of each line is the line-continuation character in .bat script files. It is used for formatting purposes.

Note 2: it is normal to get the following warnings executing the script:

• TlbImp : warning TI0000 : Primary interop assembly 'X' is already registered for type library 'Y'. This is because you are generating Interop assemblies for COM type libraries that already have a PIA registered.
• TlbImp : warning TI0000 : The type library importer could not convert the signature for the member 'X'.
• TlbImp : warning TI0000 : Type library importer encountered a property getter 'X' on type 'Y' without a valid return type. The importer will attempt to import this property as a method instead.

but at the end of each execution you should get a success message and a file with the name MyCompany.Interop.<filename>dll:

         Type library imported to <path>\MyCompany.Interop.<filename>dll

Language: Text

Copy Code (IE only)

@ECHO OFF
"C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\tlbimp.exe" ^
   "C:\Program Files (x86)\Common Files\DESIGNER\MSADDNDR.DLL" ^
   /out:"MyCompany.Interop.Extensibility.dll" ^
   /keyfile:"C:\mycompany.snk" ^
   /strictref:nopia /nologo /asmversion:1.0.0.0 /sysarray

PAUSE
CLS

"C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\tlbimp.exe" ^
   "C:\windows\syswow64\stdole2.tlb" ^
   /out:MyCompany.Interop.Stdole.dll ^
   /keyfile:"C:\mycompany.snk" ^
   /strictref:nopia /nologo /asmversion:1.0.0.0

PAUSE
CLS

"C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\tlbimp.exe" ^
   "C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE12\mso.dll" ^
   /out:MyCompany.Interop.Office12.dll ^
   /keyfile:"C:\mycompany.snk" ^
   /strictref:nopia /nologo /asmversion:1.0.0.0 ^
   /reference:MyCompany.Interop.Stdole.dll

PAUSE
CLS

"C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\tlbimp.exe" ^
   "C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\vbe6ext.olb" ^
   /out:MyCompany.Interop.VBAExtensibility.dll ^
   /keyfile:"C:\mycompany.snk" ^
   /strictref:nopia /nologo /asmversion:1.0.0.0 ^
   /reference:MyCompany.Interop.Office12.dll

PAUSE

Related articles

• HOWTO: Create an add-in for the VBA editor (32-bit or 64-bit) of Office with Visual Studio .NET

Top