Structure/Flow in Formula Properties: If/Then/ElseIf/Else Construct

INTRODUCTION
You can do many things with formula properties by directly manipulating other property values – such as concatenating strings or calculating a rectangular area by multiplying the rectangle’s length by its width. You may find, however, that for certain scheduling situations, you need a more complex, multi-line formula. Perhaps you need to make several intermediate calculations, or need to test one or more values and take different actions based on the results of each test. One way to do the latter is to make use of an If/Then/ElseIf/Else construct. Keep in mind that once your formula goes beyond a simple single expression, you need to explicitly indicate the final value to be returned by the formula, by assigning that value to “RESULT”, which must be in all capital letters. For a conditional statement like the If/Then/ElseIf/Else construct, each branch that the formula can take must eventually have a RESULT assignment.

SYNTAX
The syntax for the If/Then/ElseIf/Else construct is fairly straightforward and should be familiar to anyone with programming experience – or an understanding of the words if, then and else. The If/Then/ElseIf/Else construct allows you to perform one or more actions if a certain condition is true. If that initial condition is false, you may optionally specify one or more additional test condtions and perform one or more actions associated with the first one of these conditions that is true. If all conditions specified are false, you may optionally specify one or more actions to be taken.

Here is the basic structure of a simple If/Then/ElseIf/Else construct. Explanations of the italicized items are listed below the structure; items shown in blue and enclosed by square brackets are optional. Note that you can further complicate things by nesting an If/Then/ElseIf/Else constructs within another one, by including the additional If/Then/ElseIf/Else construct as one of the statements to be evaluated.

If ifcondition Then
    [ifstatements]
[ElseIf elsif1condition Then
    [elseif1statements]]
[ElseIf elsif2condition Then
    [elseif2statements]]
[… insert additional ElseIf conditions
and optional statements as desired]
[Else
    [elsestatements]]
End If

ifcondition
This required statement must evaluate to True or False. See my previous post on operators for the comparison operators you can use to generate True of False in a condtional statement.

ifstatements
One or more optional statements may be placed here. These will be executed when the ifcondition is True.

elseif1condition
elseif1conditionstatements
elseif2condition
elseif2conditionstatements
One or more optional ElseIf “blocks” [the ElseIf, the associated condition, the Then and the optional statements] can be added to an If/Then/ElseIf/Else construct. When the ifcondition is false, any ElseIf conditions will be evaluated, in order, until either the first True ElseIf condition is encountered or all are found to be false. If a true condition is found, any optional statements associated with that ElseIf block are executed. ElseIf blocks must occur before the Else block, if an Else block is used.

elsestatements
When the ifcondition and all ElseIf conditions [if any are provided] are false, any optional elsestatements are executed.

There is a single-line syntax that could be used for very simple If statements, but that can be difficult to read, so I would recommend sticking with the format noted above. Note that in that form, each If must have a corresponding End If, but that an optional ElseIf does not have an End If. Speaking of legibility, indenting the statements to be executed makes it much easier to read than if you leave the statements flush with the If, ElseIf, Else and End If lines.

Here is another tip, to save you potential debugging problems, which I have experienced myself: note carefully that ElseIf has no space in it, whereas End If does.

EXAMPLE
Here is an example of the If/Then/ElseIf/Else construct in action, based on a question that has come up in the Architectural Desktop Discussion Groups several times. Automatic properties are wonderful [no data entry required, beyond that already done to define the object], but there are occasions where you may want to override that value in a schedule. For example, you may be using Space objects to represent rooms, and use the Height automatic property to represent your ceiling height in a room schedule. This works fine for most spaces, but you may have a few that have multi-level ceilings, and would like to be able to make note that the height varies in the schedule, rather than listing only one of two or more heights. This can be done quite easily by using a formula property. You will need the following:

• An unformatted version of the automatic Height property for Spaces. See my blog article on unformatted properties for more on how to set up an unformatted property.

• A manual property with a default value that would never be used as an override for the Height property, to hold the override value. Since in this case we want the override to indicate “VARIES”, the manual property will have its type set to Text, and the default value will be an empty string.

• A formula property, which tests the value in the manual property and, if it is the default value, passes through the unformatted Height property value. If the manual property contains an override value, the override value is passed through. A data format for the Height value is then assigned to the formula property, and this property is the one that is referenced by the Height column in the schedule.

The formula property for this would be:

If "[HeightManual]" = "" Then
    RESULT = CDbl([HeightUnformatted])
Else
    RESULT = "[HeightManual]"
End If

7/5/2005 Update:
I have amended my original sample formula, adding "CDbl()" to the [HeightUnformatted] RESULT, so that a whole number would be interpreted as a real number and not an interger. This is necessary for architectural formatting to be applied to the result when there is no override, and the [HeightUnformatted] property contains a whole number.

The following screen shots from Architectural Desktop 2006 show this formula with two test values for the HeightManual property: one with the default empty string, and one with the string “VARIES”. As designed, the formula returns the value of HeightUnformatted in the first case and the string VARIES in the second. Click on the image to view a larger version.


HeightManual set to default empty string.

HeightManual set to VARIES.