Writing macros is usually fun for the people who write them, though some say that it takes a special kind of person to get off on this stuff. But while it might be fun for some people, for organizations, it's pure pain. Since not everyone enjoys it or does it well, an organizational decision to invest in macros usually means betting heavily on a few people, or bringing in expensive consultants or contractors. Since the organization can develop a dependency on these scarce resources, the decision to invest in macros must be taken seriously.

There are four characteristics of situations in which function macros provide significant advantages that justify the organizational pain. Understanding these characteristics is much easier if we understand the concept of a spreadsheet cliché. A spreadsheet cliché is a structure that we use repeatedly. For example, we often compute running sums of the line directly above. Typical uses of this cliché are calculation of total headcount given a hiring stream, or annual revenue give a stream of monthly revenue. With this in mind, let's examine some of the reasons for writing function macros.

If you have a set of spreadsheet assets that must be maintained over a significant period of time, you can lower maintenance costs significantly by identifying clichés in those assets and reimplementing them in Visual Basic. Thereafter, you'll find reductions in the total number of cells used and the total number of formulas. You can also expect significant reductions in the Fault Feedback Ratio (FFR), which measures the number of faults generated by repairing a fault. The Fault Enhancement/Feature Ratio (FEFR), a measure of faults generated in new development or in enhancements, is also reduced.

When you can capture in a single macro a complex cell-gymnastic routine, you elevate the thinking of anyone working with the spreadsheets that use it. Good candidates for function macros are manipulations that require intermediate displays not for purposes of visibility or inspection, but simply because you can't figure out how to perform the computation in a single cell or array formula. Even when you can express the calculation in a single formula, that formula can be very long. If it wraps in the formula bar, it can be difficult to read. Sometimes it's wise to reduce such an expression to a simple call to a function macro.

When you improve readability in this way, everyone finds it easier to read and understand the spreadsheet. This helps reduce errors, lowers costs, and reduces your dependency on the author of the work.

When you want to reuse a spreadsheet cliché, you must copy and paste. Sometimes that's all you have to do, when you're lucky, but often you must make slight modifications to the copy. Excel supports this style of development, probably too well, because it entices us to use this practice too often. If you later change the cliché slightly, you have to chase around and fix all the (possibly customized) copies. This offers a multitude of opportunities for elevating the FFR and the FEFR (both bad things). When clichés are implemented as function macros, reuse generates far fewer such cost penalties.

We sometimes find that we need to perform similar but not identical computations. The variations between two similar computations might be such that the required spreadsheet structures aren't exactly parallel. When we capture these differences in a function macro, and provide the macro with enough intelligence to decide how to treat each case we present to it, the two invocations become isomorphic — directly analogous. The complexity is then buried in the macro, and we have achieved unification at the spreadsheet level. This lowers maintenance costs and improves understandability.

The ideal candidate for a function macro is a spreadsheet cliché that:

• Appears several times
• Displays cells you don't really care about
• Might change someday
• Appears in more than one form
• Must be read and understood at some future date, especially by people other than the author

In VBA, macros are implemented in modules, which are attached to the workbook, but which aren't visible as worksheets. To access the VBA modules you have to use Excel's Visual Basic Editor. For the time being, though, we focus on what you have to type into the module, rather than on how you get to do that.

Function macros in VBA have four parts:

• A function statement that declares the name of the function and its arguments and data types
• Data type declarations
• The computation itself, including any return statements
• An "End Function" statement

Pretty simple-sounding. As they say, the devil is in the details. Before we discuss these parts, let's detour a little bit to explain data types. Visual Basic is an example of a strongly-typed computer language, which means that you have to tell it what kind of data every variable has to hold. If you declare a variable to be an integer, for example, and then later try to store a floating-point number in it, the program won't work.

VBA works a lot better if you declare the data types of functional arguments and the function's local variables. A local variable is a temporary quantity that has a value during the time the function is executing.

There are lots of types, but only a few that we need to know about:

Integer

The Integer data type is for whole numbers from -32,768 to 32,767.

