Understand the VBA objects and procedures Essential VBA Language Elements

Knowledge

Upon completion of today’s session, the student will:

Understand the VBA objects and procedures

Essential VBA Language Elements

Skill

Upon completion of today’s session, a student will be able to:

Execute Sub Procedure and Function

Work with Variables and Arrays

Use labels and comments in VBA code

Introducing the Excel Object Model

Object Properties, Methods, Events

VBA Sub and Function Procedure

Executing Sub procedure

Executing Function Procedure

Essential VBA Language Elements

Using Variables, Constants and Data Types

Using Assignment Statements

Working with Arrays

Using Label

Using Comments in VBA code

Lab work

Lesson 4.1 VBA Object and Procedures

Excel Is an Object? You’ve used Excel for quite a while, but you probably never thought of it as an object. The more you work with VBA, the more you view Excel in those terms. You’ll understand that Excel is an object and that it contains other objects. Those objects, in turn, contain still more objects. In other words, VBA programming involves working with an object hierarchy.

At the top of this hierarchy is the Application object — in this case, Excel itself (the mother of all objects).

Climbing the Object Hierarchy The Application object contains other objects. Following is a list of some of the more useful objects contained in the Excel Application:

✓ Addin

✓ Window

✓ Workbook

✓ WorksheetFunction

Each object contained in the Application object can contain other objects. For example, the following is a list of objects that can be contained in a Workbook object:

✓ Chart (which is a chart sheet)

✓ Name

✓ VBProject

✓ Window

✓ Worksheet

In turn, each of these objects can contain still other objects. Consider a Worksheet object, which is contained in a Workbook object, which is contained in the Application object. Some of the objects that can be contained in a Worksheet object are

✓ Comment

✓ Hyperlink

✓ Name

✓ PageSetup

✓ PivotTable

✓ Range

Put another way, if you want to do something with a range on a particular worksheet, you may find it helpful to visualize that range in the following manner:

Range➪contained in Worksheet➪contained in Workbook➪contained in Excel

Is this beginning to make sense?

When you start digging around, you’ll find that Excel has more objects than you can shake a stick at. Even old-timers like me can get overwhelmed. The good news is that you’ll never have to actually deal with most of these objects. When you’re working on a problem, you can just focus on a few relevant objects — which you can often discover by recording a macro.

Collections are another key concept in VBA programming. A collection is a group of objects of the same type. And to add to the confusion, a collection is itself an object.

Here are a few examples of commonly used collections:

✓ Workbooks: A collection of all currently open Workbook objects

✓ Worksheets: A collection of all Worksheet objects contained in a particular Workbook object

✓ Charts: A collection of all Chart objects (chart sheets) contained in a particular Workbook object

✓ Sheets: A collection of all sheets (regardless of their type) contained in a particular Workbook object

You may notice that collection names are all plural, which makes sense (at least I think so).

“What are collections for?” you may rightfully ask. Well, for example, they are very useful when you want to do stuff with not just one worksheet, but with a couple of them or all of them. As you’ll see, your VBA code can loop through all members of a collection and do something to each one.

Every object has properties. You can think of properties as attributes that describe the object. An object’s properties determine how it looks, how it behaves, and even whether it is visible. Using VBA, you can do two things with an object’s properties:

✓ Examine the current setting for a property.

✓ Change the property’s setting.

methods In addition to properties, objects have methods. A method is an action you perform with an object. A method can change an object’s properties or make the object do something.

This simple example uses the ClearContents method on a Range object to erase the contents of 12 cells on the active sheet:

Sub ClearRange()

Range(“A1:A12”).ClearContents

End Sub

events In this section, I briefly touch on one more topic that you need to know about: events. Objects respond to various events that occur. For example, when you’re working in Excel and you activate a different workbook, a Workbook Activate event occurs. You could, for example, have a VBA macro that is designed to execute whenever an Activate event occurs for a particular Workbook object.

VBA Sub and Function Procedures

Sub procedures Every Sub procedure starts with the keyword Sub and ends with an End Sub statement.

Looking at Function procedures Every Function procedure starts with the keyword Function and ends with an End Function statement.

4) Executing Sub procedure directly

Executing the procedure from the Macro dialog box

Executing a macro by using a shortcut key

Executing the procedure from a button or shape

Executing Function procedures Functions, unlike Sub procedures, can be executed in only two ways:

✓ By calling the function from another Sub procedure or Function procedure

✓ By using the function in a worksheet formula

Calling the function From a Sub procedure Because you can’t execute a function directly, you must call it from another procedure.

Calling a function from a worksheet formula

Lesson 4.2 Programming Concepts

Because VBA is a real, live programming language, it uses many elements common to all programming languages. In this chapter, I introduce you to several of these elements: comments, variables, constants, data types, arrays, and a few other goodies. If you’ve programmed with other languages, some of this material will be familiar. If you’re a programming newbie, it’s time to roll up your sleeves and get busy.

A variable is simply a named storage location in your computer’s memory that’s used by a program. You have lots of flexibility in naming your variables, so make the variable names as descriptive as possible. You assign a value to a variable by using the equal sign operator. (More about this later in the “Using Assignment Statements” section.)

The variable names in these examples appear on both the left and right sides of the equal signs. Note that the last example uses two variables.

x = 1

InterestRate = 0.075

LoanPayoffAmount = 243089

DataEntered = False x = x + 1

UserName = “Bob Johnson”

Date_Started = #3/14/2013#

MyNum = YourNum * 1.25

VBA enforces a few rules regarding variable names:

✓ You can use letters, numbers, and some punctuation characters, but the first character must be a letter.

✓ VBA does not distinguish between uppercase and lowercase letters.

✓ You cannot use any spaces, periods, or mathematical operators in a variable name. ✓ You cannot use the following characters in a variable name: #, $, %, &, or !.

✓ Variable names can be no longer than 255 characters. But nobody even gets close to that limit.

To make variable names more readable, programmers often use mixed case (for example, InterestRate) or the underscore character (interest_rate).

VBA has many reserved words that you can’t use for variable names or procedure names. These include words such as Sub, Dim, With, End, Next, and For. If you attempt to use one of these words as a variable, you may get a compile error (which means your code won’t run). So, if an assignment statement produces an error message, double-check and make sure that the variable name isn’t a reserved word.

VBA does allow you to create variables with names that match names in Excel’s object model, such as Workbook and Range. But, obviously, using names like that just increases the possibility of getting confused. Here’s a perfectly valid (but very confusing) macro that declares Range as a variable name and works with a cell named Range, on a worksheet named Range.

What are VBA’s data types? When I talk about data type, I’m referring to the manner in which a program stores data in memory — for example, as integers, real numbers, or strings. Although VBA can take care of these details automatically. So resist the urge to use a variable named Workbook or Range and use something like MyWorkbook or MyRange instead.

What are VBA’s data types? When I talk about data type, I’m referring to the manner in which a program stores data in memory — for example, as integers, real numbers, or strings. Although VBA can take care of these details automatically, it does so at a cost. (There’s no free lunch.) Letting VBA handle your data typing results in slower execution and inefficient memory use. For small applications, this usually doesn’t present much of a problem. But for large or complex applications, which may be slow or need to conserve every last byte of memory, you need to be on familiar terms with data types.

VBA automatically handles all the data details, which makes life easier for programmers. Not all programming languages provide this luxury. For example, some languages are strictly typed, which means the programmer must explicitly define the data type for every variable used.

VBA does not require that you declare the variables that you use, but it’s definitely a good practice. You’ll see why later in this chapter.

VBA has a variety of built-in data types. Table 7-1 lists the most common types of data that VBA can handle.

In general, choose the data type that uses the smallest number of bytes but can still handle all the data you want to store in the variable.

An exception to the “smallest number of bytes” rule is Long. Most VBA programmers use Long instead of Integer because doing so offers a slight performance increase. But for small procedures, you would never notice any difference between Integer and Long data types.

If you read the previous sections, you now know a bit about variables and data types. In this section, you discover how to declare a variable as a certain data type.

If you don’t declare the data type for a variable you use in a VBA routine, VBA uses the default data type: Variant. Data stored as a variant acts like a chameleon; it changes type depending on what you do with it. For example, if a variable is a Variant data type and contains a text string that looks like a number (such as “143”), you can use this variable for string manipulations as well as numeric calculations. VBA automatically handles the conversion. Letting VBA handle data types may seem like an easy way out — but remember that you sacrifice speed and memory.

Before you use variables in a procedure, it’s an excellent practice to declare your variables — that is, tell VBA each variable’s data type. Declaring your variables makes your macro run faster and use memory more efficiently. The default data type, Variant, causes VBA to repeatedly perform time-consuming checks and reserve more memory than necessary. If VBA knows a variable’s data type, it doesn’t have to investigate and can reserve just enough memory to store the data.

To force yourself to declare all the variables you use, include these two words as the first statement in your VBA module:

Option Explicit

When this statement is present, you won’t be able to run your code if it contains any undeclared variables.

You need to use Option Explicit only once: at the beginning of your module, prior to the declaration of any procedures in the module. Keep in mind that the Option Explicit statement applies only to the module in which it resides. If you have more than one VBA module in a project, you need an Option Explicit statement for each module.

Suppose that you use an undeclared variable (that is, a Variant) named CurrentRate. At some point in your routine, you insert the following statement:

CurentRate = .075

The variable name is misspelled (missing an r) and can be very difficult to spot. If you don’t notice it, Excel will interpret it as a different variable, and it will probably cause your routine to give incorrect results. If you use Option Explicit at the beginning of your module (forcing you to declare the CurrentRate variable), Excel generates an error if it encounters a misspelled variation of that variable.

To ensure that the Option Explicit statement is inserted automatically whenever you insert a new VBA module, turn on the Require Variable Definition option. You find it in the Editor tab of the Options dialog box (in the VBE, choose Tools➪Options). I highly recommend doing so.

Suppose that you use an undeclared variable (that is, a Variant) named CurrentRate. At some point in your routine, you insert the following statement:

CurentRate = .075

Declaring your variables also lets you take advantage of a shortcut that can save some typing. Just type the first two or three characters of the variable name and then press Ctrl+Space. The VBE will either complete the entry for you or — if the choice is ambiguous — show you a list of matching words to select from. In fact, this slick trick works with reserved words and functions, too. Figure 7-1 shows an example of how this works.

You now know the advantages of declaring variables, but how do you do it? The most common way is to use a Dim statement. Here are some examples of variables being declared:

Dim YourName As String

Dim January_Inventory As Double

Dim AmountDue As Double

Dim RowNumber As Long

Dim X

The first four variables are declared as a specific data type. The last variable, X, is not declared as a specific data type, so it’s treated as a Variant (it can be anything).

Besides Dim, VBA has three other keywords that are used to declare variables:

✓ Static

✓ Public

✓ Private

I explain more about the Dim, Static, Public, and Private keywords later on, but first I must cover two other topics that are relevant here: a variable’s scope and a variable’s life.

Recall that a workbook can have any number of VBA modules. And a VBA module can have any number of Sub and Function procedures. A variable’s scope determines which modules and procedures can use the variable. Table has the details.

The lowest level of scope for a variable is at the procedure level. (A procedure is either a Sub or a Function procedure.) Variables declared with this scope can be used only in the procedure in which they are declared. When the procedure ends, the variable no longer exists (it goes to the great big bucket in the sky), and Excel frees up its memory. If you execute the procedure again, the variable comes back to life, but its previous value is lost.

Humorous declarations Topics such as variables, data types, declarations, and scope can be pretty boring, so I came up with some semi-humorous declaration statements for your amusement. These are all valid declarations: Dim King As String, Kong As Long Dim Mouthful as Byte Dim Julian As Boolean Dim Unmarried As Single Dim Dewey As Decimal Dim Trouble As Double Dim WindingRoad As Long Dim Blind As Date Public Nuisance Private FirstClass Static Cling, Electricity Dim BaseballCards As New Collection Dim DentalFloss As String You can probably come up with some others

The most common way to declare a procedure-only variable is with a Dim statement. Dim doesn’t refer to the mental capacity of the VBA designers. Rather, it’s an old programming term that’s short for dimension, which simply means you are setting aside memory for a particular variable. You usually place Dim statements immediately after the Sub or Function statement and before the procedure’s code.

The following example shows some procedure-only variables declared by using Dim statements:

Sub MySub()

Dim x As Integer

Dim First As Long

Dim InterestRate As Single

Dim TodaysDate As Date

Dim UserName As String

Dim MyValue

‘ ... [The procedure’s code goes here] ...

End Sub

Notice that the last Dim statement in the preceding example doesn’t declare a data type for the MyValue variable; it declares only the variable itself. The effect is that the variable MyValue is a Variant.

By the way, you can also declare several variables with a single Dim statement, as in the following example:

Dim x As Integer, y As Integer, z As Integer

Dim First As Long, Last As Double

Unlike some languages, VBA doesn’t allow you to declare a group of variables to be a particular data type by separating the variables with commas. For example, though valid, the following statement does not declare all the variables as Integers:

Dim i, j, k As Integer

In this example, only k is declared to be an Integer; the other variables default to the Variant data type.

If you declare a variable with procedure-only scope, other procedures in the same module can use the same variable name, but each instance of the variable is unique to its own procedure. In general, variables declared at the procedure level are the most efficient because VBA frees up the memory they use when the procedure ends.

Sometimes you want a variable to be available to all procedures in a module. If so, just declare the variable (using Dim or Private) before the module’s first Sub or Function statement — outside any procedures. This is done in the Declarations section, at the beginning of your module. (This is also where the Option Explicit statement is located.)

Figure 7-2 shows how you know when you’re working with the Declarations section. Use the drop-down list on the right and go directly to the Declarations section. Do not pass Go and do not collect $200.

As an example, suppose that you want to declare the CurrentValue variable so that it’s available to all the procedures in your module. All you need to do is use the Dim statement in the Declarations section:

Dim CurrentValue As Double

With this declaration in place — and in the proper place — the CurrentValue variable can be used from any other procedure within the module, and it retains its value from one procedure to another.

If you need to make a variable available to all the procedures in all your VBA modules in a workbook, declare the variable at the module level (in the Declarations section) by using the Public keyword. Here’s an example:

Public CurrentRate As Long

The Public keyword makes the CurrentRate variable available to any procedure in the workbook — even those in other VBA modules. You must insert this statement before the first Sub or Function statement in a module.

If you would like a variable to be available to modules in other workbooks, you must declare the variable as Public and establish a reference to the workbook that contains the variable declaration. Set up a reference by using the Tools➪References command in VBE. In practice, sharing a variable across workbooks is hardly ever done. In fact, I’ve never done it once in my entire VBA programming career. But I guess it’s nice to know that it can be done, in case it ever comes up as a Jeopardy! question.

Normally, when a procedure ends, all the procedure’s variables are reset. Static variables are a special case because they retain their value even when the procedure ends. You declare a static variable at the procedure level. A static variable may be useful if you need to track the number of times you execute a procedure. You can declare a static variable and increment it each time you run the procedure.

As shown in the following example, you declare static variables by using the Static keyword:

Sub MySub()

Static Counter As Integer

Dim Msg As String

Counter = Counter + 1

Msg = “Number of executions: “ & Counter

MsgBox Msg

End Sub

The code keeps track of the number of times the procedure was executed and displays the number in a message box. The value of the Counter variable is not reset when the procedure ends, but it is reset when you close and reopen the workbook.

Even though the value of a variable declared as Static is retained after a variable ends, that variable is unavailable to other procedures. In the preceding MySub procedure example, the Counter variable and its value are available only within the MySub procedure. In other words, it’s a procedure-level variable.

Nothing lives forever, including variables. The scope of a variable not only determines where that variable may be used, it also affects under which circumstances the variable is removed from memory.

You can purge all variables from memory by using three methods:

✓ Click the Reset toolbar button (the little blue square button on the Standard toolbar in the VBE).

✓ Click “End” when a runtime error message dialog box shows up.

✓ Include an End statement anywhere in your code. This is not the same as an End Sub or End Function statement.

Otherwise, only procedure-level variables will be removed from memory when the macro code has completed running. Static variables, module level variables, and global (public) variables all retain their values in between runs of your code.

If you use module-level or global-level variables, make sure they have the value you expect them to have. You never know whether one of the situations I just mentioned may have caused your variables to lose their content!

A variable’s value may (and usually does) change while your procedure is executing. That’s why they call it a variable. Sometimes you need to refer to a value or string that never changes. In such a case, you need a constant — a named element whose value doesn’t change.

As shown in the following examples, you declare constants by using the Const statement. The declaration statement also gives the constant its value:

Const NumQuarters As Integer = 4

Const Rate = .0725, Period = 12

Const ModName As String = “Budget Macros”

Public Const AppName As String = “Budget Application”

Using constants in place of hard-coded values or strings is an excellent programming practice. For example, if your procedure needs to refer to a specific value (such as an interest rate) several times, it’s better to declare the value as a constant and refer to its name rather than the value. This makes your code more readable and easier to change. And if the interest rate changes, you have to change only one statement rather than several.

Like variables, constants have a scope. Keep these points in mind:

