Lesson 5 Excel VBA Effective Decision Making

Category: [General,Excel] Demo Available

Effective Decision Making

IF Else And Or Not Iif

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 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:

IIf(Condition, True part, False part)

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

Select Case

The other method of checking for single or multiple conditions is the Select Case Function. This is another method used for decision making based on a condition or criteria. It, in my opinion, is much better than If etc. This has the syntax

Select Case
    Case
        Do something
    Case
        Do something
    Case Else
        Do something else
End Select

As you can see the "Select Case" Function is very similar to the "If" Function in that it will only perform some action if a condition is met. While this may seem no better than the "If" Function I feel that it is a MUCH better choice than the "If" Function If more than one condition or expression needs to be tested. Not only is it more efficient but it has a much better structure than the "If" Function. This means it is far easier to read or decipher and believe me you WILL need to go back through your written code frequently to find out a problem (De-bug). While these two reasons alone are enough for me, there is another and that is it has FAR more flexibility. We will first look at the "Select Case" Function in it's simplest form

Sub TheSelectCase()

Select Case Range("A1").Value

Case 100

Range("B1") = 50

End Select

End Sub

As you may have noticed, this does not include a "Case Else" Statement. This is because, like the "Else" Statement in the "If" Function it is optional. The "Select Case" in it's simplest form as shown above, holds no advantage over the "If" Function, in fact it could be argued that it is an incorrect use of the "Select Case" Function. Let us say you need to perform any one of 5 actions depending on the Value of Range A1. If so we could use:

Sub TheSelectCase()

Select Case Range("A1").Value

Case 100

Range("B1").Value = 50

Case 150

Range("B1").Value = 40

Case 200

Range("B1").Value = 30

Case 350

Range("B1").Value = 20

Case 400

Range("B1").Value = 10

End Select

End Sub

This, in my opinion, is a far better structure and easier to read than an "If" Function with multiple "ElseIf" Statements. If none of the above Conditions were met nothing would occur, unless we use the optional "Case Else" Statement, like:

Sub TheSelectCase()

Select Case Range("A1").Value

Case 100

Range("B1").Value = 50

Case 150

Range("B1").Value = 40

Case 200

Range("B1").Value = 30

Case 350

Range("B1").Value = 20

Case 400

Range("B1").Value = 10

Case Else

Range("B1").Value = 0

End Select

End Sub

So If the Value of Range A1 is NOT 100,150,200,350 or 400 then place a Value of 0 (zero) in Range B1. Now while this demonstrates how we can check multiple conditions with the "Select Case" Function, what if we want to perform some action If the Range A1 is equal to any one of the Values 100,150,200,350 or 400. If this is the case (no pun indented) we could use:

Sub TheSelectCase()

Select Case Range("A1").Value

Case 100, 150, 200, 350, 400

Range("B1").Value = Range("A1").Value

Case Else

Range("B1").Value = 0

End Select

End Sub

I don't believe anybody could argue against this being a far better structure than an "If" Function with multiple "Or" Operators.

We used the "If" Function combined with the "And" operator above to demonstrate how to let Excel know if the Value of Range A1 is between two numbers. We can do this also with the "Select Case" Function with even greater ease:

Sub TheSelectCase()

Select Case Range("A1").Value

Case 100 To 500

Range("B1").Value = Range("A1").Value

Case Else

Range("B1").Value = 0

End Select

End Sub

As the above example demonstrates, we use the Keyword "To" to test whether Range A1 is between 100 and 500 and if so place the value of Range A1 in Range B1. We can take this a step further if needed to test for multiple "To" conditions, like:

Sub TheSelectCase()

Select Case Range("A1").Value

Case 100 To 500, 600 To 1100, 1200 To 2000

Range("B1").Value = Range("A1").Value

Case Else

Range("B1").Value = 0

End Select

End Sub

So now with this one "Select Case" Function we can check to see if Range A1 is between any one of three different Values. We could if we needed add more. Another major advantage of the "Select Case" Function over the If Function is that we can use it to determine if certain Text is between two other Text Strings ie; alphabetically. Let's assume you are only interested in the content of Range A1 if the Text within it is alphabetically between "Aardvark" and "Elephant", to do this we could use:

Sub TheSelectCase()

Select Case Range("A1").Text

Case "Aardvark" To "Elephant"

Range("B1").Value = "it's between"

Case Else

Range("B1").Value = "it's not between"

End Select

End Sub

So if the text in Range A1 is "Budgie" range B1 will read "It's between". If the Text in A1 is "Zebra" then Range B1 would read "It's not between".

So as you can see there are many ways within VBA for Excel we can use to evaluate and determine a Value or Text. You will find yourself using the "If" and "Select Case" Functions quite frequently and as I have already indicated, the Select Case is often a much better option.

Excel and Dates

Dates are frequently used in Excel and in VBA for Excel, also for this reason I believe it is an important aspect to at least know the fundamentals of. The text below is from the Excel help file and explains how Excel sees or interprets Dates.

How Microsoft Excel performs date and time calculations