Long

Whole numbers of any size.

Double

The Double is a floating point number. There are limits on the size of a double, positive and negative, but they're so enormous that you need not be concerned. Really. You can assign an integer value to a variable that you've declared Double, but not vice-versa.

Boolean

A Boolean is either TRUE or FALSE

Variant

A variant is an ambiguous data type. It can be anything.

Object

The Object data type is reserved for representing Excel entities, such as a range or a cell.

String

Use the String data type to represent segments of text.

The first few lines of any function are local variable type declarations. Each declaration is of the form

Local variable type declarations must be in Dim statements, and are separated by commas. For example,

This Dim statement declares that i and j are Integers, and answer is a Double. You can have as many declarations as you want on a single line. You can omit the "As <data-type>" part of a declaration, but if you do the corresponding variable is declared Variant.

You must also declare the data types of the functional arguments. These declarations are inserted into the Function statement, as shown:

Here we've used the underscore at the end of line 1. Underscore is the line continuation character. When you break a statement across two or more lines, you must end each line with an underscore (except the last one).

It's also a good idea to add comments to your code, so you and others will find it easier to figure out what a particular piece of code is supposed to do. To add a comment to a line of code, insert a single quote, followed by the comment. Excel considers everything on the line after the single quote to be a comment.

An assignment statement assigns a value to a variable. The statement below sets the value of i to be 2.

The data type of the variable whose value is being assigned must be the same as the data type of the value you assign to it, except when the variable is declared Variant. Variant matches anything.

To return a value from a function, assign that value to the name of the function. For example,

Here again we've used the underscore at the end of line 1. Underscore is the line continuation character.

Most of the computations you need can be done with only a few kinds of Visual Basic statements.

Excel objects have properties that hold values or other objects. For example, when you invoke a function macro with an argument of a range of cells, that argument is represented by a Range object. One important property of a Range is its Cells, which is a collection of the cells in the Range. To access this property, if arg is the Range object, use the notation arg.Cells. Other properties of a Range object are its Columns collection and its Rows collection, accessed as arg.Columns and arg.Rows, respectively.

When a property holds a collection object, you can access a specific member of that collection by using an index. For example, arg.Columns(3) returns the third column of the Range arg. Some collections are 2-dimensional. For example, arg.Cells(1,2) returns the cell that is in the first row and second column of the range.

Each class of Excel object supports a set of methods specific to that class of object. For example, Collection objects support a Count method that returns the number of elements in the collection. You invoke a method on an object in exactly the same way you access a property. Thus, if arg is a Range, since arg.Columns returns the collection of columns in arg, arg.Columns.Count returns the number of columns in the Range arg.

Iteration is the process of applying the same set of statements repeatedly. There are lots of ways to iterate in Visual Basic for Excel, but we only need one — the simplest form of index iteration. In this form of index iteration, there is a loop variable that must declared Integer. We supply its starting value, and its ending value, and it increments by one unit for each iteration. Here's the syntax:

In this example, minVal and maxVal can be any expressions that evaluate to integers. do-some-statements is any list of valid statements, including iteration statements. Naturally, if any of these statements are iteration statements, their loop variables must be called something other than i, to avoid conflicts with the i we're already using as the statement's iteration variable.

The conditional statement allows you to test for the truth or falsity of a condition, and then, depending on the result, the program will execute one of two sets of statements. Its syntax is

Here the <test-condition> is any expression that returns a Boolean value. For example x>1, or arg.Columns.Count < 35. <then-statements> and <else-statements> are sequences of statements.

Arrays are ordered sets of data, indexed by integers. They can have any number of dimensions, but for our purposes, we need only 1- or 2-dimensional arrays. To declare an array called, for example, ExpenseCategories, use a Dim statement to set its size and data type.

This example declares the array Expenses to be a two-dimensional array of size 3x2. It then assigns the (3,2) element a value of 1.2.