✓ To make a constant available within only a single procedure, declare the constant after the procedure’s Sub or Function statement.

✓ To make a constant available to all procedures in a module, declare the constant in the Declarations section for the module.

✓ To make a constant available to all modules in the workbook, use the Public keyword and declare the constant in the Declarations section of any module.

Unlike a variable, the value of a constant does not vary. If you attempt to change the value of a constant in a VBA routine, you get an error. This isn’t too surprising because the value of a constant must remain constant. If you need to change the value of a constant while your code is running, what you really need is a variable.

Excel and VBA contain many predefined constants, which you can use without the need to declare them yourself. The macro recorder usually uses constants rather than actual values. In general, you don’t need to know the value of these constants to use them. The following simple procedure uses a built-in constant (xlCalculationManual) to change the Calculation property of the Application object. (In other words, this changes the Excel recalculation mode to manual.)

Sub CalcManual()

Application.Calculation = xlCalculationManual

End Sub

I discovered the xlCalculationManual constant by recording a macro while I changed the calculation mode. I also could have looked in the Help system and found this:

So the actual value of the built-in xlCalculationManual constant is –4135. Obviously, it’s easier to use the constant’s name than try to remember such an odd value. As you can see, many of the built-in constants are just arbitrary numbers that have special meaning to VBA.

To find the actual value of a built-in constant, use the Immediate window in the VBE and execute a VBA statement such as the following:

? xlCalculationAutomatic

If the Immediate

Excel can work with both numbers and text, so it should come as no surprise that VBA has this same power. Text is often referred to as a string. You can work with two types of strings in VBA:

✓ Fixed-length strings are declared with a specified number of characters. The maximum length is 65,526 characters. That’s a lot of characters! As a point of reference, this chapter contains about half that many characters.

✓ Variable-length strings theoretically can hold as many as two billion characters. If you type five characters per second, it would take you about 760 days to bang out two billion characters — assuming you don’t take any breaks to eat or sleep.

When declaring a string variable with a Dim statement, you can specify the maximum length if you know it (it’s a fixed-length string) or let VBA handle it dynamically (it’s a variable-length string). The following example declares the MyString variable as a string with a maximum length of 50 characters. (Use an asterisk to specify the number of characters, up to the 65,526 character limit.) YourString is also declared as a string, but its length is unspecified:

Dim MyString As String * 50

Dim YourString As String

When declaring a fixed-length string that exceeds 999, do not use a comma in the number that specifies the string size. In fact, never use commas when entering a numeric value in VBA. VBA doesn’t like that.

Another data type you may find useful is Date. You can use a string variable to store dates, but then you can’t perform date calculations. Using the Date data type gives your routines greater flexibility. For example, you might need to calculate the number of days between two dates. This would be impossible (or at least extremely challenging) if you used strings to hold your dates.

A variable defined as a Date can hold dates ranging from January 1, 0100, to December 31, 9999. That’s a span of nearly 10,000 years and more than enough for even the most aggressive financial forecast. You can also use the Date data type to work with time data (VBA lacks a Time data type).

These examples declare variables and constants as a Date data type:

Dim Today As Date

Dim StartTime As Date

Const FirstDay As Date = #1/1/2013#

Const Noon = #12:00:00#

In VBA, place dates and times between two hash marks, as shown in the preceding examples.

Date variables display dates according to your system’s short date format, and they display times according to your system’s time format (either 12- or 24-hour formatting). The Windows Registry stores these settings, and you can modify them via the Regional and Language Options dialog box in the Windows Control Panel. Therefore, the VBA-displayed date or time format may vary, depending on the settings for the system on which the application is running.

When writing VBA code, however, you must use one of the U.S. date formats (such as mm/dd/yyyy). So the following statement assigns a day in October (not November) to the MyDate variable (even if your system is set to use dd/ mm/yyyy for dates):

MyDate = #10/11/2013#

When you display the variable (with the MsgBox function, for example), VBA shows MyDate using your system settings. So if your system uses the dd/mm/ yyyy date format, MyDate will be displayed as 11/10/2013.

An assignment statement is a VBA statement that assigns the result of an expression to a variable or an object. Excel’s Help system defines the term expression as

. . . a combination of keywords, operators, variables, and constants that yields a string, number, or object. An expression can be used to perform a calculation, manipulate characters, or test data. I couldn’t have said it better myself, so I won’t even try.

Much of your work in VBA involves developing (and debugging) expressions. If you know how to create formulas in Excel, you’ll have no trouble creating expressions. With a worksheet formula, Excel displays the result in a cell. A VBA expression, on the other hand, can be assigned to a variable.

Assignment statement examples In the assignment statement examples that follow, the expressions are to the right of the equal sign:

x = 1

x = x + 1

x = (y * 2) / (z * 2)

HouseCost = 375000

FileOpen = True

Range(“TheYear”).Value = 2013

Assignment statement examples In the assignment statement examples that follow, the expressions are to the right of the equal sign:

x = 1

x = x + 1

x = (y * 2) / (z * 2)

HouseCost = 375000

FileOpen = True

Range(“TheYear”).Value = 2013

Expressions can be as complex as you need them to be; use the line continuation character (a space followed by an underscore) to make lengthy expressions easier to read.

Often, expressions use functions: VBA’s built-in functions, Excel’s worksheet functions, or functions that you develop with VBA. I discuss functions in Chapter 9.

About that equal sign As you can see in the preceding example, VBA uses the equal sign as its assignment operator. You’re probably accustomed to using an equal sign as a mathematical symbol for equality. Therefore, an assignment statement like the following may cause you to raise your eyebrows:

z = z + 1

In what crazy universe is z equal to itself plus 1? Answer: No known universe. In this case, the assignment statement (when executed) increases the value of z by 1. So if z is 12, executing the statement makes z equal to 13. Just remember that an assignment uses the equal sign as an operator, not a symbol of equality.

Operators play a major role in VBA. Besides the equal sign operator (discussed in the previous section), VBA provides several other operators. Table 7-3 lists these operators. These should be familiar to you because they are the same operators used in worksheet formulas (except for the Mod operator).

When writing an Excel formula, modulo arithmetic is done using the MOD function. For example, the following formula returns 2 (the remainder when you divide 12 by 5):

=MOD(12,5)

In VBA, the Mod operator is used like this (and z has a value of 2):

z = 12 Mod 5

VBA also provides a full set of logical operators. Of these, Not, And, and Or are most frequently used. I’ve never seen anyone use the other three logical operators.

Most programming languages support arrays. An array is a group of variables that share a common name. You refer to a specific variable in the array by using the array name and an index number in parentheses. For example, you can define an array of 12 string variables to hold the names of the months of the year. If you name the array MonthNames, you can refer to the first element of the array as MonthNames (1), the second element as MonthNames (2), and so on.

Before you can use an array, you must declare it. No exceptions. Unlike normal variables, VBA is very strict about this rule. You declare an array with a Dim or Public statement, just as you declare a regular variable. However, you also need to specify the number of elements in the array. You do this by specifying the first index number, the keyword To, and the last index number — all inside parentheses. The following example shows how to declare an array of 100 integers:

Dim MyArray(1 To 100) As Integer

When you declare an array, you can choose to specify only the upper index. If you omit the lower index, VBA assumes that it’s 0. Therefore, both of the following statements declare the same 101-element array:

Dim MyArray (0 To 100) As Integer

Dim MyArray (100) As Integer

If you want VBA to assume that 1 (rather than 0) is the lower index for your arrays, include the following statement in the Declarations section of your module:

Option Base 1

This statement forces VBA to use 1 as the first index number for arrays that declare only the upper index. If this statement is present, the following statements are identical, both declaring a 100-element array:

Dim MyArray (1 To 100) As Integer

Dim MyArray (100) As Integer

The arrays created in the previous examples are all one-dimensional arrays. Think of one-dimensional arrays as a single line of values. Arrays you create in VBA can have as many as 60 dimensions — although you rarely need more than two or three dimensions in an array. The following example declares an 81-integer array with two dimensions:

Dim MyArray (1 To 9, 1 To 9) As Integer

You can think of this array as occupying a 9 x 9 matrix — perfect for storing all numbers in a Sudoku puzzle.

To refer to a specific element in this array, you need to specify two index numbers (similar to its “row” and its “column” in the matrix). The following example shows how you can assign a value to an element in this array:

MyArray (3, 4)= 125

This statement assigns a value to a single element in the array. If you’re thinking of the array in terms of a 9 x 9 matrix, this assigns 125 to the element located in the third row and fourth column of the matrix.

Here’s how to declare a three-dimensional array, with 1,000 elements:

Dim My3DArray (1 To 10, 1 To 10, 1 To 10) As Integer

You can think of a three-dimensional array as a cube. Visualizing an array of more than three dimensions is more difficult. Sorry, I haven’t yet mastered the fourth dimension and beyond

You can also create dynamic arrays. A dynamic array doesn’t have a preset number of elements. Declare a dynamic array with an empty set of parentheses:

Dim MyArray () As Integer

Before you can use this array, you must use the ReDim statement to tell VBA how many elements the array has. Usually, the number of elements in the array is determined while your code is running. You can use the ReDim statement any number of times, changing the array’s size as often as needed. The following example demonstrates how to change the number of elements in a dynamic array. It assumes that the NumElements variable contains a value, which your code calculated.

ReDim MyArray (1 To NumElements)

When you redimension an array by using ReDim, you wipe out any values currently stored in the array elements. You can avoid destroying the old values by using the Preserve keyword. The following example shows how you can preserve an array’s values when you redimension the array:

ReDim Preserve MyArray(1 To NumElements)

If MyArray currently has ten elements and you execute the preceding statement with NumElements equaling 12, the first ten elements remain intact, and the array has room for two additional elements (up to the number contained in the variable NumElements). If NumElements equals 7 however, the first seven elements are retained but the remaining three elements meet their demise.

In early versions of BASIC, every line of code required a line number. For example, if you had written a BASIC program in the ’70s (dressed, of course, in your bell bottoms), it may have looked something like this:

010: LET X=5

020: LET Y=3

030: LET Z=X*Y

040: PRINT Z

050: END

VBA permits the use of such line numbers, and it even permits text labels. You don’t typically use a label for each line, but you may occasionally need to use a label. For example, insert a label if you use a GoTo statement (which I discuss in Chapter 10). A label must begin with the first nonblank character in a line and end with a colon.

The information in this chapter becomes clearer as you read subsequent chapters. If you want to find out more about VBA language elements, I refer you to the VBA Help system. You can find as much detail as you need, or care, to know.

A comment is the simplest type of VBA statement. Because VBA ignores these statements, they can consist of anything you want. You can insert a comment to remind yourself why you did something or to clarify some particularly elegant code you wrote.

Use comments liberally and extensively to describe what the code does (which isn’t always obvious by reading the code itself). Often, code that makes perfect sense today mystifies you tomorrow. Been there. Done that.

You begin a comment with an apostrophe (‘). VBA ignores any text that follows an apostrophe in a line of code. You can use a complete line for your comment or insert your comment at the end of a line of code. The following example shows a VBA procedure with four comments:

Sub FormatCells()

‘ Exit if a range is not selected

If TypeName(Selection) <> “Range” Then

MsgBox “Select a range.”

Exit Sub End If

‘ Format the cells

With Selection

.HorizontalAlignment = xlRight .WrapText = False

‘ no wrap

.MergeCells = False

‘ no merged cells

End With

End Sub

Q. 1 Write a program to demonstrate use of static variables.

Q. 2 Write a program that uses comments.

Q. 3 Write a VBA procedure to show length of a string.

Q. 4 Write a VBA procedure to show month name of a given month number.

Q. 5 Write a VBA procedure to show file size of a file.

Write a VBA procedure to show file size of a file.

Write a VBA procedure to check the number of times that specific procedure was run by the user.

Write 2 VBA procedures (say proc_Example1 and proc_Example2) and both of them should be able to manipulate the value of a global variable (say globalVar1).

A Range object represents a range contained in a Worksheet object. Range objects, like all other objects, have properties (which you can examine and sometimes change) and methods (which perform actions on the object).

A Range object can be as small as a single cell (for example, B4) or as large as every one of the 17,179,869,184 cells in a worksheet (A1:XFD1048576).

When you refer to a Range object, the address is always surrounded by double quotes, like this:

Range(“A1:C5”)

If the range consists of one cell, you still need the quotes:

Range(“K9”)

If the range happens to have a name (created by using the Formulas➪Defined Names➪Define Name command in Excel), you can use an expression like this:

Range(“PriceList”)

The Ways to Refer to a Range

Range Object Properties

Range Object Methods

Built-In VBA Functions

Worksheet Functions in VBA

Custom Functions

GoTo Statement

The If-Then structure

The Select Case structure

Lab work

A Range object can be as small as a single cell (for example, B4) or as large as every one of the 17,179,869,184 cells in a worksheet (A1:XFD1048576).

When you refer to a Range object, the address is always surrounded by double quotes, like this:

Range(“A1:C5”)

If the range consists of one cell, you still need the quotes:

Range(“K9”)

If the range happens to have a name (created by using the Formulas➪Defined Names➪Define Name command in Excel), you can use an expression like this:

Range(“PriceList”)

Unless you tell Excel otherwise by qualifying the range reference, it assumes that you’re referring to a range on the active worksheet. If anything other than a worksheet is active (such as a chart sheet), the range reference fails, and your macro displays an error message and stops running.

As shown in the following example, you can refer to a range outside the active sheet by qualifying the range reference with a worksheet name from the active workbook:

Worksheets(“Sheet1”).Range(“A1:C5”)

If you need to refer to a range in a different workbook (that is, any workbook other than the active workbook), you can use a statement like this:

Workbooks(“Budget.xlsx”).Worksheets(“Sheet1”).Range(“A1:C5”)

A Range object can consist of one or more entire rows or columns. You can refer to an entire row (in this case, row 3) by using syntax like this:

Range(“3:3”)

You can refer to an entire column (the fourth column in this example) like this:

Range(“D:D”)

In Excel, you select noncontiguous ranges by holding down the Ctrl key while selecting various ranges with your mouse. Figure 8-1 shows a noncontiguous range selection. You shouldn’t be surprised that VBA also lets you work with noncontiguous ranges. The following expression refers to a two-area noncontiguous range. Notice that a comma separates the two areas.

Range(“A1:B8,D9:G16”)

Be aware that some methods and properties cause havoc with noncontiguous ranges. You may have to process each area separately by using a loop.

The more you work with VBA, the more you realize that it’s a fairly well conceived language and is usually quite logical (despite what you may be thinking right now). Often, VBA provides multiple ways to perform an action. You can choose the most appropriate solution for your problem. This section discusses some of the other ways to refer to a range.

This chapter barely scratches the surface for the Range object’s properties and methods. As you work with VBA, you’ll probably need to access other properties and methods. The Help system is the best place to find out about them, but it’s also a good idea to record your actions and examine the code Excel generates. You’re probably getting tired of hearing that advice by now, but it really is good advice.

Rather than use the VBA Range keyword, you can refer to a range via the Cells property.

Notice that I wrote Cells property, not Cells object or even Cells collection. Although Cells may seem like an object (or a collection), it’s really not. Rather, Cells is a property that VBA evaluates. VBA then returns an object (more specifically, a Range object). If this seems strange, don’t worry. Even Microsoft appears to be confused about this issue. In some earlier versions of Excel, the Cells property was known as the Cells method. Regardless of what it is, just understand that Cells is a handy way to refer to a range.

The Cells property takes two arguments: a row number and a column number. Both of these arguments are numbers, even though we usually refer to columns by using letters. For example, the following expression refers to cell C2 on Sheet2:

Worksheets(“Sheet2”).Cells(2, 3)

You can also use the Cells property to refer to a multi-cell range. The following example demonstrates the syntax you use:

Range(Cells(1, 1), Cells(10, 8))

This expression refers to an 80-cell range that extends from cell A1 (row 1, column 1) to cell H10 (row 10, column 8).

The following statements both produce the same result; they enter a value of 99 into a 10-by-8 range of cells. More specifically, these statements set the Value property of the Range object:

Range(“A1:H10”).Value = 99 Range(Cells(1, 1), Cells(10, 8)).Value = 99

The advantage of using the Cells property to refer to ranges becomes apparent when you use variables rather than actual numbers as the Cells arguments. And things really start to click when you understand looping, which I cover in Chapter 10.

The Offset property provides another handy means for referring to ranges. This property, which operates on a Range object and returns another Range object, lets you refer to a cell that is a particular number of rows and columns away from another cell.

Like the Cells property, the Offset property takes two arguments. The first argument represents the number of rows to offset; the second represents the number of columns to offset.

The following expression refers to a cell one row below cell A1 and two columns to the right of cell A1. In other words, this refers to the cell commonly known as C2:

Range(“A1”).Offset(1, 2)

The Offset property can also use negative arguments. A negative row offset refers to a row above the range. A negative column offset refers to a column to the left of the range. The following example refers to cell A1:

Range(“C2”).Offset(-1, -2)

And, as you may expect, you can use 0 as one or both of the arguments for Offset. The following expression refers to cell A1:

Range(“A1”).Offset(0, 0)

