If Then test in Excel VBA macros

I can see that quite many search for information about If-Then conditions and constructions in VBA, and since there is a demand I have made this beginner's page on how to test conditions or constraints with "If Then".

A similar way of testing is to use Select Case, which is often better if you are testing many conditions.

If Then

Much programming is about testing a statement and then do something, if the statement is true or false. One very common method is the "If...Then" test. It can be very simple:

Sub TestValue()

If Range("A1").Value > 0 Then
   'Action - do something
End If

End Sub

Else

In the example above nothing will happen, if the value is zero or less than zero. We can counter that by using a small "Else":

Sub TestValue()

If Range("A1").Value > 0 Then
   'Action - do something
Else
   'Do something else
End If

End Sub

Now we are covered - no matter the value in cell A1 something will happen. But the test above could easily be insufficient. In that case we can extend the test with an "ElseIf":

Sub TestValue()

Dim vMyVal

vMyVal = Range("A1").Value

If vMyVal > 0 Then
   'Action - do something if greater than zero
ElseIf vMyVal = 0 Then
   'Action - do something else if equal to zero
Else
   'Action - do a third thing if less than zero
End If

End Sub

You can have as many ElseIf as you want, but if there are many, one should consider using Select Case instead.

Nested tests

Sub TestValue()

Dim vMyVal

vMyVal = Range("A1").Value

'If the cell has content
If Len(vMyVal) > 0 Then
   'Is it a numeric value?
   If IsNumeric(vMyVal) Then
      If vMyVal > 0 Then
         'Action if greater than zero
      Else
         'Action if =< 0
      End If
   Else
      'Action if it is not a numeric value
   End If
End If

End Sub

Here we first test, if cell A1 has a content (if the length is greater than zero), and if it is, our action depends on whether it is a numeric value or not. If it is numeric we check if it is greater than zero or not, and this controls our action. In that way you can nest or merge many If-Then constructions, but it soon gets confusing.

Logical operatorers "And" and "Or"

One of the strenghts in If-Then constructions is that you can use logical operators: "And" or "Or". See the following example:

If Range("A1").Value > 0 And Range("A2").Value > 10 Then
   'Action
End If

There will only be some action, if cell A1 is greater than zero AND if cell A2 is greater than 10. Both conditions must be true - otherwise nothing will happen. If you replace the word "And" with "Or", something will happen, if A1 is greater than zero OR if A2 is greater than 10.