1. Technology

VBA - The Visual Basic Working Partner

A Introduction to the Programming Language of Office


VBA - The Visual Basic Working Partner
Updated June 20, 2014

One of the most outstanding qualities of Visual Basic is that it's a complete development environment. Whatever you want to do, there's a 'flavor' of Visual Basic to help you do the job! You can use Visual Basic for desktop and mobile and remote development (VB.NET), scripting (VBScript) and Office development (VBA !) If you have tried VBA and you want to know more about how to use it, this is the tutorial for you. (This course is based on the version of VBA found in Microsoft Office 2010.)

If you're searching a course in Microsoft Visual Basic .NET, you have also found the right place. Check out: Visual Basic .NET 2010 Express - A "From the Ground Up" Tutorial

VBA as a general concept will be covered in this article. There's more to VBA than you might think! You can also find articles about the Office VBA sisters:

-> Getting Started Using VBA: The Word Working Partner

-> Getting Started Using VBA: The Excel Working Partner

There are basically two way to develop programs that can work with Office applications: VBA and VSTO. In October 2003, Microsoft introduced an enhancement to the professional programming environment Visual Studio .NET called Visual Studio Tools for Office - VSTO. But even though VSTO leverages the considerable advantages of .NET in Office, VBA remains more popular than VSTO. VSTO requires the use of the Professional or higher version of Visual Studio - which will probably cost you more than the Office application you're using - in addition to the Office application. But since VBA is integrated with the host Office application, you don't need anything else.

VBA is used primarily by Office experts who want to make their work faster and easier. You seldom see large systems written in VBA. VSTO, on the other hand, is used by professional programmers in larger organizations to create Add-Ins that can be quite sophisticated. An application from a third party, like a paper company for Word or an accounting firm for Excel, is more likely to be written using VSTO.

In their documentation, Microsoft notes that there are basically three reasons to use VBA:

-> Automation & Repetition - Computers can do the same thing over and over much better and faster than people can.

-> Extensions to User Interaction - Do you want to suggest exactly how someone should format a document or save a file? VBA can do that. Do you want to validate what someone enters? VBA can do that too.

-> Interaction between Office 2010 Applications - A later article in this series is called Word and Excel Working Together. But if this is what you need, you might want to consider Office automation, that is, writing the system using VB.NET and then using the functions from an Office application like Word or Excel as needed.

Microsoft has stated that they will continue to support VBA and it's featured prominently in the Official Microsoft Office 2010 Development Roadmap. So you have as much assurance as Microsoft ever provides that your investment in VBA development won't be obsolete in the near future.

On the other hand, VBA is the last remaining Microsoft product that depends on VB6 "COM" technology. It's over twenty years old now! In human years, that would make it older than Lestat the Vampire. You might see that as "tried, tested and true" or you might think of it as "ancient, worn-out, and obsolete". I tend to favor the first description but you should be aware of the facts.

The first thing to understand is the relationship between VBA and Office applications like Word and Excel. The Office application is a host for VBA. A VBA program can never be executed by itself. VBA is developed in the host environment (using the Developer tab in the Office application ribbon) and it must be executed as part of a Word document, an Excel workbook, an Access database or some other Office host.

The way VBA is actually used is different too. In an application like Word, VBA is used primarily as a way to access the objects of the host environment such as accessing the paragraphs in a document with the Word's Word.Document.Paragraphs object. Each host environment contributes unique objects that are not available in the other host environments. (For example, there is no "workbook" in a Word document. A workbook is unique to Excel.) The Visual Basic code is mainly there to make it possible to use objects customized for each Office host application.

The fusion between VBA and host specific code can be seen in this code sample (taken from the Microsoft Northwind sample database) where purely VBA code is shown in red and Access specific code is shown in blue. The red code would be the same in Excel or Word but the blue code is unique to this Access application.

Click Here to display the illustration

VBA itself is almost the same as it has been for years. The way it integrates with the host Office application and the Help system has been improved more.

The 2010 version of Office doesn't display the Developer tab by default. The Developer tab takes you into the part of the application where you can create VBA programs so the first thing you need to do is change that option. Simply go to the File tab, Options, Customize Ribbon and click the Developer box in Main Tabs.

The Help system works much more smoothly than it has in previous versions. You can get help for your VBA questions either offline, from a system that is installed with your Office application, or online from Microsoft over the Internet. The two interfaces are designed to look a lot alike:

Click Here to display the illustration

If your Internet connection is fast, the online help will give you more and better information. But the locally installed version will probably be faster and in most cases it's just as good. You might want to make the local help the default and then use the online help if the local version doesn't give you what you want. The fastest way to go online is to simply select "All Word" (or "All Excel" or other app) from the Search dropdown in the help. This will immediately go online and perform the same search, but it won't reset your default selection.

Click Here to display the illustration

On the next page, we get started with how to actually create a VBA program.

  1. About.com
  2. Technology
  3. Visual Basic
  4. Programming Office
  5. Get Started Using Visual Basic for Applications (VBA)

©2014 About.com. All rights reserved.