Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

Before you can begin creating custom functions, you need to become familiar with the Visual Basic Editor, or VB Editor for short. The VB Editor enables you to work with VBA modules, which are containers for your VBA code.

Activating the VB Editor

When you work in Excel, you can switch to the VB Editor by using either of the following techniques:

• Press Alt+F11.

• Choose Developer Code Visual Basic.

Figure 22-6 shows the VB Editor. Chances are that your VB Editor window won't look exactly like the window shown in the figure. This window is highly customizable. You can hide windows, change their sizes, "dock" them, rearrange them, and so on.

Figure 22-6: The Visual Basic Editor window.

The VB Editor Components

The VB Editor consists of a number of components. I briefly describe some of the key components in the following sections.

MENU BAR

The VB Editor menu bar is like the menu bar for most Windows applications. It contains commands that you use to work with the various components in the VB Editor. The VB Editor also features shortcut menus. Right-click virtually anything in a VB Editor window to get a shortcut menu of common commands.

TOOLBARS

The standard toolbar, directly under the menu bar by default, is one of six VB Editor toolbars that are available. You can customize toolbars, move them around, dock them, display additional toolbars, and so forth.

PROJECT WINDOW

The Project window displays a tree diagram that consists of every workbook that's currently open in Excel (including add-ins and hidden workbooks). In the VB Editor, each workbook is known as a project. I discuss the Project window in more detail in the upcoming section, "Using the Project Window." If the Project window is not visible, press Ctrl+R.

CODE WINDOW

A code window contains VBA code. Just about every item in a project has an associated code window. To view a code window for an object, either double-click the object in the Project window, or select the item and then click the View Code button at the top of the Project window.

For example, to view the code window for the Sheet1 object for a particular workbook, double- click Sheet1 in the Project window. Unless you've added some VBA code, the code window will be empty. I discuss code windows later on in this chapter (see "Using Code Windows").

PROPERTIES WINDOW

The Properties window contains a list of all properties for the selected object. Use this window to examine and change properties. You can use the F4 shortcut key to display the Properties window.

IMMEDIATE WINDOW

The Immediate window is most useful for executing VBA statements directly, testing statements, and debugging your code. This window may or may not be visible. If the Immediate window is not visible, press Ctrl+G. To close the Immediate window, click the Close button on its title bar.

Using the Project Window

When you work in the VB Editor, each Excel workbook and add-in that's currently open is considered a project. You can think of a project as a collection of objects arranged as an outline. You can expand a project by clicking the plus sign (+) at the left of the project's name in the Project window. To contract a project, click the minus sign (−) to the left of a project's name. Figure 22-7 shows the Project window with two projects listed.

Figure 22-7: A Project window with two projects listed.

If you try to expand a project that is password protected, you are prompted to enter the password.

Every project expands to show at least one node called Microsoft Excel Objects. This node expands to show an item for each worksheet and chart sheet in the workbook (each sheet is considered an object), and another object called ThisWorkbook (which represents the Workbook object). If the project has any VBA modules, the project listing also shows a Modules node with the modules listed there. A project may also contain a node called Forms (which contains UserForm objects) and a node called Class Modules (which contain Class Module objects). This book focuses exclusively on standard VBA modules and does not cover the objects contained in the Microsoft Excel Objects node, UserForms node, or Class Modules node.

RENAMING A PROJECT

By default, all projects are named VBAProject. In the Project window, the workbook name appears (in parentheses) next to the project name. For example, a project may appear as follows:

VBAProject (budget.xlsm)

You may prefer to change the name of your project to a more descriptive name. To do so, follow these steps:

1. Select the project in the Project window.