Here’s a statement that inserts the time of day into the cell to the right of the active cell:

ActiveCell.Offset(0,1) = Time

When you record a macro in relative mode, Excel uses the Offset property quite a bit. Refer back to Chapter 6 for an example.

The Offset property is most useful when you use variables rather than actual values for the arguments. In Chapter 10, I present some examples that demonstrate this.

A Range object has dozens of properties. You can write VBA programs nonstop for the next 12 months and never use them all. In this section, I briefly describe some of the more commonly used Range properties. For complete details, consult the Help system in the VBE.

Some Range properties are read-only properties, which means that your code can look at their values, but it can’t change them (“look, but don’t touch”). For example, every Range object has an Address property, which holds the range’s address. You can access this read-only property, but you can’t change it — which makes perfect sense when you think about it.

By the way, the examples that follow are typically statements rather than complete procedures. If you’d like to try any of these (and you should), create a Sub procedure to do so. Also, many of these statements work properly only if a worksheet is the active sheet.

The Value property The Value property represents the value contained in a cell. It’s a read-write property, so your VBA code can either read or change the value.

The following statement displays a message box that shows the value in cell A1 on Sheet1:

MsgBox Worksheets(“Sheet1”).Range(“A1”).Value

It stands to reason that you can read the Value property only for a single-cell Range object. For example, the following statement generates an error:

MsgBox Worksheets(“Sheet1”).Range(“A1:C3”).Value

You can, however, change the Value property for a range of any size. The following statement enters the number 123 into each cell in a range:

Worksheets(“Sheet1”).Range(“A1:C3”).Value = 123

Value is the default property for a Range object. In other words, if you omit a property for a Range, Excel uses its Value property. The following statements both enter a value of 75 into cell A1 on the active worksheet:

Range(“A1”).Value = 75 Range(“A1”) = 75

I wasn’t being fully truthful when I wrote “you can read the Value property only for a singlecell Range object.” In fact, you can assign the values in a multi-cell range to a variable, as long as the variable is a variant. That’s because a variant can act like an array. Here’s an example: Dim x As Variant x = Range(“A1:C3”).Value Then you can treat the x variable as if it were an array. This statement, for example, returns the value in cell B1: MsgBox x(1, 2)

The Text property returns a string that represents the text as it’s displayed in a cell — the formatted value. The Text property is read-only. For example, suppose that cell A1 contains the value 12.3 and is formatted to display two decimals and a dollar sign ($12.30). The following statement displays a message box containing $12.30:

MsgBox Worksheets(“Sheet1”).Range(“A1”).Text

But the next statement displays a message box containing 12.3:

MsgBox Worksheets(“Sheet1”).Range(“A1”).Value

If the cell contains a formula, the Text property returns the result of the formula. If a cell contains text, then the Text property and the Value property will always return the same thing, because text (unlike a number) can’t be formatted to display differently.

The Count property The Count property returns the number of cells in a range. It counts all cells, not just the nonblank cells. Count is a read-only property, just as you would expect. The following statement accesses a range’s Count property and displays the result (9) in a message box:

MsgBox Range(“A1:C3”).Count

The Column property returns the column number of a single-cell range. Its sidekick, the Row property, returns the row number of a single-cell range. Both are read-only properties. For example, the following statement displays 6 because cell F3 is in the sixth column:

MsgBox Sheets(“Sheet1”).Range(“F3”).Column

The next expression displays 3 because cell F3 is in the third row:

MsgBox Sheets(“Sheet1”).Range(“F3”).Row

If the Range object consists of more than one cell, the Column property returns the column number of the first column in the range, and the Row property returns the row number of the first row in the range

Don’t confuse the Column and Row properties with the Columns and Rows properties (discussed earlier in this chapter). The Column and Row properties return a single value. The Columns and Rows properties, on the other hand, return a Range object. What a difference an “s” makes.

The Address property Address, a read-only property, displays the cell address for a Range object as an absolute reference (a dollar sign before the column letter and before the row number). The following statement displays the message box shown in Figure 8-2:

MsgBox Range(Cells(1, 1), Cells(5, 5)).Address

The HasFormula property The HasFormula property (which is read-only) returns True if the single-cell range contains a formula. It returns False if the cell does not have a formula. If the range consists of more than one cell, VBA returns True only if all cells in the range contain a formula, or False if all cells in the range don’t have a formula. The property returns Null if there is a mixture of formulas and nonformulas in the range. Null is kind of a no-man’s land: The answer is neither True nor False; any cell in the range may or may not have a formula.

You need to be careful when you work with properties that can return Null. More specifically, the only data type that can deal with Null is Variant.

For example, assume that cell A1 contains a value and cell A2 contains a formula. The following statements generate an error because the range doesn’t consist of all formulas or all nonformulas:

Dim FormulaTest As Boolean FormulaTest = Range(“A1:A2”).HasFormula

The Boolean data type can handle only True or False. Null causes Excel to complain and display an error message. To fix this type of situation, the best thing to do is make sure that the FormulaTest variable is declared as a Variant rather than as a Boolean. The following example uses VBA’s handy TypeName function (along with an If-Then-Else construct) to determine the data type of the FormulaTest variable. If the range has a mixture of formulas and nonformulas, the message box displays Mixed! Otherwise it displays either True or False.

As I note earlier in this chapter (see “The Cells property”), a property can return an object. The Font property of a Range object is another example of that concept at work. The Font property returns a Font object.

A Font object, as you may expect, has many accessible properties. To change some aspect of a range’s font, you must first access the range’s Font object and then manipulate the properties of that object. This may be confusing, but maybe this example will help.

The following statement uses the Font property of the Range object to return a Font object. Then the Bold property of the Font object is set to True. In plain English, this statement makes the cell display in boldface:

Range(“A1”).Font.Bold = True

Truth is, you don’t really need to know that you’re working with a special Font object that’s contained in a Range object. As long as you use the proper syntax, it will work just fine. Often, recording your actions while you record a macro will tell you everything you need to know about the proper syntax.

The Interior property Here’s yet another example of a property that returns an object. A Range object’s Interior property returns an Interior object (strange name, but that’s what it’s called). This type of object referencing works the same way as the Font property (which I describe in the preceding section).

For example, the following statement changes the Color property of the Interior object contained in the Range object:

Range(”A1”).Interior.Color = 8421504

In other words, this statement changes the cell’s background to middle gray. What’s that? You didn’t know that 8421504 is middle gray? For some insights into Excel’s wonderful world of color, see the nearby sidebar “A quick and dirty color primer.”

The Formula property The Formula property represents the formula in a cell. This is a read-write property, so you can access it to either view the formula in a cell or insert a formula into a cell. For example, the following statement enters a SUM formula into cell A13:

Range(“A13”).Formula = “=SUM(A1:A12)”

Notice that the formula is a text string and is enclosed in quotation marks.

If the formula itself contains quotation marks, things get a bit tricky. For example, let’s say you want to insert this formula by using VBA:

=SUM(A1:A12)&” Stores”

This formula displays a value, followed by the word Stores. To make this formula acceptable, you need to replace every quotation mark in the formula with two quotation marks. Otherwise, VBA will get confused and claim that there’s a syntax error (because there is!). So here’s a statement that will enter a formula that contains quotes:

Range(”A13”).Formula = ”=SUM(A1:A12)&”” Stores”””

By the way, you can access a cell’s Formula property even if the cell doesn’t have a formula. If a cell has no formula, the Formula property returns the same as its Value property.

If you need to know whether a cell has a formula, use the HasFormula property (discussed earlier in this chapter).

Be aware that VBA “speaks” U.S. English. This means that in order to put a formula in a cell, you must use the U.S. syntax. If you use a non-English version of Excel, read up on the FormulaLocal property.

The NumberFormat property The NumberFormat property represents the number format (expressed as a text string) of the Range object. This is a read-write property, so your VBA code can either examine the number format or change it. The following statement changes the number format of column A to a percent with two decimal places:

Columns(“A:A”).NumberFormat = “0.00%”

Follow these steps to see a list of other number formats. Better yet, turn on the macro recorder while you do this:

1. Activate a worksheet. 2. Access the Format Cells dialog box by pressing Ctrl+1. 3. Click the Number tab. 4. Select the Custom category to view and apply some additional number format strings.

As you know, a VBA method performs an action. A Range object has dozens of methods but, again, you won’t need most of these. In this section, I point out some of the more commonly used Range object methods.

The Select method

The Copy and Paste methods

The Clear method

The Delete method

Use the Select method to select a range of cells. The following statement selects a range on the active worksheet:

Range(“A1:C12”).Select

Before selecting a range, it’s often a good idea to use one additional statement to ensure that the correct worksheet is active. For example, if Sheet1 contains the range you want to select, use the following statements to select the range:

Sheets(“Sheet1”).Activate Range(“A1:C12”).Select

Contrary to what you may expect, the following statement generates an error if Sheet1 is not already the active sheet. In other words, you must use two statements rather than just one: one to activate the sheet and another to select the range.

Sheets(“Sheet1”).Range(“A1:C12”).Select

If you use the GoTo method of the Application object to select a range, you can forget about selecting the correct worksheet first. This statement activates Sheet1 and then selects the range:

Application.Goto Sheets(“Sheet1”).Range(“A1:C12”)

The GoTo method is the VBA equivalent of pressing F5 in Excel, which displays the GoTo dialog box.

You can perform copy and paste operations in VBA by using the Copy and Paste methods. Note that two different objects come into play. The Copy method is applicable to the Range object, but the Paste method applies to the Worksheet object. It actually makes sense: You copy a range and paste it to a worksheet.

The Clear method deletes the contents of a range, plus all the cell formatting. For example, if you want to zap everything in column D, the following statement does the trick:

Columns(“D:D”).Clear

You should be aware of two related methods. The ClearContents method deletes the contents of the range but leaves the formatting intact. The ClearFormats method deletes the formatting in the range but not the cell contents.

Clearing a range differs from deleting a range. When you delete a range, Excel shifts the remaining cells around to fill up the range you deleted.

The following example uses the Delete method to delete row 6:

Rows(“6:6”).Delete

When you delete a range that’s not a complete row or column, Excel needs to know how to shift the cells. (To see how this works, experiment with the Excel Home➪Cells➪Delete command.)

The following statement deletes a range and then fills the resulting gap by shifting the other cells to the left:

Range(“C6:C10”).Delete xlToLeft

The Delete method uses an argument that indicates how Excel should shift the remaining cells. In this case, I use a built-in constant (xlToLeft) for the argument. I could also use xlUp, another named constant.

Except for a few people who think Excel is a word processor, all Excel users incorporate worksheet functions in their formulas. The most common worksheet function is the SUM function, and you have hundreds of others at your disposal.

A function essentially performs a calculation and returns a single value. The SUM function, of course, returns the sum of a range of values. The same holds true for functions used in your VBA expressions: Each function does its thing and returns a single value.

The functions you use in VBA can come from three sources:

✓ Built-in functions provided by VBA

✓ Worksheet functions provided by Excel

✓ Custom functions that you (or someone else) write, using VBA

VBA provides numerous built-in functions. Some of these functions take arguments, and some do not.

VBA function examples In this section, I present a few examples of using VBA functions in code. In many of these examples, I use the MsgBox function to display a value in a message box. Yes, MsgBox is a VBA function — a rather unusual one, but a function nonetheless. This useful function displays a message in a pop-up dialog box and also returns a value. For more details about the MsgBox function, see Chapter 15.

VBA provides numerous built-in functions. Some of these functions take arguments, and some do not.

VBA functions that do more than return a value A few VBA functions go above and beyond the call of duty. Rather than simply return a value, these functions have some useful side effects. Table lists them.

MsgBox Displays a handy dialog box containing a message and buttons. The function returns a code that identifies which button the user clicks. See Chapter 15 for details.

InputBox Displays a simple dialog box that asks the user for some input. The function returns whatever the user enters into the dialog box. I discuss this in Chapter 15.

Shell Executes another program. The function returns the task ID (a unique identifier) of the other program (or an error if the function can’t start the other program).

The first example uses VBA’s Date function to display the current system date in a message box:

Sub ShowDate()

MsgBox “Today is: “ & Date

End Sub

Notice that the Date function doesn’t use an argument. Unlike worksheet functions, a VBA function with no argument doesn’t require an empty set of parentheses. In fact, if you type an empty set of parentheses, the VBE will promptly remove them.

To get the system time, use the Time function. And if you want it all, use the Now function to return both the date and the time.

Finding a string length The following procedure uses the VBA Len function, which returns the length of a text string. The Len function takes one argument: the string. When you execute this procedure, the message box displays your name, and the number of characters in your name (see Figure 9-1).

Sub GetLength()

Dim MyName As String

Dim StringLength As Integer

MyName = Application.UserName

StringLength = Len(MyName)

MsgBox MyName & “ has “ & StringLength & “ characters.”

End Sub

Excel also has a LEN function, which you can use in your worksheet formulas. The Excel version and the VBA function work the same.

Displaying the name of a month The following procedure uses the MonthName function, which returns the name of a month. MonthName uses one argument, an integer between 1 and 12.

Sub ShowMonthName()

Dim ThisMonth As Long

ThisMonth = Month(Date)

MsgBox MonthName(ThisMonth)

End Sub

This procedure uses the Month function to get the current month (as a value), and this value is assigned to the ThisMonth variable. The MonthName function then converts the value to text. So if you run this procedure in April, the message box displays the text April.

Actually, the ThisMonth variable isn’t required. You can get the same effect with this expression that uses three VBA functions:

MonthName(Month(Date))

Actually, the ThisMonth variable isn’t required. You can get the same effect with this expression that uses three VBA functions:

MonthName(Month(Date))

Here, the current date is passed as an argument to the Month function, which returns a value that is passed as an argument to the MonthName function.

Determining a file size The following Sub procedure displays the size, in bytes, of the Excel executable file. It finds this value by using the FileLen function.

Sub GetFileSize() Dim TheFile As String TheFile = “C:\Program Files (x86)\Microsoft Office\Office15\EXCEL.EXE” MsgBox FileLen(TheFile) End Sub

Notice that this routine hard codes the filename (that is, it explicitly states the path). This isn’t a good idea. The file might not be on the C drive, or the Excel folder may have a different name. The following statement shows a better approach:

TheFile = Application.Path & “\EXCEL.EXE”

Path is a property of the Application object. It simply returns the name of the folder in which the application (that is, Excel) is installed (without a trailing backslash).

Identifying the type of a selected object The following procedure uses the TypeName function, which returns the type of the selection on the worksheet (as a string):

Sub ShowSelectionType()

Dim SelType As String

SelType = TypeName(Selection)

MsgBox SelType

End Sub

The selection could be a Range, a Picture, a Rectangle, a ChartArea, or any other type of object that can be selected.

The TypeName function is very versatile. You can also use this function to determine the data type of a variable.

Function What It Does

MsgBox Displays a handy dialog box containing a message and buttons. The function returns a code that identifies which button the user clicks. See Chapter 15 for details.

InputBox Displays a simple dialog box that asks the user for some input. The function returns whatever the user enters into the dialog box. I discuss this in Chapter 15.

Shell Executes another program. The function returns the task ID (a unique identifier) of the other program (or an error if the function can’t start the other program).

How do you find out which functions VBA provides? Good question. The best source is the Excel Visual Basic Help system. Another way is to type VBA, followed by a period. You get a list of items, as shown in Figure 9-2. Those with a green icon are functions. If this feature isn’t working, choose the VBE’s Tools➪Options command, click the Editor tab, and place a checkmark next to Auto List Members.

I compiled a partial list of functions, which I share with you in Table 9-2. I omitted some of the more specialized or obscure functions.

For complete details on a particular function, type the function name into a VBA module, move the cursor anywhere in the text, and press F1.

Although VBA offers a decent assortment of built-in functions, you might not always find exactly what you need. Fortunately, you can also use most of Excel’s worksheet functions in your VBA procedures. The only worksheet functions that you cannot use are those that have an equivalent VBA function.

VBA makes Excel’s worksheet functions available through the WorksheetFunction object, which is contained in the Application object. Here’s an example of how you can use Excel’s SUM function in a VBA statement:

Total = Application.WorksheetFunction.Sum(Range(“A1:A12”))

Calculating a mortgage payment The next example uses the PMT worksheet function to calculate a mortgage payment. I use three variables to store the data that’s passed to the Pmt function as arguments. A message box displays the calculated payment.

Sub PmtCalc()

Dim IntRate As Double

Dim LoanAmt As Double

Dim Periods As Integer

IntRate = 0.0825 / 12 Periods = 30 * 12

LoanAmt = 150000

MsgBox WorksheetFunction.PMT(IntRate, Periods, -LoanAmt)

End Sub

As the following statement shows, you can also insert the values directly as the function arguments:

MsgBox WorksheetFunction.PMT(0.0825 /12, 360, -150000)

However, using variables to store the parameters makes the code easier to read and modify, if necessary.

Using a lookup function The following example uses VBA’s InputBox and MsgBox functions, plus Excel’s VLOOKUP function. It prompts for a part number and then gets the price from a lookup table. In Figure 9-4, range A1:B13 is named PriceList.

