Why VBA Still Makes Sense

Article
09/07/2009

Not infrequently I am asked, “So, should I use VBA? Is it going to be around in Office 2010? Is it supported? Should I migrate away from VBA now? Can I count on this technology?” (Here I go with a response!)

These are fair questions, because customers need to know that the software systems they employ are ones they can count on. There’s no question that the IT landscape in terms of teams, tools, software, networks, and so forth have changed dramatically since 1993, when VBA, or Visual Basic for Applications, made its way into Excel. But, VBA still has a place in this world. It still makes sense, and I’ll explain why.

First, here are some answers: 1) VBA is included in Office 2010 much as it was in Office 2007. 2) It is indeed supported 3) You should continue to use VBA where it fits the needs of your business and migrate only if the need arises.

Let me elaborate on #3 a little more, because it is the locus of most questions and issues.

In contrast to 1993, business productivity solution developers on the Microsoft Office platform have more options in terms of what they use to create, build, deploy, and maintain solutions. For example, many developers now target the .NET Framework, and they are accustomed to using VB.NET, C#, or another .NET-compatible language. Many of these developers use Visual Studio .NET, and they are building a wide variety of solutions that integrate Web, databases, middleware, and client applications. More importantly, the user experience is quite different from 1993. Now, users are working in a highly collaborative, real-time sharing, online/offline, mobile, global world. They are populating documents with data from a wide variety of sources, and they are re-purposing the documents and data in very creative ways.

In 1993, users worked mostly in a monovalent way. Work was done in an exclusive application that didn’t have the broad reach into databases and Web sites like today. Importing CSV files was the primary way of ‘reaching’ into other data. Sharing consisted of saving a spreadsheet up to a file share. Customizing the application was primarily the task of users. Most IT departments had other things on their mind than tinkering with Office customizations. VBA gained huge popularity because of its ability to allow a non-greenscreen programmer to customize her or his application experience. For example, a user could write a VBA ‘script’ or macro that would automate repetitive tasks and save a ton of time. Over the years, many hundreds of millions of documents have been imbued with this kind of code and saved users untold numbers of hours.

The good news: Even though the user context has changed a lot since 1993, 1997 and beyond, using VBA can still help users save time and effort by automating tasks and customizing their Microsoft Office experience. And, as the application features have evolved to adapt to new user needs has also evolved and grown. Gladly, VBA has remained in step with these evolutionary changes. For example: a great little routine I wrote a long time ago is some VBA that I hooked up to a custom button in Outlook. It allows me, with the click of a button, to save off the attachments for any number of selected emails. The code loops through all selected emails, saves the attachments to a central location I have designated with some logic about how they are stored there so I can sort and find them easily. It then optionally deletes the selected emails. It’s a great little routine that saves me lots of time.

So, what’s the VBA authoring experience like? What are the advantages?

1. You can record macros in some of the applications. So, if there is a task you do over and over, just start the macro recorder and the Office application will communicate with the VBA environment for you and write the code for you. You can then re-run this macro any time to run the steps automatically.

2. OK—recording macros is great, but it’s not always enough. And, there are some applications that, while not endowed with full macro-recording capability, still allow you to write the code yourself. VBA really sets itself apart by having such a rich set of built-in tools. First, you have a rich Visual Basic Editor (VBE).

This is the environment for authoring VBA code. The VBE has Intellisense which makes it very easy to call into the application and pass the right information.

You also have integrated Help. If you are unsure what to type, what a specific method does, etc., just press F1, and the help system will give you the information, and, in many many cases, a sample of what to type.

VBE also has debugging tools. While writing your code or even just looking at a recorded macro, you might want to run the code but stop at a certain location and see what the code is doing.

There are lots of other debugging tools that let you alter code on the fly and gain greater insight into what the code is doing.

Additionally, VBE lets you easily reference applications and code that is not in your immediate project.

Has a co-worker created a special collection of code routines that everyone typically needs or uses when writing VBA solutions? No problem, just reference this external code, and it’s now available in your VBA solution. Above all, the VBA syntax is pretty easy to learn. It’s based on BASIC (there’s an intriguing history tracing through QBasic, Alan Cooper, and so forth that I won’t go into here), so it’s pretty easy to get into and start using.

For example, here’s some syntax that checks, when my document is closed, to see if I left comments in there (fortunately, in Office 2007 we made it easier to scub docs of this kind of thing without needing a custom macro, but you get the idea):

