Search a database of thousands!
OzGrid Excel Newsletter
Contained at the bottom of eachnewsletter is Helpful Information on how to get the most from ournewsletters.
Should you wish to no longer receive ournewsletter, send an email with the words "action: Unsubscribe"in the body of the email, or clickhere.
Youare more than welcome to pass this on to as many people as you wish.
What's New at OzGrid.com| New/Updated Pages | Microsoft Excel tips
Microsoft Excel VBA tips | Helpful Information | 50% Off!
Check Out These Great Links:
Search a database of thousands!What's new at OzGrid.com [Top]
Hi All,
Firstly our sincere condolences to all those fellow Australians and other nationalities, affected by the recent bombings in Bali.
Welcome to the Wonderful World of OzGrid for the month of November. Another busy month has slipped by and we are moving into the Australian summer now and onwards towards Christmas and the New Year. We are making huge progress with our Excel add-ins and software, and are now not only selling Excel software, but business related software as well. Our final aim is to be the biggest location on the www to house Excel and VBA for Excel information and business software.
In addition to this we have now purchased a search facility which will allow our visitors to not only search any one of the 150+ products we have for sale on our site, but also the thousands of different software packages available in the RegNow database. RegNow are the affiliates that we use to sell third party software via our site, and they have a huge database of software available. Click Here to search OzGrid's site or Click Here to search the RegNow database.
Our Excel and VBA for Excel Training Courses are still going well, look out for some changes/updates in the New Year. To order Online or Email training Go Here. All Online courses can then be accessed by going to ExcelTraining and clicking the Log In button. Email lessons will be sent within 48 hours. As mentioned last month, we are also offeringDownload Training in all levels of Excel/VBA for Excel. All these courses offer the exact same content (including Time Saving Solutions)as our other courses. The reason this method is at a reduced cost is that it does not include answers to questions.
Perpetual Specials...... [Top]
We have also decided to run a special for our newsletter readers each month. The special will always be a 50% reduction in the cost of a product or service to be nominated each month by us. This special will only be available to newsletter readers and will only be available from the 10th of the month (which is the day the newsletter is sent out) for ten days. The following month a new special will be advertised. The special for this month is: 50% off any OzGrid Add-Ins If you wish to take advantage of this special, please forward an email to [email protected] including the exact email you used to subscribe to our newsletter and we send you an invoice for 50% of the advertized price. Upon payment you will be sent your choice via an email attachment, or if you prefer, a link to where you can download your choice.
Check Out These New/Updated Pages: [Top]
Free Excel Downloads |Excel Smart Tools |Global Software Search |VBA All Sorts | Search/Index
350 Extra Business Functions! | Business Planning Software | Add-ins and Software
Over the next two newsletters we thought we would look into one of Excel's most confusing but necessary features and that is Nesting. For those of you that are not aware, the word Nesting in this context refers to Excels ability to use the result of one function as the argument for another. Before we go into this in any more detail though, it is important that you understand what we mean when we use the word argument in a cell.
The definition of 'argument' is, the values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references and names.
When we nest functions there are basically two rules that we must adhere to, these are:
Lets now look in a bit more details at function arguments. Most Functions in Excel take arguments. The exception to this are such functions as the NOW function and TODAY function. When these are entered into a cell they are entered as =NOW() or =TODAY(). The use of the empty parenthesis simply means that the function takes no arguments. This also highlights another critical aspect of Excels functions and that is that they all have an opening and closing set of parenthesis. It is within these parenthesis that we supply the arguments to a particular function.
When you use the Function Wizard, found under Insert>Function to insert a function, you may have noticed that most of the argument names for that particular function are often bolded while some are not. What this means is that any bolded argument must have data supplied to it, while the non-bolded arguments are optional.
Lets now look at some examples of nested functions. Possibly the most common function that is used for nesting is the IF function. The syntax for the IF Function is logical_test,value_if_true,value_if_false. Lets assume that we want to use the IF function to return one value if the sum of a range of cells exceeds a certain value, whereas if it does not exceed a certain value we simply want the cell to appear empty. The function we could use for this is as shown below:
=IF(SUM($A$1:$A$10)>100,"Level Exceeded","")
In this case we have nested the SUM function within the IF function. In other words we have simply used the result of SUM($A$1:$A$10)>100 to return either TRUE or FALSE to the logical test argument of the IF function. This is a very simple nested function. We could now also rather than simply display the text "Level Exceeded", nest another function within the IF to calculate a formula if the level exceeded 100. This could be done as shown below:
=IF(SUM($A$1:$A$10)>100,AVERAGE($A$1:$A$10),"")
In this case, we have nested up to two levels with both the SUM function and the AVERAGE function being second level functions. This is because they are supplying the arguments of the IF function. For those that are not aware simply by reading this formula what it would do, it would simply return the average of the cells $A$1:$A$10, but only if the sum of the cells $A$1:$A$10 are greater than 100. If 100 or less our function would return empty text ("") and make our cell appear empty.
Lets now also nest another function into the third argument of the IF function (value_if_false). Lets assume now that if our SUM formula is 100 or less we want to count how many numbers there are in the cells $A$1:$A$10, which or course could only be 10 or less.
=IF(SUM($A$1:$A$10)>100,AVERAGE($A$1:$A$10),COUNT($A$1:$A$10))
In the above example we have now used a function to supply all of the arguments of the IF function. We have color coded this last example so that you can see where each function begins and ends.
We will leave it at that for this month so that it is not too much too quick and in next months issue we will look at just how we can use the Function Wizard to write very deeply nested and complicated functions with ease.
Microsoft Excel VBA tips [Top]
This month we will look at part two of three of How to use VBA in Excel Efficiently. For those of you who may have missed part one of this, you can read it on line (as you can for any past issues) in our newsletter archives section. It is issue 18
The first thing that we will do for this is dispel what seems to be a common myth with VBA programmers and that is that removing comment text from within your modules will help speed up code. While this can modestly reduce the size of the overall project, it has absolutely no direct effect on the execution speed of code. What all this means is, you are far better off leaving comments in your code than removing them.
In past issues we have looked in detail at the Select Case statement as opposed to the IF function. If you are using a Select Case Statement which possibly requires the criteria for many Cases to be checked, a very simple yet often overlooked way to help a Select Case statement execute faster is to always put the most likely case first. This alone can help speed up that section of your code by around 600%. Of course this is dependent on the number of Case statements you have within the Select Case.
For instance, lets look at a very simply example where we are using a Select Case statement to determine whether a cells value is between two other numeric values.
Sub SelectCaseSlow()
Select Case Sheet1.Range("A1")
Case -10000 To -9000
MsgBox "It is Case 1"
Case -8999 To -7000
MsgBox "It is Case 2"
Case -6999 To -5000
MsgBox "It is Case 3"
Case -4999 To -3000
MsgBox "It is Case 4"
Case -2999 To -1000
MsgBox "It is Case 5"
Case -999 To 0
MsgBox "It is Case 6"
Case 1 To 100
MsgBox "It is Case 7"
Case Else
MsgBox "It is no Case"
End Select
End Sub
In the above example, if we were to assume that the number residing in cell A1 is rarely below 0, our Select Case has been written in a very inefficient manner. This is simply because Excel would need to check all of the conditions above the Case 1 to 100 before it found a match. Unfortunately, most people would probably write the function in this manner for no other reason other than the numbers are going in a logical order ie; lowest to highest. When in reality, if we make the assumption stated above, the number should go from highest to lowest. This way Excel would most likely find a match on the very first Case statement and need not proceed any further. Of course, as has been stated in part one of this subject, these types of issues will only have a noticeable affect when used many times over and/or are part of a much bigger project. We will, however, in part three of this be looking at how we can actually time things down to the millisecond.
Evaluating Logical Expressions
When evaluating logical expressions, it is most common to use a simple IF statement if only needing to evaluate whether something is TRUE or FALSE. However, if we are actually going to be using the boolean result, ie; TRUE or FALSE to either pass to a variable or for the property of another object etc., we need not bother with the IF statement at all. Lets look at another example now, whereby we are running some code which will check the value of range A1 and if that value is greater than 100 we wish to set the visible property of that worksheet to FALSE. In other words, hide it. The most common way to do this and also quite inefficient, is as shown below:
Sub BooleanWithIf()
If Sheet1.Range("A1") > 100 Then
Sheet1.Visible = False
Else
Sheet1.Visible = True
End If
End Sub
As you can see in the above example, if the value of range A1 is greater than 100, we hide sheet1. If it is not greater than 100, we would show sheet1, or leave it visible if it was already.
A better and more efficient way to write this function, which would do exactly the same is as shown below:
Sub BooleanNoIf()
Sheet1.Visible = Not (Sheet1.Range("A1") > 100)
End Sub
This same principal, with some lateral thinking, can be applied to many, many situations in Excel VBA.
Checking Characters
Another common practice is checking whether a cell contains any characters or not. This is often done by using some simple code such as:
IF Range (A1) = "" Then
This could be used to check whether the string length of a cell is 0. A better way, however, is to use the LEN function which counts the number of characters in a cell, so we could replace the above statement with
IF LEN(Range (A1)) = 0 Then
Excel would evaluate this statement faster than the first one.
Perhaps the Fastest Methods
The last two ways that we will look at are possibly two of the easiest ways to speed up VBA code. The second one in particular can increase our speed very dramatically. When we write code efficiently, there should rarely be a need to ever select an object, eg; range, sheet etc., to run some code on it. Doing so will only slow down your code and force your screen to re-paint each time an object is selected. This is most apparent when you record a macro and then play it back. It is important to remember that the macro recorder, while very helpful in assisting us, writes extremely inefficient code. This is because it can only record our cumbersome movements and key strokes for the duration of the macro being recorded. As a general rule, if you record a macro, you should always be able to eliminate such words as Select, Selection, Active, Activate. However, there are some times when even the most efficient code will cause some amount of screen flickering. When this is the case, we can simply use the ScreenUpdating Property of the Application Object. This can simply be used by placing the line of code as shown below at the very start of your procedure.
Application.ScreenUpdating = FALSE
<Remaining code>
Application.ScreenUpdating = TRUE
This will stop the screen flickering, which is most apparent with recorded macros. Although it is a good idea to set the ScreenUpdating back to TRUE at the end of your code, it is not generally needed as ScreenUpdating automatically goes back to TRUE as soon as focus is shifted back to the Excel interface. One thing you should note when using ScreenUpdating = FALSE is that if you show a MessageBox or UserForm etc., while ScreenUpdating is turned off, the moving of the UserForm or MessageBox will cause it to cascade all over the screen, making your project look very unprofessional. Basically, the user would not be able to move the UserForm or MessageBox out of the way to see what is in a certain cell. This simply means that if a UserForm, MessageBox etc., is to be shown, we should always turn ScreenUpdating back to TRUE first.
Finally, the last thing we will look at for part two, is possibly one that can speed our code up more than any other single line of code. This is to switch Calculation to manual when performing certain tasks via VBA code. This is probably most obvious when perhaps running a loop which is deleting or inserting rows into Excel. If we have formulae in the workbook which is referencing the range where rows are being inserted/deleted., it will force the formulae to recalculate at each insertion/deletion. This can slow down code to such an extent that you may even think your PC has locked up. Switching Calculation to Manual before performing such tasks can speed up code hundreds of times. If you can be sure that Calculation is in automatic before running your code, you can simply use some code as shown below:
Application.Calculation = xlCalculationManual
<rest of your code>
Application.Calculation - xlCalculationAutomatic
However, there are of course times when we cannot be sure whether Calculation was ever in automatic to start with and hence switching it back to automatic, would not leave Excel with its same settings as before our code was run. This has the potential to cause all sorts of problems for the user and so it is best if we first determine what mode of calculation the user is in before switching to manual. This way we can put things back to how they were.
Sub PutBackToNormal()
Dim xlCalc As XlCalculation
XlCalc = Application.Calculation
Application.Calculation = xlCalculationManual
'<Your Code>
Application.Calculation = xlCalc
End Sub
In the above example, we have first passed the calculation mode of Excel to a variable and then used that variable to set calculation back to how it was.
That's all for this month. Tune in next month for Part 3 of How to use VBA in Excel Efficiently.
- until next month - keep Excelling!
Kind regards
Dave Hawley
Youare more than welcome to pass this on to as many people as you wish.
SPECIAL! Get all the OzGrid Add-ins, together and Save! 
$54.95
Readthis issue and past issues online here: http://www.ozgrid.com/News/Archive.htm
Microsoft and Microsoft Excel are registered trademarks of MicrosoftCorporation
