Sunday, 11 February 2007

OzGrid's Free Excel Newsletter

Microsoft Excel and VBA for Excel Training Information (auto response)

The newsletter is divided into four sections:

1.    What's new at OzGrid.com

2.    Microsoft Excel tips

3.    Microsoft Excel VBA tips

4.    Helpful information

It is published on about the 10^th of each month and always written by myself Dave Hawley.

You are more than welcome to pass on this newsletter to as many people as you wish, all I ask is you pass it on in it's entirety. 

Should you wish to no longer receive our newsletter, send an email with the words "Leave Newsletter" as the subject field, or click here.

Contained at the bottom of each newsletter is Helpful Information on how to get the most from our newsletters. If you think there is something missing, please let me know.

Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation

What's new at OzGrid.com

Hi all

Looks like we are well and truly moving into the year 2002 judging by the steady increase in our emails. Seems like all those that have holidays around Christmas time are returning back to work to discover the rest of the world has been rolling along! If you are one of those that takes holidays around Christmas, I hope you and your families enjoyed every minute of it!

Added to the site this month

http://www.ozgrid.com/download/default.htm Look for "AddingCustomMenus.zip", "EnableMacros.zip", "ListMatch.zip" and "VLookUpExample.zip"

This code will add a list to your ComboBox or ListBox in ascending order and remove all duplicates. http://www.ozgrid.com/VBA/UserForms.htm

There is some new code at the very bottom of this page that can be used to add a blank row at each change in a list. http://www.ozgrid.com/VBA/ExcelRanges.htm

This code (at the bottom of the page) can be used to create a drop-down "pick from" list including all entries above it each time a user right clicks in any cell. It is a work-around of Excel's "Pick from list" option which only includes text. http://www.ozgrid.com/VBA/ExcelWorkbookEvents.htm There is also a download to demonstrate this.

Excel XP

Last week I made some time to have quick look over Excel XP and overall I was quite impressed with what I saw. There are actually quite a few new features, unlike the transition from Excel 97 to Excel 2000! One of the favorites 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 Table reports etc. In fact there are now 18 levels of protection that we can choose from. The new help on Excels Functions is also very good and allows us to copy and paste real examples onto Worksheets. There is easily accessed help on function and formulas errors as well as a nifty "Show calculation steps..." which makes debugging functions much easier. These are only a few of many new features! There are also some new voice technology features that may or 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.

Time and Wage Sheet

Over the last month I have been developing a time and wage sheet for small business owners, 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.

Probably the biggest advantage is it's ease of use. You only need to enter employee details, set a pay period, then enter their hours worked each day and the rest is done for you. different pay rates for Saturdays, Sundays and public holidays are all optional, as are the 2 levels of overtime rates! Once you have filled out the information you simply save the Workbook as a Template and then your done. You can edit, modify and change information at any time.

We will initially be selling the "OzGrid Time and Wage Sheet" for USD35.00 once Beta testing is complete. If you would like to order one just drop me an email (davidh@ozgrid.com) and we will inform you as soon as it's ready.

Microsoft Excel tips

I have said this before, but I will say it again "do not use array formulas unless absolutely necessary!" You will find examples of array formulas all over the WWW on most Excel sites, but what you won't find them telling you is the consequence of using them (or at least too many of them), or that they slow a Workbook down to a crawl! Excel has the Dfunctions that are far more efficient for these types of calculations. The help in Excel has some good detail on these functions that I urge you all to read. Why do I tell you this? Because this month I have seen no less than eight Workbooks that have become unusable, thanks to array formulas. Once you start to use array formulas you are on a slippery slope. If you are not familiar with array formulas you can read about them here: http://www.ozgrid.com/Excel/Arrays.htm If you are not familiar with the Dfunctions you can find some examples of them here: http://www.ozgrid.com/download/default.htm under "DFunctionsWithValidation.zip"

