Excel Switch VBA Function: Translate Values into Other Values
Excel Switch Function
The Excel Switch function is used to translate specific values into corresponding values you define.
Switch(Variable=TextToEvaluate, TextToSubstitute, Variable= TextToEvaluate, TextToSubstitute) ‘This could be a continuous statement and not limited to two sets of data
Variable = Variable to be evaluated
TextToEvaluate = Text to be evaluated against the variable. If the variable matches the text, the output is replaced by the value we defined
TextToSubstitute = Text that will be used for the substitution.
Now let’s look at an Excel example. Let’s assume we have a bunch of string values contained in Column A which we want to substitute based on specific values we have defined.For the purposes:
Dim StringToProcess As String ‘Variable to hold the string to be processed
Dim A As Long ’Variable to process through the rows
For A = 2 To 5
StringToProcess = ActiveSheet.Cells(A, 1).Value
ActiveSheet.Cells(A, 2).Value = Switch(StringToProcess = “Excel”, “Great Tool”, StringToProcess = “ExcelGuide”, “Great site”, StringToProcess = “Cow”, “Animal”, StringToProcess = “Cars”, “Love them”)