Page 103 - Excel Progamming Weekend Crash Course

h540629 ch06.qxd 9/2/03 9:33 AM Page 78

78 Saturday Morning

With...End With

The With...End With construct cannot be called a control statement because
it does not modify code execution. It does, however, provide a handy short-
hand that simplifies writing code in certain situations. Recall that for using
an object property or an object method, the syntax is:

ObjectName.PropertyName
If you need to access a lot of properties or methods of the same object it can
require writing the object name over and over again many times. With...End
With lets you simplify the code as follows:
With ObjectName
.Property1 = Value1
.Property2 = Value2
.Method1
.Method2
End With
The above is exactly equivalent to the following:

ObjectName.Property1 = Value1
ObjectName.Property2 = Value2
ObjectName.Method1
ObjectName.Method2
With...End With is just a convenience, but it can save a lot of typing.

The If...Then Statement

The If...Then statement, or If statement for short, is used in a program to execute a
block of code if a specified logical condition is True. Optionally, it can also execute another
block only if the condition is False. In its simplest form, the If statement looks similar to:

If condition Then
block1
End If

If condition is True, the statements in the block (indicated by block1) are executed. If
condition is False, they are not executed. The block of statements can contain one or more
VBA statements, with no real length restriction (although it is good practice to keep things
short).
If there is only one statement to be executed, the End If can be omitted and the entire
If construct placed on one line:

If condition Then statement