I've been tasked with creating a financial planning tool in Excel that would benefit from some custom functions/macros.
My initial reaction was to use VBA. I've used it to drive Excel before (say 5 years ago). But I then began to wonder if I would be better off using VSTO.
Has anyone has experience using both techs and can list the pros and cons so that I can evaluate which course would be best.
I don't think that there is anything inherently wrong with coding this in VBA within Excel. It has the advantage of running in excel's process space and the VBA interaction with Excels object model is very simple. However you (and your users) need to really think of your tool as an Excel add-in.
If you want your tool to have a separate identity than that of Excel, then Automation is your option. It's a little slower (you are "out of process") and the object model is marginally less clean, but your tool will have it's own identity.
(note if you use C# and if you have the option use VS2010 and C#4 there are a bunch of automation enhancements that are worth the upgrade)
I would suggest that you stick with VBA for your standard development with Excel, and learn .NET on this side. Using .NET is definitely the next step, but it makes your Excel development much harder.
Further, VSTO does not enable the creating of user defined worksheet functions ("UDFs"), so you would need a VBA front-end, or create a managed COM add-in without using VSTO, in order to do this. By comparison, VBA allows you to create UDFs with virtually no effort.
Using .NET has many advantages, mostly regarding strong-typing, full OOP capabilities, and the ability to organize larger-sized projects. but VBA has enormous advantages over .NET when it comes to deployment, which is quite complicated with Excel when dealing with .NET or VSTO. VBA is also an easier language to learn and start out with.
Overall, I would suggest that you use VBA for your day-to-day development, but learn VB.NET or C# on the side so that your programming skills can grow outside of the Excel arena. Eventually, your .NET skills can become strong enough so that you will prefer to use it over VBA, but you will have to become pretty good at .NET for that day to come.
(For another similar opinion on this, see Do I lose the benefits of macro recording if I develop Excel apps in Visual Studio?.)
Edit: Update regarding Andy's comment, below:
Issues like deployment, debugging and UDFs were ones that I was looking for comparison information on. Judging by the responses to the question I should have mentioned that I have 5+ years experience with C#, whereas my VBA skills (or lack thereof) only come out 3 or 4 times a decade
Ok, yes, you should have said! Most people with questions like this are VBA programmers who are looking to get into .NET. So I misunderstood.
In your case, you should use C#, but I would strongly suggest using C# 4.0 on Visual Studio 2010 for this, it will greatly improve the syntax that is required when operating against a COM object model such as Excel. VS 2010 is currently in beta 2 and the RTM date is set for April 12th, so we are almost there.
As for deployment, with your experience I don't think you'll have too much trouble with setup packages or the like and Visual Studio Tools for Office (VSTO) is excptionally good for two things:
Creating a custom ribbon arrangement for your add-in via a drag and drop designer. Without the drag-and-drop designer you have to provide XML instead. XML is just fine if you ask me, but the drag-and-drop designer really is a dream to use
Utilizing .NET controls on a worksheet. I don't know if this is part of what you plan on doing, but VSTO enables .NET controls to be used on the worksheet. This is a really nice capability for a .NET programmer since these controls are a bit smoother looking and are specifically designed to work with .NET.
Unfortunately, VSTO is only available for Excel 2003 and above and I think you have to create separate add-ins for Excel 2003 and Excel 2007. Managed COM add-ins made without using VSTO, on the other hand, can be made compatible for Excel 2000 and above with no difficulty. Lastly, VSTO does not support the creation of UDFs and, therefore, you'd have to either create a managed automation add-in for that or utilize a VBA front-end which calls your VSTO functions.
Overall, I would go with VSTO if you can limit yourself to Excel 2007 and above. I would consider VSTO if your requirements are for Excel 2003 and above. And I would go with a managed COM add-in if you need to be able to run on Excel versions 2000 and above.
For UDF support, I would create a managed automation add-in, which would be viable for Excel 2002 and above. If you need UDFs on Excel 2000 or below then you would need a VBA front end which calls COM-visible methods in your .NET assembly.
These are the basic pro's and con's, as I see it. Let me know if if you need to know more.
-- Mike
SpreadsheetGear for .NET lets you add an Excel compatible spreadsheet component to your .NET (WinForms, ASP.NET, etc...) applications without the downsides (performance, ease of use) of COM Interop.
You can learn more about the SpreadsheetGear Windows Forms spreadsheet control here, see live ASP.NET samples here and download the free trial here if you want to try it yourself.
Disclaimer: I own SpreadsheetGear LLC