Another poor bit of advice I see a lot on the WWW and in other Excel newsletters, is the use of entire columns as a reference. This is just silly and rarely needed as it creates an enormous amount of unnecessary overheads.   You should either restrict your 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 being used is a Volatile one. A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet. A nonvolatile function is recalculated only when the input variables change. Two of the most common volatile functions are , NOW and TODAY, if you are going to be using these a lot it can pay to place them in one cell and then simply reference the cell housing them.

Let's look at some ways you can restrict your range to only the cells needed. As mentioned above a dynamic range is one of the best ways, but you can also place your cell reference into a single 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 a regular basis and so are tempted to use A:A in all your formulas that 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 other functions, all looking in at least 63000 more cells than needed. Times that by 20 functions and you are adding 1260000 more cells than needed! You can of course double this if you use two Columns! So rather than do this simply place this 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 column numbers.

Syntax

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

Row_num   is the row number to use in the cell reference.

Column_num   is the column number to use in the cell reference.

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 a specified value in a specified order. Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself.

Syntax

MATCH(lookup_value,lookup_array,match_type)

Lookup_value   is the value you use to find the value you 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 cells containing possible lookup values. Lookup_array can be an array or an array reference.

Match_type   is the number -1, 0, or 1. Match_type specifies 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 MATCH function to supply the Row_num argument to the ADDRESS function and use -9E+306 (a very small negative number) to match. As we have used -1 as 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 cell with the INDIRECT function: =SUM(INDIRECT(B1))

From Excels help

INDIRECT

Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.

Syntax

INDIRECT(ref_text,a1)

Ref_text   is a reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference 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 of reference 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 total reference cells by potentially millions! Using simple but effective habits like this will make your spreadsheets far more robust, faster and smaller.

Here is another little trick you can also use that can be of immense value. Lets suppose you have a VLOOKUP function that needs to look in any one of 5 different named ranges depending on a 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 look in named range Table1, If A1=2 look in named range Table2 etc 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 any number between 1 and 5 in cell A1 and our VLOOKUP will look in the corresponding table!

Please note these examples are not supposed to be definitive answers to everyone's situation, but rather they are intended to plant the seed of "thinking outside the box"

Microsoft Excel VBA tips

This month we shall look at the Intersect Method. The Intersect is used to return a Range Object which is representative of the rectangular intersection of two or more ranges. Unless you are reasonably proficient with Excel VBA that is probably as clear as mud! Let's use some layman's terms. The Intersect Method can be used to determine if a range is part of another range. Suppose you need to know if a range (that has been selected by a user) includes the range D10, the Intersect will do the job nicely!

Sub IntersectMethod()

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

To test this out simply paste it into a standard Module, then select any range of cells and run the code.

Ok, but how does it work? To understand this we first need to understand that (as with many things in Excel) a Range is an Object as is a Worksheet and 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 object variable 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

As soon as you run this code you will get a Run time error telling us that "Object variable 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 rRange and the tip text will display rRange = Nothing. This is because we have dimensioned (Dim) a variable to the Range Object type, Set it to Range A1 but then Set it to the Nothing keyword. Let's correct this:

Sub ObjectDefault()
Dim rRange As Range

Set rRange = Range("A1")

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

No problems running this code is there?

So what this all means is, when we use the Intersect Method it will return a Range Object, but only if the 2 or more ranges specified actually intersect.

One of the more common areas to use the Intersect is in the Private Module of a Worksheet and in particular the Change Event! 

Let's suppose that each time a user enters some data onto a Worksheet we need to know if data entered is part of the range A1:B10. If it is, we need to copy this 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

This will do the job. The Target will always be the cell which has been changed. So if you type any data at all into say cell A5, it will also appear on Sheet2 in A5.

Adding Descriptions to Custom Functions

I have had quite a few requests this month asking if it's possible to add descriptions to your User-defined functions (UDF's). Well the answer is yes and here's how:

There are at least 2 ways you can use to add a description to you UDF's, one is relatively well known, while the other is a simple but little known 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

DavidH@OzGrid.com

Helpful Information