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

 Select Case statement

This is an alternative way to write logical statements and designed for multiple and similar conditions tested in a look up table together.

It is deemed faster than a conventional If statement and more clinical to write and understand.

Structure (syntax):

Select Case Grade/Expression

    Case Value 1

        Execute multiple lines of code if true 1

        .....

    Case Value 2

        Execute multiple lines of code if true 2

        .....

    Case Value 3

        Execute multiple lines of code if true 3

        .....

    Case Value ^N

        Execute multiple lines of code if true ^N

        .....

    Case Else

        Execute multiple lines of code if false

        .....

End Select

Case Value is the value being test logically against the Grade or Expression for a true match. It continues down the list in order until it finds a true match with a catch using Case Else as the false option.

It sits all within the Select Case block using the End Select to terminate the logical test environment.

Note the keyword To meaning a range (the And operator) which is much easier to write as a condition. Also, using the , (comma) which acts as the Or operator only enhances the way you can use this statement quickly.

Next Topic: Looping (Do...Until/While...Loop)