Select Case

There are innumerable ways to accomplish the same task with If/Then/Else and ElseIf code structures. But keep in mind that using a large number of If/Then/Else and ElseIf statements can make it difficult to follow the logic of your program. You should consider using the Select/Case code structure in situations where you find yourself using a large number of ElseIf statements. The Select/Case code structure is used when you need to test the value of a variable multiple times and, based on the outcome of those tests, execute a single block of code. The Select/Case syntax is fairly simple and easy to understand.

Select Case expression Case conditionl

'This block of code executes if conditionl is true. Case condition2

'This block of code executes if condition2 is true. 'There is no limit on the number of cases you can use Case Else

'This block of code executes if none of the other conditions were true. End Select

A Select/Case structure must begin with Select Case and end with End Select. The expression immediately following Select Case is typically a variable of numerical or string data type. Next, a list of one or more code blocks is entered just beneath the keyword Case and a condition. The condition is a comparison to the expression in the opening line of the structure. VBA proceeds down the list until it finds a condition that evaluates as true, then executes the block of code within that case element. Any additional case elements following one that evaluates as true are ignored, even if their conditions are also true. Thus, order of the case elements is important. The last case element should use Case Else. This ensures that at least one block of code executes if all other conditions are false.

The following example uses a Select/Case structure in a VBA function designed to work with an Excel spreadsheet. The input value should be numerical and expressed as a percentage. This percentage represents a student's score and is passed into the function and stored in the variable studentScore. The variable studentScore is used as the test expression for the

Select/Case structure.

Public Function AssignGrade(studentScore As Single) As String Select Case studentScore Case 90 To 100

AssignGrade = "B" Case 70 To 80

AssignGrade = "D" Case Else

AssignGrade = "F" End Select End Function

There are two forms for writing the conditionals in the case elements; both are shown in this example. The first case element uses Case 90 To 100. This condition is specified as a range of values with the lower value inserted first followed by the To keyword and then the upper value of the range. This condition evaluates as true if the value stored in the variable studentScore is greater or equal to 90 and less than or equal to 100.

If the value of studentScore is less than 90, VBA proceeds to the next case element which is Case Is >= 80. This is the other form for a condition using the Is keyword to specify a range with a comparison operator >= (greater than or equal to). If the value of studentScore is greater than or equal to 80, this condition is true and the block of code within this element executes (assuming the previous condition was false). Again, VBA proceeds down the list until it finds a true condition and then evaluates that case element's code block. If Case Is >= 60 in the AssignGrade() function is placed at the top of the Select/Case structure, then all students with a percentage higher than 60 would be assigned a grade of D even if they have a score of 100.

Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook

Post a comment