The Switch function

I rediscovered this very cool function while writing the CommentsControl add-in, and I thought that it’s, unfortunately, rarely used.

Here’s how MS defines it:

Evaluates a list of expressions and returns a Variant value or an expression associated with the first expression in the list that is True.


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

Basically, think of it as a special Select Case statement, like this:

Select Case True
Case expr-1
Case expr-2
Case expr-n
End Select

The main advantage is that it is compact and easy to write, but the main disadvantage is that it evaluates *all* the expressions, like IIF(), so if one of them returns an error, Switch() will fail.

In my case, I used it to test 3 OptionButtons, like this

Dim Choice As Long

Choice = Switch(OptionButton1.Value, 0, OptionButton2.Value, 1, OptionButton3.Value, 2)

Posted in Uncategorized

6 thoughts on “The Switch function

  1. I think actually has a Select Case True syntax. The criticism I’ve read is that the comparisons can be poorly structured, i.e., not necessarily mutually exclusive:

    DougsPhysique = Switch(DougIsTall, 1, DougIsFunnyLooking,2)

    But your example looks like a handy way to make a concise Case statement, as you said.

  2. Because Switch() forms part of VBA5, it can be used in MS Access/Jet SQL syntax e.g.

    SELECT CompanyName, SWITCH(
      Country IN (‘Canada’, ‘Mexico’, ‘USA’), ‘North America’,
      Country IN (‘Argentina’, ‘Brazil’, ‘Venezuela’), ‘South America’,
      Country IS NULL, ‘(Not Known)’,
      TRUE, ‘Rest of the world’)
    AS Locale FROM Customers;

    The fact Switch() is ‘not necessarily mutually exclusive’ is an advantage when you consider the alternative is a set of nested/concatenated IIf statements.



  3. First of all I want to thank Dick for building such a great blog and all that are sharing valuable tips. I have a problem about excel chart and will appreciate if someone here can give me some hint. I have lots of charts from Excel. When I forward these charts to the graphic designer who will use them to make a handbook, she has trouble to select the colors as Excel charts don’t seem to have CMYK. So she needs to edit every single chart to make them printable. In order to relieve her burdon, I am thinking if there is something that can make Excel charts compatible with her photoshop or something. Would anyone have an idea? Thank you in advance!

  4. Very cool.

    Your use of SWITCH is a natural in this case because OptionButtons only have two states, True and False.

  5. Yes, it’s a useful function. One Iuse extensively in SQL / Crystal reports… In fact it was by accident that I discovered it was also valid in VBA :-)

  6. Hi JPG,

    Yes, Switch can be a useful tool together with IIf. I’ve used them as well as Choose for several years. Note that CHOOSE is also available in XL.

    It’s unfortunate that VB evaluates all choices and possible results before making any decisions (but that is also true for its handling of boolean operators). That reduces the value of constructs such as IIf some since one cannot create

    Also, and I don’t know why, but MS didn’t extend IIf into VBScript (and hence ASP). This lack makes for some very clumsy code constructs. :(

    Finally, yes, I know one can easily implement one’s own version of IIfwith
    Function IIf(c,t,f):If c then IIf=t else IIf=f:End function

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.