Program by Levels

 

VBA for Excel Beginners Level

 

VBA for Excel Intermediate Level

 

VBA for Excel Advanced Level

 

Tutorial on VBA for Excel (Macros)

bringing you the simplest and most efficient ways
to do things in VBA for Excel

VBA Lesson 2-9: VBA for Excel Statements
(Levels: Advanced)

In alphabetical order, here are the 17 VBA words that you need to learn to work efficiently with statements:

And, Case, Do, Else, ElseIf, End, Exit, For, If, Kill, Loop, Next, Or, Select, To, Until, With

When you have saved a workbook on your disk and you want to remove it you can use the KILL statement:
Kill "C:\myFile.xls"

IF

If..Then...End If

When there is only one condition and one action, you will use the simple statement:
If Selection.Value > 10 Then
  Selection.Offset(1,0).Value = 100
End If

In plain English: if the value of the selected cell is greater than 10 then the value of the cell below is 100 if not do nothing.

Note: Tests on strings are case sensitive so when you test a string of characters and you don't know if the user will use upper case or lower case letters, use the function LCase function within your test and write the strings in your code in lower case letters:

If LCase(Selection.Value).Value= "yes" then...
With this approach, your test will be valid whatever case your client uses (Yes, YES  or any other combination of cases).

If..Then...End If (multiple tiers)

When there are only two conditions that you want to check sequentially, you will use the statement:
If Selection.Value > 10 Then
  If Selection.Value = 12 Then
            Selection.Offset(1,0).Value = 100
  End If
End If

In plain English: first check if the value of the selected cell is greater that 10. If it is not do nothing. If it is check if the value of the selected cell is equal to 12. If so set the value of the cell below at 100 else do nothing.

If..Then...And...End If

When there are two inclusive conditions, you will use the statement:
If Selection.Value > = 10 And Selection.Offset(0,1).Value < 20 Then
  Selection.Offset(1,0).Value = 100
End If

In plain English: if the value of the selected cell is greater or equal to 10 and smaller  than 20 the value of the cell below is 100 otherwise do nothing.

If..Then...Or...End If

When there are two exclusive conditions and one action, you will use the statement:
If Selection.Value = 10 Or Selection.Offset(0,1).Value = 20 Then
  Selection.Offset(1,0).Value = 100
End If

In plain English: if the value of the selected cell is equal to 10 or equal to 20 then the value of the cell below is 100 otherwise do nothing.

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

When there is only one condition but two actions, you will use the statement:
If Selection.Value > 10 Then
  Selection.Offset(1,0).Value = 100
Else
  Selection.Offset(1,0).Value = 50
End If

In plain English: if the value of the selected cell is greater than 10 then the value of the cell below is 100 else the value of the cell below is 50.

If..Then..ElseIf...End If
When there are more than one condition linking each to a different action you will use the statement:
If Selection.Value = 1 Then
  Selection.Offset(1, 0).Value = 10
ElseIf Selection.Value = 2 Then
  Selection.Offset(1, 0).Value = 20
ElseIf Selection.Value = 3 Then
  Selection.Offset(1, 0).Value = 30
ElseIf Selection.Value = 4 Then
  Selection.Offset(1, 0).Value = 40
ElseIf Selection.Value = 5 Then
  Selection.Offset(1, 0).Value = 50
End If

In plain English: If the value of the selected cell is 1 then the value of the cell below is 10  but if the value of the selected cell is 2  then the value of the cell below is 20  but if the value of the selected cell is 3  then the value of the cell below is 30  but if the value of the selected cell is 4  then the value of the cell below is 40  but if the value of the selected cell is 5  then the value of the cell below is 50  but then if the value of the selected cell is not 1, 2, 3, 4 or 5 do nothing.

Select Case

Let's say that a variable or a cell can take 25 different values and depending on that value 25 different things should  happen. You can either build a very long sequence of If/ElseIf statements or use "Select Case"

Do..Loop

The Do...Loop statement does pretty much the same thing as the For..Next statement but you don't need to declare a counter because the Loop stops when it encounters a certain condition. Try the following procedure by first entering 1 in cells A1 to A7 or A27 or to as far down as you want to go.

Sub proTest()
Dim varCounter as Integer 

      Range("A1").Select
      Do Until Selection.Value = ""
                  Selection.Value = Selection.Value + 1
                  Selection.Offset(1, 0).Select
        Loop

End Sub

In plain English: starting in cell A1 add 1 to the value of the selected cell and move one cell down. Do this until the value of the selected cell is nothing.

Variation on the statement:

Do Until Selection.Value = ""
Do until the selected cell is empty

Do While Selection.Value < > ""
Do as long as  the selected cell is  not empty

Loop Until Selection.Value = " "
Loop until the selected cell is empty

Loop While Selection.Value < > " "
Loop as long as the selected cell is not empty

Exit...

You may exit a FOR..NEXT,   DO...LOOP and even a procedure at any time with the EXIT statement

If Selection.Value > 10 Then Exit For

If Selection.Value > 10 Then Exit Do

If Selection.Value > 10 Then Exit Sub

With...End With

In the old days when computer memory was rare and expensive and computers were not very powerful programmers would us a lot of With..End With statements because is was less requiring on the memory and the capacities of the computer. When you develop in VBA for Excel (very small programs)  memory is not really an object and our personal computers are as powerful as the large computers of yesterday. The macro recorder uses a lot of With..End With statements but I personally don't. Anyway here how it works.

Range("A3") .Select
With Selection.Font
                .Name = " Arial"
                .Size = 24
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .ColorIndex = xlAutomatic
End With

is the same as writing

Range("A3") .Select
Selection.Font.Name = " Arial"
Selection.Font.Size = 24
Selection.Font.Strikethrough = False
Selection.Font.Superscript = False
Selection.Font.Subscript = False
Selection.Font.OutlineFont = False
Selection.Font.Shadow = False
Selection.Font.Underline = xlUnderlineStyleNone
Selection.Font.ColorIndex = xlAutomatic

Both work it's your choice.

For..Next

The FOR...NEXT loop is the one I use the most. It allows you to repeat an action a certain number of times.

Sub proTest()

        Range("A1") = 10
        Range("A2").Select

        For varCounter = 1 To 10
                Selection.Value = Selection.Offset(-1, 0).Value * 2
                Selection.Offset(1, 0).Select
        Next

End Sub

In plain English: Set the value of cell A1 to 10 then select cell A2. While the counter is going from 1 to 10 (10 times in other words) the value of the selected cell is twice the value of the cell above...move one cell down. Resulting from this procedure, Cell A1=10, A2=20, A3=40.....A11=10,240.

If you'd write:
For varCounter = 1 To 10 Step 2
the deed would be performed only 5 times. Resulting from this procedure, cell A1=10, A2=20, A3=40.....A6=320.

Your can also start at the bottom and go up
For varCounter = 10 To 1 Step -1

When you use a For..Next statement on a set of data it is interesting to count the number of rows and have your counter move from 1 to the number of rows.
For varCounter= 1 to varNbRows

This page is one of the 33 lessons from the
Tutorial on VBA for Excel (Macros)
VBA for Excel made simple
                 
       

 

Developed and Presented by PLI Consultant Inc