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:

Code

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

Next

Output