celebrity writers

MS Visual Basic for Applications

How to use Visual Basic for Applications (VBA) with MS Office tools, including VBA for MS Excel, VBA for MS Word, and a general overview of Microsoft Visual Basic for Applications.

Jan 16, 2007

Using VBA with Office tools, theory of VB and VBA, and an up to date list of resources available on Suite 101.

Introduction

Microsoft Visual Basic is one of the easiest programming languages to get started with for budding programmers. It has a very complete object model, easy to understand syntax (based around several flavors of traditional BASIC), and can provide Windows style interaction with the user.

The Visual element in Visual Basic comes from the point and click interface - it is programmed visually, at least at the user interface level. Actions (scripts, macros) are attached to specific parts of the user interface. These are executed or evaluated at the time that the user interacts with the software.

The general approach makes it very quick and easy to put together an application, and while the result might suffer from some performance issues, it is a great platform if performance is not the most important factor. Where VB comes into it's own, however, is when it is integrated with other Microsoft applications.

Macros & VBA Scripting

In the original Microsoft Office suite (Word, Excel, Access, PowerPoint etc.) the user could record macros, and then edit them. These macros provided a way to automate commonly performed functions - anything from typing text, to formatting a document.

At some point, Microsoft introduced Visual Basic for Applications. This provided the power of the VB language coupled with the object model of the Office Suite. In other words, each Office component could be interacted with in a way that provided functionality beyond simple macro recording.

Put another way; where users could record key presses, menu choices, mouse movements and so on, now they could also write little programs to interact with the application in a conditional manner. In fact, VBA brings all the power of a programming language to the macro environment. This includes features such as:

In short, entire application extensions can be written inside the Office Suite.

Macros & Toolbars

Using the MS Office feature of customizable toolbars, it is also possible to attach these macros to an easy interface. The basic steps are very easy:

Create a macro (VB script) : Alt+F11
Right click anywhere in the toolbar area
Select Customize...
In the Commands tab, choose Macros in the Categories list
Select the Macro
Drag it to a toolbar

This last step can be replaced with the following, to create a new toolbar (initially floating):

Select the Toolbars tab
Click New...
Follow the instructions

Then, you can drag and drop macros (VB scripts - Sub functions) to the newly created toolbar.

VBA for Word & Excel

The most common reason to use VBA in Excel is to extend the basic functionality for simple calculations. Conditional summing, where a cell value is summed based on the heading in the row, or some other related information, is one of the best examples. It is, however, also possible to create reports, formatted data for export, or even web pages from raw data entered in Excel.

Essentially, VBA provides an extension to the Formula feature in Excel, which already contains some powerful programming-like mechanics.

MS Word users will typically want to manipulate the document object using VBA in Word. These can include tasks such as formatting for HTML (inserting tags, URLs, etc.) as well as building and formatting documents automatically. It is also possible to link to Excel (and Access) to build documents based on external data, and use the built-in formatting features of Word (Table of Content creation, styles, etc.) to create an aesthetic result.

Adding VBA to the mix allows for some very sophisticated document and content creation systems to be built up.