When we talk about which language to use we often, if not always get talking about the technical advantages of a given tool, XLL’s are faster than VBA, VBA is easier to deploy than VSTO, VSTO is more secure than VBA, and so on and so on. Simon has some information here and I have some .Net stuff here. But one aspect that’s over looked is what each offers the developer.
Let’s consider an example. I’m writing a adding for the users in my department, it needs to contain some UDF’s, apply a custom formatting option to a selected range, help import a strangely formatted XML file, and link to a database and pull back some data. What are you going to write that in?
Well from a technical point of view you might like to used an XLL for the UDF’s, a Com adding for the formatting and database stuff and make use of some .Net classes for the XML processing. Nice!
For 90 percent of us, we will solve every problem using VBA, simply because that’s the only tool we have. But what if?
Here’s what I think different technologies offer the developer, not the technological advantage, it the things that make the developer’s live more enjoyable!
VBA: For simple stuff it’s quickest. As things get more complex and or the difficulty of what you are trying to do gets harder VBA becomes more of an effort (think of userform stuff, VBA, vs .Net?!). If you need something that needs constant changes, or something that you expect to grow in size over a period of time it might be worth doing it VBA.
VB6 (Com addins): If the UI you need is a bit more complex, than it might be time to switch to VB6, the more powerful forms and use of active X components means that you can have more choice when designing them. Also from a deployment POV, you have to worry less about the thing being installed on the PC, as you can compile them into the DLL
As the size and complexity of the project increases, switching to VB6 can be a help. Functionality can be complied in to different parts, external libraries can be used and packaged easily, source code can be shared with other developers – although this is rare for Excel projects.
Another good reason for a developer to pick VB6 com adding over VBA ones is that, although generally VB6 code can be changed with little effort to run in VBA, there are times when VBA does not quite have the same feature set, and dropping some code straight in to a VB6 com addin is just easier than messing around trying to convert it to VBA.
VSTO: Well its IDE isn’t 100 years old, thats a big plus! But the most compelling reason why an Excel dev would want to use VSTO, would be to make use of the framework. Forms are incredibly powerful in .Net (form namespace), but so too is all the functionality that the framework provided, like the threading name space, or the services name space. When we start to try and do really grand stuff, .Net is brilliant.
C/C++ [XLL]: Purely from an easy of development POV, I can only really think of 2 things XLL would over. One would be a situation where you need to deploy is a “hostile” environment, so the extra work in the coding would be repaid in the effort saved deploying. The other would be if there is a C library with some complex functions in it, a bit like with the VB/VBA issue, it might just be easier to keep the C code and wrap it up in a XLL.
VSTA: What's VSTA?!?!
So there you go that’s my take on it, disagree?
