Excel Tutorial Series – VBA Macros, Part One Of Two

This is part five of the Excel Tutorial Series. Read all tutorial posts by browsing the Tutorial category.

What is Visual Basic for Applications (VBA)?

Visual Basic For Applications, or VBA as it is usually abbreviated, is a programming language developed by Microsoft, closely related to Visual Basic, that is used to automate Microsoft Office and other applications.

These programs expose their functionality through what is called an Object Model — a series of functions (more commonly called methods), properties and objects or classes that allow you to programmatically manipulate the operation of these programs, and access their data.

This tutorial will discuss VBA in general, but will highlight Excel in particular, and to a lesser extent, Outlook.

Most of the major Office programs allow you to record or write VBA macros: Excel, Outlook, PowerPoint, Publisher, Word, Access, Project, Visio. Other programs that support VBA include OpenOffice's Calc spreadsheet program, and Zoho Sheet.

Unlike many other runtime environments, VBA is hosted entirely within its parent program, which is a commercial product. In other words, each Office program comes with its own IDE. The source code is compiled directly in the program and isn't usable as a standalone program outside its host.

Most people are introduced to, and are familiar with, VBA through the macro recorder. This tool lets you record your actions in the user interface and play them back. It is especially useful for simple, repeatable actions that would otherwise be tedious. This allows end users to quickly complete a set of actions that would (relatively speaking) take much longer to complete by hand.

However, the macro recorder unintelligently produces poor code that, in hindsight, I would recommend beginners avoid. When I started using VBA code a few years ago, I used the macro recorder to learn the syntax of VBA. And to a certain extent, I relied on it to teach me how to code, since I had little to no programming experience or training.

In retrospect, the macro recorder's concrete-bound code turned out to be a speed bump that hindered my growth. It requires conscious effort to correct the poor coding habits the macro recorder teaches you. Better to not have to start from behind. As Mark Twain wrote, "It is easier to stay out than get out." I'll reserve my full criticism of the macro recorder for another post. Let's continue.

Here is an example of code produced by the macro recorder.

Note that the code selects each cell and turns the font bold, faithfully duplicating my actions on the worksheet. Technically what it does is toggle the Bold Property of the selection's Font class to True.

Understanding the syntax and logic of this example is critical to understanding how VBA (and programming in general) works. There is an object, called Range, which consists of one or more cells on a worksheet. This object contains properties (or attributes) which describes the text style of the characters within the Range object.

It is important to note that, in most cases, we do not have to select objects in order to manipulate them in VBA; unfortunately the macro recorder faithfully (and unintelligently) records the end result of your actions. We could edit the code as follows and get the same result:

Or even shorter:

Although the examples in this section are shown in Excel, the principles still apply to whatever Office program you are using.

---

Objects, Methods, Properties

As I mentioned earlier, understanding the syntax of VBA is critical to understanding how to use it. The object model hierarchy describes the available objects, properties and methods for a given program, and how to access them. There are three major types of items you work with in VBA: Objects, Properties and Methods.

Objects

Objects are collections of related items, like worksheets, ranges and charts. To understand objects, I like the standard car analogy. A car is an object, which itself contains objects and properties, such as a steering wheel, seats and doors. Objects hold data and perform actions on data. They have specific methods for performing custom actions, within known limits.

Properties

Properties are characteristics of objects. They describe the magnitude or quantity of an attribute of an object, although they can also describe whether an attribute exists at all. For example, an object might have a Length Property, telling you how many members it contains. For a car without headlights (a race car?), the HeadLight Property would be False.

Methods

Methods are actions performed on, or by, Objects. In VB they are usually called Functions (because you declare them using the Function keyword).

All VBA is written in the Visual Basic Editor (unless you feel compelled to write it in another program like Notepad++) – press Alt-F11 to start the VB Editor in any Office program that supports VBA. Here is the VB window for a Word document.

Technically, macros that you write are called "procedures" — only the recorded ones are called "macros." All procedures are bounded by a "Sub" and "End Sub" which delimits the start and end of most procedures, respectively. Functions are bookended by "Function" and "End Function." Following the "Sub" is the name of the macro, for example

Sub MyMacro()

defines the start of a procedure named MyMacro. The open+close parentheses set are required. If a macro uses parameters (variables containing data being passed to the procedure), they will be placed between the parentheses.

After typing the line above and pressing Enter, "End Sub" is automatically added two lines below, to allow you to write code in between. All code is written between these tags.

Since most people use Excel to manipulate worksheet cells, the main object we review is the Range Object.

Range Object

A Range consists of at least one worksheet cell. For example, A1 is a range consisting of only one cell, while A1:G100 contains 700 cells (a section of cells 7 columns wide and 100 rows deep). Both are Range objects.

The Range Object itself contains other Objects, such as a Cells Object which consists of all the cells in the range. Some of the properties of the Range Object include the RowHeight property, which specifies the height of the rows of the range, and NumberFormat, which specifies what cell format is being used.

Some of the methods of the Range Object include Cut, which works like Ctrl-X to move cells, and AdvancedFilter to filter and copy filtered ranges.

