VBA HOME PAGE

Menu

Recording macros

Looking at the code

Ways of running macros

Where macros are stored

Reasons to write macros

Writing macros

Procedure types

Visual Basic editor (VBE)

Rules & conventions

Excel objects

Range/Selection objects

Object hierarchy

Object browser

Chart objects

Pivot Table objects

Formulas

Visual Basic Functions

Creating Add-Ins

Variables & constants

Object variables

Arrays

Collections

Message Box

VBA Input Box

Excel Input Box

Making decisions (If)

Making decisions (Case)

Looping (Do...Loop)

Looping (For...Loop)

With...End With blocks

User defined functions

Event handling

Error handling

Debugging

Creating User Forms

DAO/ADO Objects

Input/Output Files

Other links

Example code snippets

Userform input example

This is one of the 'control flows' VBA provides which is required to make your VBA code procedures more flexible, reduce the amount of code required and make the system think for itself!

Without any of these 'control flows', your code is very linear and rigid which never is really suitable when trying to mimic 'real world' processes.

The four 'control flows' are:

1.  If...Then...Else...End If

2.  Select Case...End Select

3.  Do...Until/While...Loop

4.  For...Counter/Each...Next

 If Statement

This is the logical (conditional test) statement that runs code the yields either a True or False value.

There are four flavours of the If I have referred to help understand how this structure is implemented in VBA:

1.  'One Line' If

2.  'True' If

3.  'Standard' If

4.  'Multiple/Nested' If

A logical test can be a value, expression, function or an object property that returns a True or False value using the logical operators <, >, <=, >=, =, <>, Not
(see Formulas).

 One Liner If

As its name suggests, it's all on one line and requires no End If block.

Structure (syntax):

If Condition [= True] Then Execute code if true

It is used as a quick way to add an extra single calling command or calling an additional procedure if the condition is true.

I use it as a test to see if the procedure should continue and if not to terminate here.

 True If

This is used in include extra code if true but is used in an If block so that multiple lines of code can be applied here.

Structure (syntax):

If Condition [= True] Then

    Execute multiple lines of code if true

    .....

End If

 Standard If

This is more common type of If block as it provides a True and False option and will therefore (logically) choose one procedure to call/run.

This can be compared to the more familiar If function in Excel where users specify a True and False returning value.

Structure (syntax):

If Condition [= True] Then

    Execute multiple lines of code if true

    .....

Else

    Execute multiple lines of code if false

    .....

End IF

The Else keyword is the new addition and acts as the False (the catch) should the true fail.

 Multiple/Nested If

What about have more than one set of true conditions with a false (as a catch)?

Nested or multiple If's can be as many as required and run in order of their logical conditions.

Structure (syntax):

If Condition 1 [= True] Then

    Execute multiple lines of code if true (1)

    .....

ElseIf Condition 2 [= True] Then

    Execute multiple lines of code if true (2)

    .....

ElseIf Condition ^N [= True] Then

    Execute multiple lines of code if true (^N)

    .....

Else

    Execute multiple lines of code if false

    .....

End If

The first condition is tested and if True stops and runs code in that block. If the first condition is False then the second If test condition is tested. Therefore, the second condition is only executed if the first condition failed.

You can use the ElseIf keyword as many times for each separate new condition but if you intend to have more than five different conditions then switching to the Select Case statement is the better practice as it's quicker and cleaner to write.

A nested If is one which starts a new block inside another If block:

Next Topic: Making Decisions (Select Case...End Select)