0

I'm developing an object-oriented C++ library which I would like to expose some functionality of to users in Excel. Ideally I would like to use the library within Excel VB and provide some higher-level functions that I can expose through the spreadsheet.

I have written a C-API for the C++ library but it's awkward to use as objects require explicit create/dispose calls.

Using the C-API, I have constructed a Python API where create/dispose calls are handled by Python classes. The Python API can be used to build higher level functions and expose them to a user through IPython notebook. The IPython notebook can be configured to load Python modules and specify the path on which the DLL is loaded without admin rights:

import sys sys.path.insert(0,notebook_directory) import mylib mylib.Config_library_path(notebook_directory) #Used for DLL loading by ctypes

this means that I can distribute the notebook as a self contained entity. I need to do the same with an Excel document and some DLLs I distribute with it.

I can write C# bindings around the C-API in the same way I did for Python but I cannot see how to get a C# DLL loaded into Excel without global DLL registration.

I can use the C-API to create an XLL and Declare functions in VB but this means I have to handle memory management within Excel VB.

What options are available for creating a self-contained Excel tool like I did for IPython notebook?

Community
  • 1
  • 1
jbcoe
  • 3,611
  • 1
  • 30
  • 45

1 Answers1

1

You are asking a vague question about a complicated decision tree:

  1. How to expose you C library to Excel. You have two choices, either as worksheet functions and macros, or into the VBA environment.
  2. For worksheet functions, you'd make an xll using one or the many C/C++ toolkits, e.g. XLW or XLL+, or with a .NET wrapper and Excel-DNA, or from Python with PyXLL.
  3. For VBA integration, you must either expose a flat C API which you consume via Declare statements in VBA, or you expose a COM library.
  4. The COM library can be written in C++ or with .NET.
  5. The COM library can be registered and loaded with only user permissions, if you take care of the registration yourself. Excel-DNA does this kind of non-admin registration for COM classes exposed from Excel-DNA add-ins. But this can be done for any COM library.

You should probably do this:

  1. Write VBA Declares that reference your C API directly, even though correct use would be a bit awkward. Use LoadLibrary to make sure the .dlls load relative to the workbook location.
  2. Make some high-level VBA helper classes that wrap these C calls.
  3. Ship this as an .xlam add-in.
Govert
  • 16,387
  • 4
  • 60
  • 70