Accessing functions in a COM DLL in Excel

Dec 13, 2012 at 7:00 AM


I've been using Excel-DNA for about an hour. It's insanely good. 

Regarding COM DLLs, I have a PerlCtrl DLL which wraps Geometry::Formula and exposes most of the methods therein, viz

BOG_TypeLib Interface Definition 

General Information
Library:	BOG_TypeLib (PerlCtrl wrap of Geometry::Formula)
File:	P:\Bruce_Axtens\Internal\BOG\BOG.dll
GUID:	543AE434-0587-42CB-8DE6-87681BF53C11
Version:	1.0

This section lists enumerations exposed by BOG_TypeLib.

This section lists the Classes exposed by BOG_TypeLib.  For each class, the methods and events are listed.



Function Circle(ByVal Formula As String, ByVal Radius As Double) As Double
The circle method provides an area, circumference, and diameter formula

I created a .NET DLL with TLBIMP, viz

Microsoft (R) .NET Framework Type Library to Assembly Converter 2.0.50727.42
Copyright (C) Microsoft Corporation. All rights reserved. Resolved referenced file 'p:\Bruce_Axtens\Internal\bog\BOG.dll' to file 'p:\Bruce_Axtens\Internal\bog\BOG.dll'. Type 'IBOG' imported. Type 'BOGControl' imported. Type library imported to BOG_TypeLib.dll

Then I wrote a simple BOG.dna file, viz

    <DnaLibrary> <ExternalLibrary Path="BOG_Typelib.dll" ComServer="true" />    </DnaLibrary>    

And then loaded the BOG.xll Now, can I get access to the functions of BOG.DLL by simply referring to them in with =function(args) or must I wrap each one? viz

Function Circle(sFunction As String, dRadius As Double) As Double
	Circle = CreateObject("BOG.Axtension.1").Circle( sFunction, dRadius)
End Function
Dec 13, 2012 at 7:47 AM
Edited Dec 13, 2012 at 7:48 AM

Hi Bruce,

External COM libraries won't be exported directly. Excel-DNA looks for static methods, while the COM methods will be instance methods.

You might actually find that your COM wrapper is already what Excel calls an 'Automation Add-In', and can be used without getting Excel-DNA in the way. The only trick is that Excel expects certain registry entries to be present before it will recognize your COM library as a valid 'Automation Add-In'. You can read a bit more, and see what those registry entries are, in a CodeProject article I wrote long ago (it's basically just a 'Programmable' subkey under the CLSID registration). One you add the registry entries, you're done. Automation Add-Ins have some issues, which is why I eventually went the .XLL route with Excel-DNA, but it might work well for you.

So if you do to go with Excel-DNA and grow it into a full Excel add-in, you'll have to make the wrappers. Depending on your .NET skills, you might write code that generates them and registers via ExcelIntegration.RegisterMethods.