Optional. The text that appears in the message box's title bar. The default is Microsoft Excel.
helpfile: Optional. The name of the Help file associated with the message box.
context: Optional. The context ID of the Help topic, which represents a specific Help topic to display. If you use the context argument, you must also use the helpfile argument.
You can assign the value returned to a variable, or you can use the function by itself without an assignment statement. This example assigns the result to the variable Ans:
Dim Ans As Long Ans = MsgBox("Continue?", vbYesNo + vbQuestion, "Tell me") If Ans = vbNo Then Exit Sub
Note that we used the sum of two built-in constants (vbYesNo + vbQuestion) for the buttons argument. Using vbYesNo displays two buttons in the message box: one labeled Yes and one labeled No. Adding vbQuestion to the argument also displays a question mark icon. When the first statement is executed, Ans contains one of two values, represented by the constant vbYes or vbNo. In this example, if the user clicks the No button, the procedure ends.
See Chapter 12, “Leveraging Custom Dialog Boxes,” for more information about the MsgBox function.
Manipulating Objects and Collections
As an Excel programmer, you'll spend a lot of time working with objects and collections. Therefore, you want to know the most efficient ways to write your code to manipulate these objects and collections. VBA offers two important constructs that can simplify working with objects and collections.
With-End With constructs
For Each-Next constructs
With-End With constructs
The With-End With construct enables you to perform multiple operations on a single object. To start understanding how the With-End With construct works, examine the following procedure, which modifies six properties of a selection's formatting. (The selection is assumed to be a Range object.)
Sub ChangeFont1() Selection.Font.Name = "Cambria" Selection.Font.Bold = True Selection.Font.Italic = True Selection.Font.Size = 12 Selection.Font.Underline = xlUnderlineStyleSingle Selection.Font.ThemeColor = xlThemeColorAccent1 End Sub
You can rewrite this procedure using the With-End With construct. The following procedure performs exactly like the preceding one:
Sub ChangeFont2() With Selection.Font .Name = "Cambria" .Bold = True .Italic = True .Size = 12 .Underline = xlUnderlineStyleSingle .ThemeColor = xlThemeColorAccent1 End With End Sub
Some people think that the second incarnation of the procedure is more difficult to read. Remember, though, that the objective is increased speed. Although the first version may be more straightforward and easier to understand, a procedure that uses the With-End With construct to change several properties of an object can be faster than the equivalent procedure that explicitly references the object in each statement.
NOTE
When you record a VBA macro, Excel uses the With-End With construct every chance it gets. To see a good example of this construct, try recording your actions while you change the page orientation using the Page Layout ➪ Page Setup ➪ Orientation command.
For Each-Next constructs
Recall from the preceding chapter that a collection is a group of related objects. For example, the Workbooks collection is a collection of all open Workbook objects. You can also work with many other collections.
Suppose you want to perform some action on all objects in a collection. Or suppose you want to evaluate all objects in a collection and take action under certain conditions. These occasions are perfect for the For Each-Next construct because you don't have to know how many elements are in a collection to use the For Each-Next construct.
The syntax of the For Each-Next construct is as follows:
For Each element In collection [instructions] [Exit For] [instructions] Next [element]
The following procedure uses the For Each-Next construct with the Worksheets collection in the active workbook. When you execute the procedure, the MsgBox function displays each worksheet's Name property. (If five worksheets are in the active workbook, the MsgBox function is called five times.)
Sub CountSheets() Dim Item as Worksheet For Each Item In ActiveWorkbook.Worksheets MsgBox Item.Name Next Item End Sub
NOTE
In the preceding example, Item is an object variable (more specifically, a Worksheet object). There's nothing special about the name Item; you can use any valid variable name in its place.
The next example uses For Each-Next to cycle through all objects in the Windows collection and count the number of windows that are hidden:
Sub HiddenWindows() Dim iCount As Integer Dim Win As Window iCount = 0 For Each Win In Windows If Not Win.Visible Then iCount = iCount + 1 Next Win MsgBox iCount & " hidden windows." End Sub
For each window, if the window is hidden, the iCount variable is incremented. When the loop ends, the message box displays the value of iCount.
Here's an example that closes all workbooks except the active workbook. This procedure uses the If-Then construct to evaluate each workbook in the Workbooks collection:
Sub CloseInactive() Dim Book as Workbook For Each Book In Workbooks If Book.Name <> ActiveWorkbook.Name Then Book.Close Next Book End Sub
A common use for the For Each-Next construct is to loop through all of the cells in a range. The next example of For Each-Next is designed to be executed after the user selects a range of cells. Here, the Selection object acts as a collection that consists of Range objects because each cell in the selection is a Range object. The procedure evaluates each cell and uses the VBA UCase function to convert its contents to uppercase. (Numeric cells are not affected.)
Sub MakeUpperCase() Dim Cell as Range For Each Cell In Selection Cell.Value = UCase(Cell.Value) Next Cell End Sub
VBA provides a way to exit a For-Next loop before all the elements in the collection are evaluated. Do this with an Exit For statement. The example that follows selects
