SWITCH

SWITCH(expr1, value1 [,expr2, value2] [,,])

Returns a value based on expressions (Variant).


expr1The first expression you want to evaluate.
value1The value to return if "expr1" evaluates to True.
expr2(Optional) The second expression you want to evaluate.
value2(Optional) The value to return if "expr2" evaluates to True.

REMARKS
* This function evaluates all the expressions from left to right.
* If none of the expressions evaluate to True, then a Null value is returned.
* Always assign this function to a Variant data type just in case a Null value is returned.
* If a Null value is returned and the data type is not Variant, then you will get a run-time error.
* If the parts are not properly paired, then you will get a run-time error.
* This function should not be used as it leads to code that is harder to read and understand.
* An alternative to using this function is to use the Switch - Case statement.
* You can use the CHOOSE function to returns the value from a list of values based on an index number.
* You can use the IIF function to returns one of two parts, depending on the evaluation of an expression.
* You can use the ISNULL function to test if a variable contains the Null Value.
* The equivalent .NET function is Microsoft.VisualBasic.Interaction.Switch
* For the Microsoft documentation refer to learn.microsoft.com

Dim myVariant As Variant 
Dim myNumber As Integer

myNumber = 10
myVariant = Switch(myNumber = 5, "Five", myNumber = 10, "Ten", myNumber = 15, "Fifteen")
Debug.Print myVariant = "Ten"

myNumber = 20
myVariant = Switch(myNumber = 5, "Five", myNumber = 10, "Ten", myNumber = 15, "Fifteen")
Debug.Print VBA.IsNull(myVariant)

Switch(sChar = "A","Apples",sChar = "B","Bananas", sChar = "C", "Clementines")

Public Sub Procedure_Name()
   Dim iValue as Integer
   Dim sResult as String

   iValue = 20
   sResult = Switch(iValue = 10, "ten", _
                    iValue = 20, "twenty", _
                    iValue = 30, "thirty")
End Sub

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top