Private Sub Document_Close()
    If Me.Comments.Count > 0 Then
        MsgBox "Hey-- you forgot to remove comments", vbInformation, "Comment Removal"
    End If
End Sub

With very little effort, I could write code that would loop through the entire collection of comments in the document and save them all to a database. If I get stuck, there is a ton of additional help, samples, tutorials, and so forth on the MSDN Office Developer Center. There is also a very active community (see the Community section of the MSDN site) with Newsgroups, MVPs, and more to help me make progress.

Finally, the language of VBA is pretty easy to learn, and it has a fair amount of power. There is good control of flow language (If…Then, Select Case) and so much more.

3. VBA is a great all-in-one kit for customizing your Office experience

All of the things I have shared up to this point are included with Office out-of-the-box. You can start developing a custom little solution today with what is included in the software on your desktop. Also, you can extend the reach of your little solution by connecting to external code libraries. We also have design the Office applications themselves to be Web-aware. For example, you can connect to Web sites in Excel and import their data directly into a workbook. Using VBA, you can write code to alter which site the user connects to. You can add little buttons to the users Ribbon so they can click one button and import from one site, click another button and import from another site. The possibilities are endless and are bound only by your imagination. In short, the out-of-the-box Microsoft Office experience is great. People love what it can do. But, there will always be a need to customize the applications to fit more specific needs, automate tasks, and reduce busy work. VBA has been and continues to be a great way to meet those needs.

4. But, what about .NET? Aren’t there great development tools in Visual Studio .NET for creating Office-based solutions?

Yes! Visual Studio .NET has an excellent set of tools that allow you to create solutions targeting the Office applications like Excel, Word, PowerPoint, and Outlook. Solutions built using Visual Studio and using .NET code to customize the Office experience are created in a very different way, with different tools, and with different requirements. Visual Studio .NET is a set of professional developer tools. There is more to learning Visual Studio’s tools, IDE and so forth than you need to know to use VBA. Also, because Visual Studio targets .NET code, there are different security, deployment, and maintenance requirements than for VBA. In short, VBA is probably an easier way to begin customizing the Office experience for many users, especially those with less technical expertise. This does not mean that Visual Studio development is particularly difficult. It only means that it requires sacrificing a little more lunch time to learn what’s involved. VBA’s simplicity makes it a great candidate for a lot of ad hoc automation tasks.

Visual Studio and .NET do provide some appealing aspects to your solution. For example, .NET provides a layer of strong security to your solution that VBA does not provide. But, overall, in my view, the main reason for creating solutions in Visual Studio .NET is to take advantage of the professional development environment, IDE, security, and so forth to create multi-tiered, industrial-strength solutions. Also, it is really, really hard to not be completely won over by how Visual Studio .NET’s Office tools create strongly typed objects out of items you add to the document surface. For example, if I add a content control to a Word document, Visual Studio automatically declares a .NET object representing this content control. Additionally, it is very easy to create task pane solutions in Office using Visual Studio .NET. With three lines of code, the task pane is all wired up.

Visual Studio also has really slick tools for creating customizations in the Office Fluent UI.

If you are just beginning, VBA is a great place to start. You can also use VBA for complex solutions. All the same, if you are willing to deepen your technical skill (always a good career move) then exploring Visual Studio is a great idea, and you will find that most of what you learned in VBA code is applicable while coding Office solutions in Visual Studio. For example, the API calls for Office in .NET are roughly synonymous with those you made in VBA. Also, you can call VBA routines from your .NET code in Visual Studio, so investments in VBA are not lost as you migrate a solution to Visual Studio.

In the final analysis, it’s about choice and flexibility. It is not an ‘either/or’ dilemma. Use VBA for ad hoc task automation, and it has a pretty low entry bar in terms of knowledge and skill. Use Visual Studio if you are or aspire to be a professional .NET developer and all that this implies.

Some things to help you get going:

Rock Thought of the Day:

I’ve been reading “U2 by U2”, a great book that gives insight into every song, album, and tour. Read it, and you come away with clues as to how the band really ‘ticks, what has kept them together for so many years, and what the costs of being rock-and-roll stars can be. Most important, their humanity, genuine love of music, and their desire to always get things right comes ringing through--- even when they mess things up or fail to get things right.

Rock On

Why VBA Still Makes Sense

Additional resources