Dennis Wallentin, aka XLDennis, is a long time contributor to the on-line Excel community and co-author of the latest version of Professional Excel Development. In recent years he has concentrated on the use of the VSTO and .NET products in conjunction with Excel, which you can read all about at his blog: VSTO & .NET & Excel, which has now been added to my blog list on the right.
Browsing the blog led me to the Microsoft All-In One Code Framework, which led me on to the MSDN Library.
Now don’t get me wrong, these are excellent resources, and well worth exploring, but what immediately struck me at the Developer’s Library was the invisibility of VBA. Not surprising, perhaps, that there should be nothing under “Development Tools and Languages”, but surely the most widely used development tool for Office Applications should have a heading of its own under Office Development? But no, to find a VBA related article we need to go down another three levels to MSDN Library: Office Development: Microsoft 2010: Excel 2010: Technical Articles: where we find:
Getting Started with VBA in Excel 2010
So what does it have to say?
Microsoft Excel 2010 is an extremely powerful tool that you can use to manipulate, analyze, and present data. Sometimes though, despite the rich set of features in the standard Excel user interface (UI), you might want to find an easier way to perform a mundane, repetitive task, or to perform some task that the UI does not seem to address. Fortunately, Office applications like Excel have Visual Basic for Applications (VBA), a programming language that gives you the ability to extend those applications.
So that’s how Microsoft see VBA these days, “an easier way to perform a mundane, repetitive task, or to perform some task that the UI does not seem to address”. To be fair, later on they do say:
VBA is not just for repetitive tasks though. You can also use VBA to build new capabilities into Excel (for example, you could develop new algorithms to analyze your data, then use the charting capabilities in Excel to display the results), and to perform tasks that integrate Excel with other Office applications such as Microsoft Access 2010. In fact, of all the Office applications, Excel is the one most used as something that resembles a general development platform. In addition to all the obvious tasks that involve lists and accounting, developers use Excel in a range of tasks from data visualization to software prototyping.
But even here it seems that Excel plus VBA is only something that “resembles a general development platform”. With this level of lack of excitement from Microsoft is it any wonder that VBA is so often seen as a toy solution that no professional developer would waste their time on? Now I’m not saying that VBA is the answer to everything, far from it, but neither is it, as often claimed, the second best solution to everything. For rapid development of convenient and efficient tools for a wide range of numerical analysis problems, in engineering and scientific applications as well as finance, I don’t know of any tool that comes close to a spreadsheet with an integrated programming language; and as the de-facto standard application Excel with VBA is the obvious tool to fill that role.
Nearly forgot; what do you think? The comment box is just down there.
Newton Excel Bach, not (just) an Excel Blog 
I’m developing a stadium heat map in Excel right now. I’m sure, once the company decides to build a real heat map will use the one I’m building as a prototype. Yes, Excel VBA is extremely powerful, especially since you can extend it even beyond Excel into other programming languages.
What would the world do without it?
At my previous work I even used it to compare documents and compare paragraphs to see which paragraph was closest to another paragraph. This would fall under “make mundane tasks easier”, well maybe not easier (in the short run) but at least more fun!
LikeLike
It seems pretty clear that Microsoft want VBA to go away. The only minor problem is the (billions of?) lines of VBA that underpin Excel-based applications all over the world. VSTO isn’t the answer. Ironically, MS had part of the answer and don’t seem to be too concerned about it any more: the DLR. Longer-term, it might be interesting to see a managed-code version of Excel. Not impossible: they managed to make Excel into a COM server back in the ’90s, didn’t they?
Gosh, it’s almost exactly the third birthday of the post where I moaned about this topic:
LikeLike
No Doug, we’re all supposed to have switched to VSTO. Pretty soon we’ll have some other tool shoved at us.
LikeLike
Thanks for the comments. I think part of the problem is that we are getting such a mixed message from Microsoft. For instance, I hadn’t even heard of VSTA until very recently so I thought I’d look it up in the Excel help (2010). Here’s what they had to say:
No results returned for vsta.
Tips for better search results:
Try a different phrase
Check your spelling
Funnily enough a search on VSTO returned exactly the same advice.
Help on VBA was a little more forthcoming, but I didn’t find anything on how to write a VBA macro, or even how to enable the Devloper Ribbon, or open the VBE. Evidently you are supposed to know these things, and if you don’t know, you don’t need to know.
It seems that MS have a picture of the Office world being divided into developers (who will use VSTO) and users, who may need to run a VBA macro, but won’t need to write one. They don’t seem to realise that many Office developers (probably the great majority, myself included) do not have programming as their principle job function, and what we need is something that will get the job done with the minimum of hassle. That doesn’t mean that they have to stick with VBA as the primary programming tool for use with Office for ever, but it does mean that if an alternative is introduced it does need to be properly integrated and properly documented, and most of all it needs to just work when transported to another computer operated by a non-developer. As far as I can see VSTO at the moment doesn’t even come close to satisfying any of those requirements.
LikeLike
A while back they added a VBA developer center to the msdn Office site.
http://msdn.microsoft.com/en-gb/office/ff688774.aspx
And this article on the Excel developer road map is interesting.
http://msdn.microsoft.com/en-gb/office/ff458124.aspx
LikeLike
Thanks Andy. I’m going to put those links in a separate post because they are certainly more useful than anything I found.
Maybe it’s me, but they do seem to be pretty well hidden though!
LikeLike
I am late for the party I guess 🙂 I found out about this link from my blog. All credit goes to dougaj4 🙂
The two links that you have mentioned above are very useful links for someone who is just a beginner. In fact, I do visit them sometimes when in doubt 🙂
Unfortunately, some of the links just lie fallow. For example see the below link.
http://msdn.microsoft.com/en-us/library/bb332345%28v=office.12%29.aspx
For a beginner this is supposed to be very important but in spite of several comments, nothing has been done unfortunately 😦
LikeLike
I see they have solved the problem in the 2010 version by dumping the map again.
LikeLike
I have to say that a lot of developers at work also seem to think that Excel is some kind of toy. Oddly enough, these are the people who have come with overly complex solutions to do something that is relatively easy to do in Excel or other Office applications. I think a lot of the problem is that they are not aware of Excel’s capabilities (not that I know all of them myself, I’m constantly surprised at what I see can be done)
LikeLike
Hi all,
Sorry to jump in late here but I was not aware of this post. If possible, drop me a line next time You refer to me or to my blog and I will be happy to give my 2c opionion (fwiw).
First of all, a good, valid and indeed a very important point. From my view I would like to address the following;
#1 Per se Microsoft does not identify Office as a development platform. From their *large* corporates’ point of view it’s just an end-user toolkit. That prevent them seeing the power and importance of using the Office suite as a development platform.
I guess that Microsoft realized that they had to support VBA at the MSDN site as they postponed all major upgrades of the Office suite when Office 2010 was released. I also expect that next version, i.e. 15.0, will only be a maintenance upgrade.
The #1 question to answer for Microsoft is: What should we do with Office?
#2 Sorry everyone but VSTO is a dead end. I guess that Microsoft had some big plans when they started to develop VSTO but the lack of support for VSTO in the online Office development community reflects all the shortcomings of VSTO…
#3 Using VB.NET to create managed COM add-ins is excellent and is a good compliment to the development of native add-ins. The same can also be said about VB6.
So when we try to summarize we end up with: no special changes within the foreseeable future.
Do You all agree?
Kind regards and once again thank You for referring to my blog,
Dennis
(I have signed up to be notified when new blog entries are made.
LikeLike
Dennis – thanks for the comments.
Also for the benefit of others, I’ll pass on the link from the latest post on your blog:
http://siddharthrout.wordpress.com/vb-net-and-excel/
Looks like an excellent site for anyone starting out with VB.net and Excel.
LikeLike
Doug,
Great, Sid is also a nice guy 🙂
Kind regards,
Dennis
LikeLike
Dennis the feeling is mutual 🙂
LikeLike