Structure/Flow in Formula Properties: Select Case/Case/Case Else/End Select Construct

INTRODUCTION
Here is another VBScript construct that you can use to change the RESULT of your formula based on the current value of a VBScript expression. The expression can be the current value of a property from a Property Set attached to the same object, a variable that you defined earlier in the formula [perhaps by manipulating the values of one or more properties] or a VBScript statement [perhaps operating on a property or a previously defined variable]. The Select Case construct allows you to specify multiple different actions based on the value of that expression. A Select Case construct can be much easier to set up and maintain than a complex If/Then/ElseIf/Else/End If construct, particularly if you start nesting additional If/Then/ElseIf/Else/End If constructs, and it does not require evaluating the expression for each conditional statement. As with the If/Then/ElseIf/Else/End If construct http://architects-desktop.blogspot.com/2005/07/structureflow-in-formula-properties.html, each branch of the Select Case construct must end in assigning the formula properties final value to “RESULT”. Depending upon your needs, the RESULT statement could be after the end of the Select Case block of code, which may simply set other variables to appropriate values. But many times you will set a RESULT in the action statements associated with each case; when doing so, be certain to include a RESULT statement in the action statements for each case branch.

SYNTAX
You start out by giving the expression, then list various possible values and the actions to take when the expression evaluates to that particular value. You can optionally include a Case Else statement to specify actions to be taken if none of the listed cases matches the current value of the expression. It is good programming practice to include a Case Else statement, even if you are one-hundred percent certain that you have covered every possible value in the Case statements, even if all you do there is assign a value that indicates an unexpected even has occurred. Seeing that value will let you know that somehow, the expression has taken on an unexpected value and make debugging your formula that much easier.

The basic structure of the Select Case construct is shown below. Explanations of the italicized items are listed below the structure; items shown in blue and enclosed by square brackets are optional. You can nest Select Case constructs within each other by including one in the action statements to be evaluated when a particular Case is true.

Select Case expression
     [Case expressionlist1
           [actionstatements1]]
     [Case expressionlist2
           [actionstatements2]]
     […insert additional Case statements,
                 expression lists and action statements here]
     [Case Else
           [elseactionstatements]]
End Select

expression
This required element is the value that is compared to the values specified for each Case. It can be an actual value [e.g. 23.0, “String”], a variable [the current value assigned is used], a math or comparison expression [11.0 + 12.0, x > 23.0] or a user-defined or VBScript function that returns a value.

expressionlist1
expressionlist2
These are the values that are compared to the expression; you must provide at least one value for each Case you include in your Select Case construct. You can list multiple values by separating each value by a comma. Each value can be any of the types listed for the expression. When one of these values matches the value of the expression, the action statements associated with that Case are executed. You may have any number of Case blocks of code.

actionstatements1
actionstatements2
One or more statements to be executed when the value for a particular Case matches that of the expression.

elseactionstatements
One or more statements to be executed if none of the Case values match the expression value.

EXAMPLES
This example is based on the way my firm handles door frame types and materials. We designate each frame type with a letter; each type has a particular material associated with it. There are a number of “office standard” types and the letters assigned to these types are reserved for that type; custom types for a particular project never make use of the letter designation assigned to an office standard type. There are certain door types that can have more than one frame type associated with it. Rather than have a separate door style for each door type/frame type combination, our frame type property is in an object-based property set. To avoid have the user have to enter both the frame type designation and the material for office standard frame types, a formula property using a Select Case construct is used to automatically assign the correct material type. A manual property is included in the property set so that the user can specify the material for custom frame types. The Case Else block of code is used to read in and return this value only when the frame type designation is not an office standard one. The following formula shows an example of this concept. It assumes the following:

• The formula property is called FrameMaterial and is the property to be included in the door schedule.

• There is a manual text property called FrameType, which has default value of “--”, into which the user will enter the appropriate letter designation for a given door’s frame type.

• There is a manual text property called FrameMaterialNonStandard, with a default value of “--”, into which the user will enter text indicating the material type of custom frame types. Note that if a standard frame type designation is used, the value of this property will not be used, whether or not the user changes it from the default value.

Select Case “[Frame Type]”
    Case “A”,”D”
        RESULT = “HM”
    Case “B”
        RESULT = “WD”
    Case “C”
        RESULT = “AL”
    Case Else
        RESULT = “[FrameMaterialNonStandard]”
End Select

Our actual formula is quite a bit longer, but the above should get the idea across.

Anyone familiar with the VBA implementation of the Case Select construct should note that VBScript does not support the To or Is keywords in an expressionlist. These are handy for specifying a range of values to compare to the expression, but are not available in a formula property. One way around this is to set the expression to true or false, using the VBScript constants vbTrue or vbFalse respectively. You can find these in 2006 in the “Insert VBScript code: “ pane in the lower right of the Formula Property Definition dialog, under Constants > Tristate. Then set up your expressionlists with comparison statements that evaluate to true or false. The following example is fairly simple, and could just as easily have been done with an If/Then/Elseif/Else construct. But if you had many ranges, with different actions to take for each range, a Select Case construct may be easier to set up and understand in the future.

dblWidth = CDbl([WidthUnformatted])
Select Case vbTrue
    Case dblWidth < 36.0
        RESULT = "SMALL"
    Case dblWidth > 36.0
        RESULT = "LARGE"
    Case Else
        RESULT = "STANDARD"
End Select

The following screen shots show the first example formula returning the value "HM" when the FrameType is "D" and returning the value in FrameTypeNonStandard, when the FrameType is "Q", which does not match the any of the values in the Case statements.

FrameType "D"

FrameType "Q"