VBA Switch Statement

This article will demonstrate how to use the VBA Switch statement.

The VBA Switch statement is used in a similar way to the VBA Select Case statement.

The Syntax of the Switch Statement

The switch statement evaluates a list of expressions to see if they are true or false, and returns the value of the first expressions that is evaluated as true.   The argument list is made up of pairs – an expression and a value that is returned if the expression is evaluated as being true.

Switch(expr-1, value-1, [ expr-2, value-2…, [ expr-n, value-n ]])

Using the Switch statement in a VBA Procedure

To use this in a macro could be similar to this example below:

Sub TestSwitch()
   Dim strCompany As String
   Dim CompanyID As Integer
   CompanyID = 2
   strCompany = Switch(CompanyID = 1, "Apple", CompanyID = 2, "IBM", CompanyID = 3, "Samsung")
   MsgBox strCompanies
End Sub

We are declaring a integer variable to hold the values from the expressions within the switch statement. We then declare another string variable to return the value that is contained within that expression.

Using the Switch Statement with a Range

Instead of hard-coding the value that the switch is going to evaluate into the code, we can use a value that we have stored in an Excel cell to return the string the we require.

Sub TestSwitch()
  Dim strCompany As String 
  Dim CompanyID As Integer
  CompanyID = Range("A2")
  strCompany = Switch(CompanyID = 1, "Apple", CompanyID = 2, "IBM", CompanyID = 3, "Samsung")
  MsgBox strCompanies
End Sub

To use this procedure, we would need to have a number in the cell A2 in the worksheet.  We can then run the macro to get the name of the company (ie Samsung).

If we do not have a number in cell A3 and we run the code, a Type Mismatch Error will be returned.

Creating a Custom Function with VBA Switch

We can also create a custom function using VBA switch and use it within our Workbook.

Function SwitchStatement(i As Integer) As String
  SwitchStatement = Switch(i= 1, "Apple", i= 2, "IBM", i= 3, "Samsung")
End Function

To use this within our workbook, we can use the Insert Function dialog box.

Click in the cell A3 and then click on Insert Function in the Function bar.

From the Insert Function dialog box, select User Defined from the Select Category drop down list, and then select SwitchStatement.

Click OK, and then click on cell A2.

The name of the company, as stored in the Switch Statement will be returned to our worksheet.