In Visual Basic by default, you access the lowest element in any dimension as index 0. Thus the lowest corner element in the above example is Expenses(0,0). This can be confusing. So I like to set an option that sets the indexes of an array to be 1-based instead of 0-based. See VBA modules in Excel.

The syntax for invoking Excel worksheet functions is simple. Let <function-name> stand for the name of the worksheet function, and let it be called on arguments arg1, arg2, … . Then

invokes the worksheet function on the arguments arg1, arg2, … .

All VBA macros in Excel reside in modules. To write a macro, you must create a module (or use an existing one). The steps for creating a module are:

• Open the VBA Editor using the menu command Tools>Macro>Visual Basic Editor (Excel 2003 and 2004) or Developer>Visual Basic in Excel 2007.
• The Project Explorer is usually open by default (look for it at the left) but if it isn't, choose the menu command View>Project Explorer to open the Project Explorer.
• Select the file to which you want to add a module
• Choose the menu command Insert>Module
  The module opens; you're now ready to type the macro, or multiple macros if you prefer. There's no restriction on the number of macros in a module.

The Visual Basic Editor pesters you every time you make a mistake, which can be annoying. To turn this off in Excel 2001:

• Choose the menu command Tools>Preferences…
• On the Editor tab of the Preferences dialog, turn off Auto Syntax Check

To turn this off in Excel 2004:

• Choose the menu command Excel>Preferences…
• On the Editor tab of the Preferences dialog, turn off Auto Syntax Check

To turn this off in Excel 2003 or Excel 2007 for Windows:

• Choose the menu command Tools>Options…
• On the Editor tab of the Options dialog, turn off Auto Syntax Check

You have some additional control over the appearance of code in the editor, including font, font size, and colors. I recommend the Consolas (Western) font for Excel 2007. To select it:

• Choose the menu command Tools>Options…
• On the Editor Format tab of the Options dialog, choose your font.

The Visual Basic comment character is single quote ('). At times, you might want to "comment out" several lines at once. Perhaps you have an extended comment that takes multiple lines, or perhaps you want to replace a piece of code with something else, but you want to retain it temporarily as you experiment with the new code. Excel in its Windows versions does have an automatic way to do this, but it isn't visible by default. I recommend that you customize the standard toolbar of your Visual Basic Editor to include the Comment Block tool and the Uncomment Block tool, to make these operations more convenient. Here's how (Sorry, Windows only): Click Right on any toolbar. Choose Customize… from the shortcut menu that appears. The Customize dialog appears. Click the Commands tab. In the Categories box, click Edit. Then scroll the Commands box until you find the two commands Comment Block and Uncomment Block. One by one, drag them onto the Standard Toolbar. Click Close to close the Customize dialog.

Module options are directives to the VBA compiler. They're somewhat technical, but fortunately we need only two. One tells the compiler to require variable declarations, and the other tells the compiler to number array indexes starting at 1 instead of 0. Include these lines at the top of every module:

Altogether, a complete module might look like the example below, which is a function macro that multiplies two ranges, cell by cell. Of course, you would never have to write such a macro, because Excel can perform this operation as array arithmetic. And since it contains no checking for identical shape and size of its two arguments, it's not very safe to use. But here it is:

Sometimes, you write a macro, and it looks correct, but it gives you unexpected results. In this kind of situation, you need a debugging tool. The Visual Basic Editor has a few utilities that help, including breakpoints. Look at the entries for "debugging" in on line help for the Visual Basic Editor.

One very useful technique is to insert in your code a statement that displays the value of some critical variable. The syntax of that statement is

There are a variety of debugging tools in the VBA editor. If you plan to write more macros, it would be a good idea to learn about them. Using on line help, find the Debug tool bar for the VBA editor, and experiment.

If you find yourself managing people who are to write or maintain VBA macros, you might need ways to evaluate their technical competence. Assessing their abilities with respect to debugging and debugging tools is a good way to do this, because it's a useful way to identify people with real experience writing macros. So even if you don't plan to become a macro expert yourself, expertise in the debugging environment can be useful in managing, evaluating and hiring people who do.