Call

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