This is a first post from a series devoted to building xll add-ins for Excel.
Add-ins are used to extend Excel functionality. It is possible to extend Excel using VBA in the same spreadsheet. However this way VBA code is tightly coupled with the spreadsheet, which makes it difficult to keep track of code versions or update code for all copies of a spreadsheet.
Users often make copies of spreadsheets because they want to modify something but want to keep original around. Unfortunately it is never clear what they modify - it can be data, code or both. As a result often there are many slightly different spreadsheets floating around. In this scenario updating your code to fix a bug or add an enhancement if almost impossible.
Add-ins let us separate code from spreadsheet data. This allows us to resolve all problems that we encounter when using spreadsheets with embedded code.
Ways to build an add-in for Excel
There are many ways to build add-ins. Here is a list of of the ways that I have tried (I prefer open source or at least free tools):
- Pros: You just need Excel and don’t need to install any other tool - it includes VBE (Visual Basic Editor). VBE is a decent development environment with integrated debugger.
- Cons: VBA is often quite slow. VBE is not very customizable. The code lives inside add-in, which prevents regular use of source control with it. Source code is available to any user (it is trivial to remove password protection if you set up one).
- Pros: Mature and free environment to develop add-ins using C#. It has very good documentation and active support forum.
- Cons: C# is not available on all platforms (so if your code needs to run from Excel as well as on a server this might be an obstacle). Even though it is possible to call unmanaged dll’s from C# setting up development and debugging process is challenging.
- Pros: Thin wrapper for Excel C API - easy to setup, use, debug. Very fast. Can be used with C++, C# or VB.NET
- Cons: The documentation is not well maintained and not very detailed.
If you are a fan of C# take a close look at Excel-DNA. If you prefer using C/C++ XLW is a better alternative. For my projects I prefer to use C/C++. I create simple C-type dll or static library with core functions. The library can be used in any other environment (Matlab, Mathematica, VBA, etc.). Then I use XLW wrapper to interface the functions with Excel.
In this series of posts I plan to cover the following topics:
- Setting up a project with XLW
- Writing simple functions callable from Excel
- Handling and reporting errors
- Input/output arrays, vectors, matrices or ranges
- Let user interrupt long calculations
- Timing functions
- Adding help to your add-in.
- Deployment tips
- Writing macros.
As I go over these topics I will share the best practices that I converged to over the years of using XLW to write add-ins.