Excel 2019 Power Programming with VBA. Michael Alexander

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 the first negative value in Row 1 of the active sheet:

      Sub SelectNegative() Dim Cell As Range For Each Cell In Range("1:1") If Cell.Value < 0 Then Cell.Select Exit For End If Next Cell End Sub

      This example uses an If-Then construct to check the value of each cell. If a cell is negative, it's selected, and then the loop ends when the Exit For statement is executed.

Controlling Code Execution

      Some VBA procedures start at the top and progress line by line to the bottom. Macros that you record, for example, always work in this fashion. Often, however, you need to control the flow of your routines by skipping over some statements, executing some statements multiple times, and testing conditions to determine what the routine does next.

      The preceding section describes the For Each-Next construct, which is a type of loop. This section discusses the additional ways of controlling the execution of your VBA procedures.

       GoTo statements

       If-Then constructs

       Select Case constructs

       For-Next loops

       Do While loops

       Do Until loops

      GoTo statements

      The most straightforward way to change the flow of a program is to use a GoTo statement. This statement simply transfers program execution to a new instruction, which must be preceded by a label (a text string followed by a colon, or a number with no colon). VBA procedures can contain any number of labels, but a GoTo statement can't branch outside a procedure.

      The following procedure uses the VBA InputBox function to get the user's name. If the name is not Howard, the procedure branches to the WrongName label and ends. Otherwise, the procedure executes some additional code. The Exit Sub statement causes the procedure to end.

      Sub GoToDemo() UserName = InputBox("Enter Your Name:") If UserName <> "Howard" Then GoTo WrongName MsgBox ("Welcome Howard…") ' -[More code here] - Exit Sub WrongName: MsgBox "Sorry. Only Howard can run this macro." End Sub

      This simple procedure works, but it's not an example of good programming. In general, you should use the GoTo statement only when you have no other way to perform an action.