The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Excel Add-In Development - simple DLL or COM DLL?

Hello,

I am developing an Excel add-in which will make some new functions available in VBA. The add-in will be distributed and deployed as a separate product. The dev. environment is Borland C++ Builder.

I know it's possible to use functions residing in a DLL by referencing them using the "Declare" statement in VB, though I feel this is not a user friendly way to do things.

It appears that it's also possible to use COM DLLs by referencing them in VBA through the main menu (Tools/References).

Which one of these two methods is more preferable, from the end users' perspective?

Thank you.
Takito
Sunday, July 29, 2007
 
 
Definitely COM... that's really what they made it for.
Bob
Sunday, July 29, 2007
 
 
COM. It is easier to use and understand as Excel will show intellisense in the IDE. I would say that most non expert users will shy away from a Declare (API calls...) but won't bat an eye at using a COM component because it behaves exactly like all the "native" Excel objects. COM is harder to develop (at least in C++) so you could consider using something like VB6 to create the COM component which then farms out all the calls to a C++ DLL.
CodeForNothing Send private email
Sunday, July 29, 2007
 
 
Thanks, I was thinking about COM too. The only thing I still don't understand is whether I have to implement some specific Excel interface or just a custom IUnknown descendant interface?
Takito
Sunday, July 29, 2007
 
 
Why not do both?
Write your stuff in a native dll.
Provide either an xll wrapper to register your funcs with Excel, or leave as raw dll.
Provide a COM wrapper or a COM add-in wrapper.
As long as you provide your VBA users with decent VBA modules with all the delcares in and egs of COM objects I don't think it will matter from a user POV.
From a performance POV (may not be an issue for what you are doing) xll will be faster. heres some stuff about performance:
http://www.codematic.net/Excel-development/Excel-vba/Excel-VBA.htm
cheers
Simon Send private email
Sunday, July 29, 2007
 
 
Takito -- you want to derive it from IDispatch, not IUnknown.

Simon -- why complicate it? Simple is better.
Bill
Sunday, July 29, 2007
 
 
I would make it Managed (CLR). Use a .NET language and COM interop (Com Callable Wrappers) for your dll. Productivity is better for CLR code than native COM in C++. If you know C++ learning C# is no biggy. Or you can try Common Language Integration (CLI) in Visual C++.
Marc Jacobi Send private email
Monday, July 30, 2007
 
 
I've already got the code written in C/C++ and only need to develop a wrapper. This is going to be a DLL which will include XLL-related stuff (for use in spreadsheets) as well as COM stuff (for use in VBA).
Takito
Monday, July 30, 2007
 
 
+1 COM.

There are some very good VBA developers, but there are even more (1000X ??) that have never made API calls and have no idea what a Declare statement is.
Nicholas Hebb Send private email
Monday, July 30, 2007
 
 
Essbase uses xlls. they provide the declares (a .bas), VBA devs just code as normal calling the declared functions. works well.
I don't think not having a COM interface has done them any harm.
If your funcs are registered with Excel you can also call them in VBA via app.run. ( ie no need for declares)
By all means add a COM interface, I would have thought any VBA dev who can set a ref and navigate an OM can probably import a set of declares, or use app.run, but I could be wrong.
cheers
Simon Send private email
Monday, July 30, 2007
 
 
Simon's got a good point with the Essbase example.

On one hand, maybe I've become cynical after reading postings on various Excel programming boards. But on the other, you'll want to ensure that your software appeals to as broad of a group of VBA programmers as possible. So it's worth jumping through a few extra hoops to make things easier for the lowest common denominator.
Nicholas Hebb Send private email
Tuesday, July 31, 2007
 
 
Depending on what sorts of functionality the library will provide, writing it for COM will also allow it to be reused with other software.

Ignoring that, you get the COM licensing model if required and the ability to export enums.

COM is still the "surface streets" of Windows, even if it isn't the highways and plumbing.  .Net is more like electrical wiring stapled to walls and baseboards in an old building.
Codger
Tuesday, July 31, 2007
 
 
Thanks for your help, I will stick with COM.
Takito
Tuesday, July 31, 2007
 
 
Marc: "I would make it Managed (CLR)."

--CLR/.NET. Why would you add the overhead of:

a) Ensuring that the proper version of .NET is installed, and installing it if not.

b) Guarantee yourself conflicts because only one version of .NET can be loaded in an application at one time, meaning that if someone else provides an Excel add-in via CLR and uses .NET 1.1.x.y, and yours requires .NET 1.1.x.z (or even worse, .NET 2.x.x.x), your add-in won't load, or vice versa - your app requires .NET 1.x and loads first, and the other one appears to be buggy because it can't work with 1.x, but requires 2.x.

c) Add the overhead of loading the .NET Framework to the already huge overhead of Excel.

d) Limit yourself to only the last couple of versions of Excel, since earlier versions won't work with .CLR add-ins (unless you also provide a COM wrapper for these versions, which means you're now providing both .CLR and COM add-ins, which sorta defeats the purpose of using the .CLR in the first place based on your reasons).

In other words, choose the right tool for the right job. Excel provides two mechanisms already; don't muddle things up by adding the wrong third option to the mix.
Ken White Send private email
Wednesday, August 01, 2007
 
 
Forgot to mention: The reasons I provided concerning multiple apps needing different versions of the .NET Framework is the *documented* reason that MS says to not use .NET when developing Explorer add-ins.
Ken White Send private email
Wednesday, August 01, 2007
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz