VBA - Visual Basic for Applications - is (still) the programming language for Office. I say, still because it's the very last little piece of the good old Visual Basic 6 that we used to know and love.
It's not that Microsoft has seen the light and realized that VB6 was, and still is, great technology. They haven't. The MBA's who run Microsoft now have consulted their spreadsheets and concluded that it would just cost them too much money and too many customers to force march all of the traditional office workers into a .NET based replacement. (As they did to all of us VB6 programmers. I guess you can tell that there is still just a little bitterness here.)
Microsoft used to license VBA to anyone to include in their own products. A lot of other companies took advantage and still use it. In fact, there are so many that Microsoft's index to all of them is spread out over five web pages from ABB Manufacturing to Zeiss Optics. But they stopped accepting new customers for VBA back in July 1, 2007. They still insist that, "Any existing partner can continue to ship VBA and Microsoft Office will continue to include it." So ... Microsoft says you can develop VBA based applications with confidence that Microsoft won't pull the rug out from under your software investment. (Uh-huh ... Sure.)
But, in fact, VBA still is the only way that you can develop your application right inside your Office application. When you click the "Developer" tab in a Microsoft Office application (like Word or Excel), the tools you have available are VB6 based technology. Microsoft has a .NET interface for Office called VSTO - Visual Studio Tools for Office - but it's not in any of their Office applications. To get VSTO, you have to invest in Microsoft Visual Studio. It's not even in the "Standard" version - you have to spring the extra cash for the "Professional" version. I have complained in the past that this policy is almost guaranteed to make VSTO fail since the most natural Office developers, the Word, Excel, and other Office experts, are unlikely to spring for the full Visual Studio Professional in addition to their Office application just to write macros - especially when the old standby, VBA, is still there for no extra charge. And they already know how to use VBA.
There is a version of the .NET based VSTO inside at least one of the Microsoft Office products now. This version is called VSTA - Visual Studio Tools for Applications and it's the same product that Microsoft is now selling to other companies instead of VBA. But until Microsoft's policy changes for the rest of Office, VBA is the way to go. That's why I've decided to update my VBA articles to show how it's done with Office 2007. The versions of VBA that this article uses as a "host" application is Word 2007. The same basic ideas apply to Excel and other Office applications.
Getting Started
If you have just installed Word 2007, the first question you might have is, "Where's VBA?" The default is to not display the Developer tab that takes you into the VBA development environment. So your first task is to change that option. To do that, go to Office button > Word Options > Popular and check the option for "Show Developer tab in the ribbon". You can see the tools in the Developer tab in the illustration.
--------
Click Here to display the illustration
Click the Back button on your browser to return
--------
As a solid traditionalist, I'm going to do the traditional "Hello, World" app to demonstrate the VBA environment in Word. In fact, I'm going to do it several times.
For our first "Hello, World" we'll use controls to display "Hello, Word!" as text in a Word document.
To create a VBA macro that adds the text "Hello, Word!" to a document, you could just click the Visual Basic icon in the Developer tab and start coding, but there's an easier way. In fact, this method is used even by experienced developers who really could start coding VBA like they were writing a letter because it's so quick and easy.
This easier way is to click "Record Macro", give the macro a name and select a location, and then just do whatever you want the macro to do in Word. Click "Stop Recording". Then click the Visual Basic button and apply any added coding that you couldn't record or that needs to be corrected. (You can't record everything, so you'll find that there are always changes and additions to be made. You also can't record mouse movements. You have to use the keyboard.) You can also record macros in Excel 2007, but you have to do it the hard way in other Office applications.
Here's the macro I created:
Sub Macro1()
'
' Macro1 Macro
'
'
Selection.Font.Color = wdColorRed
Selection.Font.Size = 48
Selection.Font.Name = "Bauhaus 93"
Selection.TypeText Text:="Hello, Word!"
End Sub
And here's the result:
--------
Click Here to display the illustration
Click the Back button on your browser to return
--------
On the next page, we look at all of the ways you can use VBA to say, "Hello, World" using VBA in Word 2007.