VBA VS. VSTO what is the difference, should I upgrade?

excel vba ide vsto
user2155507 picture user2155507 · May 27, 2013 · Viewed 11.6k times · Source

I am a huge fan of the KISS principle (Keep it simple) and a long time VBA for excel user. However all the articles I can find on VBA vs. VSTO (Visual Studio Tools for Office) seem very complicated.

Firstly, I am sick of the very poor IDE built into Excel and I am looking for a new one. VSTO using Visual Studio as the IDE seems to be the only alternative.

So I seek to understand what is the difference between VBA and VSTO to know if it is worth me learning to use VSTO?

Answer

Charles Williams picture Charles Williams · May 28, 2013

Which toolset you use is largely a matter of personal preference, but here are some things to consider:
1. Performance: For most Excel-related tasks VSTO performs very slowly compared to VBA because of the additional Interop layer.
2. UDFs: VSTO does not support UDFs
3. Object Model: Its harder to work with the Excel object model with VSTO than with VBA because there is no .NET Macro recorder to give you hints.
4. IDE: With VSTO you get access to the latest Visual Studio IDE (assuming you have a Visual Studio license)
5. .Net Framework: VSTO gives you access to the very rich and powerful .NET framework
6. Learning curve: VB.NET is a different language to VBA and the .NET framework and object model differences mean that the transition is not as simple as it could be.
7. If you want to go the visual studio/.NET route I would recommend you look at Addin Express or XLDNA rather than VSTO: both of these have less limitations than VSTO

Related questions

Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
How do I declare a global variable in VBA?

I wrote the following code: Function find_results_idle() Public iRaw As Integer Public iColumn As Integer iRaw = 1 iColumn = 1 And I get the error message: "invalid attribute in Sub or Function" Do you know what I did wrong? I tried …

Read More
How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

How can I use regular expressions in Excel and take advantage of Excel's powerful grid-like setup for data manipulation? In-cell function to return a matched pattern or replaced value in a string. Sub to loop through a column of data …

Read More