Tuesday, 20 September 2016

VBA Switch Statement Syntax and Example

SWITCH statement in VBA:
The SWITCH statement in VBA evaluates a list of conditions and returns the value of the True/match condition from list.
Incase of False it will returns the NULL value, that we need to handle by If condition to perform the False case operation.
Syntax:
Switch ( Condition1,value1, Condition2,value2, ... ConditionN,valueN)
Here:
Condition1,Condition2...ConditionN are the conditions to be evaluate.
value1, value2, ... valueN are the values to be written if the corresponding condition is true.

Example:
In the below example we are checking the Range("A1") value and returing the Grade type in Range("B1") 

Sub SwitchStatement()
Dim XValue As String
Dim X As Integer
X = ActiveSheet.Range("A1").Value

 XVal = Switch((X > 75 Or X = 100), "A-Grade", (X >= 50 And X < 75), "B-Grade", (X < 50 And X >=35), "C-Grade")

If IsNull(XVal) = True Then
    ActiveSheet.Range("B1").Value = "Not Applicable"
Else
    ActiveSheet.Range("B1").Value = XVal
End If
End Sub

Thanks,
TAMATAM

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts