excel programming

Question

I am new to Excel programming.
What are the differences among UDF, Macro, Add-in, Automation Add-in, XLL, VSTO
which one I should use and under some circumstances?

Thanks!

Answer 1

Use VBA for Excel 2003 and earlier (though you could still use it in 2007 if you want).

VSTO is for Excel 2003 or 2007.

The old Excel 4.0 macro language should definitely not be used anymore, they keep disabling parts and the functionality is disappearing. We're having to rewrite all our spreadsheets to get the old macros out.

Macros are also another name for VBA code.

An Add-in is just another Excel workbook that has code in it (like VBA), so that you can use the code in different books, and keep the code directly out of the users book.

An UDF is just a function written in VBA that get's called as a worksheet function in a cell in a spreadsheet (it can also be stored in an Add-in).

Answer 2

Lance has good definitions to your questions. In terms of what to use when, VBA vs VSTO--if you are needing to use .net framework go with VSTO. For instance is I wanted to make a VB.net program interface with a spreadsheet learning VSTO would be the way to go (that and vba does not have an array.sort like vb.net which drives me crazy). However if you are making a nifty spreadsheet that will do some cool things for your end users (formulas, charts etc) just whip up some VBA code within the spreadsheet. It all depends on what you have to do. Best tool for the job.

Answer 3

I'll try to group/oppose some of the elements you mention:

VSTO vs. VBA:
VBA (visual basic for applications) is the "classic" way to write Office automation. Excel has a development interface which you can fire from office to write macros and UDFs, and which hasn't changed for about 10 years. On the plus side, deployment is trivial, and VBA offers nice features like macro recording, which record your actions into code, and provide a good way to figure out the object model.
VSTO came about more recently, and allows you to automate office using .NET (uses COM). You can leverage all .NET and Visual Studio (ex: add WPF forms), which gives great flexibility but the deployment is more complex.
UDF vs. Macro vs. Add-In
User defined functions are custom-made methods which will stay attached to your workbook; once added gain the same status as the "built-in" Excel functions: you can call them from worksheets like =MyFunction()
Macros are procedures which will stay attached to your workbook. They can be called directly by the user, or attached to events (ex: when a worksheet is selected, do this).
Add-ins are not attached to a specific document, but to the application itself. Typically a macro or UDF is attached to the workbook: when you open the document, the code becomes available to you, and when you give the document, the code is copied as well. By contrast, Add-Ins are attached to the application: the moment you launch Excel, the add-in becomes available to you. Tools like the Solver are an Add-in. Note that Add-Ins can be written in either VBA or using VSTO (you can also write UDFs in .NET, but it's atypical).
When to use what
Add-In vs Macro/UDFs: write an add-in if your functionality should be accessible from any workbook.
VSTO or no VSTO: this is a matter of debate. Most people who are familiar with "classic" VBA automation don't like VSTO too much, because the learning curve is a bit steep. If you are used to working with .Net, this should not be a bit issue - but VSTO is somewhat quirky compared to "normal" .Net apps development.
Note also that if you use VSTO, your code can't be edited by the user. This is arguably desirable, but at the same time, Excel power-users usually know how to use VBA and macros, and expect to be able to tweak the code. That can lead to interesting discussions.
Personally, I typically use VSTO for add-ins, and I use it as soon as I see that lots of logic / procedural code is going into macros in VBA. What VSTO gives me is the ability to write testable code, in Visual Studio, and also high-performance code when heavy calculation is involved. The other reason to use VSTO is to leverage WPF to customize office.