This variation of the If...Then...Else statement allows you to choose from several alternatives. Adding clauses expands the functionality of the If...Then...Else statement, so you can control program flow based on different possibilities.

If condition1 Then
[statementblock1]
[ElseIf condition2 Then
[statementblock2] ]
[Else
[statementblockn] ]
End If

You can have as many ElseIf statements as you want in a block If, but none can appear after the Else clause. Block If statements can be nested; that is, contained within one another.

Another way to choose between several alternatives is the Select Case statement.

Repeat a group of instructions a specified number of times

For counter = start To end [ Step increment ]
[statementblock]
[Exit For]
[statementblock]
Next [counter [, counter]]

The Step value controls loop execution is as follows:

Once the loop has been entered and all the statements in the loop have executed, Step is added to counter. At this point, either the statements in the loop execute again (based on the same test that caused the loop to execute in the first place), or the loop is exited and execution continues with the statement following the Next statement.

You can nest For...Next loops by placing one For...Next loop within another. Give each loop a unique variable name as its counter. The following construction is correct:

Another way to construct the Next statements is as follows:

Repeat a group of statements for each element in an array or collection.

This statement differs from the For . . . Next statement, in that you iterate over the contents of a collection of objects without the need to provide an index. Some ActiveX collections do not provide a way to use indexes, so this statement is the only way to use the contents of a collection.

For Each index In container
[statementblock]
Next [index]

The For . . . Each block is entered if there is at least one element in container. Once the loop has been entered, all the statements in the loop are executed for the first element in container. If there are more elements in container, the statements in the loop continue to execute for each element. When there are no more elements in container, the loop is exited and execution continues with the statement following the Next statement.

If the container is an object, it must resolve to an OLE container object. If it is an OLE container, and the container contains objects, the index variable must be DIMmed as an object also. For each element in the container (array, enumerator for the OLE container), the index variable is set to that value and the statements executed.

You cannot use the For . . .Each . . . Next statement with an array of user-defined types because a Variant cannot contain a user-defined type.

Any number of Exit For statements may be placed anywhere in the loop as an alternative way to exit. Exit For is often used after evaluating some condition. For example, an If …Then statement would transfer control to the statement immediately following Next.

This example uses the For Each...Next statement to display the value of each element in a collection. Here, k is an element of the collection named MyArr.

The single-line form of If...Then...Else is often useful for short, simple conditional tests.

If condition Then thenpart [Else elsepart]

Parts:

The thenpart is executed if condition is true; if condition is false, then elsepart is executed. If the Else clause is not present, control passes to the next statement in the program.

You can have multiple statements within a condition, but they must be on the same line and separated by colons.

The block form of If...Then...Else provides more structure and flexibility than the single-line form and is usually easier to read, maintain, and debug.

If condition1 Then
[statementblock1]
[ElseIf condition2 Then
[statementblock2] ]
[Else
[statementblock3] ]
End If

Parts:

In executing a block If, the map tests condition1, the first numeric expression. If the expression is true, the statements following Then are executed. If the first expression is false, the map begins evaluating each ElseIf condition in turn. When the map finds a true condition, the statements following the corresponding Then are executed. If none of the ElseIf conditions is true, the statements following Else are executed. After executing the statements following Then or Else, the program continues with the statement following End If.

The Else and ElseIf blocks are both optional. You can have as many ElseIf clauses as you like in a block If, but none can appear after an Else clause. Any of the statement blocks can contain nested block If statements.

The map looks at what appears after the Then reserved word to determine whether or not an If statement is a block If. If anything other than a comment appears after Then, the statement is treated as a single-line If statement.

A block If statement must be the first statement on a line. The Else, ElseIf, and End If parts of the statement can have only a line number or line label in front of them. The block must end with an End If statement.

Use the If… Then… Else statement to define two blocks of statements. One of the statements runs when the specified condition is True, and the other one runs when the condition is False. When you want to define more than two blocks of statements, use the ElseIf Statement.

You can nest up to ten levels of If... Then... Else statements. If you need to create an expression with more than ten levels, you must redefine it using the ElseIf statement or the Select Case...End Case Statement.

This example tests the length of a field after spaces have been removed to determine if the field contains data. If the source field contains no data, a string is placed in the target field that says No Data Available. If there is data in the source field, the data is placed in the target field.

Require variables be declared before they are first used

Option Explicit

The Option Explicit statement tells the expression language to require the use of a Dim, Public, or Private statement. It is usually the first statement in an expression, so it is used more as a policy setting rather than a property that is turned on and off. The default is Option Implicit.

The Option Implicit statement, which does not require that variables be declared before they are first used, is the default behavior.

Option Explicit applies to the entire "expression context", excluding Process steps and Transformation steps. Expression context is defined as either the entire conversion, or all expressions in a context.

If you use variables in functions elsewhere (such as target field expression or BeforeTransformation event) you must use the Option Explicit statement in your expressions each time.

This statement, which includes Option Explicit, generates an error at run time, since the declared variable name is misspelled. If the Option Explicit function is not used, the error is simply written as an error message in the log. The compiler does not error out, since it automatically defines the misspelled variable name for you. It is, therefore, useful to always use Option Explicit when declaring variable names in a transformation.

Cause transformation to error if field elements do not exist

Option FieldExplicit

This can be set in the BeforeTransformation event, and applies to all code modules. If it is to be used in a single code module, it is usually the very first statement in that module.

When writing user-defined functions, Option FieldExplicit is the default behavior.

To treat missing elements as Nulls, use the Option FieldImplicit Statement.

Cause transformation to treat missing field elements as Null

Option FieldImplicit

This can be set in the BeforeTransformation event, and applies to all code modules. If it is to be used in a single code module, it is usually the very first statement in that module. When writing user-defined functions, Option FieldExplicit Statement is the default behavior.

Turn off the requirement that variables be declared before they are first used

Option Implicit

This is usually the very first statement in a code module, so it is used more as a policy setting rather than a property that gets turned on and off.

When writing user-defined functions, keep in mind that Option Implicit is the default behavior. Use of Option Implicit's opposite, the Option Explicit statement, is recommended, since Option Explicit requires that variables be declared before they are used.

This program simply results in a bug in the code. The interpreter does not complain, since it would just automatically define the misspelled variable name for you.

Conditionally execute one of several statement blocks, depending on the value of an expression

Select Case testexpression
[Case expressionlist1
[statementblock1] ]
[Case expressionlist2
[statementblock2] ]
...
[Case Else
[statementblockn] ]
End Select

If testexpression matches the expressionlist associated with a Case clause, the statement block following that Case clause is executed up to the next Case clause, or for the final one, up to End Select. Control then passes to the statement following End Select.

You can use multiple expressions or ranges in each Case clause.

You also can specify ranges and multiple expressions for character strings.

If testexpression matches more than one Case clause, only the statements following the first match are executed.

Select Case statements can be nested. Each Select Case statement must have a matching End Select statement.

The expression list that follows the Case in a Select Case statement must be a list of constant numeric or string expressions. If you want to use variables for different cases, use the If...Then...Else Statement instead.

Execute a series of statements in a loop as long as a given condition is true

While condition
[statementblock]
Wend

The argument condition is a numeric or string expression that evaluates True (nonzero) or False (0 or Null).

If condition is true, all statements in statementblock are executed until the Wend statement is encountered. Control then returns to the While statement and condition is checked again. If condition is still true, the process is repeated.

If it is not true, execution resumes with the statement following the Wend statement.

While...Wend loops can be nested to any level. Each Wend corresponds to the most recent While.

This example uses While...Wend to print the numbers 1 through 4: