Conditional Statements in Excel VBA
Related Link: If…Then…Else Statements (VBA).
There are primarily two Conditional Statements in VBA: If…Then…Else Statements and Select…Case Statement. In both of these, one or more conditions are evaluated and a block of code is executed depending on the result of the evaluation.
Select…Case Statement (VBA)
If…Then…Else Statements (VBA)
———————————————————————————————–
Contents:
Using the To keyword to specify the upper and lower range of values
Using the Is keyword (with a comparison operator) to compare values
Using a comma to separate multiple expressions or ranges in each Case clause
Select…Case compared to If…Then…Else Statements
———————————————————————————————–
Executes different blocks of code or statements, depending on the respective condition(s) being met. It evaluates an expression and based on its result executes one of the many set of statements. It is very similar to the If…Then…Else statements.
Select Case expression
Case expression_value_1
statements_1
Case expression_value_n
statements_n
Case Else
else_statements
End Select
expression can be a variable, a field or a range. It can be expresesed using a vba function -> as “IsNumeric(rng)” or “rng.HasFormula”, where ‘rng’ is a Range variable. It should evaluate to a Boolean value, String value, Numeric value or Date type ie. to any of the basic data types of Boolean, Byte, Integer, Long, Single, Double, Decimal, Date, Object, String, Variant, … It is necessary to specify an expression. It is the value of this expression which is tested and compared (with expression_value) in each Case and when it matches, the statements specified in the matching Case are executed.
expression_value [expression_value_1 … expression_value_n] -> the data type of expression_value should match or be convertible to that of expression. In each Case, the value of the expression is compared to the expression_value, and if a match is found, the specified statements get executed. It is necessary to specify atleast one expression_value. Expression_values are tested in the order they are mentioned. These (expression_value) are like a list of conditions and when a condition is met, the relevant block of code gets executed.
statements [statements_1 … statements_n] -> statements specified in a particular Case get executed if the value of the expression matches the relevant expression_value of that Case.
Case Else -> expression_values are tested in the order they are mentioned and if a match is found, its respective statements get executed and no subsequent expression_value is tested thereafter. If no match is found for any expression_value, Case Else clause comes into play and the else_statements get executed. It is Optional to have the Case Else clause.
else_statements -> these statements get executed if no match is found in any of the expression_values. It is optional to specify else_statements. If a match has not been found in any expression_value and else_statements are not specified also, in this case no code will be executed in the Select…Case Statements block.
End Select -> terminates the Select…Case block of statements and it is necessary to mention these keywords at the end.
Example:
Sub selectCase1()
‘making strAge equivalent to “young” will return the message “Less than 40 years”
Dim strAge As String
strAge = “young”
Select Case strAge
Case “senior citizen”
MsgBox “Over 60 years”
Case “middle age”
MsgBox “Between 40 to 59 years”
Case “young”
MsgBox “Less than 40 years”
Case Else
MsgBox “Invalid”
End Select
End Sub
Using the To keyword to specify the upper and lower range of values
Use the To keyword in expression_value to specify the upper and lower range of matching values, as shown below. The value to the left of To keyword should be less than or equal to the value to the right of the To keyword. Range can also be specified for character strings.
Example:
Sub selectCaseTo()
‘entering marks as 69 will return the message “Average”; entering marks as 101 will return the message “Out of Range”
Dim iMarks As Integer
iMarks = InputBox(“Enter marks”)
Select Case iMarks
Case 70 To 100
MsgBox “Good”
Case 40 To 69
MsgBox “Average”
Case 0 To 39
MsgBox “Failed”
Case Else
MsgBox “Out of Range”
End Select
End Sub
Using the Is keyword (with a comparison operator) to compare values
To include a comparison operator (=, <>, <, >, <=, or >=) in expression_value, use the Is keyword. The Is keyword is automatically inserted before a comparison operator, if not specifically included. See below example.
Example:
Sub selectCaseIs()
‘if sngTemp equals 39.5, returned message is “Moderately Hot”
Dim sngTemp As Single
sngTemp = 39.5
Select Case sngTemp
Case Is >= 40
MsgBox “Extremely Hot”
Case Is >= 25
MsgBox “Moderately Hot”
Case Is >= 0
MsgBox “Cool Weather”
Case Is < 0
MsgBox “Extremely Cold”
End Select
End Sub
Using a comma to separate multiple expressions or ranges in each Case clause
Multiple expressions or ranges can be specified in each Case clause, by separating each expression with a comma (which has the effect of the OR operator). Multiple expressions or ranges can also be specified for character strings. See below examples.
Example:
Sub selectCaseMultiple_1()
‘if alpha equates to “Hello”, the returned message is “Odd Number or Hello”
Dim alpha As Variant
alpha = “Hello”
Select Case alpha
Case a, e, i, o, u
MsgBox “Vowels”
Case 2, 4, 6, 8
MsgBox “Even Number”
Case 1, 3, 5, 7, 9, “Hello”
MsgBox “Odd Number or Hello”
Case Else
MsgBox “Out of Range”
End Select
End Sub
Example:
In this example, the string comparison “apples” To “grapes” determines a value between “apples” and “grapes” in alphabetical order, and uses the default text comparison method of Binary (which is case-sensitive) because Option Compare Statement is not specified – see below.
Sub SelectCaseMultiple_OptionCompare_NotSpecified()
‘Option Compare is NOT specified and therefore text comparison will be case-sensitive
‘bananas will return the message “Text between apples and grapes, or specifically mangoes, or the numbers 98 or 99”; oranges will return the message “Out of Range”; Apples will return the message “Out of Range”.
Dim var As Variant, strResult As String
var = InputBox(“Enter”)
Select Case var
Case 1 To 10, 11 To 20: strResult = “Number is between 1 and 20”
Case “apples” To “grapes”, “mangoes”, 98, 99: strResult = “Text between apples and grapes, or specifically mangoes, or the numbers 98 or 99”
Case Else: strResult = “Out of Range”
End Select
MsgBox strResult
End Sub
You can compare string data using string comparison methods of Binary, Text or Database. Database is used only with Microsoft Access.
Option Compare Binary makes string comparisons based on a binary sort order (in Microsoft Windows, the code page determines the sort order – wherein ANSI 1252 is used for English and many European languages) -> A < B < U < Z < a < b < u < z < À < Û < à < û
Option Compare Text makes string comparisons which are not based on a case-sensitive text sort order -> (A=a) < (À = à) < (B=b) < (U=u) < (Û = û) < (Z=z)
Option Compare Statement (viz. Option Compare Binary or Option Compare Text) can be used to set the comparison method. It must be used at the module level, before any procedure. If the Option Compare Statement is not specified, the default text comparison method is Binary.
Example – Option Compare Binary or Default
Option Compare Binary
———————————————————————–
Sub OptionCompareBinary_Specified()
‘Option Compare Binary is specified and therefore text comparison will be case-sensitive
‘”Apples” will return the message “Out of Range”; “Grapes” will return “Out of Range” (“G” comes before “a” in ANSI code); “gRound” will return “Text between apples and grapes”, whereas “ground” is “Out of Range”
Dim str As String
str = InputBox(“Enter text”)
Select Case str
Case “apples” To “grapes”
MsgBox “Text between apples and grapes”
Case Else
MsgBox “Out of Range”
End Select
End Sub
Example – Option Compare Text
Option Compare Text
———————————————————————-
Sub OptionCompareText_Specified()
‘Option Compare Text is specified and therefore text comparison will NOT be case-sensitive;
‘Both “Apples” and “Grapes” will return the message “Text between apples and grapes”; both “gRound” and “ground” will return “Out of Range”
Dim str As String
str = InputBox(“Enter text”)
Select Case str
Case “apples” To “grapes”
MsgBox “Text between apples and grapes”
Case Else
MsgBox “Out of Range”
End Select
End Sub
Select…Case block of statements can be nested within each other and also with If…Then…Else statements and VBA loops (as inner or outer loop), without any limit. When Select…Case is nested within the other, it must be a complete block and terminate with its own End Select, within a specific Case or Case Else clause of the outer Select…Case block.
Example: (using nested Select…Case statements)
Sub selectCaseNested1()
‘check if a range is empty; and if not empty, whether has a numeric value and if numeric then if also has a formula; and if not numeric then what is the text length.
Dim rng As Range, iLength As Integer
Set rng = ActiveSheet.Range(“A1”)
Select Case IsEmpty(rng)
Case True
MsgBox rng.Address & ” is empty”
Case Else
Select Case IsNumeric(rng)
Case True
MsgBox rng.Address & ” has a numeric value”
Select Case rng.HasFormula
Case True
MsgBox rng.Address & ” also has a formula”
End Select
Case Else
iLength = Len(rng)
MsgBox rng.Address & ” has a Text length of ” & iLength
End Select
End Select
End Sub
Example: (Text Manipulation with nested Conditional Statements and VBA Loops)
Function StringManipulation(str As String) As String
‘This code customizes a string text as follows:
‘1. removes numericals from a text string;
‘2. removes leading, trailing & inbetween spaces (leaves single space between words);
‘3. adds space (if not present) after each exclamation, comma, full stop and question mark;
‘4. capitalizes the very first letter of the string and the first letter of a word after each exclamation, full stop and question mark;
Dim iTxtLen As Integer, iStrLen As Integer, n As Integer, i As Integer, ansiCode As Integer
‘—————————
‘REMOVE NUMERICALS
‘chr(48) to chr(57) represent numericals 0 to 9 in ANSI/ASCII character codes
For i = 48 To 57
‘remove all numericals from the text string using vba Replace function:
str = Replace(str, Chr(i), “”)
Next i
‘—————————
‘REMOVE LEADING, TRAILING & INBETWEEN SPACES (LEAVE SINGLE SPACE BETWEEN WORDS)
‘use the worksheet TRIM function. Note: the TRIM function removes space character with ANSI code 32, does not remove the nonbreaking space character with ANSI code 160
str = Application.Trim(str)
‘—————————
‘ADD SPACE (IF NOT PRESENT) AFTER EACH EXCLAMATION, COMMA, DOT AND QUESTION MARK:
‘set variable value to string length:
iTxtLen = Len(str)
For n = iTxtLen To 1 Step -1
‘Chr(32) returns space; Chr(33) returns exclamation; Chr(44) returns comma; Chr(46) returns full stop; Chr(63) returns question mark;
If Mid(str, n, 1) = Chr(33) Or Mid(str, n, 1) = Chr(44) Or Mid(str, n, 1) = Chr(46) Or Mid(str, n, 1) = Chr(63) Then
‘check if space is not present:
If Mid(str, n + 1, 1) <> Chr(32) Then
‘using Mid & Right functions to add space – note that current string length is used:
str = Mid(str, 1, n) & Chr(32) & Right(str, iTxtLen – n)
‘update string length – increments by 1 after adding a space (character):
iTxtLen = iTxtLen + 1
End If
End If
Next n
‘—————————
‘DELETE SPACE (IF PRESENT) BEFORE EACH EXCLAMATION, COMMA, DOT & QUESTION MARK:
‘reset variable value to string length:
iTxtLen = Len(str)
For n = iTxtLen To 1 Step -1
‘Chr(32) returns space; Chr(33) returns exclamation; Chr(44) returns comma; Chr(46) returns full stop; Chr(63) returns question mark;
If Mid(str, n, 1) = Chr(33) Or Mid(str, n, 1) = Chr(44) Or Mid(str, n, 1) = Chr(46) Or Mid(str, n, 1) = Chr(63) Then
‘check if space is present:
If Mid(str, n – 1, 1) = Chr(32) Then
‘using the worksheet Replace function to delete a space:
str = Application.Replace(str, n – 1, 1, “”)
‘omit rechecking the same character again – position of n shifts (decreases by 1) due to deleting a space character:
n = n – 1
End If
End If
Next n
‘—————————
‘CAPITALIZE LETTERS:
‘capitalize the very first letter of the string and the first letter of a word after each exclamation, full stop and question mark, while all other letters are lower case
iStrLen = Len(str)
For i = 1 To iStrLen
‘determine the ANSI code of each character in the string
ansiCode = Asc(Mid(str, i, 1))
Select Case ansiCode
’97 to 122 are the ANSI codes equating to small cap letters “a” to “z”
Case 97 To 122
If i > 2 Then
‘capitalizes a letter whose position is 2 characters after (1 character after, will be the space character added earlier) an exclamation, full stop and question mark:
If Mid(str, i – 2, 1) = Chr(33) Or Mid(str, i – 2, 1) = Chr(46) Or Mid(str, i – 2, 1) = Chr(63) Then
Mid(str, i, 1) = UCase(Mid(str, i, 1))
End If
‘capitalize first letter of the string:
ElseIf i = 1 Then
Mid(str, i, 1) = UCase(Mid(str, i, 1))
End If
‘if capital letter, skip to next character (ie. next i):
Case Else
GoTo skip
End Select
skip:
Next i
‘—————————
‘manipulated string:
StringManipulation = str
End Function
Sub Str_Man()
‘specify text string to manipulate & get manipulated string
Dim strText As String
‘specify the text string, which is required to be manipulated
strText = ActiveSheet.Range(“A1”).Value
‘the manipulated text string is entered in range A5 of the active sheet, on running the procedure:
ActiveSheet.Range(“A5”).Value = StringManipulation(strText)
End Sub
Use the GoTo statement to jump to a line within the procedure. The GoTo statement consists of 2 parts: (1) The GoTo statement which is the GoTo keywords followed by a Label which is the identifier; and (2) The Label – this consists of the Name of the Label followed by a colon, and then has a line of code. On satisfying a condition, the GoTo statement transfers control to a separate line of code within the procedure, identified by the Label. GoTo statement is usually avoidable if there is an alternate solution (usually there is – many times you can use If…Then…Else and Select…Case statements alternatively). It makes the code somewhat unreadable and confusing. It is used best for error handling, viz. “On Error GoTo”.
For live codes of running Select…Case Statements, click to download excel file.
Select…Case compared to If…Then…Else Statements
Both are Conditional Statements, wherein one or more conditions are evaluated and a block of code is executed depending on the result of the evaluation.
The difference lies in that in a Select…Case statement, a single expression (or variable) is considered and evaluated at a time. The variable to be evaluated is determined in the first line of “Select Case expression”, and then multiple Case statements specify the possible values. Whereas in If…Then…Else statements, multiple expressions (or variables) can be considered and evaluated simultaneously. Select…Case statement tests a single item for several possible values, whereas If…Then…Else statements test multiple items for several possible values. In this sense, If…Then…Else statements are more flexible in testing multiple variables for multiple conditions.
In case of a large number of conditions, If…Then…Else statements might get and appear confusing and the code tends to become unreadable.