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

 

 

 

 

 


Making Decisions
(If...Then...Else...End If)

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.

Sub OneLinerIfTest()
    Dim strResult As String
    strResult =
InputBox(prompt:="Please enter amount", _
                                            Title:="Data Entry")

    If strResult = Empty Then Exit Sub 'Terminates here if empty
   

    Continues here if not empty...


End Sub

 

 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

 

Sub TrueIfTest()

 

       Code runs here first before it enters the If block...


    If ActiveCell.Value < 0 Then

       ActiveCell.Font.Bold = True

       ActiveCell.Font.Color = vbRed

 

       Additional code continues here only if true...

 

    End If

 

    Code continues here whether true or not!...

 

End Sub

 

 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

 

Sub StandardIfTest()

 

       Code runs here first before it enters the If block...

    ActiveCell.ClearFormats


    If ActiveCell.Value < 0 Then

       ActiveCell.Font.Bold = True

       ActiveCell.Font.Color = vbRed

 

       Additional code continues here only if true...

   
Else 'FALSE

       ActiveCell.Font.Italic = True

       ActiveCell.Font.Color = vbRed

       Additional code continues here only if false...

 

    End If

 

    Code continues here whether true or false...

 

End Sub

 

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

 

Sub MultipleIfTest()

 

       Code runs here first before it enters the If block...

    ActiveCell.ClearFormats


    If ActiveCell.Value < 0 Then

       ActiveCell.Font.Bold = True

       ActiveCell.Font.Color = vbRed

 

       Additional code continues here only if true 1...
 

    ElseIf ActiveCell.Value = 0 And ActiveCell.Value <= 100 Then

       ActiveCell.Font.Underline = xlSingle

       ActiveCell.Font.Color = vbBlue

 

       Additional code continues here only if true 2...


   
Else 'FALSE - catch for non true values

       ActiveCell.Font.Italic = True

       ActiveCell.Font.Color = vbRed

       Additional code continues here only if false...

 

    End If

 

    Code continues here whether true or false...

 

End Sub

 

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:

Sub NestedIfTest()

 

       Code runs here first before it enters the If block...

    ActiveCell.ClearFormats


    If IsNumeric(ActiveCell.Value) Then 'Is it a number?

      

       'Nested If Block inside the first If Block

       If ActiveCell.Value < 0 Then

           ActiveCell.Font.Bold = True

           ActiveCell.Font.Color = vbRed

            Else

           ActiveCell.Font.Bold = False

           ActiveCell.Font.Color = vbBlack    
         
 End If
 

       Additional code continues here only if true...

   
Else 'FALSE

       ActiveCell.Font.Italic = True

       ActiveCell.Font.Color = vbRed

       Additional code continues here only if false...

 

    End If

 

    Code continues here whether true or false...

 

End Sub

 

 

 



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

 

 

Want to teach yourself Access? Free online guide at About Access Databases

Tip: Revert to the Select Case statement for multiple If's that exceed 5 condtiotns.

VBA Keywords: ActiveCell, Font, Bold, Italic, Underline, Color, ClearFormats, InputBox IsNumeric and Exit Sub.

 

 

Home | Terms of Use | Privacy Policy | Contact

copyright 2010 TP Development & Consultancy Ltd, All Rights Reserved.

All trademarks are copyrighted by their respective owners. Please read our terms of use and privacy policy.