CODEWITHSUNDEEP
excel
Zach Smith
Zach Smith

Reputation: 8961

When would you choose Excel COM over Excel's C-API for customizing Excel functionality?

Looking at Microsoft's overview on integrating with Excel HERE:

They list the C-API, VBA and COM as separate APIs. The web-page has two statements that are seemingly contradictory:

-- ONE

C API and XLLs: DLLs that are integrated with Excel. These DLLs provide the most direct and fastest interface for the addition of high-performance worksheet functions, although at the cost of some complexity compared with later technologies.

-- TWO

Since the introduction of Visual Basic for Applications (VBA) sheets in Excel version 5, and the Visual Basic Editor (VBE) in version 8 (Excel 97), the easiest way for users to customize Excel is to use VBA instead of XLM. Consequently, much of the new functionality introduced in later versions of Excel is available through VBA, but not through XLM or the C API. For example, several commands, event traps, and enhanced dialog box capabilities are available through VBA, but not through XLM or the C API.

On the one hand the C-API seems like the preferred method of Excel integration for those with a technical toolset (because it is significantly more performant). But on the other hand it seems like a wrapper for the now-defunct XLM method of extending Excel that has the advantage of being performant if that is actually a requirement.

In general my goal of interacting with Excel is to scrape values from cells and send to a server, and then adding values to cell-ranges. Such functionality will be mostly based on commands from a customized Ribbon.

I think this will also involve accessing cell properties, locking and unlocking ranges, and general interaction with sheets.

Questions:

  1. Are all VBA interactions with Excel done by COM?
  2. Does VBA have access to all of Excel's functions the way that the C-API does?
  3. Why would the C-API not include access to Excel's COM?

Since there are tools available that allow for both COM and C-API access (i.e. ExcelDNA), both COM and and C-API utilization are possible.

Upvotes: 2

Views: 1326

Answers (1)

Govert
Govert

Reputation: 16907

  1. VBA is more tightly integrated in Excel that just using the COM interfaces. E.g. VBA allows the creation of user-defined functions, and these functions have a scope related to the Workbook containing the VBA code. You couldn't re-implement the VBA integration using only the public Excel COM interfaces. VBA has access to the full COM object model.

  2. You can call all the worksheet functions from your VBA code, and you can call all the worksheet functions via the C API. But, as your quote indicates, there is other functionality of Excel that is not available through the C API, but only through the COM object model (which VBA has access to).

  3. The C API is a one binary glue mechanism between code components, COM is another binary glue mechanism between code components. Excel has chosen to expose different sets of functionality via these two binary glue mechanisms. From this perspective your question doesn't really make sense.

An Excel add-in developed with the C API (an .xll) can also use the COM object model (with some effort). It would be harder for a COM add-in to use the C API, dues to peculiarities of the C API add-in initialization.

Maybe to highlight the practical importance of the two quotes you start with:

Suppose you are making an add-in for Excel, not using VBA. This might be the case is you want to use another language (Python, .NET, C++ etc.). Then you have two integration mechanisms, the C API (.xll) and the COM add-in model. Of these two, only the C API allows you to make high-performance user-defined functions that can be called from the worksheet. However, the COM object model allows better manipulation in a macro context, and exposes extra events that you can see directly through the C API. So there is a trade-off.

Frameworks like Excel-DNA (for making .NET-based add-ins) allow you to build an add-in that integrates using the C API, but also allows you to talk to the COM object model.

To confuse things more, there is a third add-in mechanism, which is the only one currently being developed further by Microsoft. This is the Javascript-based "Apps for Office". These are more limited in their feature set than the other two add-in add-in mechanisms - they don't allow you to make user-defined functions, you have a limited object model to interact with and it's slow. But the Javascript-based add-ins have one big advantage - they run on the various non-Windows Excel platforms - the web-based Excel, the iOS and Android Excel versions as well as Mac (and of Windows).

Upvotes: 6

Related Questions