User Rating: 4 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Inactive
 

 

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:

Select...Case Statement (VBA)

Syntax

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

Option Compare Setting

Nesting

GoTo statement

Select…Case compared to If…Then…Else Statements

-----------------------------------------------------------------------------------------------

 


 

Select...Case Statement (VBA)

 

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.

 

 

Syntax

 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

 

 

 

 

Option Compare Setting:

 

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

 

 

 

 

Nesting:

 

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

 

 

 

 

GoTo statement 

 

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.