The Switch Function (VBA and UDF)

Switch is a VBA function that operates in a similar way to the Select Case statement.  The function arguments are a series of pairs of values, the first of each pair being an expression that can be evaluated as TRUE or FALSE, and the second a value of any data type.  The function return value is the value immediately following the first expression that evaluates to TRUE.  If none of the expressions are true then the VBA function returns null.

I was recently reminded of an Excel User Defined Function (UDF) that works in a similar way, written by Eng-Tips regular “electricpete”.  The original version of the UDF appeared here:

This is both a useful function in it’s own right and a good illustration of the use of the VBA ParamArray function argument.  The code is shown below, and is also included in a sample spreadsheet that may be downloaded from SwitchPY. The spreadsheet includes several examples of the UDF used to generate soil PY curves for use in the analysis of piles under lateral loads.

Function SW(ParamArray invar()) As Variant

' Original function posted by "electricpete" on Eng-Tips forum: 12 Sep 2010
' Minor modifications by Doug Jenkins 27 Jan 2012

' implement logic similar to switch
' example call
'  =switch(boolean1, value1, boolean2, value2, boolean3, value3....)
'    returns the value corresponding to the first true boolean

' at least one of the boolean expressions must be true
' requires an even number of arguments
' the syntax is pretty much identical to vba switch, except that there is no explicit allowance for else value
' if you want an else clause, enter true for the next to last argument, followed by the associated value

' Note that indexing of invar starts at 0, regardless of Option Base statement

' Check to confirm even number of arguments (as required)

    Dim ctr As Long        ' loop counter
Dim tempswitch As Variant        ' variable which will hold the output value

If UBound(invar) Mod 2 <> 1 Then
        SW = "Error: Need even number of arguments for sw"
        Exit Function
    End If

    ctr = 0        ' initialize counter
    Do While True        ' loop until broken by exit command
        ' Check for boolean input
If VarType(invar(ctr)) <> vbBoolean Then
            SW = "Error 1st 3rd 5th etc arguments of sw must be boolean"
            Exit Function
        End If

If invar(ctr) Then        ' in this case have found a true value, assign function and exit
tempswitch = invar(ctr + 1)
SW = tempswitch
            Exit Do
        Else        ' Else have not found true yet, update counter and continue loop
ctr = ctr + 2
        End If

' Check for reaching end of invar without having found true
If ctr + 1 > UBound(invar) Then
            SW = "Error: sw needs at least one true boolean argument"
            Exit Function
        End If

End Function

An example of the use of the UDF is shown in the screenshot below:

Switch Function used to generate PY curves

In Column D the UDF is used to select between two values, which has no real advantage over the use of a single IF function:

Simple use of the Sw UDF

A more complex example is shown below:

Complex Sw() UDF example

In this example the first expression checks if the depth of the section (D12) is below a calculated transition level (D16), and if so it returns the adjacent value in Column D:

  •  =sw($D$12>$D$16,D26,

The second statement checks if the pile deflection, B26, is less than 3 x the Y50 value (D15), and if so returns the adjacent value in Column D:

  • B26<=3*$D$15,D26,

The third statement checks if the pile deflection, is greater than 15 x the Y50 value (D15), and if so returns a calculated value:

  • B26>15*$D$15,$D$13*0.72*$D$12/$D$16,

Finally any remaining values are evaluated with a different formula:

  •  TRUE,$D$13*0.72*(1-(1-$D$12/$D$16)*(B26-$D$15*3)/(12*$D$15)))

The UDF includes error checking to check that there are an even number of inputs, the first input in each pair evaluates as a boolean (i.e. TRUE or FALSE), and that at least one of the boolean expressions is TRUE.  The only change I have made is to the message return method if an error is found.  The original function used a message box, which I have changed to the function returning a text error message (to avoid getting a million message boxes if a function with an error was copied to a million cells).

This entry was posted in Excel, Geotechnical Engineering, Newton, UDFs, VBA and tagged , , , , . Bookmark the permalink.

9 Responses to The Switch Function (VBA and UDF)

  1. JP says:

    I had no idea VBA had a switch() function. Reminds me of the PHP function of the same name. Thanks for sharing that! Learn something new every day.


    • dougaj4 says:

      I t was new to me too, both when I saw it the first time in 2010, and when I saw it a couple of days ago!

      Hence the blog link, maybe I’ll remeber it this time.


  2. John Tolle says:

    I can’t resist posting a link to my approach to the same thing:


    • dougaj4 says:

      Thanks John, some interesting ideas there. Must have a play with your mini UDF (arr()).


    • lhm says:

      @John – nice solution. I think your Arr(.) function is equivalent to using choose({1,2,3,…},.) but without the need for an integer array. The following formula might also work for this and could be extended to around 50 arguments or so:
      =CHOOSE(1-LOG(NPV(1,A1>5,A1<5,A1=5,1),2),"gt 5","lt 5","eq 5")
      …your Cases() example is a lot clearer though🙂


  3. dougaj4 says:

    Is there anything that can’t be done with the NPV function?🙂


    • lhm says:

      Yes, it’s a surprisingly versatile and quick function. I did a quick speed test for a million cells (actually a full column) and three conditions which gave the following results:

      0.32s IF
      0.60s NPV
      17.0s arr
      18.5s sw
      43.3s cases

      Nested IF statements are fastest as one would expect but the formula above is not far off – which was corrected by adding an extra comma before the second 1 i.e. NPV( … ,,1). This was tested in Excel 2010, I also tried Excel 2007 but the cases() function didn’t compile correctly and the udfs were considerably slower.


      • dougaj4 says:

        Thanks for that Lori. A bit dissapointing the UDFs are so slow. I might have a go at speeding them up some time (but not tonight).

        On the other hand, even in the worst case I suppose that 43 millions of a second per cell is not too bad for most applications.


  4. Pingback: Daily Download 33: Miscellaneous | Newton Excel Bach, not (just) an Excel Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s