Michael Alexander - Excel 2019 Power Programming with VBA

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 Withconstruct. 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 Withconstruct to change several properties of an object can be faster than the equivalent procedure that explicitly references the object in each statement.

When you record a VBA macro, Excel uses the With- End Withconstruct 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.

Recall from the preceding chapter that a collection is a group of related objects. For example, the Workbookscollection is a collection of all open Workbookobjects. 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- Nextconstruct because you don't have to know how many elements are in a collection to use the For Each- Nextconstruct.

The syntax of the For Each- Nextconstruct is as follows:

For Each element In collection [instructions] [Exit For] [instructions] Next [element]

The following procedure uses the For Each- Nextconstruct with the Worksheets collection in the active workbook. When you execute the procedure, the MsgBoxfunction displays each worksheet's Nameproperty. (If five worksheets are in the active workbook, the MsgBoxfunction is called five times.)

Sub CountSheets() Dim Item as Worksheet For Each Item In ActiveWorkbook.Worksheets MsgBox Item.Name Next Item End Sub

In the preceding example, Itemis an object variable (more specifically, a Worksheetobject). There's nothing special about the name Item; you can use any valid variable name in its place.

The next example uses For Each- Nextto 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 iCountvariable 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- Thenconstruct to evaluate each workbook in the Workbookscollection:

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- Nextconstruct is to loop through all of the cells in a range. The next example of For Each- Nextis designed to be executed after the user selects a range of cells. Here, the Selectionobject acts as a collection that consists of Rangeobjects because each cell in the selection is a Rangeobject. The procedure evaluates each cell and uses the VBA UCasefunction 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- Nextloop before all the elements in the collection are evaluated. Do this with an Exit Forstatement. 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- Thenconstruct to check the value of each cell. If a cell is negative, it's selected, and then the loop ends when the Exit Forstatement is executed.

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- Nextconstruct, 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

The most straightforward way to change the flow of a program is to use a GoTostatement. 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 GoTostatement can't branch outside a procedure.

The following procedure uses the VBA InputBoxfunction to get the user's name. If the name is not Howard , the procedure branches to the WrongNamelabel and ends. Otherwise, the procedure executes some additional code. The Exit Substatement 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 GoTostatement only when you have no other way to perform an action. In fact, the only time you really need to use a GoTostatement in VBA is for error handling (refer to Chapter 4, “Working with VBA Sub Procedures”).

Finally, it goes without saying that the preceding example is not intended to demonstrate an effective security technique!

Perhaps the most commonly used instruction grouping in VBA is the If- Thenconstruct. This common instruction is one way to endow your applications with decision-making capability. Good decision-making is the key to writing successful programs.

The basic syntax of the If- Thenconstruct is as follows:

If condition Then true_instructions [Else false_instructions]

The If- Thenconstruct is used to execute one or more statements conditionally. The Elseclause is optional. If included, the Elseclause lets you execute one or more instructions when the condition that you're testing isn't True.