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
TIP
VBA exits a Select Case construct as soon as a True case is found. Therefore, for maximum efficiency, you should check the most likely case first.
Select Case structures can also be nested. The following procedure, for example, uses the VBA TypeName function to determine what is selected (a range, nothing, or anything else). If a range is selected, the procedure executes a nested Select Case and 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 Case constructs as deeply as you need, but make sure that each Select Case statement has a corresponding End Select statement.
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 blocks of instructions
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 GoTo statement to control the flow. If the iCount variable, 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 GoTo statement when not absolutely necessary. Using GoTo statements to loop is contrary to the concept of structured coding. (See the “What is structured programming?” sidebar.) A GoTo statement 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 GoTo statements for your decision-making.
For-Next loops
The simplest type of a good loop is a For-Next loop. Its syntax is as follows:
For counter = start To end [Step stepval] [instructions] [Exit For] [instructions] Next [counter]
What is structured programming?
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 GoTo statement. 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.
VBA is a structured language. It offers standard structured constructs, such as If-Then-Else and Select Case and the For-Next, Do Until, and Do While loops. Furthermore, VBA fully supports modular code construction.
If you're new to programming, form good structured programming habits early.
The following is an example of a For-Next loop that doesn't use the optional Step value or the optional Exit For statement. This routine executes the Sum = Sum + Sqr(Count) statement 100 times and displays the result, that is, the sum of the square roots of the first 100 integers.
Sub SumSquareRoots() Dim Sum As Double Dim Count As Integer Sum = 0 For Count = 1 To 100 Sum = Sum + Sqr(Count) Next Count MsgBox Sum End Sub
In this example, Count (the loop counter variable) starts out as 1 and increases by 1 each time the loop repeats. The Sum variable simply accumulates the square roots of each value of Count.
CAUTION
When you use For-Next loops, it's important to understand that the loop counter is a normal variable—nothing special. As a result, it's possible to change the value of the loop counter in the block of code executed between the For and Next statements. Changing the loop counter inside a loop, however, is a bad practice and can cause unpredictable results. You should take precautions to ensure that your code doesn't change the loop counter.
You can also use a Step value to skip some values in the loop. Here's the same procedure rewritten to sum the square roots of the odd numbers between 1 and 100:
Sub SumOddSquareRoots() Dim Sum As Double Dim Count As Integer Sum = 0 For Count = 1 To 100 Step 2 Sum = Sum + Sqr(Count) Next Count MsgBox Sum End Sub
In this procedure, Count starts out as 1 and then takes on values of 3, 5, 7, and so on. The final value of Count used in the loop is 99. When the loop ends, the value of Count is 101.
A Step value in a For-Next
