How to Use Select Case in Access VBA …and Why

Microsoft Access, Microsoft Office, VBA

The Select Case statement in Visual Basic for Applications sometimes “competes” with the If…Then…Else statement, because the two can both be used for testing information. When we want to find out about more than one thing, condition, or what have you, we sometimes need to ask the equivalent of several questions. Which way we go, which type of code, is often our choice. All this emerges from the fact that computers ALWAYS need to do one thing at a time—they can’t make intuitive leaps, as humans can.

The If…Then…Else asks “If” something is true, much as in the IF function in Excel. If the answer is Yes, or True, we follow the instruction in the “Then” part of the statement. When it’s No, or False, we follow the one in the “Else”.

Should there be more than one question, we can hook another If on the tail of the first. We would use the keyword “ElseIf” rather than Else, to signify there’s more to come. And though we can only nest seven Ifs in an Excel formula, we can use as many as we like in Access VBA. Practically speaking, we don’t need that many most of the time.

So why should we use the Select Case statement? The two kinds of test ultimately work similarly; the end result will often be the same. But there is one point—writing a Select Case statement is a little simpler. It requires a little less typing. If you need to ask the equivalent of more than a few questions, there’s less chance of a typo.

The If…Then…Else has one advantage over Select Case. It can test for multiple conditions up front—that is, when the Ifs are set. If you need to check on department, salary, and time in job, for instance, it’s easier to use If…Then…Else. But if you can divide your tests into one-at-a-time questions, Select Case will do fine.

For more info, give our Access VBA class a look.