Microsoft Excel stores dates as sequential numbers known as serial values and stores times as decimal fractions because time is considered a portion of a day. Dates and times are values and therefore can be added, subtracted, and included in other calculations. For example, to determine the difference between two dates, you can subtract one date from the other. You can view a date or time as a serial number or a decimal fraction by changing the format of the cell that contains the date or time to General format.

Microsoft Excel supports two date systems: the 1900 and 1904 date systems. The default date system for Microsoft Excel 97 for Windows is 1900. To change to the 1904 date system, click Options on the Tools menu, click the Calculation tab, and then select the 1904 date system check box.

Note: When you enter a date in Microsoft Excel 97, or later and you enter only two digits for the year, Microsoft Excel enters the year as follows:

The years 2000 through 2029 if you type 00 through 29 for the year. For example, if you type 5/28/19, Microsoft Excel assumes the date is May 28, 2019.

The years 1930 through 1999 if you type 30 through 99 for the year. For example, if you type 5/28/91, Microsoft Excel assumes the date is May 28, 1991.

End of Excel Help

Using Dates in VBA for Excel offers a lot more flexibility, but also has more pitfalls that can catch the uninformed out. This is due mainly to the fact that Excel is used globally and there is more than one Date system. We will look at the two most common and that is the American (Month-Day-Year) and the European (Day-Month-Year). This issue will be nearly non-existent if you know for a fact that the code written in VBA will only be used on one Date system. But should you write a Procedure that will be used by more than one Date system, problems can arise. In today's market it is not unusual for say an English company to have to deal with a Spreadsheet that uses the American Date System or an vice versa. You can imagine the problems that could arise if the Dates you insert into a Spreadsheet via VBA are assumed to be of the American Date System when in reality they are of the European Date System. Fortunately the makers of VBA for Excel have realised this and provided a universal Function to eliminate possible disasters. I would urge you to again form a good habit early and incorporate it whenever dealing with Dates. The Function is called the "DateSerial" Function. It has the Syntax:

DateSerial(year, month, day)

As you can see the Function uses neither the American or European Date System. Let's say you want to insert the Date 5-6-2001 (European System) into cell A1, you could use:

Sub UniversalDate()

Dim dTheDate As Date

dTheDate = DateSerial(2001, 6, 5)

Range("A1").Value = dTheDate

End Sub

Using this Function will eliminate any possible confusion of the Date System used. Whenever you use or Parse a Date to a Variable or Range you must enclose it within the # (Hash signs) eg;

dTheDate = #22/5/01#

If you try and type this into Excel exactly as is you will see that Excel will automatically change it to the American Date System whether you want it to or not. In other words it will end up like:

dTheDate = #5/22/01#

You may or may not notice the Month and Day being switched if you are happily typing away. But worse than this is if you use:

Sub UniversalDate()

Dim dTheDate As Date

dTheDate = #10/12/01#

Range("A1").Value = dTheDate

End Sub

….and you are used to the European Date System you would assume the Date going into Range A1 is the 10th Day of December, 2001. Guess what, WRONG! You will actually end up with the 12th Day of October, 2001 instead. Of course if we aware of this and we have formed the good habit of using the "DateSerial" Function for all Dates no such problem will arise. Eg;

Sub UniversalDate()

Dim dTheDate As Date

dTheDate = DateSerial(2001, 12, 10)

Range("A1").Value = dTheDate

End Sub

I hope this stresses the importance of using the DateSerial Function whenever you are working with Dates in Excel

Date Functions

Now that we realise the possible pitfalls of working with Dates I will show you some handy Functions you can use in Excel when working with Dates.

The first one is the "Date" Function, this will return the current systems Date. As it is returning the current system Date, it will use the Date System of the PC it is run on.

Next we have the "DateValue" Function. This is the same as the Worksheet Formula DATEVALUE in that it will return a real Date from a String Date, eg;

Sub UniversalDate()

Dim dTheDate As Date

dTheDate = DateValue("12/May/01")

Range("A1").Value = dTheDate

End Sub

This will Parse the Date Value for 12th day of May, 2001 to our Date Variable "dTheDate", which in turn will place it in cell A1. You may also have noticed that we could also use the "DateValue" function to prevent mishaps when working with Dates. There is no reason why you couldn't and it won't matter so long as you use one or the other.

To add a specified period of time to a Date we could use the "DateAdd" Function, this has the Syntax:

DateAdd(interval, number, date)

You would use it like below:

Sub DateAddFunction()

Dim dTheDate As Date

dTheDate = DateAdd("d", 45, "18/may/2001")

Range("A1").Value = dTheDate

End Sub

In this particular case the Date Variable dTheDate would return the Date 45 days from the 18th day of May, 2001, which is the 2nd of July, 2001. Although the Function is called "DateAdd" in can be used to subtract a specified time period from a Date. To do this you would simply use a negative number. The other allowed arguments for the Interval are:

yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second

There are many other types of Functions that can be used with Dates but as this is level 1 VBA I won't confuse the issue by going into them all. The two most important points to remember when working with Dates are How Microsoft Excel performs date and time calculations and being aware of the International issues associated with Dates.

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery

stars (0 Reviews)

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
OzGrid is in no way associated with Microsoft.

Website designed by Astrava Solutions Ltd.