Tricks of the Microsoft Office Gurus

2017-07-07 02:10:07

The basic unit of VBA programming is the macro, which is a block of code in a module that you reference as a unit. So far you've seen that there are two types of macros: command macros (also known as Subprocedures) and function macros (or Function procedures).

The Structure of Macro

To recap what you learned earlier, a command macro is allowed to modify its environment, but it can't return a value. Here is the basic structure of a command macro:

Sub MacroName (argument1, argument2, ...) [VBA statements] End Sub

Here, MacroName is the name of the command macro, and arguments is the optional list of values for the arguments accepted by the macro. For example, Listing 11.1 presents a command macro that enters some values for a loan in various worksheet ranges and then adds a formula to calculate the loan payment.

This Chapter's Examples

You'll find the Word and Excel files used as examples in this chapter on my website at www.mcfedries.com/OfficeGurus.

Listing 11.1. A Sample Command Macro

Sub EnterLoanData() Range("A1").Value = .08 Range("A2").Value = 10 Range("A3").Value = 10000 Range("A4").Formula = "=PMT(A1/12, A2*12, A3)" End Sub

A Function macro, on the other hand, doesn't usually modify its environment, but it does return a value. Here is its structure:

Function MacroName (argument1, argument2, ...) [VBA statements] MacroName = returnValue End Function

For example, Listing 11.2 is a function macro that sums two ranges, stores the results in variables named totalSales and totalExpenses (see "Understanding Program Variables," later in this chapter, to learn more about variables), and then uses these values and the fixedCosts argument to calculate the net margin:

Listing 11.2. A Sample Function Macro

Function CalcNetMargin(fixedCosts) totalSales = Application.Sum(Range("Sales")) totalExpenses = Application.Sum(Range("Expenses")) CalcNetMargin = (totalSales - totalExpenses - fixedCosts)/totalSales End Function

Writing Your Own Macro

Although the Macro Recorder makes it easy to create your own homegrown command macros, you can't use it to create function macros, and there are plenty of macro features that you can't access with mouse or keyboard actions or by selecting menu options. In Excel, for example, VBA has a couple of dozen information macro functions that return data about cells, worksheets, workspaces, and more. Also, the VBA control functions enable you to add true programming constructs such as looping, branching, and decision making.

To access these macro elements, you need to write your own VBA routines from scratch. This is easier than it sounds because all you really need to do is enter a series of statements in a module. The problem here, of course, is that you likely don't know any VBA statements yet, so you're not in a position to write your own macros. That's fine, because I'll be introducing you to some VBA a bit later (see "VBA Programming Basics").

For now, let's work through a simple example to illustrate the process. With a module window open and active, follow these steps to write your own command macro:

1.	Place the insertion point where you want to start the macro. (Make sure the insertion point isn't inside an existing macro.)
2.	If you want to begin your macro with a few comments that describe what the macro does, type an apostrophe (') at the beginning of each comment line.
3.	To start the macro, type `Sub`, followed by a space and the name of the macro. For the example, type `HelloWorld`. When you press Enter at the end of this line, VBA automatically adds a pair of parentheses at the end of the macro name. It also tacks on an `End Sub` line to mark the end of the procedure. Your code should now look like this: Sub HelloWorld() End Sub
4.	Between the Sub and End Sub lines, type the VBA statements you want to include in the macro. For the example, type `MsgBox "Hello World!"`. Here's the final macro: Sub HelloWorld() MsgBox "Hello World!" End Sub

Indent Statements for Clarity

To make your code easier to read, you should indent each line by pressing the Tab key at the beginning of the line. Note that VBA preserves the indentation on subsequent lines, so you have to indent only the first line.

When you press Enter to start a new line, VBA analyzes the line you just entered and performs three chores:

It formats the color of each word in the line: by default, VBA keywords are blue, comments are green, errors are red, and all other text is black.

VBA keywords are converted to their proper case. For example, if you type end sub, VBA converts this to End Sub when you press Enter.

It checks for syntax errors. VBA signifies a syntax error either by displaying a dialog box to let you know what the problem is, or by not converting a word to its proper case or color.

Always Enter Keywords in Lowercase

By always entering VBA keywords in lowercase letters, you'll be able to catch typing errors by looking for those keywords that VBA doesn't recognize (in other words, the ones that remain in lowercase).

Running a Command Macro from the Visual Basic Editor

After you create a command macro, you can run it directly from the Visual Basic Editor:

Open the module containing the command macro.

Place the insertion point anywhere inside the macro.

Select Run, Run Sub/User Form or press F5.