 |
| <<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
|---|
|
IF Else And Or Not If
The "If" Function in
VBA for Excel is very similar to the "IF" function used in a Worksheet
formula. It will return either True or False
and it does no more or less than this. As with the "IF" used in
the Worksheet formula the "If" in VBA can take up to two arguments, one for True
and one for False. So the syntax for the "If" is simply:
If <Condition to check>
Is True Then
'Do one
thing
Else
'Do another
thing
End If
So this same Function
used in a realistic way could be
Sub
TheIfFunction()
If Range("A1").Value > 100
Then
Range("B1").Value = 50 +
Range("A1").Value
Else
Range("B1").Value = 100
End
If
End Sub
This is telling Excel that If the Value of A1 is greater than 100 (True) then change the Value of B1 to the Value of A1 plus another 50, Else (False) changes the Value of B1 to 100. This would be the "If" Function used in it's simplest form. Once Excel encounters the "If" Function it will check the Value of A1, if the value is greater than 100 it will enter into the True argument:
Range("B1").Value = 50 + Range("A1").Value
From there it will Exit the "If" or in other words it will skip the False argument:
Range("B1").Value = 100
But lets assume we wanted
Excel to check if Range A1 is equal to 500 first and only go on if it's not
(False), to achieve this we would need to extend the "If" so it will possibly
check two conditions before exiting the remainder of the "If". This is how we
could do this:
Sub
TheIfFunction()
If Range("A1").Value = 500 Then
Range("C1").Value = 100 -
Range("A1").Value
ElseIf Range("A1").Value > 100 Then
Range("C1").Value = 50 + Range("A1").Value
Else
Range("B1").Value =
100
End
If
End
Sub
This Function is saying that, If Range A1 is equal to 500 (True) then:
Range("C1").Value = 100 - Range("A1").Value
But If Range A1 is NOT equal to 500 then check another condition, which is:
ElseIf Range("A1").Value > 100 Then
If this is True then:
Range("C1").Value = 50 + Range("A1").Value
Finally if neither of these conditions are True then:
Range("B1").Value = 100
We could in theory keep adding an unlimited
amount of "ElseIf" Functions to check for multiple conditions. The problem with
this is that our "If" Function would become almost impossible to read and more
importantly, decipher. I will show you a much better method soon for checking
multiple conditions, but for now we will stick with the "If" Function.
There are two other common Keywords used in conjunction with the "If" Function, they are the "And" and the "Or" Operators. We will look first at the "And" operator.
The "And" operator is use to perform a
conjunction of two conditions. Whenever we use the "And" operator with the "If"
Function it will only ever return True if BOTH conditions are met
(True and True). So if we used the "If" combined with the "And"
like below:
Sub
TheIfAndFunction()
If Range("A1").Value > 100 And
Range("A1").Value < 500 Then
Range("B1").Value =
Range("A1").Value
End
If
End
Sub
This would tell Excel that If Range A1 is between 100 and 500 (True) then make:
Range("B1").Value = Range("A1").Value
If Range A1 is Not between 100 and 500 do
nothing. We could again add an unlimited amount of "And" operators all checking
different conditions, but again this would become very hard to decipher and is
also not very efficient.
The other common Operator used with the "If"
Function is the "Or" Operator. This will check if one of two conditions
are True and return True if only one of them is met (True and
False) or (False and True). Below is an example of
this:
Sub
TheIfOrFunction()
If
Range("A1").Value = 100 Or Range("A1").Value = 500 Then
Range("B1").Value = Range("A1").Value
End
If
End
Sub
This "If" Statement will return True If Range
A1 is equal to 100 OR If Range A1 is equal to 500 any other condition
would return False and do nothing.
These two Operators are by far the most
commonly used Operators used with the "If" Function.
So the "If" Function can be used to determine whether a Function is either True or False and act accordingly. Combining it with the Operators "And" and "Or", can extend it's functionality. In all the above examples the "If" Functions use the "End If" Keywords. These simply let Excel know that the "If" Function has finished. If we restrict our "If" Function to one line of code only we can omit the "End If" completely, like below:
Sub
NoEndIf()
If
Range("A1").Value = 100 Then Range("B1").Value =
20
End
Sub
This can at times make your code slightly
easier to read. There is no performance gain by doing the "If" Function this
way, so don't get caught in the trap of always trying to fit your "If" Function
onto one line. If by doing so you cannot read the entire line without scrolling
to the right use two or more lines with the "End
If".
There is one other way of evaluating a
condition with the "If" and that is called the "Iif". I will only show you this
because it exists, but I do not recommend using it for two
reasons.
It's slightly
slower
It has no advantage over the "If
Else"
The syntax for IIf is:
To use this in a similar way as the "If", we
could use:
Sub
TheIIf()
IIf
Range("A1").Value = 100, Range("B1").Value = 20, Range("B1") =
50
End
Sub
But as I have said I would avoid using this as it holds no advantage.
The other operator we can use with the "If" Statement is the "Not" Statement. This is used to reverse the "If" Statement
Sub IfNot()
If Not Range("A1") = 100 Then
MsgBox "Not 100", vbInformation, "OzGrid Example"
End If
End Sub
In the example above we have used the "Not" statement to reverse the logic of the "If" statement. By this I mean we have told our "If" statement to return True if Range A1 is not equal to 100