2. Make sure that the Properties window is displayed (press F4 if it's not displayed).

3. Use the Properties window to change the name from VBAProject to something else.

After making the change, the Project window displays the new name.

ADDING A NEW VBA MODULE

A new Excel workbook does not have any VBA modules. To add a VBA module to a project, select the project's name in the Project window and choose Insert Module.

RENAMING A MODULE

VBA modules have default names, such as Module1, Module2, and so on. To rename a VBA module, select it in the Project window, and then change the Name property by using the Properties window. (A VBA module has only one property-Name.) If the Properties window is not visible, press F4 to display it. Figure 22-8 shows a VBA module that is being renamed to modFunctions.

Figure 22-8: Use the Properties window to change the name of a VBA module.

REMOVING A VBA MODULE

If you want to remove a VBA module from a project, select the module's name in the Project window and choose File Remove xxx (where xxx is the name of the module). You are asked whether you want to export the module before removing it. Exporting a module makes a backup file of the module's contents. You can import an exported module into any other project.

Using Code Windows

With the exception of Reference objects, each object in a project has an associated code window. To summarize, these objects can be

• The workbook itself (the item named ThisWorkbook in the Project window)

• A worksheet or chart sheet in a workbook (for example, Sheet1 or Chart1 in the Project window)

• A VBA module (a module that contains general VBA code, including the code for custom worksheet functions)

• A UserForm (a module that contains code for a custom dialog box)

• A Class module (a special type of module that enables you to create new object classes)

• A Reference (a list of references inserted by using the Tools References command)

MINIMIZING AND MAXIMIZING WINDOWS

At any given time, the VB Editor may have lots of code windows. Figure 22-9 shows an example.

Figure 22-9: Code window overload.

Code windows are much like worksheet windows in Excel. You can minimize them, maximize them, hide them, rearrange them, and so on. Most people find that it's much easier to maximize the code window that they're working on. Sometimes, however, you may want to have two or more code windows visible. For example, you may want to compare the code in two modules or copy code from one module to another.

Minimizing a code window gets it out of the way. You also can click the Close button in a code window's title bar to close the window completely. To open it again, just double-click the appropriate object in the Project window.

You can't close a workbook from the VB Editor. You must reactivate Excel and close it from there.

STORING VBA CODE

In general, a module can hold three types of code:

• Sub procedures: A procedure is a set of instructions that performs some action. For example, you may have a Sub procedure that combines various parts of a workbook into a concise report.

• Function procedures: A function is a set of instructions that returns a single value or an array. You can use Function procedures in worksheet formulas.

• Declarations: A declaration is information about a variable that you provide to VBA. For example, you can declare the data type for variables that you plan to use. Declarations go at the top of the module.

A single VBA module can store any number of procedures and declarations.

Entering VBA Code

This section describes the various ways of entering VBA code in a code window. For Function procedures, the code window will always be a VBA module. You can add code to a VBA module in three ways:

• Use your keyboard to type it.

• Use the Excel macro-recorder feature to record your actions and convert them into VBA code.

• Copy the code from another module and paste it into the module that you are working on.

ENTERING CODE MANUALLY

Sometimes, the most direct route is the best one. Type the code by using your keyboard. Entering and editing text in a VBA module works just as you expect. You can select text and copy it, or cut and paste it to another location.

Use the Tab key to indent the lines that logically belong together-for example, the conditional statements between an If and an End If statement. Indentation is not necessary, but it makes the code easier to read.

A single instruction in VBA can be as long as you want. For the sake of readability, however, you may want to break a lengthy instruction into two or more lines. To do so, end the line with a space followed by an underscore character, and then press Enter and continue the instruction on the following line. The following code, for example, is a single statement split over three lines.

If IsNumeric(MyCell) Then _ Result = "Number" Else _ Result = "Non-Number"

Notice that I indented the last two lines of this statement. Doing this is optional, but it helps to clarify the fact that these three lines make up a single statement.

After you enter an instruction, the VB Editor performs the following actions to improve readability:

• It inserts spaces between operators. If you enter Ans=1+2 (without any spaces), for example, VBA converts it to

  Ans = 1 + 2

• The VB Editor adjusts the case of the letters for keywords, properties, and methods. If you enter the following text

  user=application.username

  VBA converts it to

  user = Application.UserName

• Because variable names are not case sensitive, the VB Editor adjusts the names of all variables with the same letters so that their case matches the case of letters that you most recently typed. For example, if you first specify a variable as myvalue (all lowercase) and then enter the variable as MyValue (mixed case), VBA changes all other occurrences of the variable to MyValue. An exception to this occurs if you declare the variable with Dim or a similar statement; in this case, the variable name always appears as it was declared.

• The VB Editor scans the instruction for syntax errors. If it finds an error, it changes the color of the line and may display a message describing the problem. You can set various options for the VB Editor in the Options dialog box (accessible by choosing Tools Options).

USING THE MACRO RECORDER

Another way to get code into a VBA module is to record your actions by using the Excel macro recorder. No matter how hard you try, you cannot record a Function procedure (the type of procedure that is used for a custom worksheet function). All recorded macros are Sub procedures. Using the macro recorder can help you to identify various properties that you can use in your custom functions. For example, turn on the macro recorder to record your actions while you change the username. Follow these steps in Excel:

1. Choose Developer Code Record Macro.

2. In the Record Macro dialog box, accept the default settings and click OK to begin recording. The Record Macro button's caption changes to Stop Recording.

3. Choose Excel Options Popular.

4. Under the Personalize Your Copy of Microsoft Office heading, change the name in the User Name box.

5. Click OK to close the Excel Options dialog box.

6. Choose Developer Code Stop Recording.

7. Press Alt+F11 to activate the VB Editor.

8. In the Project window, select the project that corresponds to your workbook.

9. Double-click the VBA module that contains your recorded code. Generally, this will be the module with the highest number (for example, Module3).

You'll find a VBA procedure that looks something like this:

Sub Macro1() ' ' Macro1 Macro ' ' Application.UserName = "Robert Smith" End Sub

Note that this is a Sub procedure, not a Function procedure. In other words, you can't use this procedure in a worksheet formula. If you examine the code, however, you'll see a reference to the UserName property. You can use this information when creating a Function procedure. For example, the following Function procedure uses the UserName property. This function, when used in a worksheet formula, returns the name of the user.

Function USER() USER = Application.UserName End Function

You can consult the VBA Help system to identify various properties, but using the macro recorder is often more efficient if you don't know exactly what you're looking for. After you identify what you need, you can check the Help system for details.

COPYING VBA CODE

This section has covered entering code directly and recording your actions to generate VBA code. The final method of getting code into a VBA module is to copy it from another module. For example, you may have written a custom function for one project that would also be useful in your current project. Rather than re-enter the code, you can open the workbook, activate the module, and use the normal Clipboard copy-and-paste procedures to copy it into your current VBA module.

You also can copy VBA code from other sources. For example, you may find a listing on a Web page or in a newsgroup. In such a case, you can select the text in your browser (or newsreader), copy it to the Clipboard, and then paste it into a module.

Saving Your Project

As with any application, you should save your work frequently while working in the VB Editor. To do so, use the Save xxxx command (where xxxx is the name of the active workbook), press Ctrl+S, or click the Save button on the standard toolbar.