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: http://www.eng-tips.com/viewthread.cfm?qid=281108

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: http://www.eng-tips.com/viewthread.cfm?qid=281108 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
    Loop

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).