Saturday, 23 January 2010
OzGrid's Free Excel Newsletter
MicrosoftExcel and VBA for Excel Training Information (auto response)
The newsletter isdivided into four sections:
1. What's new at OzGrid.com
2. Microsoft Excel tips
3. Microsoft Excel VBA tips
4. Helpful information
It ispublished on about the 10th of each month and always written bymyself Dave Hawley.
You are more thanwelcome to pass on this newsletter to as many people as you wish, all I ask isyou pass it on in it's entirety.
Should you wish to no longer receive ournewsletter, send an email with the words "Leave Newsletter" as thesubject field, or clickhere.
Contained at the bottom of eachnewsletter is Helpful Information on how to get the most from ournewsletters. If you think there is something missing, please let meknow.
Microsoft and Microsoft Excel are registered trademarks of MicrosoftCorporation
What's new at OzGrid.com
Lookslike we are well and truly moving into the year 2002 judging by the steadyincrease in our emails. Seems like all those that have holidays around Christmastime are returning back to work to discover the rest of the world has beenrolling along! If you are one of those that takes holidays around Christmas, I hopeyou and your families enjoyed every minute of it!
Addedto the site this month
http://www.ozgrid.com/download/default.htmLook for "AddingCustomMenus.zip", "EnableMacros.zip","ListMatch.zip" and "VLookUpExample.zip"
Thiscode will add a list to your ComboBox or ListBox in ascending order and removeall duplicates. http://www.ozgrid.com/VBA/UserForms.htm
There is some new code at the verybottom of this page that can be used to add a blank row at each change in alist. http://www.ozgrid.com/VBA/ExcelRanges.htm
This code (at the bottom of thepage) can be used to create a drop-down "pick from" list including all entries above it eachtime a user right clicks in any cell. It is a work-around of Excel's "Pickfrom list" option which only includes text. http://www.ozgrid.com/VBA/ExcelWorkbookEvents.htmThere is also a download to demonstrate this.
Last week I made some time to have quick look over Excel XP and overall Iwas quite impressed with what I saw. There are actually quite a few newfeatures, unlike the transition from Excel 97 to Excel 2000! One of thefavorites for me is the ability to set various levels of Worksheet protection.No longer does sheet protection need to stop a user from formatting, sorting,using Auto filters (this alone is something I welcome) or using Pivot Tablereports etc. In fact there are now 18 levels of protection that we can choosefrom. The new help on Excels Functions is also very good and allows us to copyand paste real examples onto Worksheets. There is easily accessed help onfunction and formulas errors as well as a nifty "Show calculationsteps..." which makes debugging functions much easier. These are only afew of many new features! There are also some new voice technology features that mayor may not be helpful to people. I only tried the "Speak cells"feature which seemed to do a pretty good job of reading back a cell(s) contents.
Timeand Wage Sheet
Overthe last month I have been developing a time and wage sheet for small businessowners, this is now very near completion and only requires some Beta testing.It's features are:
Probablythe biggest advantage is it's ease of use. You only need to enter employeedetails, set a pay period, then enter their hours worked each day and the restis done for you. different pay rates for Saturdays, Sundays and public holidaysare all optional, as are the 2 levels of overtime rates! Once you have filledout the information you simply save the Workbook as a Template and then yourdone. You can edit, modify and change information at any time.
Wewill initially be selling the "OzGrid Time and Wage Sheet" forUSD35.00 once Beta testing is complete. If you would like to order one just dropme an email ([email protected])and we will inform you assoon as it's ready.
Microsoft Excel tips
Ihave said this before, but I will say it again "do not use arrayformulas unless absolutely necessary!" You will find examples of arrayformulas all over the WWW on most Excel sites, but what you won't find themtelling you is the consequence of using them (or at least too many of them), orthat they slow a Workbook down to a crawl! Excel has the Dfunctionsthat are far more efficient for these types of calculations. The help in Excelhas some good detail on these functions that I urge you all to read. Why do Itell you this? Because this month I have seen no less than eightWorkbooks that have become unusable, thanks to array formulas. Once you start touse array formulas you are on a slippery slope. If you are not familiar witharray formulas you can read about them here: http://www.ozgrid.com/Excel/Arrays.htmIf you are not familiar with the Dfunctions you can find some examples of themhere: http://www.ozgrid.com/download/default.htmunder "DFunctionsWithValidation.zip"
Another poor bit of advice I see alot on the WWW and in other Excel newsletters, is the use of entire columns as areference. This is just silly and rarely needed as it creates an enormous amount of unnecessary overheads. You should either restrictyour reference range to a dynamic named range ( http://www.ozgrid.com/Excel/DynamicRanges.htm) or only to the cells needed. This becomes especially true if the function beingused is a Volatile one. A volatile function must be recalculated whenevercalculation occurs in any cells on the worksheet. A nonvolatile functionis recalculated only when the input variables change. Two of the mostcommon volatile functions are , NOW and TODAY, if you are going tobe using these a lot it can pay to place them in one cell and then simplyreference the cell housing them.
Let's look at some ways you canrestrict your range to only the cells needed. As mentioned above a dynamic rangeis one of the best ways, but you can also place your cell reference into asingle cell. Assume you have a list of numbers in Column "A"down to row 2000. You know this list will be added to and taken from on aregular basis and so are tempted to use A:A in all your formulasthat need to reference these numbers. So you may end up with:
..and possibly hundreds of otherfunctions, all looking in at least 63000 more cells than needed. Times that by20 functions and you are adding 1260000 more cells than needed! You can ofcourse double this if you use two Columns! So rather than do this simply placethis formula in a cell:
From Excels help
Creates a cell address as text, given specified row and columnnumbers.
Row_num is the row number to use in the cellreference.
Column_num is the column number to use in the cellreference.
Abs_num specifies the type of reference to return.
From Excels help
Returns the relative position of an item in an array that matches aspecified value in a specified order. Use MATCH instead of one of the LOOKUPfunctions when you need the position of an item in a range instead of the itemitself.
Lookup_value is the value you use to find the valueyou want in a table.
Lookup_array is a contiguous range of cellscontaining possible lookup values. Lookup_array can be an array or an arrayreference.
Match_type is the number -1, 0, or 1. Match_typespecifies how Microsoft Excel matches lookup_value with values in lookup_array.
End of Excels help
*Note we use the MATCHfunction to supply the Row_num argument to the ADDRESS functionand use -9E+306 (a very small negative number) to match. As we have used -1as match_type it will always find the last number, even if we have blank rows.Unless we have a number smaller than a negative 9 followed by 306 zeros!
Then we simply reference the cellwith the INDIRECT function: =SUM(INDIRECT(B1))
From Excels help
Returns the reference specified by a text string. References areimmediately evaluated to display their contents. Use INDIRECT when you want tochange the reference to a cell within a formula without changing the formulaitself.
Ref_text is a reference to a cell that contains anA1-style reference, an R1C1-style reference, a name defined as a reference, or areference to a cell as a text string. If ref_text is not a valid cell reference,INDIRECT returns the #REF! error value.
A1 is a logical value that specifies what type ofreference is contained in the cell ref_text.
End of Excels help
BTW, the INDIRECT function is also Volatile, but it's only being used once and it is reducing our totalreference cells by potentially millions! Using simple but effective habits likethis will make your spreadsheets far more robust, faster and smaller.
Here is another little trick youcan also use that can be of immense value. Lets suppose you have a VLOOKUPfunction that needs to look in any one of 5 different named ranges depending ona cells content. You could of course use:
In other words if A1=1 lookin named range Table1, If A1=2 look in named range Table2etc You may then copy this down for a number of rows. Here is a much better way.
All you need to do now is type anynumber between 1 and 5 in cell A1 and our VLOOKUP will lookin the corresponding table!
Please note these examples arenot supposed to be definitive answers to everyone's situation, but ratherthey are intended to plant the seed of "thinking outside the box"
Microsoft Excel VBA tips
Thismonth we shall look at the Intersect Method. The Intersect is used toreturn a Range Object which is representative of the rectangular intersection oftwo or more ranges. Unless you are reasonably proficient with Excel VBA that isprobably as clear as mud! Let's use some layman's terms. The Intersect Methodcan be used to determine if a range is part of another range. Suppose youneed to know if a range (that has been selected by a user) includes therange D10, the Intersect will do the job nicely!
If Intersect(Selection, Range("D10")) Is Nothing Then
MsgBox "D10 is NOT part of the Selection"
MsgBox "D10 is part of the Selection"
Totest this out simply paste it into a standard Module, then select any range ofcells and run the code.
Ok,but how does it work? To understand this we first need to understand that (aswith many things in Excel) a Range is an Object as is a Worksheetand Workbook etc. The default value for an unassigned Object is Nothing.Nothing is a keyword in Excel VBA that is often used to disassociate an objectvariable from an actual object. Lets put this to the test:
Dim rRange As Range
Set rRange = Range("A1")
Set rRange = Nothing
Assoon as you run this code you will get a Run time error telling us that "Objectvariable or With block variable not set" if you then click Debug,you will be taken to the line of code: .Select.Hover your mouse pointer over rRangeand the tip text will display rRange =Nothing. This is because we have dimensioned (Dim) a variable to the RangeObject type, Set it to Range A1 but then Set it to the Nothingkeyword. Let's correct this:
Dim rRange As Range
Set rRange = Range("A1")
Set rRange = Nothing
Noproblems running this code is there?
Sowhat this all means is, when we use the Intersect Method it will return a RangeObject, but only if the 2 or more ranges specified actually intersect.
Oneof the more common areas to use the Intersect is in the Private Module of aWorksheet and in particular the Change Event!
Let'ssuppose that each time a user enters some data onto a Worksheet we need to knowif data entered is part of the range A1:B10. If it is, we need to copythis to the same range, but on a another sheet.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:B10")) Is Nothing Then
Sheet2.Range(Target.Address) = Target
Thiswill do the job. The Targetwill always be the cell which has been changed. So if you type any data at allinto say cell A5, it will also appear on Sheet2 in A5.
AddingDescriptions to Custom Functions
Ihave had quite a few requests this month asking if it's possible to adddescriptions to your User-defined functions (UDF's). Well the answer is yes andhere's how:
There are at least 2 ways you can use to add a description toyou UDF's, one is relatively well known, while the other is a simple but littleknown method. Lets use the better known method first.
Application.MacroOptions Macro:="MyCustomFunction", _ Category:=3'Add to Math & Trig
Until next month, keep Excelling!