Sub GetPrice()

Dim PartNum As Variant

Dim Price As Double

PartNum = InputBox(“Enter the Part Number”)

Sheets(“Prices”).Activate

Price = WorksheetFunction.VLOOKUP(PartNum, Range(“PriceList”), 2, False) MsgBox PartNum & “ costs “ & Price

End Sub

The procedure starts this way:

1. VBA’s InputBox function asks the user for a part number.

2. This statement assigns the part number the user enters for the PartNum variable.

3. The next statement activates the Prices worksheet, just in case it’s not already the active sheet.

4. The code uses the VLOOKUP function to find the part number in the table. Notice that the arguments you use in this statement are the same as those you would use with the function in a worksheet formula. This statement assigns the result of the function to the Price variable.

5. The code displays the price for the part via the MsgBox function. This procedure doesn’t have any error handling, and it fails miserably if you enter a nonexistent part number. (Try it.) If this were an actual application that’s used in an actual business, you would want to add some statements that deal with errors more gracefully. I discuss error handling later.

You can’t use the Excel Paste Function dialog box to insert a worksheet function into a VBA module. Instead, enter such functions the old-fashioned way: by hand. However, you can use the Paste Function dialog box to identify the function you want to use and find out about its arguments.

You can also take advantage of the VBE’s Auto List Members option, which displays a drop-down list of all worksheet functions. Just type Application. WorksheetFunction, followed by a period. Then you’ll see an list of the function you can use, as shown in Figure 9-5. If this feature isn’t working, choose the VBE’s Tools➪Options command, click the Editor tab, and place a checkmark next to Auto List Members.

Newcomers to VBA often confuse VBA’s built-in functions and Excel’s workbook functions. A good rule to remember is that VBA doesn’t try to reinvent the wheel. For the most part, VBA doesn’t duplicate Excel worksheet functions.

For most worksheet functions that are unavailable as methods of the WorksheetFunction object, you can use an equivalent VBA built-in operator or function. For example, the MOD worksheet function is not available in the WorksheetFunction object because VBA has an equivalent, its built-in Mod operator.

Bottom line? If you need to use a function, first determine whether VBA has something that meets your needs. If not, check out the worksheet functions. If all else fails, you may be able to write a custom function by using VBA.

The following procedure uses the VBA Len function, which returns the length of a text string. The Len function takes one argument: the string. When you execute this procedure, the message box displays your name, and the number of characters in your name (see Figure 9-1).

Sub GetLength()

Dim MyName As String

Dim StringLength As Integer

MyName = Application.UserName

StringLength = Len(MyName)

MsgBox MyName & “ has “ & StringLength & “ characters.”

End Sub

Sub GetLength()

Dim MyName As String

Dim StringLength As Integer

MyName = Application.UserName

StringLength = Len(MyName)

MsgBox MyName & “ has “ & StringLength & “ characters.”

End Sub

Sub ShowMax()

Dim TheMax As Double

TheMax = WorksheetFunction.MAX(Range(“A:A”))

MsgBox TheMax

End Sub

You can use the MIN function to get the smallest value in a range. And as you might expect, you can use other worksheet functions in a similar manner. For example, you can use the LARGE function to determine the kth-largest value in a range. The following expression demonstrates this:

SecondHighest = WorksheetFunction.LARGE(Range(“A:A”),2)

Notice that the LARGE function uses two arguments. The second argument represents the kth part — 2, in this case (the second-largest value).

The next example uses the PMT worksheet function to calculate a mortgage payment. Three variables are used to store the data that’s passed to the Pmt function as arguments. A message box displays the calculated payment.

Sub PmtCalc()

Dim IntRate As Double

Dim LoanAmt As Double

Dim Periods As Long

IntRate = 0.0625 / 12

Periods = 30 * 12

LoanAmt = 150000

MsgBox WorksheetFunction.PMT(IntRate, Periods, -LoanAmt)

End Sub

The following example uses VBA’s InputBox and MsgBox functions, plus Excel’s VLOOKUP function. It prompts for a part number and then gets the price from a lookup table. Below, range A1:B13 is named PriceList.

The range, named PriceList, contains prices for parts.

Sub GetPrice()

Dim PartNum As Variant

Dim Price As Double

PartNum = InputBox(“Enter the Part Number”)

Sheets(“Prices”).Activate

Price = WorksheetFunction.VLOOKUP(PartNum, Range(“PriceList”), 2, False)

MsgBox PartNum & “ costs “ & Price

End Sub

Here’s how the GetPrice procedure works:

VBA’s InputBox function asks the user for a part number.

The part number the user enters is assigned to the PartNum variable.

The next statement activates the Prices worksheet, just in case it’s not already the active sheet.

The code uses the VLOOKUP function to find the part number in the table.

Notice that the arguments you use in this statement are the same as those you would use with the function in a worksheet formula. This statement assigns the result of the function to the Price variable.

The code displays the price for the part via the MsgBox function.

I’ve covered VBA functions and Excel worksheet functions. The third category of functions you can use in your VBA procedures is custom functions. A custom function (also known as a User Defined Function, or UDF) is one you develop yourself by using (what else?) VBA. To use a custom function, you must define it in the workbook in which you use it — or else define the functions in an add-in.

Here’s an example of defining a simple Function procedure and then using it in a VBA Sub procedure:

Function MultiplyTwo(num1, num2) As Double

MultiplyTwo = num1 * num2

End Function

Sub ShowResult()

Dim n1 As Double, n2 As Double

Dim Result As Double

n1 = 123 n2 = 544

Result = MultiplyTwo(n1, n2)

MsgBox Result

End Sub

The custom function MultiplyTwo has two arguments. The ShowResult Sub procedure uses this Function procedure by passing two arguments to it (in parentheses). The ShowResult procedure then displays a message box showing the value returned by the MultiplyTwo function.

I probably don’t have to tell you that the MultiplyTwo function is fairly useless. It’s much more efficient to perform the multiplication in the ShowResult Sub procedure. I include it simply to give you an idea of how a Sub procedure can make use of a custom function.

You can also use custom functions in your worksheet formulas. For example, if MultiplyTwo is defined in your workbook, you can write a formula such as this one:

=MultiplyTwo(A1,A2)

This formula returns the product of the values in cells A1 and A2.

Creating custom worksheet functions is an important (and very useful) topic. So important (and useful) that I devote an entire chapter to it. See Chapter 20 for some examples that are actually useful.

You are undoubtedly familiar with Excel’s worksheet functions — even Excel novices know how to use common worksheet functions such as SUM, AVERAGE, and IF. By my count, Excel contains more than 300 predefined worksheet functions, not counting those available through add-ins, such as the Analysis ToolPak (which is included with Excel). And if that’s not enough, you can create functions by using VBA.

With all the functions available in Excel and VBA, you may wonder why you would ever need to create functions. The answer: to simplify your work. With a bit of planning, custom functions are very useful in worksheet formulas and VBA procedures. Often, for example, you can significantly shorten a formula by creating a custom function. After all, shorter formulas are more readable and easier to work with.

As you develop custom functions for use in your worksheet formulas, it’s important that you understand a key point. VBA worksheet Function procedures are essentially passive. For example, code within a Function procedure cannot manipulate ranges, change formatting, or do many of the other things that are possible with a Sub procedure. An example may help.

It might be useful to create a function that changes the color of text in a cell based on the cell’s value. Try as you might, however, you can’t write such a function. It always returns an error value.

Just remember this: A function used in a worksheet formula returns a value — it does not perform actions with objects.

A VBA function is a procedure that’s stored in a VBA module. You can use these functions in other VBA procedures or in your worksheet formulas.

A module can contain any number of functions. You can use a custom function in a formula just as if it were a built-in function. If the function is defined in a different workbook, however, you must precede the function name with the workbook name. For example, assume you developed a function called DiscountPrice (which takes one argument), and the function is stored in a workbook named PRICING.XLS.

To use this function in the PRICING.XLS workbook, enter a formula such as

this:

If you want to use this function in a different workbook, enter a formula such as this:

If the custom function is stored in an add-in, you don’t need to precede the function name with the workbook name. I discuss add-ins in Chapter 22.

Custom functions appear in the Insert Function dialog box, in the User Defined category. The easiest way to enter a custom function into a formula is to use the Insert Function command or click the Insert Function button on the Standard toolbar. Both of these methods display the Insert Function dialog box.

Remember that a function’s name acts like a variable. The final value of this variable is the value returned by the function. To demonstrate, examine the following function, which returns the user’s first name:

This function starts by assigning the UserName property of the Application object to a variable named FullName. Next, it uses the VBA InStr function to locate the first space in the name. If there is no space, FirstSpace is equal to 0 and FirstName is equal to the entire name. If FullName does have a space, the Left function extracts the text to the left of the space and assigns it to FirstName.

Notice that FirstName is the name of the function and is also used as a variable name in the function. The final value of FirstName is the value that’s returned by the function. Several intermediate calculations may be going on in the function, but the function always returns the last value assigned to the variable that is the same as the function’s name.

All of the examples in this chapter are available at this topic’s Web site.

To work with functions, you need to understand how to work with arguments. The following points apply to the arguments for Excel worksheet functions and custom VBA functions:

Arguments can be cell references, variables (including arrays), constants, literal values, or expressions.

Some functions have no arguments.

Some functions have a fixed number of required arguments (from 1 to 60). Some functions have a combination of required and optional arguments.

The examples in this section demonstrate how to work with various types of arguments.

A function with no argument

Like Sub procedures, Function procedures need not have arguments. For example, Excel has a few built-in worksheet functions that don’t use arguments, including RAND, TODAY, and NOW.

Here’s a simple example of a function with no arguments. The following function returns the UserName property of the Application object. This name appears in the Options dialog box (General tab). This simple but useful example shows the only way you can get the user’s name to appear in a worksheet formula:

When you enter the following formula into a worksheet cell, the cell displays the current user’s name:

As with the Excel built-in functions, you must include a set of empty parentheses when using a function with no arguments.

A single-argument function is designed for sales managers who need to calculate the commissions earned by their salespeople. The commission rate depends on the monthly sales volume; those who sell more earn a higher commission rate. The function returns the commission amount based on the monthly sales (which is the function’s only argument — a required argument). The calculations in this example are based on Table

You can use several approaches to calculate commissions for sales amounts entered into a worksheet. You could write a lengthy worksheet formula such as this:

A couple reasons make this a bad approach. First, the formula is overly complex. Second, the values are hard-coded into the formula, making the formula difficult to modify if the commission structure changes.

Another approach, which doesn’t require a table of commissions, is to create a custom function:

After you define this function in a VBA module, you can use it in a worksheet formula. Entering the following formula into a cell produces a result of 3,000. The amount of 25000 qualifies for a commission rate of 12 percent:

The next example builds on the preceding one. Imagine that the sales manager implements a new policy: The total commission paid increases by 1 percent for every year the salesperson has been with the company.

I modified the custom Commission function (defined in the preceding section) so that it takes two arguments, both of which are required arguments. Call this new function Commission2:

I simply added the second argument (Years) to the Function statement and included an additional computation that adjusts the commission before exiting the function. This additional computation multiplies the original commission by the number of years in services, divides by 100, and then adds the result to the original computation.

Here’s an example of how you can write a formula by using this function.

(It assumes that the sales amount is in cell A1; cell B1 specifies the number of years the salesperson has worked.)

Here’s an example of how you can write a formula by using this function.

(It assumes that the sales amount is in cell A1; cell B1 specifies the number of years the salesperson has worked.)

Using a worksheet range as an argument is not at all tricky; Excel takes care of the behind-the-scenes details.

Assume that you want to calculate the average of the five largest values in a range named Data. Excel doesn’t have a function that can do this, so you would probably write a formula:

This formula uses Excel’s LARGE function, which returns the nth largest value in a range. The formula adds the five largest values in the range named Data and then divides the result by 5. The formula works fine, but it’s rather unwieldy. And what if you decide that you need to compute the average of the top six values? You would need to

rewrite the formula — and make sure that you update all copies of the formula.

Wouldn’t this be easier if Excel had a function named TopAvg? Then you could compute the average by using the following (nonexistent) function:

This example shows a case in which a custom function can make things much easier for you. The following custom VBA function, named TopAvg, returns the average of the N largest values in a range:

This function takes two arguments: InRange (which is a worksheet range) and N (the number of values to average). It starts by initializing the Sum variable to 0. It then uses a For-Next loop to calculate the sum of the N largest values in the range. Note that I use the Excel LARGE function within the loop. Finally, TopAvg is assigned the value of Sum divided by N.

You can use all Excel worksheet functions in your VBA procedures except those that have equivalents in VBA. For example, VBA has a Rnd function that returns a random number. Therefore, you can’t use the Excel RAND function in a VBA procedure.

Many Excel built-in worksheet functions use optional arguments. An example is the LEFT function, which returns characters from the left side of a string. Its official syntax follows:

The first argument is required, but the second is optional. If you omit the optional argument, Excel assumes a value of 1. Therefore, the following formulas return the same result:

The custom functions you develop in VBA also can have optional arguments. You specify an optional argument by preceding the argument’s name with the keyword Optional, followed by an equal sign and the default value. If the optional argument is missing, the code uses the default value.

