Michael Alexander - Excel 2019 Power Programming with VBA

The following example of a Select Caseconstruct shows another way to code the GreetMeexamples presented in the preceding section:

Sub GreetMe() Dim Msg As String Select Case Time Case Is < 0.5 Msg = "Good Morning" Case 0.5 To 0.75 Msg = "Good Afternoon" Case Else Msg = "Good Evening" End Select MsgBox Msg End Sub

And here's a rewritten version of the Discountexample using a Select Caseconstruct. This procedure assumes that Quantityis always an integer value. For simplicity, the procedure performs no error checking.

Sub Discount3() Dim Quantity As Variant Dim Discount As Double Quantity = InputBox("Enter Quantity: ") Select Case Quantity Case "" Exit Sub 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

The Casestatement also can use a comma to separate multiple values for a single case. The following procedure uses the VBA Weekdayfunction to determine whether the current day is a weekend (that is, the Weekdayfunction returns 1or 7). The procedure then displays an appropriate message.

Sub GreetUser1() Select Case Weekday(Now) Case 1, 7 MsgBox "This is the weekend" Case Else MsgBox "This is not the weekend" End Select End Sub

The following example shows another way to code the previous procedure:

Sub GreetUser2() Select Case Weekday(Now) Case 2, 3, 4, 5, 6 MsgBox "This is not the weekend" Case Else MsgBox "This is the weekend" End Select End Sub

Here's another way to code the procedure, using the Tokeyword to specify a range of values:

Sub GreetUser3() Select Case Weekday(Now) Case 2 To 6 MsgBox "This is not the weekend" Case Else MsgBox "This is the weekend" End Select End Sub

To demonstrate the flexibility of VBA, here is a final example in which each case is evaluated until one of the expressions evaluates to True:

Sub GreetUser4() Select Case True Case Weekday(Now) = 1 MsgBox "This is the weekend" Case Weekday(Now) = 7 MsgBox "This is the weekend" Case Else MsgBox "This is not the weekend" End Select End Sub

Any number of instructions can be written after each Casestatement, and they're all executed if that case evaluates to True. If you use only one instruction per case, as in the preceding example, you might want to put the instruction on the same line as the Casekeyword (but don't forget the VBA statement-separator character, the colon). This technique makes the code more compact. Here's an example:

Sub Discount3() Dim Quantity As Variant Dim Discount As Double Quantity = InputBox("Enter Quantity: ") Select Case Quantity Case "": Exit Sub 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

VBA exits a Select Caseconstruct as soon as a True case is found. Therefore, for maximum efficiency, you should check the most likely case first.

Select Casestructures can also be nested. The following procedure, for example, uses the VBA TypeNamefunction to determine what is selected (a range, nothing, or anything else). If a range is selected, the procedure executes a nested Select Caseand tests for the number of cells in the range. If one cell is selected, it displays One cell is selected. Otherwise, it displays a message with the number of selected rows.

Sub SelectionType() Select Case TypeName(Selection) Case "Range" Select Case Selection.Count Case 1 MsgBox "One cell is selected" Case Else MsgBox Selection.Rows.Count & " rows" End Select Case "Nothing" MsgBox "Nothing is selected" Case Else MsgBox "Something other than a range" End Select End Sub

This procedure also demonstrates the use of Case Else, a catchall case. You can nest Select Caseconstructs as deeply as you need, but make sure that each Select Casestatement has a corresponding End Selectstatement.

This procedure demonstrates the value of using indentation in your code to clarify the structure. For example, take a look at the same procedure without the indentations:

Sub SelectionType() Select Case TypeName(Selection) Case "Range" Select Case Selection.Count Case 1 MsgBox "One cell is selected" Case Else MsgBox Selection.Rows.Count & " rows"Case "Nothing" MsgBox "Nothing is selected" Case Else MsgBox "Something other than a range" End Select End Sub

Fairly incomprehensible, eh?

Looping is the process of repeating a block of instructions. You might know the number of times to loop, or the number may be determined by the values of variables in your program.

The following code, which enters consecutive numbers into a range, demonstrates what is considered to be a bad loop . The procedure uses two variables to store a starting value ( StartVal) and the total number of cells to fill ( NumToFill). This loop uses the GoTostatement to control the flow. If the iCountvariable, which keeps track of how many cells are filled, is less than the value of NumToFill, the program control loops back to DoAnother.

Sub BadLoop() Dim StartVal As Integer Dim NumToFill As Integer Dim iCount As Integer StartVal = 1 NumToFill = 100 ActiveCell.Value = StartVal iCount = 1 DoAnother: ActiveCell.Offset(iCount, 0).Value = StartVal + iCount iCount = iCount + 1 If iCount < NumToFill Then GoTo DoAnother Else Exit Sub End Sub

This procedure works as intended, so why is it an example of bad looping? Programmers generally frown on using a GoTostatement when not absolutely necessary. Using GoTostatements to loop is contrary to the concept of structured coding. (See the “What is structured programming?” sidebar.) A GoTostatement makes the code much more difficult to read because representing a loop using line indentations is almost impossible. In addition, this type of unstructured loop makes the procedure more susceptible to error. Furthermore, using lots of labels results in spaghetti code —code that appears to have little or no structure and has a tangled flow.

Because VBA has several structured looping commands, you almost never have to rely on GoTostatements for your decision-making.

The simplest type of a good loop is a For- Nextloop. Its syntax is as follows:

For counter = start To end [Step stepval] [instructions] [Exit For] [instructions] Next [counter]

Hang around with programmers, and sooner or later you'll hear the term structured programming . You'll also discover that structured programs are considered superior to unstructured programs.

So, what is structured programming, and can you do it with VBA?

The basic premise of structured programming is that a routine or code segment should have only one entry point and one exit point. In other words, a body of code should be a stand-alone unit, and program control should not jump into or exit from the middle of this unit. As a result, structured programming rules out the GoTostatement. When you write structured code, your program progresses in an orderly manner and is easy to follow—as opposed to spaghetti code, in which a program jumps around.

A structured program is easier to read and understand than an unstructured one. More important, it's also easier to modify.