Learn VBA : Select Case

Previously we talked about the If statements and how we can use these to perhaps route our respondents through a survey only showing the questions that are relevant to them. Another way to route our survey or script is to use the select statement and in this article we will show you how to do just that.

Now that we know the structure of these two statements, perhaps we should look at a third way to test things and do something bases on the results. The third option we have is the Select Case, the skeleton code for that is,

Select Case ( Value )
	Case is ( Condition )
		' Do something
	Case is ( Condition )
		' Do something
	Case ......
		' Do something
	Case else
		' Do something
End Select

Where Value is the thing that we are checking and the Conditions, however many we need are checked one after the other until one is valid and then we do something. If none of the conditions are true then we do what is in the Case Else. A real life example might be,

AskGenderAgain:

	Gender.ask()

Select Case ( Gender.response.value )
	Case is = {Male}
		Cars.ask()
		Beer.ask()
	Case is = {Female}
		Handbags.ask()
		Shoes.ask()
	Case Else
		Goto AskGenderAgain
End Select

As well as testing categorical style questions we can test numeric’s. An example of case statement testing a numeric could be something like this one.

Select Case NumberDays

	Case 0
		' Do Something
	Case 1 to 4
		' Do Something
	Case 5
		' Do Something
	Case > 5
		' Do Something
End Select

As we can see in this example we can test just a single number ( 0 ) or a range ( 1 to 4 ) or even if a number is greater than a specific value ( > 5 ). Using Select case is a very good way of testing lots of conditions and in my mind should be used instead of multiple if statements where ever possible. As always, if you would like any more help on this or any of our other topics then leave a comment or send us an email and we will be happy to help.

Leave a Comment