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 10^th 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

Hiall

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.

ExcelXP

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:

1. Allows up to 50 employees.
2. Step-by-step instructions.
3. Nomination of public holidays (optional).
4. Allows for 2 levels of overtime payments (optional).
5. Allows nomination of Saturday and Sunday hourly pay as well as 2 levels of Saturday and Sunday overtime payments  (optional).
6. Allows nomination of public holiday hourly pay as well as 2 levels of public holiday overtime payments  (optional).
7. Setting of pay period from 1 day to 2 weeks.
8. Automatic pay calculations based on employee details entered (points 3, 4, 5 and 6).
9. Display of days, weekdays, Saturdays, Sundays and public holidays in pay period set.
10. Facility for "Additional" pays each day.
11. Grand total of all pays for pay period.
12. Grand total of all hours for pay period.
13. Total pay by employee for pay period.
14. Total hours by employee for pay period.

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:

• =SUM(A:A)
• =MAX(A:A)
• =MIN(A:A)
• =LARGE(A:A,2)
• =SMALL(A:A,2)

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

="A1:"&ADDRESS(MATCH(-9E+306,A:A,-1),1)

From Excels help

ADDRESS

Creates a cell address as text, given specified row and columnnumbers.

Syntax

ADDRESS(row_num,column_num,abs_num,a1,sheet_text)

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

MATCH

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.

Syntax

MATCH(lookup_value,lookup_array,match_type)

Lookup_value   is the value you use to find the valueyou want in a table.

• Lookup_value is the value you want to match in lookup_array. For example, when you look up someone's number in a telephone book, you are using the person's name as the lookup value, but the telephone number is the value you want.
  
  
• Lookup_value can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.

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.

• If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.
  
  
• If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.
  
  
• If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A,...2, 1, 0, -1, -2,..., and so on.
  
  
• If match_type is omitted, it is assumed to be 1.

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

INDIRECT

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.

Syntax

INDIRECT(ref_text,a1)

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.

• If a1 is TRUE or omitted, ref_text is interpreted as an A1-style reference.
  
  
• If a1 is FALSE, ref_text is interpreted as an R1C1-style reference.

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:

=IF(A1=1,VLOOKUP("Dave",Table1,2,FALSE),IF(A1=2,VLOOKUP("Dave",Table2,2,FALSE),IF(A1=3,VLOOKUP("Dave",Table2,3,FALSE),IF(A1=4,VLOOKUP("Dave",Table4,2,FALSE),IF(A1=5,VLOOKUP("Dave",Table5,2,FALSE),"")))))

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.

1. Select cell A1 and go to Data>Validation.
2. Choose "Whole number" then "Between" 1 and 5
3. Set an input and error message if you like and click OK.
4. In cell B1, put: =CHOOSE(A1,"Table1","Table2","Table3","Table4","Table5",)
5. Change the mega VLOOKUP to: =IF(A1="","",VLOOKUP("Dave",INDIRECT(B1),2,FALSE))

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!

SubIntersectMethod()

If Intersect(Selection, Range("D10")) Is Nothing Then
    MsgBox "D10 is NOT part of the Selection"
Else
    MsgBox "D10 is part of the Selection"
End If

End Sub

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:

Sub ObjectDefault()
Dim rRange As Range

Set rRange = Range("A1")
Set rRange = Nothing

With rRange
    .Select
End With

End Sub

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:

SubObjectDefault()
Dim rRange As Range

Set rRange = Range("A1")

With rRange
    .Select
End With
Set rRange = Nothing
End Sub

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
End If

End Sub

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.

1. Open up the VBE (Alt+F11) and select anywhere within your Function code.
2. Now Push F2 to open the "Object Browser".
3. At the top of the Object Browser there are 2 drop down boxes. Click the top one and select "VBAProject".
4. You should now have all Modules and global Objects showing in the "Classes" box situated at the bottom of the Object Browser.
5. Click on the name of the Module that houses your UDF.
6. In the "Members of..." box to the right you should see the names of all Functions and Procedures within the selected Module.
7. Simply right click on the name of your UDF and select "Properties".
8. Type a description for your UDF, then click Ok and then Save.

1. Go to Tools>Macro>Record new macro.
2. In the Description box, type a description for your UDF. Type the name of your UDF and click Ok.
3. Now Stop recording.
4. Open the VBE (Alt+F11) and replace the word "Sub" with "Function"
5. Add your Function arguments as normal.

Application.MacroOptions Macro:="MyCustomFunction", _ Category:=3'Add to Math & Trig

Until next month, keep Excelling!

Kind regards

Dave Hawley

[email protected]

HelpfulInformation