Selecting the right tool for the Toolbox

The XL Toolbox is written in Visual Basic for Applications (VBA), the programming language that is built into the Office applications. When I started to create “Daniel’s Functions”, the predecessor of the XL Toolbox, using VBA was the logical choice: It came with Excel for free, and BASIC is very easy to learn. In the early days, I wrote computer programs in BASIC on the Commodore C64, and later moved on to Turbo Pascal on an IBM PC. Thus, the learning curve for VBA was not very steep.

Limitations of VBA

As the XL Toolbox grew bigger, I encountered several limitations of VBA. First of all, since the source code is contained in an Excel addin file, I had to constantly export the code from the .XLAM version (Excel 2007 and later) and import it into the .XLA version (Excel 2000-2003). To facilitate the process, I wrote the Excel VBA Developer Tools.

Still, keeping the code up to date in two different files is cumbersome. However, I always wanted to provide the XL Toolbox for all versions of Excel that I encountered in my work life myself. Even today, at the end of the year 2013, Excel 2003 is still widely used. It remains to be seen if Microsoft’s termination of support for Windows XP next year will change this situation. My guess is that there will still be many people who use Windows XP with Office 2003, as both products are very stable and leave little to desire from the perspective of an end user who works with them.

Another problem came up when more and more people started to use 64-bit versions of Windows and/or Excel. The XL Toolbox makes extensive use of the Win32 API by calling functions included with Windows DLLs. For example, the entire graphic export feature could not work without these Win32 API calls. (This is also one important reason why there is no XL Toolbox for Mac.)

Long-time users of the XL Toolbox will know that I took me many releases and bug-fixes to finally be able to call the XL Toolbox fully 64-bit compatible.

Finally, VBA is a ‘no-frills’ programming environment. Very often during development, one is forced to reinvent the wheel. VBA’s object orientation is limited. All in all, I find myself spending too much time on ensuring compatibility and writing code for tasks that other programming languages and frameworks come included with.

.NET and Visual Studio Tools for Office

The natural choice for a modern programming framework to develop Office applications is Microsoft’s .NET platform and a set of extensions called ‘Visual Studio Tools for Office’ (VSTO). In recent iterations of Visual Studio, Microsoft’s integrated development environment (IDE), VSTO is included with the more expensive editions that are called ‘Professional’. Visual Studio Professional 2012 costs about 615 Euros.

Using Visual Studio with VSTO has one major disadvantage though: It is not easily feasible to produce one addin that works with all versions of Excel from 2000 through 2013. Addins produced wit Visual Studio 2010 and later are compatible with Office 2007 through 2013, but cannot be used with Excel 2000 or 2003.

An absolute requirement for the XL Toolbox is the ability to install the addin without administrator rights. I used an evaluation version of Visual Studio Professional 2013 to produce a very basic Excel addin and package it in a Windows installer file. As expected, this file did not install without administrator rights.

Apparently there are ways to install Office addins based on .NET using the fabulous Inno Setup, which is my preferred installer.

The alternative: Addin Express

Addin Express is a commercial solution to develop Office applications. It costs much less than Visual Studio Professional: 249 Euros. Addin Express requires Visual Studio, but the company says that the free ‘Express’ version suffices.

One of the big advantages of Addin Express is that it can produce addins for all versions of Excel from a single code base, including the ability to create either the classic menus and toolbars, or the modern ‘Ribbon’ user interface. The documentation can be downloaded for free, and it is very extensive.

The disadvantage of course it the dependency on a third party, even though one may obtain the source code for the Addin Express runtime if one chooses to pay for it.

My choice: Visual Studio Tools for Office

I am currently inclined to go ahead and purchase a licence for Visual Studio Professional, including VSTO. The main reason for this was to be able to use ‘pure’ Microsoft, without dependency on a commercial third party (I will probably still depend on third-party libraries such as FreeImage).

This decision would imply that there will be no further development of the XL Toolbox for Excel 2003. However, even though this workhorse does a very good job, it will soon be history when Microsoft ends its support for Windows XP and Office 2003. The old XL Toolbox (version 6.50) will remain available for those users who hold on to Excel 2003. The new versions however will require one of Excel 2007, 2010, or 2013, as well as the .NET framework 4.5 (available for free).

The new XL Toolbox will be called Daniel’s XL Toolbox NG, where ‘NG’ stands for ‘new generation’ or ‘next generation’. It will use semantic versioning and continue with version 7.0.0.

It will take a long time to produce the first complete version of the XL Toolbox NG. Currently I spend the little time that I can spare for this project reading up on .NET and the Windows Presentation Foundation (WPF). These are very complex topics.

I have decided to release developmental ‘alpha’ versions of the XL Toolbox NG, so users can benefit from new features as they are added over time. The old and the new Toolbox will be able to coexist on a given system.