Excel: Contents

VBA: Contents

Home

Search

Contacts

Articles

Resources Workbooks

Consulting Services

VBA Excel Tips: Statements

The functions and formulas offered below are:

  Indispensable

  Very useful

  Interesting

My favorite statement is the SELECT CASE statement. When I learned it, I had the feeling of becoming a real programmer. No more tons of "IF" and "ELSEIF", just a SELECT CASE.

I present it to you on this page and I have just created a Resource Workbook on it.

The statements that I used more often are Beep, If..Then..EndIf,Do...Loop, For...Next, SelectCaseand With... End With

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 LCasefunction within your test and write the strings in your code in lower case letters:
If LCase(Selection.value)= "various" then...
or
Select Case LCase(Selection.value)
or
DO WHILE LCase(Selection.value)<>"various"
With this approach, your test will be valid whatever case of letter your client uses.

Beep
Add some sound to your procedures with the statement Beep
If yourCondition Then
  Beep
End If

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

or more simply:
If Selection.Value > 10 Then Selection.Offset(1,0) = 100

If..Then...EndIf (multiple tiers)
When there is only two conditions that you want to check sequentially and one action, you will use thestatement:
If Selection.Value > 10 Then
  If Selection.Value = 12 Then
  Selection.Offset(1,0) = 100
  End If
  Selection.Offset(1,0) = 20
End If

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

If..Then...Or...EndIf
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) = 100
End If

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

If..Then..ElseIf...E ndIf
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) = 10
ElseIf Selection.Value = 2 Then
  Selection.Offset(1, 0) = 20
ElseIf Selection.Value = 3 Then
  Selection.Offset(1, 0) = 30
ElseIf Selection.Value = 4 Then
  Selection.Offset(1, 0) = 40
ElseIf Selection.Value = 5 Then
  Selection.Offset(1, 0) = 50
End If

Select Case
When you have to test many conditions, you might want to use the Select Case statement instead of the If Then..ElseIf. The syntax is as follow:
Select Case Selection.Value
  Case 1
    Selection.Offset(1, 0) = 10
  Case 2
    Selection.Offset(1, 0) = 20
  Case 3
    Selection.Offset(1, 0) = 30
  Case 4
    Selection.Offset(1, 0) = 40
  Case 5
    Selection.Offset(1, 0) = 50
End Select

Discover the power of SELECT CASE in the Resource Workbook on the topic.

There is no function in Excel to transform "95" into "ninety five". I have created one using many many many "IFs".

Discover how I did it in my Resource Workbooks.

Excel: Contents

VBA: Contents

Home

Search

Contacts

Articles

Resources Workbooks

Consulting Services