Debugging a Function procedure can be a bit more challenging than debugging a Sub procedure. If you develop a function for use in worksheet formulas, you find that an error in the Function procedure simply results in an error display in the formula cell (usually #VALUE!). In other words, you don’t receive the normal runtime error message that helps you locate the offending statement.

Place MsgBox functions at strategic locations to monitor the value of specific variables. Fortunately, message boxes in Function procedures pop up when you execute the procedure. Make sure that only one formula in the worksheet uses your function, or the message boxes appear for each formula that’s evaluated — which could get very annoying.

Test the procedure by calling it from a Sub procedure. Run-time errors appear normally in a pop-up window, and you can either correct the problem (if you know it) or jump right into the debugger.

Set a breakpoint in the function and then use the Excel debugger to step through the function. You can then access all of the usual debugging tools. Refer to Chapter 13 to find out about the debugger.

This function randomly chooses one cell from an input range. The range passed as an argument is actually an array, and the function selects one item from the array at random. If the second argument is 1, the selected value changes whenever the worksheet is recalculated. (The function is made volatile.) If the second argument is 0 (or is omitted), the function is not recalculated unless one of the cells in the input range is modified.

You can use this function for choosing lottery numbers, selecting a winner from a list of names, and so on.

Some Excel worksheet functions take an indefinite number of arguments. A familiar example is the SUM function, which has the following syntax:

The first argument is required, but you can have as many as 29 additional arguments. Here’s an example of a SUM function with four range arguments:

This function is similar to the Excel CONCATENATE function, which combines text arguments into a single string. The difference is that this custom function inserts a space between each pair of concatenated strings.

The second argument, string2( ), is an array preceded by the ParamArray keyword. If the second argument is empty, the UBound function returns -1 and the function ends. If the second argument is not empty, the procedure loops through the elements of the string2 array and processes each additional argument. The LBound and UBound functions determine the beginning and ending elements of the array. The beginning element is normally 0 unless you either declare it as something else or use an Option Base 1 statement at the beginning of your module.

ParamArray can apply to only the last argument in the procedure. It is always a variant data type, and it is always an optional argument (although you don’t use the Optional keyword). Figure 21-2 shows this function in use. Examine the figure to see how the results differ from those produced by the Excel Concatenate function, which doesn’t insert a space between the concatenated items.

The Excel Insert Function dialog box is a handy tool that lets you choose a worksheet function from a list and prompts you for the function’s arguments. And, as I note earlier in this chapter, your custom worksheet functions also appear in the Insert Function dialog box. Custom functions appear in the User Defined category.

Function procedures defined with the Private keyword do not appear in the Insert Function dialog box. Therefore, if you write a Function procedure that’s designed to be used only by other VBA procedures (but not in formulas), you should declare the function as Private

The Insert Function dialog box displays a description of each built-in function. But, as you can see in Figure 21-3, a custom function displays the following text as its

description: No help available.

By default, the Insert Function dialog box does not provide a description for custom functions.

To display a meaningful description of your custom function in the Paste Function dialog box, perform a few additional, (nonintuitive) steps:

1. Activate a worksheet in the workbook that contains the custom function.

2. Choose Tools Macro Macros (or press Alt+F8). The Macro dialog box appears.

3. In the Macro Name field, type the function’s name.

Note that the function does not appear in the list of macros; you must type the name in.

4. Click the Options button.

The Macro Options dialog box appears.

5. In the Description field, type a description for the function.

6. Click OK.

7. Click Cancel.

Some VBA procedures start at the code’s beginning and progress line by line to the end, never deviating from this top-to-bottom program flow. Macros that you record always work like this. In many cases, however, you need to control the flow of your code by skipping over some statements, executing some statements multiple times, and testing conditions to determine what the procedure does next. Hang on to your hat and enjoy the ride, because you’re about to discover the essence of programming.

Some programming newbies can’t understand how a dumb computer can make intelligent decisions. The secret is in several programming constructs that most programming languages support. Table 10-1 provides a quick summary of these constructs.

A GoTo statement offers the most straightforward means for changing a program’s flow. The GoTo statement simply transfers program execution to a new statement, which is preceded by a label.

Your VBA routines can contain as many labels as you like. A label is just a text string followed by a colon.

The following procedure shows how a GoTo statement works:

Sub CheckUser()

UserName = InputBox(“Enter Your Name: “)

If UserName <> “Steve Ballmer” Then

GoTo WrongName

MsgBox (“Welcome Steve...”)

‘ ...[More code here] ...

Exit Sub

WrongName:

MsgBox

As in many other aspects of life, effective decision-making is the key to success in writing Excel macros. If this book has the effect I intend, you’ll soon share my philosophy that a successful Excel application boils down to making decisions and acting upon them.

In this section, I discuss two programming structures that can empower your VBA procedures with some impressive decision-making capabilities: If-Then and Select Case.

The If-Then structure Okay, I’ll come right out and say it: If-Then is VBA’s most important control structure. You’ll probably use this command on a daily basis (at least I do).

Use the If-Then structure when you want to execute one or more statements conditionally. The optional Else clause, if included, lets you execute one or more statements if the condition you’re testing is not true. Here’s the simple CheckUser procedure I presented earlier, recoded to use the If-Then-Else structure: Sub CheckUser2() UserName = InputBox(”Enter Your Name: ”) If UserName = ”Steve Ballmer” Then MsgBox (”Welcome Steve...”) ' ...[More code here] ... Else MsgBox ”Sorry. Only Steve Ballmer can run this.” End If End Sub You would probably agree that this version is much easier to follow.

The following routine demonstrates the If-Then structure without the optional Else clause:

Sub GreetMe()

If Time < 0.5 Then

MsgBox “Good Morning”

End Sub

The GreetMe procedure uses VBA’s Time function to get the system time. If the current time is less than .5 (in other words, before noon), the routine displays a friendly greeting. If Time is greater than or equal to .5, the routine ends and nothing happens.

To display a different greeting if Time is greater than or equal to .5, you can add another If-Then statement after the first one:

Sub GreetMe2()

If Time < 0.5 Then

MsgBox “Good Morning”

If Time >= 0.5 Then

MsgBox “Good Afternoon”

End Sub

Notice that I used >= (greater than or equal to) for the second If-Then statement. This ensures that the entire day is covered. Had I used > (greater than), then no message would appear if this procedure were executed at precisely 12:00 noon. That’s pretty unlikely, but with an important program like this, we don’t want to take any chances.

An If-Then-Else example Another approach to the preceding problem uses the Else clause. Here’s the same routine recoded to use the If-Then-Else structure:

Sub GreetMe3()

If Time < 0.5 Then

MsgBox “Good Morning” Else

MsgBox “Good Afternoon”

End Sub

Notice that I use the line continuation character (underscore) in the preceding example. The If-Then-Else statement is actually a single statement. VBA provides a slightly different way of coding If-Then-Else constructs that use an End If statement. Therefore, the GreetMe procedure can be rewritten as

Sub GreetMe4()

If Time < 0.5 Then

MsgBox “Good Morning”

Else

MsgBox “Good Afternoon”

End If

End Sub

In fact, you can insert any number of statements under the If part, and any number of statements under the Else part. I prefer to use this syntax because it’s easier to read and makes the statements shorter.

What if you need to expand the GreetMe routine to handle three conditions: morning, afternoon, and evening? You have two options: Use three If-Then statements or use a nested If-Then-Else structure. Nesting means placing an If-Then-Else structure within another If-Then-Else structure. The first approach, using three If-Then statements, is simplest:

Sub GreetMe5()

Dim Msg As String

If Time < 0.5 Then

Msg = “Morning”

If Time >= 0.5 And Time < 0.75 Then

Msg = “Afternoon”

If Time >= 0.75 Then

Msg = “Evening”

MsgBox “Good “ & Msg

End Sub

I added a new twist by using a variable. The Msg variable gets a different text value, depending on the time of day. The MsgBox statement displays the greeting: Good Morning, Good Afternoon, or Good Evening.

The following routine performs the same action but uses an If-Then-End If structure:

Sub GreetMe6()

Dim Msg As String

If Time < 0.5 Then

Msg = “Morning”

End If

If Time >= 0.5 And Time < 0.75 Then

Msg = “Afternoon”

End If

If Time >= 0.75 Then

Msg = “Evening”

End If

MsgBox “Good “ & Msg

End Sub

The Select Case structure is useful for decisions involving three or more options (although it also works with two options, providing an alternative to the If-Then-Else structure).

The examples in this section are available at this book’s website. A Select Case example The following example shows how to use the Select Case structure. This also shows another way to code the examples presented in the previous section:

Sub ShowDiscount3()

Dim Quantity As Integer

Dim Discount As Double

Quantity = InputBox(“Enter Quantity: “)

Select Case Quantity

Case 0 To 24

Discount = 0.1

Case 25 To 49

Discount = 0.15

Case 50 To 74

Discount = 0.2

Case Is >= 75

Discount = 0.25

End Select

MsgBox “Discount: “ & Discount

End Sub

Q. 1 What is cells property and how is it used in code?

Q. 2 Write a procedure that uses select case structure for discount calculation.

Q. 3 Show the use of GoTo statement with an example.

Q. 4 What are custom functions? Write a simple custom function.

Write a VBA function which will always calculate the 4th largest value in a given range.

Create a table in excel with columns ID, Name, Year, Subject, Marks, CGPA. Write a VBA procedure to create a table, which takes ID as input from user, and displays marks and CGPA of the entered ID.

For-Next loop

Do-While loop

Do-Until loop

The MsgBox Function

The InputBox Function

The Built-in Dialog Boxes

Lab work

The term looping refers to repeating a block of VBA statements numerous times. Why use loops? I can think of a few reasons. Your code can . . .

✓ Loop through a range of cells, working with each cell individually.

✓ Loop through all open workbooks (the Workbooks collection) and do something with each one.

✓ Loop through all worksheets in a workbook (the Worksheets collection) and do something with each one.

✓ Loop through all the elements in an array.

✓ Loop through all characters in a cell.

✓ Loop through all charts on a worksheet (the ChartObjects collection) and do something with each chart.

✓ Loop through other things that I haven’t thought of.

VBA supports several types of loops, and the examples that follow demonstrate a few ways that you can use them.

In this example, the Quantity variable is being evaluated. The routine checks for four different cases (0–24, 25–49, 50–74, and 75 or greater).

Any number of statements can follow each Case statement, and they all are executed if the case is true. If you use only one statement, as in this example, you can put the statement on the same line as the Case keyword, preceded by a colon — the VBA statement separator character. In my opinion, this makes the code more compact and a bit clearer. Here’s how the routine looks, using this format:

Sub ShowDiscount4 ()

Dim Quantity As Integer

Dim Discount As Double

Quantity = InputBox(“Enter Quantity: “)

Select Case Quantity

Case 0 To 24: Discount = 0.1

Case 25 To 49: Discount = 0.15

Case 50 To 74: Discount = 0.2

Case Is >= 75: Discount = 0.25

End Select

MsgBox “Discount: “ & Discount

End Sub

When VBA executes a Select Case structure, the structure is exited as soon as VBA finds a true case and executes the statements for that case. A nested Select Case example As demonstrated in the following example, you can nest Select Case structures. This routine examines the active cell and displays a message describing the cell’s contents. Notice that the procedure has three Select Case structures, and each has its own End Select statement.

Sub CheckCell()

Dim Msg As String

Select Case IsEmpty(ActiveCell)

Case True

Msg = “is blank.”

Case Else

Select Case ActiveCell.HasFormula

Case True

Msg = “has a formula”

Case Else

Select Case IsNumeric(ActiveCell)

Case True

Msg = “has a number”

Case Else

Msg = “has text”

End Select

MsgBox “Cell “ & ActiveCell.Address & “ “ & Msg

End Sub

The logic goes something like this:

1. Find out whether the cell is empty.

2. If it’s not empty, see whether it contains a formula.

3. If there’s no formula, find out whether it contains a numeric value or text. When the routine ends, the Msg variable contains a string that describes the cell’s contents. As shown in Figure 10-1, the MsgBox function displays that message. You can nest Select Case structures as deeply as you need to, but make sure that each Select Case statement has a corresponding End Select statement.

VBA supports another type of looping structure known as a Do-While loop. Unlike a For-Next loop, a Do-While loop continues until a specified condition is met.

The following example uses a Do-While loop. This routine uses the active cell as a starting point and then travels down the column, multiplying each cell’s value by 2. The loop continues until the routine encounters an empty cell.

Sub DoWhileDemo()

Do While ActiveCell.Value <> Empty

ActiveCell.Value = ActiveCell.Value * 2

ActiveCell.Offset(1, 0).Select

Loop

End Sub

The Do-Until loop structure is similar to the Do-While structure. The two structures differ in their handling of the tested condition. A program continues to execute a Do-While loop while the condition remains true. In a Do-Until loop, the program executes the loop until the condition is true.

The following example is the same one presented for the Do-While loop but recoded to use a Do-Until loop:

You can use a single loop to loop through a one-dimensional range of cells.

Place a command button on your worksheet and add the following code lines:

Dim i As Integer

For i = 1 To 6

Cells(i, 1).Value = 100

Next i

The code lines between For and Next will be executed six times. For i = 1, Excel VBA enters the value 100 into the cell at the intersection of row 1 and column 1. When Excel VBA reaches Next i, it increases i with 1 and jumps back to the For statement. For i = 2, Excel VBA enters the value 100 into the cell at the intersection of row 2 and column 1, etc.

Note: it is good practice to always indent (tab) the code between the words For and Next. This makes your code easier to read.

You can use a double loop to loop through a two-dimensional range of cells.

Place a command button on your worksheet and add the following code lines:

Dim i As Integer, j As Integer

For i = 1 To 6

For j = 1 To 2

Cells(i, j).Value = 100

Next j

Next i

For i = 1 and j = 1, Excel VBA enters the value 100 into the cell at the intersection of row 1 and column 1. When Excel VBA reaches Next j, it increases j with 1 and jumps back to the For j statement. For i = 1 and j = 2, Excel VBA enters the value 100 into the cell at the intersection of row 1 and column 2. Next, Excel VBA ignores Next j because j only runs from 1 to 2. When Excel VBA reaches Next i, it increases i with 1 and jumps back to the For i statement. For i = 2 and j = 1, Excel VBA enters the value 100 into the cell at the intersection of row 2 and column 1, etc.

You can use a triple loop to loop through two-dimensional ranges on multiple Excel worksheets.

Place a command button on your worksheet and add the following code lines:

Dim c As Integer, i As Integer, j As Integer

For c = 1 To 3

For i = 1 To 6

For j = 1 To 2

Worksheets(c).Cells(i, j).Value = 100

Next j

Next i

Next c

The only change made compared to the code for the double loop is that we have added one more loop and added Worksheets(c). in front of Cells to get the two-dimensional range on the first sheet for c = 1, the second sheet for c = 2 and the third sheet for c = 3. Download the Excel file to see this result.

Besides the For Next loop, there are other loops in Excel VBA. For example, the Do While Loop. Code placed between Do While and Loop will be repeated as long as the part after Do While is true.

1. Place a command button on your worksheet and add the following code lines:

Dim i As Integer

i = 1

Do While i < 6

Cells(i, 1).Value = 20

i = i + 1

Loop

As long as i is lower than 6, Excel VBA enters the value 20 into the cell at the intersection of row i and column 1 and increments i by 1. In Excel VBA (and in other programming languages), the symbol '=' means becomes. It does not mean equal. So i = i + 1 means i becomes i + 1. In other words: take the present value of i and add 1 to it. For example, if i = 1, i becomes 1 + 1 = 2. As a result, the value 20 will be placed into column A five times (not six because Excel VBA stops when i equals 6).

Dim i As Integer

i = 1

Do While Cells(i, 1).Value <> ""

Cells(i, 2).Value = Cells(i, 1).Value + 10

i = i + 1

Loop

As long as Cells(i, 1).Value is not empty (<> means not equal to), Excel VBA enters the value into the cell at the intersection of row i and column 2, that is 10 higher than the value in the cell at the intersection of row i and column 1. Excel VBA stops when i equals 7 because Cells(7, 1).Value is empty. This is a great way to loop through any number of rows on a worksheet.

The For ... Next loop uses a variable, which is set to a series of values within a specified range. The VBA code inside the loop is then executed for each value. This is best explained by way of a simple example:

The above simple For ... Next loop sets the variable i to have the values 1, 2, 3, ..., 10, and for each of these values, runs through the VBA code inside the loop. Therefore, in the above example, the loop adds each of the members of the array iArray to the variable, Total.

In the above example, no step size is specified, so the loop uses the default step size of 1, when looping from 1 to 10. However, you may sometimes want to step through a loop using different sized steps. This can be done using the Step keyword, as shown in the following simple example.

In the above For loop, because the step size is specified as 0.1, the value of the variable d is set to the values 0.0, 0.1, 0.2, 0.3, ..., 9.9, 10.0 for each execution of the VBA code inside the loop.

You can also use negative step sizes in the VBA For loop, as is illustrated below:

In this example, the step size is specified as -1, and so the loop sets the variable i to have the values, 10, 9, 8, ..., 1.

The For Each loop is similar to the For ... Next loop but, instead of running through a set of values for a variable, the For Each loop runs through every object within a set of objects. For example, the following code shows the For Each loop used to list every Worksheet in the current Excel Workbook:

If, you want to exit a 'For' Loop early, you can use the Exit For statement. This statement causes VBA to jump out of the loop and continue with the next line of code outside of the loop. For example, when searching for a particular value in an array, you could use a loop to check each entry of the array. However, once you have found the value you are looking for, there is no need to continue searching, so you exit the loop early.

The Exit For statement is illustrated in the following example, which loops through 100 array entries, comparing each to the value 'dVal'. The loop is exited early if dVal is found in the array:

The Do While loop repeatedly executes a section of code while a specified condition continues to evaluate to True. This is shown in the following Sub procedure, where a Do While loop is used to print out all values of the Fibonacci Sequence until the current value is greater than 1,000:

' Sub procedure to list the Fibonacci series for all values below 1,000

Sub Fibonacci()

Dim i As Integer ' counter for the position in the series

Dim iFib As Integer ' stores the current value in the series

Dim iFib_Next As Integer ' stores the next value in the series

Dim iStep As Integer ' stores the next step size

' Initialise the variables i and iFib_Next

i = 1

iFib_Next = 0

' Do While loop to be executed as long as the value of the

' current Fibonacci number exceeds 1000

Do While iFib_Next < 1000

If i = 1 Then

' Special case for the first entry of the series

iStep = 1

iFib = 0

Else

' Store the next step size, before overwriting the

' current entry of the series

iStep = iFib

iFib = iFib_Next

End If

' Print the current Fibonacci value to column A of the

' current Worksheet

Cells(i, 1).Value = iFib

' Calculate the next value in the series and increment

' the position marker by 1

iFib_Next = iFib + iStep

i = i + 1

Loop

End Sub

It can be seen that, in the above example, the condition iFib_Next < 1000 is tested at the start of the loop. Therefore, if the first value of iFib_Next were greater than 1,000, the loop would not be executed at all.

Another way that you can implement the Do While loop is to place the condition at the end of the loop instead of at the beginning. This causes the loop to be executed at least once, regardless of whether or not the condition initially evaluates to True.

The following code shows the form of a Do While Loop which has the condition at the end of the loop:

The Do Until loop is very similar to the Do While loop. The loop repeatedly executes a section of code until a specified condition evaluates to True. This is shown in the following sub procedure, where a Do Until loop is used to extract the values from all cells in Column A of a Worksheet, until it encounters an empty cell:

In the above example, as the condition IsEmpty(Cells(iRow, 1)) is at the start of the Do Until loop, the loop will only be entered if the first cell encountered is non-blank.

However, as illustrated in the Do While loop, you may sometimes want to enter the loop at least once, regardless of the initial condition. In this case, the condition can be placed at the end of the loop, as follows:

You’re probably already familiar with the VBA MsgBox function — I use it quite a bit in the examples throughout this book. The MsgBox function, which accepts the arguments shown in Table 15-1, is handy for displaying information and getting simple user input. It’s able to get user input because it’s a function. A function, as you recall, returns a value. In the case of the MsgBox function, it uses a dialog box to get the value that it returns. Keep reading to see exactly how it works.

Here’s a simplified version of the syntax for the MsgBox function:

MsgBox(prompt[, buttons][, title])

Argument What It Affects

Prompt The text Excel displays in the message box Buttons A number that specifies which buttons (along with what icon) appear in the message box (optional)

Title The text that appears in the message box’s title bar (optional)

You can use the MsgBox function in two ways:

✓ To simply show a message to the user. In this case, you don’t care about the result returned by the function.

✓ To get a response from the user. In this case, you do care about the result returned by the function. The result depends on the button that the user clicks.

If you use the MsgBox function by itself, don’t include parentheses around the arguments. The following example simply displays a message and does not return a result. When the message is displayed, the code stops until the user clicks OK.

Sub MsgBoxDemo()

MsgBox “Click OK to begin printing.”

Sheets(“Results”).PrintOut

End Sub

In this case, printing commences when the user clicks OK. Notice that there is no way to cancel the printing? The next section describes how to fix that.

Getting a response from a message box If you display a message box that has more than just an OK button, you’ll probably want to know which button the user clicks. You’re in luck. The MsgBox function can return a value that represents which button is clicked. You can assign the result of the MsgBox function to a variable.

In the following code, I use some built-in constants (which I describe later in Table 15-2) that make it easy to work with the values returned by MsgBox

Sub GetAnswer()

Dim Ans As Integer

Ans = MsgBox(“Start printing?”, vbYesNo)

Select Case Ans

Case vbYes

ActiveSheet.PrintOut

Case vbNo

MsgBox “Printing canceled”

End Select

End Sub

Figure shows how it looks. When you execute this procedure, the Ans variable is assigned a value of either vbYes or vbNo, depending on which button the user clicks. The Select Case statement uses the Ans value to determine which action the code should perform.

The flexibility of the buttons argument makes it easy to customize your message boxes. You can choose which buttons to display, determine whether an icon appears, and decide which button is the default (the default button is “clicked” if the user presses Enter).

Table 15-2 lists some of the built-in constants you can use for the buttons argument. If you prefer, you can use the value rather than a constant (but I think using the built-in constants is a lot easier)

To use more than one of these constants as an argument, just connect them with a + operator. For example, to display a message box with Yes and No buttons and an exclamation icon, use the following expression as the second MsgBox argument:

vbYesNo + vbExclamation

Or, if you prefer to make your code less understandable, use a value of 52 (that is, 4 + 48).

The following example uses a combination of constants to display a message box with a Yes button and a No button (vbYesNo) as well as a question mark icon (vbQuestion). The constant vbDefaultButton2 designates the second button (No) as the default button — that is, the button that is clicked if the user presses Enter. For simplicity, I assign these constants to the Config variable and then use Config as the second argument in the MsgBox function:

Sub GetAnswer3()

Dim Config As Integer

Dim Ans As Integer

Config = vbYesNo + vbQuestion + vbDefaultButton2

Ans = MsgBox(“Process the monthly report?”, Config)

If Ans = vbYes Then

RunReport

End Sub

The following routine provides another example of using the MsgBox function:

Sub GetAnswer4()

Dim Msg As String, Title As String

Dim Config As Integer, Ans As Integer

Msg = “Do you want to process the monthly report?”

Msg = Msg & vbNewLine & vbNewLine

Msg = Msg & “Processing the monthly report will “

Msg = Msg & “take approximately 15 minutes. It “

Msg = Msg & “will generate a 30-page report for “

Msg = Msg & “all sales offices for the current “

Msg = Msg & “month.” Title = “XYZ Marketing Company”

Config = vbYesNo + vbQuestion

Ans = MsgBox(Msg, Config, Title)

If Ans = vbYes Then

RunReport

End Sub

This example demonstrates an efficient way to specify a longer message in a message box. I use a variable (Msg) and the concatenation operator (&) to build the message in a series of statements. The vbNewLine constant inserts a line break character that starts a new line (use it twice to insert a blank line). I also use the title argument to display a different title in the message box. Figure 15-4 shows the message box Excel displays when you execute this procedure.

Previous examples have used constants (such as vbYes and vbNo) for the return value of a MsgBox function. Besides these two constants, Table 15-3 lists a few others

And that’s pretty much all you need to know about the MsgBox function. Use message boxes with caution, though. There’s usually no reason to display message boxes that serve no purpose. For example, people tend to get annoyed when they see a message box every day that reads, “Good morning, thanks for loading the Budget Projection workbook.”

The VBA InputBox function is useful for obtaining a single piece of information typed by the user. That information could be a value, a text string, or even a range address. This is a good alternative to developing a UserForm when you need to get only one value.

InputBox syntax Here’s a simplified version of the syntax for the InputBox function:

InputBox(prompt[, title][, default])

The InputBox function accepts the arguments listed in Table.

Argument What It Affects

Prompt The text displayed in the input box

Title The text displayed in the input box’s title bar (optional)

Default The default value for the user’s input (optional)

An InputBox example Here’s a statement showing how you can use the InputBox function:

TheName = InputBox(“What is your name?”, “Greetings”)

When you execute this VBA statement, Excel displays the dialog box shown in Figure 15-5. Notice that this example uses only the first two arguments and does not supply a default value. When the user enters a value and clicks OK, the routine assigns the value to the variable TheName.

The following example uses the third argument and provides a default value. The default value is the username stored by Excel (the Application object’s UserName property).

Sub GetName()

Dim TheName As String

TheName = InputBox(“What is your name?”, _

“Greetings”, Application.UserName)

End Sub

Sub AddSheets()

Dim Prompt As String

Dim Caption As String

Dim DefValue As Integer

Dim NumSheets As String

Prompt = “How many sheets do you want to add?”

Caption = “Tell me...”

DefValue = 1

NumSheets = InputBox(Prompt, Caption, DefValue)

If NumSheets = “” Then

Exit Sub

‘Canceled

If IsNumeric(NumSheets) Then

If NumSheets > 0 Then

Sheets.Add Count:=NumSheets

Else

MsgBox “Invalid number”

End If

End Sub

The InputBox always displays a Cancel button. If the user clicks Cancel, the InputBox function returns an empty string. VBA’s InputBox function always returns a string, so if you need to get a value, your code will need to do some additional checking. The following example uses the InputBox function to get a number. It uses the IsNumeric function to check whether the string is a number. If the string does contain a number, all is fine. If the user’s entry cannot be interpreted as a number, the code displays a message box.

Another type of InputBox The information presented in this section applies to VBA’s InputBox function. In addition, you have access to the InputBox method, which is a method of the Application object.

One big advantage of using the Application InputBox method is that your code can prompt for a range selection. The user can then select the range in the worksheet by highlighting the cells. Here’s a quick example that prompts the user to select a range:

Sub GetRange()

Dim Rng As Range

On Error Resume Next

Set Rng = Application.InputBox _

(prompt:=”Specify a range:”, Type:=8)

If Rng Is Nothing Then

Exit Sub

MsgBox “You selected range “ & Rng.Address

End Sub

In this simple example, the code tells the user the address of the range that was selected. In real life, your code would actually do something useful with the selected range. A nice thing about this example is that Excel takes care of the error handling. If you enter something that’s not a range, Excel tells you about it and lets you try again.

The Application.InputBox method is similar to VBA’s InputBox function, but it also has some differences. Check the Help system for complete details.

The fileFilter argument determines what appears in the dialog box’s Files of Type drop-down list. This argument consists of pairs of file filter strings followed by the wild card file filter specification, with commas separating each part and pair. If omitted, this argument defaults to the following:

All Files (*.*), *.*

Notice that this string consists of two parts, separated by a comma:

All Files (*.*)

and

*.*

The first part of this string is the text displayed in the Files of Type drop-down list. The second part determines which files the dialog box displays. For example, *.* means all files.

The code in the following example brings up a dialog box that asks the user for a filename. The procedure defines five file filters. Notice that I use the VBA line continuation sequence to set up the Filter variable; doing so helps simplify this rather complicated argument.

Sub GetImportFileName ()

Dim Finfo As String

Dim FilterIndex As Integer

Dim Title As String

Dim FileName As Variant

‘ Set up list of file filters

FInfo = “Text Files (*.txt),*.txt,” & _

“Lotus Files (*.prn),*.prn,” & _

“Comma Separated Files (*.csv),*.csv,” & _

“ASCII Files (*.asc),*.asc,” & _

“All Files (*.*),*.*”

‘ Display *.* by default FilterIndex = 5

‘ Set the dialog box caption Title = “Select a File to Import”

‘ Get the filename FileName = Application.GetOpenFilename (FInfo, _

FilterIndex, Title)

‘ Handle return info from dialog box

If FileName = False Then

MsgBox “No file was selected.”

Else

MsgBox “You selected “ & FileName

End If

End Sub

Sometimes, you don’t need to get a filename; you just need to get a folder name. If that’s the case, the FileDialog object is just what the doctor ordered.

The following procedure displays a dialog box that allows the user to select a directory. The selected directory name (or “Canceled”) is then displayed by using the MsgBox function.

Sub GetAFolder()

With Application.FileDialog(msoFileDialogFolderPicker)

.InitialFileName = Application.DefaultFilePath & “\”

.Title = “Please select a location for the backup”

.Show

If .SelectedItems.Count = 0 Then

MsgBox “Canceled”

Else

MsgBox .SelectedItems(1)

End If

End With

End Sub

The Excel GetSaveAsFilename method works just like the GetOpenFilename method, but it displays the Excel Save As dialog box rather than its Open dialog box. The GetSaveAsFilename method gets a path and filename from the user but doesn’t do anything with that information. It’s up to you to write code that actually saves the file.

The syntax for this method follows:

object.GetSaveAsFilename ([InitialFilename], [FileFilter], [FilterIndex], [Title], [ButtonText])

The GetSaveAsFilename method takes Table 15-6’s arguments, all of which are optional.

Sometimes, you don’t need to get a filename; you just need to get a folder name. If that’s the case, the FileDialog object is just what the doctor ordered.

The following procedure displays a dialog box that allows the user to select a directory. The selected directory name (or “Canceled”) is then displayed by using the MsgBox function.

Sub GetAFolder()

With Application.FileDialog(msoFileDialogFolderPicker)

.InitialFileName = Application.DefaultFilePath & “\”

.Title = “Please select a location for the backup”

.Show

If .SelectedItems.Count = 0 Then

MsgBox “Canceled”

Else

MsgBox .SelectedItems(1)

End If

End With

End Sub

The FileDialog object lets you specify the starting directory by specifying a value for the InitialFileName property. In this case, the code uses Excel’s default file path as the starting directory.

Displaying Excel’s Built-in Dialog Boxes One way to look at VBA is that it’s a tool that lets you mimic Excel commands. For example, consider this VBA statement:

Range(“A1:A12”).Name = “MonthNames”

Executing this VBA statement has the same effect as choosing Formulas➪ Defined Names➪Define Name to display the New Name dialog box, then typing MonthNames in the Name box and A1:A12 in the Refers to box, and clicking OK.

When you execute the VBA statement, the New Name dialog box does not appear. This is almost always what you want to happen; you don’t want dialog boxes flashing across the screen while your macro executes.

In some cases, however, you may want your code to display one of Excel’s many built-in dialog boxes and let the user make the choices in the dialog box. You can do this by using VBA to execute a Ribbon command. Here’s an example that displays the New Name dialog box. The address in the Refers to box represents the range that’s selected when the command is executed (see Figure 15-9).

Application.CommandBars.ExecuteMso “NameDefine”

Your VBA code can’t get any information from the dialog box. For example, if you execute the code to display the New Name dialog box, your code can’t get the name entered by the user, or the range that’s being named.

The ExecuteMso is a method of the CommandBars object and accepts one argument, an idMso parameter that represents a Ribbon control. Unfortunately, these parameters are not listed in the Help system. And because the Ribbon hasn’t been around forever, code that uses the ExecuteMso method is not compatible with versions prior to Excel 2007.

Here’s another example of using the ExecuteMso method. This statement, when executed, displays the Font tab of the Format Cells dialog box:

Application.CommandBars.ExecuteMso “FormatCellsFontDialog”

Q. 1 How do you show a user form?

Q.2 How do you get a folder name by using a file dialog?

Q.3 How do you view code window?

Assume there is a list numbers in column B, which occupy up to 5000 rows. Write a VBA procedure to find the number of EMPTY cells in this range of column B.

Write a VBA procedure to create a pattern as shown in the screenshot below.

Inserting a new Userform

Viewing the UserForm Code Window

Displaying a UserForm

A UserForm Example

Adding controls

Control properties

Dialog Box Controls

Working with Dialog Box Control

Lab work

When creating a UserForm, you usually take the following general steps:

1. Determine how the dialog box will be used and at what point it will be displayed in your VBA macro.

2. Press Alt+F11 to activate the VBE and insert a new UserForm object. A UserForm object holds a single UserForm.

3. Add controls to the UserForm. Controls include items such as text boxes, buttons, check boxes, and list boxes.

4. Use the Properties window to modify the properties for the controls or for the UserForm itself.

5. Write event-handler procedures for the controls (for example, a macro that executes when the user clicks a button in the dialog box). These procedures are stored in the Code window for the UserForm object.

6. Write a procedure (stored in a VBA module) that displays the dialog box to the user. Don’t worry if some of these steps seem foreign. I provide more details in the following sections, along with step-by-step instructions for creating a UserForm.

When you are designing a UserForm, you are creating what developers call the Graphical User Interface (GUI) to your application. GUI also stands for Gaming Under the Influence, but that’s not relevant here.

Take some time to consider what your form should look like and how your users are likely to want to interact with the elements on the UserForm. Try to guide them through the steps they need to take on the form by carefully considering the arrangement and wording of the controls. Like most things VBA-related, the more you do it, the easier it gets.

UserForm Insert a UserForm object with the following steps:

1. Activate the VBE by pressing Alt+F11.

2. Select the workbook in the Project window.

3. Choose Insert➪UserForm. The VBE inserts a new UserForm object, which contains an empty dialog box.

Figure 16-2 shows a UserForm — an empty dialog box. Your job, if you choose to accept it, is to add some controls to this UserForm.

When you activate a UserForm, the VBE displays the Toolbox in a floating window, as shown in Figure 16-2. You use the tools in the Toolbox to add controls to your UserForm. If, for some reason, the Toolbox doesn’t appear when you activate your UserForm, choose View➪Toolbox.

To add a control, just click the desired control in the Toolbox and drag it into the dialog box to create the control. After you add a control, you can move and resize it by using standard techniques.

Table lists the various tools, as well as their capabilities. To determine which tool is which, hover your mouse pointer over the control and read the small pop-up description.

Changing properties for a UserForm control Every control you add to a UserForm has a number of properties that determine how the control looks or behaves. In addition, the UserForm itself also has its own set of properties. You can change these properties with the aptly named Properties window. Figure 16-3 shows the Properties window when a CommandButton control is selected.

The Properties window appears when you press F4, and the properties shown in this window depend on what is selected. If you select a different control, the properties change to those appropriate for that control. To hide the Properties window and get it out of the way, click the Close button in its title bar. Pressing F4 will always bring it back when you need it.

Properties for controls include the following:

✓ Name

✓ Width

✓ Height

✓ Value

✓ Caption

If you select the UserForm itself (not a control on the UserForm), you can use the Properties window to adjust UserForm properties.

Each control has its own set of properties (although many controls have some common properties). To change a property using the Properties window:

1. Make sure that the correct control is selected in the UserForm.

2. Make sure the Properties window is visible (press F4 if it’s not).

3. In the Properties window, click on the property that you want to change.

4. Make the change in the right portion of the Properties window.

1. Make sure that the correct control is selected in the UserForm.

2. Make sure the Properties window is visible (press F4 if it’s not).

3. In the Properties window, click on the property that you want to change.

4. Make the change in the right portion of the Properties window.

If you select the UserForm itself (not a control on the UserForm), you can use the Properties window to adjust UserForm properties.

UserForm object has a Code module that holds the VBA code (the event-handler procedures) executed when the user works with the dialog box. To view the Code module, press F7. The Code window is empty until you add some procedures. Press Shift+F7 to return to the dialog box.

Here’s another way to switch between the Code window and the UserForm display: Use the View Code and View Object buttons in the Project window’s title bar. Or right-click the UserForm and choose View Code. If you’re viewing code, double-click the UserForm name in the Project window to return to the UserForm.

You display a UserForm by using the UserForm’s Show method in a VBA procedure.

The macro that displays the dialog box must be in a VBA module — not in the Code window for the UserForm.

The following procedure displays the dialog box named UserForm1:

Sub ShowDialogBox()

UserForm1.Show

‘ Other statements can go here

End Sub

When Excel displays the dialog box, the ShowDialogBox macro halts until the user closes the dialog box. Then VBA executes any remaining statements in the procedure. Most of the time, you won’t have any more code in the procedure. As you later see, you put your event-handler procedures in the Code window for the UserForm. These procedures kick in when the user works with the controls on the UserForm.

The VBE provides a name for each control you add to a UserForm. The control’s name corresponds to its Name property. Use this name to refer to a particular control in your code. For example, if you add a CheckBox control to a UserForm named UserForm1, the CheckBox control is named CheckBox1 by default. You can use the Properties box to make this control appear with a check mark. Or, you can write code to do it:

UserForm1.CheckBox1.Value = True

Most of the time, you write the code for a UserForm in the UserForm’s code module. If that’s the case, you can omit the UserForm object qualifier and write the statement like this:

CheckBox1.Value = True

Your VBA code can also check various properties of the controls and take appropriate actions. The following statement executes a macro named PrintReport if the check box (named CheckBox1) is checked:

If CheckBox1.Value = True Then Call PrintReport

This section’s UserForm example is an enhanced version of the ChangeCase macro from the beginning of the chapter. Recall that the original version of this macro changes the text in the selected cells to uppercase. This modified version uses a UserForm to ask the user which type of change to make: uppercase, lowercase, or proper case.

This dialog box needs to obtain one piece of information from the user: the type of change to make to the text. Because the user has three choices, your best bet is a dialog box with three OptionButton controls. The dialog box also needs two more buttons: an OK button and a Cancel button. Clicking the OK button runs the code that does the work. Clicking the Cancel button causes the macro to finish without doing anything.

This workbook is available at the book’s website. However, you get more out of this exercise if you follow the steps provided here and create it yourself.

These steps create the UserForm. Start with an empty workbook.

1. Press Alt+F11 to activate the VBE.

2. If multiple projects are in the Project window, select the project that corresponds to the workbook you’re using.

3. Choose Insert➪UserForm. The VBE inserts a new UserForm object with an empty dialog box.

4. Press F4 to display the Properties window.

5. In the Properties window, change the dialog box’s Caption property to Change Case.

6. The dialog box is a bit too large, so you may want to click it and use the handles (on the right and bottom sides) to make it smaller. Step 6 can also be done after you position all the controls in the dialog box.

Adding the CommandButtons Ready to add two CommandButtons — OK and Cancel — to the dialog box? Follow along:

1. Make sure that the Toolbox is displayed; if it isn’t, choose View➪Toolbox.

2. If the Properties window isn’t visible, press F4 to display it.

3. In the Toolbox, drag a CommandButton into the dialog box to create a button. As you see in the Properties box, the button has a default name and caption: CommandButton1.

4. Make sure that the CommandButton is selected; then activate the Properties window and change the following properties: Property Change To Name OKButton Caption OK Default True

5. Add a second CommandButton object to the UserForm and change the following properties: Property Change To Name CancelButton Caption Cancel Cancel True

6. Adjust the size and position of the controls so your dialog box looks something like Figure 16-4.

Userform

This chapter teaches you how to create an Excel VBA Userform. The Userform we are going to create looks as follows:

To add the controls to the Userform, execute the following steps.

1. Open the Visual Basic Editor. If the Project Explorer is not visible, click View, Project Explorer.

2. Click Insert, Userform. If the Toolbox does not appear automatically, click View, Toolbox. Your screen should be set up as below.

3. Add the controls listed in the table below. Once this has been completed, the result should be consistent with the picture of the Userform shown earlier. For example, create a text box control by clicking on TextBox from the Toolbox. Next, you can drag a text box on the Userform. When you arrive at the Car frame, remember to draw this frame first before you place the two option buttons in it.

4. Change the names and captions of the controls according to the table below. Names are used in the Excel VBA code. Captions are those that appear on your screen. It is good practice to change the names of controls. This will make your code easier to read. To change the names and captions of the controls, click View, Properties Window and click on each control.

To show the Userform, place a command button on your worksheet and add the following code line:

Private Sub CommandButton1_Click()

DinnerPlannerUserForm.Show

End Sub

We are now going to create the Sub UserForm_Initialize. When you use the Show method for the Userform, this sub will automatically be executed.

1. Open the Visual Basic Editor.

2. In the Project Explorer, right click on DinnerPlannerUserForm and then click View Code.

3. Choose Userform from the left drop-down list. Choose Initialize from the right drop-down list.

4. Add the following code lines:

Private Sub UserForm_Initialize()

'Empty NameTextBox

NameTextBox.Value = ""

'Empty PhoneTextBox

PhoneTextBox.Value = ""

'Empty CityListBox

CityListBox.Clear

'Fill CityListBox

With CityListBox

.AddItem "San Francisco"

.AddItem "Oakland"

.AddItem "Richmond"

End With

'Empty DinnerComboBox

DinnerComboBox.Clear

'Fill DinnerComboBox

With DinnerComboBox

.AddItem "Italian"

.AddItem "Chinese"

.AddItem "Frites and Meat"

End With

'Uncheck DataCheckBoxes

DateCheckBox1.Value = False

DateCheckBox2.Value = False

DateCheckBox3.Value = False

'Set no car as default

CarOptionButton2.Value = True

'Empty MoneyTextBox

MoneyTextBox.Value = ""

'Set Focus on NameTextBox

NameTextBox.SetFocus

End Sub

Explanation: text boxes are emptied, list boxes and combo boxes are filled, check boxes are unchecked, etc.

We have now created the first part of the Userform. Although it looks neat already, nothing will happen yet when we click the command buttons on the Userform.

1. Open the Visual Basic Editor.

2. In the Project Explorer, double click on DinnerPlannerUserForm.

3. Double click on the Money spin button.

4. Add the following code line:

Private Sub MoneySpinButton_Change()

MoneyTextBox.Text = MoneySpinButton.Value

End Sub

Explanation: this code line updates the text box when you use the spin button.

5. Double click on the OK button.

6. Add the following code lines:

Private Sub OKButton_Click()

Dim emptyRow As Long

'Make Sheet1 active

Sheet1.Activate

'Determine emptyRow

emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Transfer information

Cells(emptyRow, 1).Value = NameTextBox.Value

Cells(emptyRow, 2).Value = PhoneTextBox.Value

Cells(emptyRow, 3).Value = CityListBox.Value

Cells(emptyRow, 4).Value = DinnerComboBox.Value

If DateCheckBox1.Value = True Then Cells(emptyRow, 5).Value = DateCheckBox1.Caption

If DateCheckBox2.Value = True Then Cells(emptyRow, 5).Value = Cells(emptyRow, 5).Value & " " & DateCheckBox2.Caption

If DateCheckBox3.Value = True Then Cells(emptyRow, 5).Value = Cells(emptyRow, 5).Value & " " & DateCheckBox3.Caption

If CarOptionButton1.Value = True Then

Cells(emptyRow, 6).Value = "Yes"

Else

Cells(emptyRow, 6).Value = "No"

End If

Cells(emptyRow, 7).Value = MoneyTextBox.Value

End Sub

Explanation: first, we activate Sheet1. Next, we determine emptyRow. The variable emptyRow is the first empty row and increases every time a record is added. Finally, we transfer the information from the Userform to the specific columns of emptyRow.

7. Double click on the Clear button.

8. Add the following code line:

Private Sub ClearButton_Click()

Call UserForm_Initialize

End Sub

Explanation: this code line calls the Sub UserForm_Initialize when you click on the Clear button.

9. Double click on the Cancel Button.

10. Add the following code line:

Private Sub CancelButton_Click()

Unload Me

End Sub

Explanation: this code line closes the Userform when you click on the Cancel button.

Test the Userform

Exit the Visual Basic Editor, enter the labels shown below into row 1 and test the Userform.

Result:

Table 16-1 lists the various tools, as well as their capabilities. To determine which tool is which, hover your mouse pointer over the control and read the small pop-up description.

Control Every control you add to a UserForm has a number of properties that determine how the control looks or behaves. In addition, the UserForm itself also has its own set of properties. You can change these properties with the aptly named Properties window. Figure 16-3 shows the Properties window when a CommandButton control is selected.

Properties for controls include the following:

✓ Name

✓ Width

✓ Height

✓ Value

✓ Caption

Each control has its own set of properties (although many controls have some common properties). To change a property using the Properties window:

1. Make sure that the correct control is selected in the UserForm.

2. Make sure the Properties window is visible (press F4 if it’s not).

3. In the Properties window, click on the property that you want to change.

4. Make the change in the right portion of the Properties window.

If you select the UserForm itself (not a control on the UserForm), you can use the Properties window to adjust UserForm properties.

Some of the UserForm properties serve as default settings for new controls you drag onto the UserForm. For example, if you change the Font property for the UserForm itself, controls that you add will use that same font. Controls that are already on the UserForm are not affected.

Every UserForm object has a Code module that holds the VBA code (the event-handler procedures) executed when the user works with the dialog box. To view the Code module, press F7. The Code window is empty until you add some procedures. Press Shift+F7 to return to the dialog box.

There are several types of forms that you can create in Excel: data forms, worksheets that contain Form and ActiveX controls, and VBA UserForms. You can use each type of form by itself, or you can combine them in different ways to create a solution that's right for you.

A data form provides a convenient way to enter or display one complete row of information in a range or table without scrolling horizontally. You may find that using a data form can make data entry easier than moving from column to column when you have more columns of data than can be viewed on the screen. Use a data form when a simple form of text boxes that list the column headings as labels is sufficient and you don't need sophisticated or custom form features, such as a list box or spin button.

Excel can automatically generate a built-in data form for your range or table. The data form displays all column headers as labels in a single dialog box. Each label has an adjacent blank text box in which you can enter data for each column, up to a maximum of 32 columns. In a data form, you can enter new rows, find rows by navigating, or (based on cell contents) update rows and delete rows . If a cell contains a formula, the formula result is displayed in the data form, but you cannot change the formula by using the data form.

A worksheet is a type of form that enables you to enter and view data on the grid, and there are several control-like features already built-in to Excel worksheets, such as comments and data validation. Cells resemble text boxes in that you can enter and format them in a variety of ways. Cells are often used as labels, and by adjusting cell height and width and merging cells, you can make a worksheet behave like a simple data entry form. Other control-like features, such as cell comments, hyperlinks, background images, data validation, conditional formatting, embedded charts, and AutoFilter can make a worksheet behave like an advanced form.

For added flexibility, you can add controls and other drawing objects to the drawing canvas of a worksheet, and combine and coordinate them with worksheet cells. For example, you can use a list box control to make it easier for a user to select from a list of items. Or, you can use a spin button control to make it easier for a user to enter a number.

Because controls and objects are stored on the drawing canvas, you can display or view controls and objects alongside associated text that is independent of row and column boundaries without changing the layout of a grid or table of data on your worksheet. Most of the time, many of these controls can also be linked to cells on the worksheet and do not require VBA code to make them work. You can set properties that determine whether a control floats freely or moves and resizes together with a cell. For example, you might have a check box that you want to move together with its underlying cell when the range is sorted. However, if you have a list box that you want to keep in a specific location at all times, you probably do not want it to move together with its underlying cell.

Excel has two types of controls: Form controls and ActiveX Controls. In addition to these sets of controls, you can also add objects from the Drawing tools, such as a AutoShapes, WordArt, SmartArt graphic, or text boxes.

The following sections describe these controls and drawing objects, and also explain how to work with these controls and objects in more detail.

Form controls are the original controls that are compatible with earlier versions of Excel, starting with Excel version 5.0. Form controls are also designed for use on XLM macro sheets.

You use Form controls when you want to easily reference and interact with cell data without using VBA code, and when you want to add controls to chart sheets. For example, after you add a list box control to a worksheet and linking it to a cell, you can return a numeric value for the current position of the selected item in the control. You can then use that numeric value in conjunction with the INDEX function to select different items from the list.

You can also run macros by using Form controls. You can attach an existing macro to a control, or write or record a new macro. When a user of the form clicks the control, the control runs the macro.

However, these controls cannot be added to UserForms, used to control events, or modified to run Web scripts on Web pages.

ActiveX controls can be used on worksheet forms, with or without the use of VBA code, and on VBA UserForms. In general, use ActiveX controls when you need more flexible design requirements than those provided by Form controls. ActiveX controls have extensive properties that you can use to customize their appearance, behavior, fonts, and other characteristics.

You can also control different events that occur when an ActiveX control is interacted with. For example, you can perform different actions, depending on which choice a user selects from a list box control, or you can query a database to refill a combo box with items when a user clicks a button. You can also write macros that respond to events associated with ActiveX controls. When a user of the form interacts with the control, your VBA code then runs to process any events that occur for that control.

Your computer also contains many ActiveX controls that were installed by Excel and other programs, such as Calendar Control 12.0 and Windows Media Player.

IMPORTANT: Not all ActiveX controls can be used directly on worksheets; some can be used only on Visual Basic for Applications (VBA) UserForms. If you try to add any one of these particular ActiveX controls to a worksheet, Excel displays the message "Cannot insert object."

However, ActiveX controls cannot be added to chart sheets from the user interface or to XLM macro sheets. You also cannot assign a macro to run directly from an ActiveX control the same way you can from a Form control.

You may also want to include SmartArt graphics, Shapes, WordArt, and text boxes on your form. You can resize, rotate, flip, color, and combine these objects to create even more complex shapes. When you type text directly in a Shape or text box object, the text becomes part of that object — if you rotate or flip the object, the text rotates or flips with it. Unlike ActiveX controls, you can assign different attributes, such as font size and font style, to individual words and characters in the object. You can also assign macros and add hyperlinks to these objects. You can even link text in a Shape or text box object to a worksheet cell and dynamically display updated values in those objects.

After adding forms and ActiveX to a worksheet form, you usually want to fine-tune and rearrange the controls in a variety of ways to create a well-designed, user friendly form. Common tasks include the following:

Controlling the display of gridlines while you work with the controls, and deciding whether to display the gridlines to the user on the final worksheet form.

Selecting and deselecting controls so that you can specify properties or make additional adjustments.

Editing text in a control, such as the caption or label.

Grouping, copying, moving, and aligning controls to organize the layout of the worksheet form.

Resizing and formatting controls to obtain the appearance that you want.

Positioning or sizing a control with a cell.

Protecting controls and linked cells according to your specific data protection needs.

Enabling or disabling the printing of controls when the worksheet form is printed.

Deleting unused controls.

Because there are three different types of controls and objects that you can modify uniquely, you might not know for sure which type of control it is just by looking at it. To determine the type of control (Form or ActiveX), select and right-click the control, and then display the shortcut menu:

If the shortcut menu contains the command Properties, the control is an ActiveX control, and you are in design mode.

If the shortcut menu contains the command Assign Macro, the control is a Form control.

TIP: To display the correct shortcut menu for the group box Form control, make sure that you select the perimeter instead of the interior of the group box.

If the shortcut menu contains the command Edit Text, the object is a Drawing object.

For maximum flexibility, you can create UserForms, which are custom dialog boxes, that usually include one or more ActiveX controls. You make UserForms available from VBA code that you create in the Visual Basic Editor. The high-level steps for creating a UserForm are as follows:

Insert a UserForm into your workbook's VBAProject. You access a workbook's VBAProject by first displaying the Visual Basic Editor (press ALT+F11) and then, on the Insert menu, clicking UserForm.

Write a procedure to display the UserForm.

Add ActiveX controls.

Modify properties for the ActiveX controls.

Write event-handler procedures for the ActiveX controls.

By using UserForms, you can also utilize advanced form functionality,. For example, you can programmatically add a separate option button for each letter of the alphabet or you can add a check box for each item in a large list of dates and numbers.

Before creating a UserForm, consider using built-in dialog boxes available from Excel that might fit your needs. These built-in dialog boxes include the VBA InputBox and MsgBox functions, the Excel InputBox method, GetOpenFilename method, GetSaveAsFilename method, and the Dialogs object of the Application object, which contains all the built-in Excel dialog boxes.

Every control you add to a UserForm has a number of properties that determine how the control looks or behaves. In addition, the UserForm itself also has its own set of properties. You can change these properties with the aptly named Properties window. Figure 16-3 shows the Properties window when a CommandButton control is selected.

Properties for controls include the following:

✓ Name

✓ Width

✓ Height

✓ Value

✓ Caption

Each control has its own set of properties (although many controls have some common properties). To change a property using the Properties window:

1. Make sure that the correct control is selected in the UserForm.

2. Make sure the Properties window is visible (press F4 if it’s not).

3. In the Properties window, click on the property that you want to change.

4. Make the change in the right portion of the Properties window.

If you select the UserForm itself (not a control on the UserForm), you can use the Properties window to adjust UserForm properties.

Later Chapter tells you everything you need to know about working with dialog box controls.

You display a UserForm by using the UserForm’s Show method in a VBA procedure.

The macro that displays the dialog box must be in a VBA module — not in the Code window for the UserForm.

The following procedure displays the dialog box named UserForm1:

Sub ShowDialogBox()

UserForm1.Show ‘ Other statements can go here

End Sub

Using information from a UserForm The VBE provides a name for each control you add to a UserForm. The control’s name corresponds to its Name property. Use this name to refer to a particular control in your code. For example, if you add a CheckBox control to a UserForm named UserForm1, the CheckBox control is named CheckBox1 by default. You can use the Properties box to make this control appear with a check mark. Or, you can write code to do it:

UserForm1.CheckBox1.Value = True

Most of the time, you write the code for a UserForm in the UserForm’s code module. If that’s the case, you can omit the UserForm object qualifier and write the statement like this:

CheckBox1.Value = True

Your VBA code can also check various properties of the controls and take appropriate actions. The following statement executes a macro named PrintReport if the check box (named CheckBox1) is checked:

If CheckBox1.Value = True Then Call PrintReport

It’s usually a good idea to change the default name the VBE has given to your controls to something more meaningful. You might consider naming the check box I just described “cbxPrintReport.” Note that I precede the name with a three-letter prefix (for “checkbox”), indicating the type of control. It is a matter of taste whether you think doing so is a good practice

These steps create the UserForm. Start with an empty workbook.

1. Press Alt+F11 to activate the VBE.

2. If multiple projects are in the Project window, select the project that corresponds to the workbook you’re using.

3. Choose Insert➪UserForm. The VBE inserts a new UserForm object with an empty dialog box.

4. Press F4 to display the Properties window.

5. In the Properties window, change the dialog box’s Caption property to Change Case. 6. The dialog box is a bit too large, so you may want to click it and use the handles (on the right and bottom sides) to make it smaller. Step 6 can also be done after you position all the controls in the dialog box..

Ready to add two CommandButtons — OK and Cancel — to the dialog box? Follow along:

1. Make sure that the Toolbox is displayed; if it isn’t, choose View➪Toolbox.

2. If the Properties window isn’t visible, press F4 to display it.

3. In the Toolbox, drag a CommandButton into the dialog box to create a button. As you see in the Properties box, the button has a default name and caption: CommandButton1.

4. Make sure that the CommandButton is selected; then activate the Properties window and change the following properties: Property Change To Name OKButton Caption OK Default True

5. Add a second CommandButton object to the UserForm and change the following properties: Property Change To Name CancelButton Caption Cancel Cancel True

6. Adjust the size and position of the controls so your dialog box looks something like Figure.

Adding the OptionButtons In this section, you add three OptionButtons to the dialog box. Before adding the OptionButtons, you add a Frame object that contains the OptionButtons. The Frame isn’t necessary, but it makes the dialog box look more professional.

1. In the Toolbox, click the Frame tool and drag it into the dialog box. This step creates a frame to hold the options buttons.

2. Use the Properties window to change the frame’s caption to Options.

3. In the Toolbox, click the OptionButton tool and drag it into the dialog box (within the Frame). Doing this creates an OptionButton control.

4. Select the OptionButton and use the Properties window to change the following properties: Property Change To Name OptionUpper Caption Upper Case Accelerator U Value True Setting the Value property to True makes this OptionButton the default.

5. Add another OptionButton and use the Properties window to change the following properties: Property Change To Name OptionLower Caption Lower Case Accelerator L

6. Add a third OptionButton and use the Properties window to change the following properties: Property Change To Name OptionProper Caption Proper Case Accelerator P

7. Adjust the size and position of the OptionButtons, Frame, and dialog box.

Your UserForm should look something like Figure.

The Accelerator property determines which letter in the caption is underlined — and more importantly, it determines what Alt-key combination selects that control. For example, you can select the Lower Case option by pressing Alt+L because the L is underlined. Accelerator keys are optional, but some users prefer to use the keyboard to navigate dialog boxes.

You may wonder why the OptionButtons have accelerator keys but the CommandButtons go without. Generally, OK and Cancel buttons never have accelerator keys because they can be accessed from the keyboard. Pressing Enter is equivalent to clicking OK because the control’s Default property is True. Pressing Esc is equivalent to clicking Cancel because the control’s Cancel property is True.

Now it’s time to make the UserForm actually do something. Here’s how to add an event-handler procedure for the Cancel and OK buttons:

1. Double-click the Cancel button. VBE activates the Code window for the UserForm and inserts an empty procedure:

Private Sub CancelButton_Click()

End Sub

The procedure named CancelButton_Click is executed when the Cancel button is clicked, but only when the dialog box is displayed. In other words, clicking the Cancel button when you’re designing the dialog box won’t execute the procedure. Because the Cancel button’s Cancel property is set to True, pressing Esc also triggers the CancelButton_ Click procedure.

2. Insert the following statement inside the procedure (before the End Sub statement): Unload UserForm1

This statement closes the UserForm (and removes it from memory) when the Cancel button is clicked.

3. Press Shift+F7 to return to the UserForm.

4. Double-click the OK button. VBE activates the code window for the UserForm and inserts an empty Sub procedure called OKButton_Click. When the UserForm is displayed, clicking OK executes this procedure. Because this button has its Default property set to True, pressing Enter also executes the OKButton_Click procedure.

5. Enter the following code so the procedure looks like this:

Private Sub OKButton_Click()

Dim WorkRange As Range

Dim cell As Range

‘ Process only text cells, no formulas

On Error Resume Next

Set WorkRange = Selection.SpecialCells _

(xlCellTypeConstants, xlCellTypeConstants)

‘ Upper case

If OptionUpper Then

For Each cell In WorkRange

cell.Value = UCase(cell.Value)

Cell

End If

‘ Lower case

If OptionLower Then

For Each cell In WorkRange

cell.Value = LCase(cell.Value)

Cell

End If

‘ Proper case

If OptionProper Then

For Each cell In WorkRange

cell.Value = Application. _

WorksheetFunction.Proper(cell.Value)

Cell

End If

Unload UserForm1

End Sub

The preceding code is an enhanced version of the original ChangeCase macro that I present at the beginning of the chapter. The macro consists of three separate blocks of code. This code uses three If-Then structures; each one has a For Each-Next loop. Only one block is executed, determined by which OptionButton the user selects. The last statement unloads (closes) the dialog box after the work is finished.

Here’s something kind of odd. Notice that VBA has a UCase function and an LCase function, but it doesn’t have a function to convert text to proper case. Therefore, I use Excel’s PROPER worksheet function (preceded by Application.WorksheetFunction) to do the proper case conversion.

Another option is to use the VBA StrConv function, with a second argument of vbProperCase. (See the Help system for details.) The StrConv function is not available in all Excel versions, so I use the PROPER worksheet function instead. It’s worth noting that the StrConv beats the PROPER function at converting to proper case. Excel’s PROPER function always capitalizes the letter that follows an apostrophe. So the word can’t becomes Can’T. StrConv doesn’t do that.

We’re almost finished with this project. The only thing missing is a way to display the dialog box. Follow these steps to create the procedure that makes the dialog box appear:

1. In the VBE window, choose Insert➪Module. The VBE adds an empty VBA module (named Module1) to the project.

2. Enter the following code:

Sub ChangeCase()

If TypeName(Selection) = “Range” Then

UserForm1.Show

Else

MsgBox “Select a range.”, vbCritical

End If

End Sub

This procedure is simple. It checks to make sure that a range is selected. If so, the dialog box is displayed (using the Show method). The user then interacts with the dialog box, and the code stored in the UserForm’s Code pane is executed. If a range is not selected, the user sees a MsgBox with the text “Select a range.”

At this point, everything should be working properly. But you still need an easy way to execute the macro. Assign a shortcut key (Ctrl+Shift+C) that executes the ChangeCase macro:

1. Activate the Excel window via Alt+F11.

2. Choose Developer➪Code➪Macros or press Alt+F8.

3. In the Macros dialog box, select the ChangeCase macro.

4. Click the Options button. Excel displays its Macro Options dialog box.

5. Enter an uppercase C for the Shortcut key. See Figure 16-6.

6. Enter a description of the macro in the Description field.

7. Click OK.

8. Click Cancel when you return to the Macro dialog box.

After you perform this operation, pressing Ctrl+Shift+C executes the ChangeCase macro, which displays the UserForm if a range is selected.

You can also make this macro available from the Quick Access toolbar. Right-click the Quick Access toolbar and choose Customize Quick Access Toolbar. The Excel Options dialog box appears, and you’ll find the ChangeCase macro listed under Macros (see Figure 16-7).

Finally, you need to test the macro and dialog box to make sure they work properly:

1. Activate a worksheet (any worksheet in any workbook).

2. Select some cells that contain text. You can even select entire rows or columns.

3. Press Ctrl+Shift+C. The UserForm appears. Figure 16-8 shows how it should look.

4. Make your choice and click OK. If you did everything correctly, the macro makes the specified change to the text in the selected cells.

If you test this procedure when only one cell is selected, you’ll find that all the cells on the worksheet are processed. That behavior is a byproduct of using the SpecialCells method. If you’d prefer to be able to process just one cell, change the first block of code to this:

If Selection.Count = 1 Then

Set WorkRange = Selection

Else

Set WorkRange = Selection.SpecialCells _

(xlCellTypeConstants, xlCellTypeConstants)

End If

Figure shows the worksheet after converting the text to uppercase. Notice that the formula in cell B15 and the date in cell B16 were not changed. The macro, as you recall, works only with cells that contain text.

As long as the workbook is open, you can execute the macro from any other workbook. If you close the workbook that contains your macro, Ctrl+Shift+C no longer has any function.

If the macro doesn’t work properly, double-check the preceding steps to locate and correct the error. Don’t be alarmed; debugging is a normal part of developing macros. As a last resort, download the completed workbook from this book’s website and try to figure out where you went wrong.

In this section, I tell you how to add controls to a UserForm, give them meaningful names, and adjust some of their properties.

Before you can do any of these things, you must have a UserForm, which you get by choosing Insert➪UserForm in the VBE. When you add a UserForm, make sure that the correct project is selected in the Project window (if more than one project is available)

Adding controls Oddly enough, the VBE doesn’t have menu commands that let you add controls to a dialog box. You must use the floating Toolbox, which I describe in Chapter 16, to add controls. Normally, the Toolbox pops up automatically when you activate a UserForm in the VBE. If it doesn’t, you can display the Toolbox by choosing View➪Toolbox.

Follow along to add a control to the UserForm:

1. Click the Toolbox tool that corresponds to the control you want to add.

2. Click in the UserForm and drag to size and position the control. Alternatively, you can simply drag a control from the Toolbox to the UserForm to create a control with the default dimensions. Figure 17-1 shows a UserForm that contains a few controls: Two OptionButtons (inside of a Frame), a ComboBox, a CheckBox, a ScrollBar, and a CommandButton.

A UserForm may contain vertical and horizontal grid lines, which help align the controls you add. When you add or move a control, it snaps to the grid. If you don’t like this feature, you can turn off the grids by following these steps:

1. Choose Tools➪Options in the VBE. 2. In the Options dialog box, select the General tab. 3. Set your desired options in the Form Grid Settings section.

Every control that you add to a UserForm has properties that determine how the control looks and behaves. You can change a control’s properties at the following two times:

✓ At design time — when you’re designing the UserForm. You do so manually, using the Properties window.

✓ At runtime — while your macro is running. You do so by writing VBA code. Changes made at runtime are always temporary; they are made to the copy of the dialog box you are showing, not to the actual UserForm object you designed.

When you add a control to a UserForm, you almost always need to make some design-time adjustments to its properties. You make these changes in the Properties window. (To display the Properties window, press F4.) Figure 17-2 shows the Properties window, which displays properties for the object selected in the UserForm — which happens to be a CheckBox control.

To change a control’s properties at runtime, you must write VBA code. For example, you may want to hide a particular control when the user clicks a check box. In such a case, you write code to change the control’s Visible property.

Each control has its own set of properties. All controls, however, share some common properties, such as Name, Width, and Height. Table lists some of the common properties available for many controls.

When you select a control, that control’s properties appear in the Properties window. To change a property, just select it in the Properties window and make the change. Some properties give you some help. For example, if you need to change the TextAlign property, the Properties window displays a drop-down list that contains all valid property values, as shown in Figure.

Dialog Box Controls: The Details

In the following sections, I introduce you to each type of control you can use in custom dialog boxes and discuss some of the more useful properties. I don’t discuss every property for every control because that would require a book that’s about four times as thick (and it would be a very boring book).

The Help system for controls and properties is thorough. To find complete details for a particular property, select the property in the Properties window and press F1.

A CheckBox control is useful for getting a binary choice: yes or no, true or false, on or off, and so on. Figure 17-4 shows some examples of CheckBox controls.

The following is a description of a CheckBox control’s most useful properties:

✓ Accelerator: A character that lets the user change the value of the control by using the keyboard. For example, if the accelerator is A, pressing Alt+A changes the value of the CheckBox control (from checked to unchecked, or from unchecked to checked). In the example shown here, I use numbers for the accelerators (Alt+1, Alt+2, and so on).

✓ ControlSource: The address of a worksheet cell that’s linked to the CheckBox. The cell displays TRUE if the control is checked or FALSE if the control is not checked. This is optional. Most of the time a CheckBox is not linked to a cell.

✓ Value: If True, the CheckBox has a checkmark. If False, it does not have a checkmark.

Don’t confuse CheckBox controls with OptionButton controls. They look kind of similar, but they are used for different purposes.

The following is a description of some useful ComboBox control properties:

✓ ControlSource: A cell that stores the value selected in the ComboBox.

✓ ListRows: The number of items to display when the list drops down.

✓ ListStyle: The appearance of the list items.

✓ RowSource: A range address that contains the list of items displayed in the ComboBox.

✓ Style: Determines whether the control acts like a drop-down list or a ComboBox. A drop-down list doesn’t allow the user to enter a new value.

✓ Value: The text of the item selected by the user and displayed in the ComboBox.

If your list of items is not in a worksheet, you can add items to a ComboBox control by using the AddItem method. More information on this method is in later Chapter

CommandButton is just a common clickable button. It is of no use unless you provide an event-handler procedure to execute when the button is clicked. Figure 17-6 shows a dialog box with nine CommandButtons. Two of these buttons feature a clipart image (specified by copying the clipart and then pasting it into the Picture field in the Properties window).

When a CommandButton is clicked, it executes an event-handler procedure with a name that consists of the CommandButton’s name, an underscore, and the word Click. For example, if a CommandButton is named MyButton, clicking it executes the macro named MyButton_Click. This macro is stored in the Code window for the UserForm.

The following is a description of some useful CommandButton control properties:

✓ Cancel: If True, pressing Esc executes the macro attached to the button. Only one of the form’s buttons should have this option set to True.

✓ Default: If True, pressing Enter executes the macro attached to the button. Again: Just one button should have this option set to True.

A Frame control encloses other controls. You do so either for aesthetic purposes or to logically group a set of controls. A frame is particularly useful when the dialog box contains more than one set of OptionButton controls. (See “OptionButton control,” later in this chapter.)

The following list describes some useful Frame control properties:

✓ BorderStyle: The frame’s appearance.

✓ Caption: The text displayed at the top of the frame. The caption can be an empty string if you don’t want the control to display a caption.

An Image control displays an image. You may want to use an Image control to display your company’s logo in a dialog box. Figure 17-7 shows a dialog box with an Image control that displays a photo of a cute little kitten.

The following list describes the most useful Image control properties:

✓ Picture: The graphics image that is displayed.

✓ PictureSizeMode: How the picture is displayed if the control size does not match the image size.

When you click the Picture property, you are prompted for a filename. However, the graphics image (after it’s retrieved) is stored in the workbook. That way, if you distribute your workbook to someone else, you don’t have to include a copy of the graphics file.

Rather than retrieve the image from a file, you can also copy and paste. Excel’s clipart collection is a great source of images. Use Insert➪Illustrations➪Online Pictures and choose an image to place in your worksheet. Select the image and press Ctrl+C to copy it to the Clipboard. Then activate your UserForm, click the Image control, and select the Picture property in the Properties box. Press Ctrl+V to paste the copied image. You can then delete the clipart image in the worksheet.

Some graphics images are very large and can make your workbook size increase dramatically. For best results, use an image that’s as small as possible.

A Label control simply displays text in your dialog box. Figure 17-8 shows a few Label controls. As you can see, you have a great deal of control over the formatting of a Label control.

The ListBox control presents a list of items from which the user can choose one or more. Figure 17-9 shows a dialog box with two ListBox controls.

ListBox controls are very flexible. For example, you can specify a worksheet range that holds the ListBox items, and the range can consist of multiple columns. Or you can fill the ListBox with items by using VBA code.

If a ListBox isn’t tall enough to display all the items in the list, a scrollbar appears so the user can scroll down to see more items.

The following list is a description of the most useful ListBox control properties:

✓ ControlSource: A cell that stores the value selected in the ListBox.

✓ IntegralHeight: This is True if the ListBox height adjusts automatically to display full lines of text when the list is scrolled vertically. If False, the ListBox may display partial lines of text when it is scrolled vertically. Note that when this property is True, the actual height of your ListBox may be slightly different, when your UserForm is shown, from what you had set it originally. In other words, the height may adjust to ensure that the last entry is entirely visible.

✓ ListStyle: The appearance of the list items.

✓ MultiSelect: Determines whether the user can select multiple items from the list.

✓ RowSource: A range address that contains the list of items displayed in the ListBox. ✓ Value: The text of the selected item in the ListBox.

If the ListBox has its MultiSelect property set to 1 or 2, then the user can select multiple items in the ListBox. In such a case, you cannot specify a ControlSource; you need to write a macro that determines which items are selected. Chapter 18 demonstrates how to do so.

Q. 1 How do you create a user form?

Q. 2 How do you add controls to a user form?

Q. 3 How do you insert a new user form?

Create a UserForm which will name and phone number as two inputs. The form will also have 4 check boxes to display the last four years (2022, 2021, 2020, 2019) and should ask them to check those years in which the user traveled internationally. If the travelled more than 2 or more years, they should be displayed a message saying, “You don’t qualify for this offer.” IF the user has travelled less than 2 times, they should be displayed a message saying, “You can avail the current offer.”

搜索此博客

Voice of China

Understand the VBA objects and procedures Essential VBA Language Elements

评论

发表评论

此博客中的热门博文

Don't be afraid, this time the two-way foil is in the hands of civilization别怕，这一次二向箔掌握在文明的手中

Zelensky signed a deal to sell the country泽连斯基签下协议，把国家给卖了，以后乌克兰的事，华尔街说了算

Marianne Situ的帖子