To browse all of the methods, objects and properties belonging to VBA, or to a particular object model, simply press F2 in the VB IDE of any compatible Office program. In the upper left corner is a dropdown list of all the type libraries referenced in the current workbook using Tools » References.

To view additional type libraries, go to Tools » References and reference the object library you want to browse. Then return to the Object Browser (F2) and choose that object library from the dropdown box in the upper left corner of the dialog. I was able to verify this behavior in Excel, Outlook, PowerPoint, Publisher, Word, Access and Project. You can also do this with a separate TypeLib browser; see Visual Basic: Inspect COM Components Using the TypeLib Information Object Library or do a search for "typelib browser" in your favorite search engine.

Each object may itself contain other objects which themselves have properties and methods. For example, the Workbook Object itself hosts a collection of Worksheet Objects. The Visual Basic Help system has a nice object model diagram which you can use to view these relationships visually, just search for "Excel Object Model" in Excel's Visual Basic Help system.

---

Our First Procedure

Many procedures start out small and are built up over time until they are much larger in scope than what we would normally write in one sitting.

Here is an example of such a procedure. We will manipulate the Range Object until we have a complex procedure that does multiple things we couldn't do using just the macro recorder (and wouldn't feel like doing manually).

The first version uses the Value property of the Range Object to assign a (string literal) value to cells A1:A20. Note that string literal values must always be enclosed in double quotes ("). Also note that an unqualified Range Object assumes the current worksheet.

After running the code we notice the column isn't wide enough to properly display the text. So we add another line of code as follows:

Autofit is a method that can be used on Range, Column and Row Objects. Like the name implies, Autofit adjusts the width of a column or row to fit the widest data being held. Now we decide we want to color every other row of text, similar to old-school computer paper.

The statement "Dim [variable_name] As [Type]" tells VBA how you intend to use each variable, and will be required if "Option Explicit" is printed at the top of your code module.

variable_name

Definition:	The name of the variable you want to use; can be any string, with the following conditions:
A variable name must start with a letter (not a number), but can include numbers. A variable name cannot be longer than 255 characters. A variable name should not be the same as any of Excel's reserved keywords, for example "Range", "Sheet" Variable names must not contain spaces. To make them more human readable you can write them in CamelCase or use underscores to separate words.

Type

Definition:	The type of variable. Types include:
Boolean = True or False Integer = A number between -32,768 and 32,767 with no decimal places Long = A number between -2,147,483,648 to 2,147,483,647 (larger scope than Integer) String = A list of alphanumeric characters up to 65k characters long Visit Data Type Summary for a complete listing.

We added a 'For Next' loop that colors the even-numbered rows yellow (yes, I realize we could have done this with Conditional Formatting, but this is a VBA tutorial). Loops will be covered in Part Two. We also declared and used a Long variable, lngCounter, and included comments describing what the procedure is doing. Comments are lines of code prefaced with a single apostrophe ('). They are ignored by the interpreter when the procedure is run. Of course you'll want the comments you write to be more meaningful than my samples.

The loop has a hard-coded count (1 to 10) which divides the loop counter by 2 and checks the remainder (see Modulo for explanation of the Mod function). If the remainder is zero, then we have an even numbered row, so we color the cell yellow.

Yet it only works on the first 10 rows. We need a way to figure out how many used cells are in the column and adjust the loop counter automatically. I know the worksheet function COUNTA is used to count text cells; we can use this in VBA as well.

The loop counter now adjusts depending on how many rows are used in column A, and the fill color is added all the way down to the bottom of the used range. Now if I want to adjust the size of the range vertically, I only need to adjust the first line of the macro and change the range to, for example, A1:A100, and the rest of the code will adjust itself accordingly.

But we're not done. "Hello World" is boring, so let's let the user decide what text they want to use.

At this point you might notice a few conventions being used:

• Sections of code are indented (or "nested") to show relationships (especially Loops and With-End With blocks)
• Some variables (i.e. "lngCounter") use prefixes to identify variable type

I highly recommend indenting code as a matter of readability. Related code should be indented at the same level to make program flow easy to understand. For example, in version 5 of the sample program above, all of the code within the For-Next Loop statements is indented one level to show it is inside the loop. The Cells statement is further indented to show that it occurs inside the If-End If statement. This makes it obvious that the If-End If statements occur inside the For-Next Loop.

The second point, on the other hand, is a matter of debate. Hungarian notation was developed by Charles Simonyi at Microsoft to make variable type identification easier. But modern IDEs will throw errors at compile time (or at least, highlight the error) if you try to assign a value to a variable that it can't handle. And both VBA and VB .NET allow you to check the variable type by clicking on the variable name and pressing Shift-F2. So the jury is out; use it if it helps you.

To improve performance, you may want to include the following at the beginning of your procedure:

Application.ScreenUpdating = False

and this at the end of your procedure, right before the End Sub:

Application.ScreenUpdating = True

This will speed up your code by turning off screen updating for the duration of the code. Excel won't redraw the screen until after the procedure is finished. See Calculation Mode and Code Optimization for more optimization tips.

By the way, did you notice that in the above set of macros, we accomplished everything without selecting anything?

To be continued in Part Two –

↑ Scroll to top
Previous Post: Post email data to the Web
Next Post: Reply to Recipient