Excel Switch VBA Function

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.




The Basics:


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”)