Over the next few weeks/months, I’m aiming to do a few post about Excel DNA. Excel DNA is a technology that allows dot Net code to be used with Excel, via a C XLL proxy. I hope to show how to start using this wonderful product, how easy it is, how much fun it is, and how cool dot Net stuff is. But before we delve deep into Excel DNA, I wanted to take a step back and ask a more fundamental question.

Why the heck should anyone programming Excel bother with anything that’s not VBA, and if not VBA, then why dot Net?

These are fundamental questions, right?

Why look beyond VBA?
I love VBA, I think it’s a truly brilliant technology, from conception to implementation. If you add API’s and .DLL to pure VBA, then there’s nothing it can’t accomplish. However, at this level it can start to get a bit tricky.  Solutions become messy, difficult to maintain, or time consuming to write in the first place. Still there’s often no need to use something else, so why bother?

This is a debate you could have ad infinitum, but there are some serious reason to start thinking about technologies* beyond VBA.

1. It’ aging.
OK, it’s aging very gracefully but there’s no getting away from the fact it’s an old technology base. Maybe not a huge issue on the face of it but there are something’s to think about. No-one knows when or if MS will “kill off” VBA, it looks unlikely to happen anytime soon, and if and when it does, it wont happen over night, but it will happen at some point.
The IDE, has not been updated in 10 years, and it’s really starting to show its age. As a developer, especially as a RAD developer, you should be concerned with this. Improvements in IDE’s have made development faster, more enjoyable, and less error prone, not as much a MS would like us all to believe, but things have moved on.
Another issue with its age is that it’s static, there’s been few, and I mean few updates to VBA since Excel 2000. In 2010, it got bummed up to version 7, which offered support for 64bit computing, but apart from that, nothing has been added to the langue for 10 years! If you need to integrate with the web, or modern databases, you’ll need external libraries, if you want to parallelise tasks good luck to you. If you want to use modern UI controls, you need to pull some strings… all these things are doable, just not easily.

2. Up skill yourself.
Lets face it, you’re comfortable with VBA, you know it inside out, and its hard to learn a new language, you can keep on using that API trick to help resize form controls, and that bit of code you have to pull data from a web service works ok. That’s fine, I agree, if it an’t broke, don’t fix it. But, don’t you want to try something new? Don’t you want to increase your skill set, make your CV look better? Wouldn’t you like to get some of the benefits other programmers are getting from using the classes Microsoft are dishing out? You can’t know every thing, let MS pick up the slack, and you can reap the rewards.

3. All the cool kids are doing it…
This is not as trivial as it sounds. How many things have you invented in VBA? Most of the code and ideas/approaches I use has been written better by someone else, someone who’s better at coding than me. New ideas and code isn’t going to come in VBA format. OK, you say, I have everything I need already, but how do you know what you need until you’ve seen the possible. Dot net will open up new opportunities for your code, new, dare I say it, better, ways to solve problems. And all the smart programmers out there writing dot Net code will be offering up their ideas for you to utilise too, why not give yourself the option to use it?

There are other reasons to move to technologies outside of VBA, there are many arguments and counter arguments to be had, but hopefully the above will get your mind ticking over… so the next part of the question…

Why dot Net?
This is a simple one to answer. It’s just easier to use an MS product than anything else. Think about it, we are talking about coding for a MS application, on a MS platform , why not use a MS tool. It’s not like you have a lot of choice anyway, you’ve got, VB6, which you in fact you don’t have because it’s very dead, you have, Delphi, which is fine, but with the best will in the world it’s never going to be able to match MS for office development… maybe Java – there are a few API scatted around the internet, but nothing to get too excited about- , or C, which is again is fine, but it’s hard work. Nope there’s no real argument to be had, if you move outside of VBA, it has  to be dot Net
Also, with the possible exception of Java, the dot Net framework is so much more powerful that anything else around, you would have to have a really, really, really, really compelling reason not to want to use it, and as we have just suggested, nothing else has that compelling reason.

So there you go, these are the reasons I think we need to be thinking about technologies outside of VBA, but wait, there more, I ask some Excel developers already using dot Net to list there top 5 reasons, here they are:

Mathias Brandewinder – Clear Lines consulting.

VBA has stopped evolving, and as such is becoming dated.

Development tools.

Testability.

VBA is limited to Office,

Version control.

And 2 bonus reasons, thanks Mathias!

Code re-use

Using .Net within Office wasn’t the most pleasant experience, but VSTO and projects like Excel DNA make it increasingly easy.

N.B – I’ve edit Mathias original notes which where much longer, so please don’t comment saying… “yeah but you can do all that in VBA”, we know, but its nearly always easier to do it in .Net – Ross.

XL Dennis – VSTO &.NET & Excel

Protection of the intellectual property

The Security paradigm that .NET is built on

The user friendly IDE allow us to be more productive

Offer modern technologies and controls to create attractive UI for the end users

Interaction with other programs and services

Simon Murphy – Codematic

Powerful IDE

Powerful languages

Rich body of dev knowledge

It is finally use-able for excel devs

Allow separation of your code from users code.

Thanks Chaps…Looking at these there are lot of points that match my thoughts, which is pleasing (I wrote the copy before I got any replies from the other guys), and there are a few that I missed. Versioning, separation of code, “its finally use-able”, what’s your take? Do you think you would dip your toe, if only some Excel blogger would get his act together and give some practical advice? Let me know either way!

Ross

* I use the word technology, and not languages here because we are talking about a different way to programme Excel, its complexly different. The languages (VB in particular) are probably the things that are most similar between .Net and VBA.  Don’t worry though the learning cure not that steep!

** at least I am, sorry mac people, maybe coco/mono might work?!