In this installment of Roll Your own Analysis Office Features, we’ll compare the two main options for building applications in MS Excel (and by extension, Analysis Office) and their relative advantages and disadvantages. Both allow you to enhance MS Office and provide access to its object model. With both, you can also access the Analysis Office VBA API to retrieve information from data sources, manipulate them or execute planning sequences/functions.
Visual Basic for Applications (VBA)
This is the classic “macro” scripting language for MS Office. If you have ever written a macro for MS Office, or even used the record macro function, you have created a VBA script. Aside from being based on a long obsolete programming language (VB6), it offers the advantage of being simple and easy to learn. It is also ubiquitous on the MS Office platform, only being missing from Office 2008 on the Mac. Its main use case is writing scripts from within MS Office. VBA macros reside inside the Workbook as content and are shared as an integral part of the workbook.
Advantages of using VBA –
- Short learning curve and Macro Recorder – VBA is a relatively simple language and you can get started with it quickly. Moreover, it has the macro recorder. This tool is wonderful for the “I’d like to do X, but don’t know how” situation. You simply start recording a macro, do what it is you want to do via macro and then stop recording. Your code may be ugly. It may not be the best way of doing something, but it works and you can often use this to quickly figure out how to perform certain operations with the Office object model.
- „No Installer“ Workbooks – While you do have to save your workbook in a macro enabled format, there user does not need to install any extra plugins to use your features.
- Works on both Windows and MacOS – Unless you are using Office 2008 on the Mac. VSTO only works on Windows.
- Analysis Office has an extensive API of VBA commands.
Disadvantages of using VBA –
- Lack of many modern language features and lack of libraries – VBA has no concept of classes or really any support for patterns more recent than about 1985. It only got the ability to fill arrays when declaring them in Office 2010. Also, if you need an advanced library for something, you are probably looking at writing it yourself. For simple tasks, this is a non-issue, but if you want to generate a new column containing the average length of the day for your sales region, by month and then do a linear regression analysis to see if this affects your sales; then VBA might not be the best way to do this.
- Federated deployment and maintenance – The downside of everything being contained in the workbook is that if you set out to make an update or fix a bug, then you have to track down every copy of that workbook in existence and make the changes manually.
Visual Studio Tools for Office (VSTO)
VSTO is a .NET framework for automating and extending MS Office applications by creating plugins. It gives the developer access to more modern and powerful programming languages, such as VB .NET (a more modern variant if Visual Basic) and Miscrsoft’s flagship programming language, C#. There are three basic approaches to VSTO extensions. They can be a plugin, which is always loaded when Excel is started, they can be a plugin which is loaded when a specific document is opened, or they can be a plugin that is loaded when a specific template is used.
Advantages of using VSTO -
- VSTO is developed in Visual Studio, which is among the most powerful and well-loved IDEs around.
- Use of VB.Net and C# languages; and their more modern features.
- Centralized deployment and maintenance – If you are using a centrally deployed dll for your plugin, you can roll out new features or bugfixes with relative ease.
- More refinement possible in the end product – You simply have a lot more control over the user’s experience with VSTO than you do with VBA.
Disadvantages of using VSTO -
- Installer Required – Unlike the “packaged as content” approach of VBA, VSTO plugins require the use of an installer.
- More complex development – There is extra frontloaded complexity when developing using VSTO instead of VBA.
- Analysis Office does not expose any .Net libraries. It is not a problem to access the VBA API commands via Application.Run(), but if you are accessing the Analysis API frequently in your extensions and wish to have clean source code, you may want to create a wrapper library.
So which should you use?
This depends of course. If your code is small, you can execute it by responding to an event (either a standard MS Excel event, or Analysis Office's CallbackAfterRedisplay event) and there is a single or limited number of points of access (i.e. there are not many copies of the workbook floating around), then VBA is the right approach. If you are dependent on CallbackAfterRedisplay , then you should use VBA in any case. For example, we could have taken the FocusAutoRefresh macro from the last installment and wrapped it into Excel's Worksheet.Change event to automate the focusing of the auto refresh rules.
If your code is large and ambitious, VBA may become unwieldy and you might want to consider a VSTO language. If you can't simply silently react on events and have to add a UI component, you should also consider VSTO. This will give you the tools that you need to make a refined UI, including your own ribbon. The techniques of yesteryear - in-sheet buttons, Active-x controls and macros from the macro list - will be regarded as hackish and barbaric by today's users. Lastly, if you need to centrally maintain a distributed network of workbooks, VSTO is the right choice.
Next time, we'll build a simple VSTO app which includes a custom ribbon.
