In this post, I want to explain how I created a dll in C#/.NET using Microsoft Visual Studio 2008 that could be accessed from an Excel VBA program.
In order for VBA to access it, a dll has to be COM-visible. See Wikipedia; Component Object Model for more details. However, Microsoft's description at Exposing .NET Framework Components to COM is quite hard to understand. I hope this step-by-step explanation of an actual example will be easier to follow.
Summary
I created a dll, called MdJLibrary.dll, that defines a function WidthInPoints in the class TextFunctions. This function takes three parameters - a string, a font name and a font size - and returns the width of that string in points. When built and deployed, the function in this dll can be accessed from Excel VBA.
Step 1. Create the dll in C#/.NET
A. Create a new project
In Microsoft Visual 2008, select File, then New and Project. Choose the Class Library template. Set Name to MdJLibrary. Click OK.
B. Define the required function
Here is the initial code. Note that neither the class nor the method can be specified as static (since, as we will see later, the class will have to inherit an interface). Also, the method has to be public so that it can later be exposed to COM.
using System;
using System.Drawing;
namespace MdJLibrary
{
/// <summary>
/// Class defining functions on text
/// </summary>
public class TextFunctions
{
/// <summary>
/// Measure the width of a given string when drawn in a font of a given size
/// </summary>
/// <param name="text">string to measure</param>
/// <param name="fontName">name of font to use</param>
/// <param name="fontSize">em-size of font (points)</param>
/// <returns>width of string in points</returns>
public int WidthInPoints(string text, string fontName, Single fontSize)
{
//create a temporary Graphics object and set the measurement units to points
Graphics g = Graphics.FromImage(new Bitmap(1, 1));
g.PageUnit = GraphicsUnit.Point;
//create a Font object from the given name and size
Font f = new Font(fontName, fontSize);
//measure the given string when drawn with this Font
SizeF stringSize = g.MeasureString(text, f);
//extract and return the width (rounded to nearest integer)
return Convert.ToInt32(stringSize.Width);
}
}
}
C. Sign it with a strong name
This avoids versioning problems, and increases security. All shared dlls should be signed, even if they're not going to be deployed to the Global Assembly Cache.
Open the project properties (select Project then Properties...), and select the Signing tab. Click Sign the assembly and either create or choose an existing strong name key file.
Step 2. Test it
Create a separate, small .NET program to test the dll.
I haven’t included the test program listing here. The key thing is that the solution needs to include an explicit reference to the new library.
Step 3. Make the dll COM-visible
There are several stages to this.
A. Provide an explicit interface
It is possible to generate an interface by using the ClassInterfaceAttribute to automatically expose the public methods, etc., but the recommended way is to define an explicit interface. The type of the ClassInterfaceAttribute is then set to None, so as to stop this automatic class interface being produced.
The ClassInterfaceAttribute is defined in the System.Runtime.InteropServices namespace, so add this to the list of using directives:
using System.Runtime.InteropServices;
The interface definition has to contain an exact copy of the WidthInPoints method signature, without any access modifiers:
public interface ITextFunctions
{
int WidthInPoints(string text, string fontName, Single fontSize);
}
The class definition now needs to be modified to show that it is implementing this interface, with the ClassInterfaceAttribute as described.
[ClassInterface(ClassInterfaceType.None)]
public class TextFunctions : ITextFunctions
{
...
B. Define a default constructor
Define a default constructor for the class, so that COM clients can create objects.
Microsoft Visual Studio 2008 creates public default constructors automatically, but I prefer to make this explicit:
//default constructor
public TextFunctions() { }
C. Mark the methods to be made COM-visible
Perhaps the easiest way of doing this is to make the whole assembly COM-visible with the ComVisibleAttribute: In the project properties, on the Application tab, click Assembly Information... and check Make assembly Com-visible. Note that this changes the ComVisible setting in the AssemblyInfo.cs file (under Properties in Solution Explorer) to true:
[assembly: ComVisible(true)]
If there are any methods that you don't want to make COM-visible you they need to be marked explicitly as [ComVisible(false)].
However, the recommended alternative is to leave the assembly setting unchanged and to explicitly add [ComVisible(true)] to each class and method that you want to expose as COM-visible:
[ComVisible(true)]
[ClassInterface(ClassInterfaceType.None)]
public class TextFunctions : ITextFunctions
{
...
[ComVisible(true)]
public int WidthInPoints(string text, string fontName, Single fontSize)
{
...
D. Identify the dll with a unique class id
Microsoft Visual Studio 2008 does this automatically by generating a unique GUID for the assembly. This can be seen in AssemblyInfo.cs:
[assembly: Guid("xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx")]
Here is the finished code:
using System;
using System.Drawing;
using System.Runtime.InteropServices;
namespace MdJLibrary
{
public interface ITextFunctions
{
int WidthInPoints(string text, string fontName, Single fontSize);
}
/// <summary>
/// Class defining functions on text
/// </summary>
[ComVisible(true)]
[ClassInterface(ClassInterfaceType.None)]
public class TextFunctions : ITextFunctions
{
//default constructor
public TextFunctions() { }
/// <summary>
/// Measure the width of a given string when drawn in a font of a given size
/// </summary>
/// <param name="text">string to measure</param>
/// <param name="fontName">name of font to use</param>
/// <param name="fontSize">em-size of font (points)</param>
/// <returns>width of string in points</returns>
[ComVisible(true)]
public int WidthInPoints(string text, string fontName, Single fontSize)
{
//create a temporary Graphics object and set the measurement units to points
Graphics g = Graphics.FromImage(new Bitmap(1, 1));
g.PageUnit = GraphicsUnit.Point;
//create a Font object from the given name and size
Font f = new Font(fontName, fontSize);
//measure the given string when drawn with this Font
SizeF stringSize = g.MeasureString(text, f);
//extract and return the width (rounded to nearest integer)
return Convert.ToInt32(stringSize.Width);
}
}
}
Step 4. Package and deploy the assembly
A. Register the component for COM interoperation
In the project properties, on the Build tab, check the Register for COM interop option.
B. Deploy the dll
A convenient way of doing this is to use the assembly registration tool, regasm.
On the Build Events tab, add the following line to the post-build event:
%SystemRoot%\Microsoft.NET\Framework\v2.0.50727\regasm $(TargetFileName) /tlb:$(TargetName).lib
%SystemRoot%\Microsoft.NET\Framework\v2.0.50727 is the default installation path for regasm in v2.0 of the .NET Framework, which is the latest version available.
regasm registers all the public classes contained in the dll, and generates and registers the type library (.lib). You can use the /reglib option to generate a .reg file that contains the registry entries instead of making the changes directly to the registry.
Step 5. Use the dll
To complete the picture, here's how to use the functions provided by the dll in Excel.
A. Add a reference to the dll
In the Visual Basic Editor window, select Tools then References....
The new library name should appear in the Available References list. Select it and click OK.
B. Create an object of the required type and call the function
In a VBA module, call CreateObject to create and return a reference to a new instance of the class defined in the dll, and then call the appropriate method. In this case:
Dim obj As Object
Dim width As Integer
Set obj = CreateObject("MdJLibrary.TextFunctions")
width = obj.WidthInPoints("Mike", "Arial", 10)
