Saturday, 1 December 2012

Excel VBA Macros-Decision Structures - IF-Then-Else-ElseIf and Select Case

Decision Structures - IF-Then-Else-ElseIf and Select Case

IF ... Then Statement
The IF ... Then is a single condition and run a single statement or a block of statement.

Example:

The following statement set variable Status to "Adult" if the statement is true:

If Age >= 18 Then Status = "Adult"

You can also use multiple-line block in the If statement as followed:

If Ago >= 18 Then
Status = "Adult"
Vote = "Yes"
End If

Note that in the multiple-line block case, End If statement is needed, where the single-line case does not.
IF ... Then ... Else
The If ... Then ... Else statement is used to define two blocks of conditions - true and false.

Example:

If Age >=22 Then
Drink = "Yes"
Else
Drink = "No"

End If

Again, note that End If statement is needed in this case as well since there is more than one block of statements.


IF ... Then ... ElseIf
The IF ... Then ... ElseIf is used to test additional conditions without using new If ... Then statements.

For Example:

If Age >= 18 and Age < 22 Then
Msgbox "You can vote"
ElseIf Age >=22 and Age < 62 Then
Msgbox "You can drink and vote"
ElseIf Age >=62 Then
Msgbox "You are eligible to apply for Social Security Benefit"
Else
Msgbox "You cannot drink or vote"
End If

Note that the last condition under Else is, implicitly, Age < 18.
Select Case
Select Case statement is an alternative to the ElseIf statement. This method is more efficient and readable in coding the the If ... Then ... ElseIf statment. 

Example:


Select Case Grade
Case Is >= 90
LetterGrade = "A"
Case Is >= 80
LetterGrade = "B"
Case Is >= 70
LetterGrade = "C"
Case Is >= 60
LetterGrade = "D"
Case Else
LetterGrade = "Sorry"
End Select

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts