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

This month has proven to be a very busy month in terms of traffic to our site and also in terms of new members signing up for our newsletter. So a big welcome to all new members, and of course a big hello to all our seasoned members! For all those new members, I hope you enjoy receiving our newsletter each month and get some 'real use' from it! Just remember that you can always read any past issues here: http://www.ozgrid.com/News/Archive.htm at anytime.

Whilst I haven't had time to add much new content to the site this month, I have been able to make available 3 new additions to the http://www.ozgrid.com/download/default.htm web page.

1. ChartByMonth.zip
2. OverDueDates.zip
3. ChangingCombo.zip

Time Zones

For those of you that are not aware,  OzGrid.com is situated in Australind, Western Australia and so is on GMT+8. The reason I mention this is because most of our clients are from outside of Australia and situated in the United States. With today's technology this really makes very little difference, but there are times that a phone call is the best way. If for any reason you would like to contact myself (Dave Hawley) via telephone you can go here: http://www.timeanddate.com/worldclock/meeting.html or here: http://www.timeanddate.com/worldclock/ to find out the time difference from your location and mine (Perth is my location). Once you have the time either ring me direct on <your exit country code> 61 8 9725 8090 or if you prefer, email me the time, date and your number and name and I'll ring you.

Other Newsletters (friendly warning)

As do millions of others from around the world I subscribe to quite a few newsletters, some great, some not so great. I was very concerned a while back when a favourite newsletter of mine (no not this one :o)) was showing how array formulas can be used. Now while there was nothing wrong with the actual content, there was no mention of the downside to using them. Quite ironic considering the normal content of this particular newsletter! This concerns me, so many people when they first discover arrays think they have found the solution to all their problems and use them quite liberally. They then find out the hard way that too many array formulas cause Excels calculation, saving, opening and closing to slow down to a crawl. This is because while array formulas are quite powerful they basically need to loop through all referenced cells and check each for the matching criteria. This can slow things down in Excel terribly. You wont notice this with only a few arrays, but put in any more than say 30 (depends on PC) and you certainly will!

The reason I tell you all this is of no benefit to me, in fact I often have clients pay me to fix up their virtually unusable spreadsheets for them. More often than not, this is done with a combination of Pivot Tables, Dfunctions, dynamic ranges and Validation being used in place of their array formulas. See DFunctionsWithValidation.zip here: http://www.ozgrid.com/download/default.htm for some relatively easy examples. I must admit I get quite annoyed whenever I see a so called experienced user suggest to a novice that they should use an array formula, when really there is no need. The novice has come for help and goes away thinking "Wow, this is great!" only to find out after months of adding array after array they must switch their Workbook to manual calculation because it takes far to long to calculate otherwise. Up to 30 minutes in cases working on a shared drive.

My most earnest advice is to use the Dfunctions (whenever possible) that are standard in Excel, they will do the job faster, cleaner and are far more flexible and easier to modify. Sure they take about 20 seconds longer to set up, but you wont regret this.

Microsoft Excel tips

For this months Excel tips I thought we would look at Conditional formatting and how to use it with a formula criteria. For those that are not familiar with this feature, it allows Excel users to have some aspects of a cell or cells formatting changed depending on the value in the cell. A very simple example would be if you wanted any number in a given range to have a background color of red, but only if the number was greater than 100. Just so we are all on the same level to begin with try this simple exercise

1. Put the numbers 1 to 10 in cells A1:A10.
2. Select the range A1:A10, starting from A1.
3. Go to Format>Conditional formatting.
4. Leave the default "Cell Value Is" and "Between".
5. In the first box put: 4 in the second put: 8
6. Click "Format" then "Patterns" then click Red for Cell shading.
7. Click Ok, then Ok again.

All values between 4 and 8 should now have a red background. This is Excel's Conditional formatting in a very simple form!

Using a Formula

There is one basic rule that we must follow if we opt to use a formula as our criteria.  That is that the formula must evaluate to TRUE or FALSE. Let's see what I mean. Using the same range as before (A1:A10) try this:

1. Select the range A1:A10, starting from A1.
2. Go to Format>Conditional formatting.
3. Change the default "Cell Value Is" to "Formula Is"
4. In the Formula box put: =SUM($B$1:$B$10)
5. Leave the red formatting and click Ok

Providing cells B1:B10 are empty (or have a sum value of zero) our format condition will not be met (False). This is simply because 0 (zero) has a value of False, while all other values would be True. To see what I mean type any number in any cell within B1:B10 and our condition will become True. Now type the the same value again in another cell within B1:B10 but make it a negative number, eg if you typed 5 to start with, type -5 in any other cell within B1:B10. Our condition has now become False again because the sum value of B1:B10 is 0. This sort of condition is of no real use to anybody it's simply to show you that any formula you use as the format condition must evaluate to True for your condition to be met. Now try this way:

1. Select the range A1:A10, starting from A1.
2. Go to Format>Conditional formatting.
3. Leave the now default "Formula Is"
4. In the Formula box put: =A1=SUM($B$1:$B$10)
5. Leave the red formatting and click Ok

This time the only cells that will become red will be the ones that are equal to the sum value of range B1:B10 (type any number between 1 and 10 in B1:B10). It is very important to note here that we did not absolute cell A1, this is because we had the range A1:A10 selected (A1 being the only active cell) so Excel's Conditional Formatting will use the formula: =A2=SUM($B$1:$B$10) in cell A2, =A3=SUM($B$1:$B$10) etc.

Let's now use this principal for something of use:

1. Right click on any Toolbar and check  "Forms" 
2. From the Forms toolbar select a CheckBox (one with the tick) and then click anywhere on the Worksheet.
3. Right click this CheckBox and select "Format Control"
4. Select the "Control" page tab, leave "Value" as Mixed, and change the "Cell link:" to $C$1
5. Click Ok.

Now toggle the CheckBox so that C1 goes from True to False, leave it as False. What we will do now is make this a additional condition in a Conditional format we will set.

1. Select the range A1:A10, starting from A1.
2. Go to Format>Conditional formatting.
3. Leave the now default "Formula Is" 
4. In the Formula box put: =AND(A1>4,A1<8,$C$1=TRUE)
5. Change the red background to "No Color" and the Font color to white
6. Click Ok, then Ok again and select any cell to deselect A1:A10

What we have done here is told Conditional formatting that if the number is greater than 4 but less than 8 and cell $C$1 (our CheckBox link) is True hide the cells contents. You can see this quite easily by toggling the CheckBox from False to True. I will often use this principal when I want to hide certain data on a cluttered spreadsheet so it's easier to read. Using this method and a bit of imagination we can make our spreadsheets interactive for the user without using any VBA at all!

This same type of principal can be used with many of the Forms toolbar controls to make your spreadsheets far more user friendly and professional. I have another interactive example of this here: http://www.ozgrid.com/download/default.htm Under ChangingCombo.zip at the very bottom of the page.

Microsoft Excel VBA tips

For this months Excel VBA tips I thought we would look at how to avoid using the If statement in our code. Quite often in VBA code we use the If statement to check whether a condition is True or False, if True we do one thing and if False we do another! Let's say we want to loop through all our Worksheets in our Workbook and if the sheets name starts with the letter "X" we hide it, or make it visible if hidden. Most people would use some code like this:

Sub ToggleSheetX()
Dim wsSheet As Worksheet

For Each wsSheet In ThisWorkbook.Worksheets
    If wsSheet.Name like ("X*") Then
        If wsSheet.Visible = True Then
            wsSheet.Visible = False
        ElseIf wsSheet.Visible = False Then
            wsSheet.Visible = True
        End If
    End If
Next wsSheet

End Sub

This would certainly do the trick but there's a lot of If's going on! We need at least one If to check the Worksheets name, but the others are really superfluous. We could simply use:

Sub ToggleSheetX()
Dim wsSheet As Worksheet

For Each wsSheet In ThisWorkbook.Worksheets
    If wsSheet.Name Like ("X*") Then
        wsSheet.Visible = Not wsSheet.Visible
    End If
Next wsSheet

End Sub 

This code will do exactly the same but is generally more efficient. 

Let's take this to one more level now and use a Loop going through a range of 2000 cells and bold all Even numbers, without using any If or Select case statements. Truth be known, this is a very poor reason to use a loop but it's for illustration purposes only. For those of you that are not familiar with my methods I will avoid loops most times as they are horribly slow!

Put the numbers 1 to 2000 in cells A1:A2000, I suggest using the Fill Handle :o)   Now place this code in a module and run it.

Sub ToggleSheetX()
Dim rCells As Range

On Error Resume Next 'For non numeric data
For Each rCells In Range("A1:A2000")
    rCells.Font.Bold = (rCells.Value Mod 2) = 0
Next rCells
On Error GoTo 0
End Sub

In case you wondering, the Mod operator is used to to divide two numbers and return only the remainder. So any number divided by 2 and has a remainder of 0 (zero) is Even. We have used the expression: 

(rCells.Value Mod 2) = 0

To return either True or False to:

rCells.Font.Bold = 

In other words if the remainder of our cell value (rCells.Value) divided by 2 is 0 (zero) our expression evaluates to True and hence sets the Bold property of the Font Object to True, if the remainder is not zero our expression evaluates to False. This same principal can be used very frequently throughout most Excel VBA code, all that is needed is some lateral thinking and a bit of imagination. Once you grasp this concept you will find you can avoid using If statements in many places and begin to write far more efficient code!

Find Method

The Find method in Excel is one of my favourite features (I often use it in place of loops!) because it is extremely fast and very flexible. However, it can catch the novice Excel VBA coder out. I have often seen posts on Excel Newsgroups and Newsletters that the Find method is not reliable. This is simply not true, it's most likely because they haven't taken the time to learn VBA properly and/or they haven't read the help on the Find Method. The explanation is most likely because they don't realize many of the last used settings are retained and you should explicitly set them each time. The syntax for Find are:

Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte)

The settings which are retained after each search are: LookIn, LookAt, SearchOrder, and MatchByte

So if you do not set these and use something like: 

Range("A1:A2000").Find(What:="Cat")

Or even

Range("A1:A2000").Find(What:="Cat", LookIn:=xlValues)

You are asking for problems! Most of us need not worry too much about SearchOrder, and MatchByte. This is because SearchOrder can be either xlByColumns or xlByRows and unless you are searching a huge range, the time diff is minimal, but having said this every bit counts! The MatchByte  is used only if you’ve selected or installed double-byte language support. 

Often when we do a search we may not know which sheet (if any) the value we are looking for resides, or in which cell. This means we must search each sheet within the Workbook and if our value is found we need to go there. This can best be achieved with the following code:

Sub FindCat()
Dim wsSheet As Worksheet
Dim rFound As Range

For Each wsSheet In ThisWorkbook.Worksheets
    Set rFound = wsSheet.UsedRange. _
        Find(What:="Cat", LookIn:=xlValues, _
            LookAt:=xlWhole, MatchCase:=False)
    If Not rFound Is Nothing Then
        Application.Goto rFound, Scroll:=True
        End
    End If
Next wsSheet

MsgBox "No match"
End Sub

The important parts of the code are that:

1. We have limited our range to look in to the UsedRange of each sheet.
2. As soon as we have found a match our loop ends abruptly (End).
3. We have used Application.GoTo to select the found cell. Using this method we can go there even though our match is on a different sheet to wherever we start our code from.

Tip  If you would like to exclude certain Columns and/or Rows from the Find method simply hide the Column or Row. The Find will not look in hidden columns and rows.

The Find method is also very useful when working with UserForm controls, in particular ComboBoxes and/or ListBoxes. Assume you have a UserForm that has 1 ComboBox and 10 Textboxes. The ComboBox has a RowSource called "MyRange" and "MyRange" is the first column of a table of data named "MyTable". Whenever you select a value from the ComboBox you would like all 10 TextBoxes filled with the corresponding data from you data table. Here's how you could do this.

1. For each TextBox change their Tag property to a number less one that corresponds to their Column position in the range "MyTable". In other words if TextBox1 will display data that corresponds to column 2 in the table use a Tag value of 1,  TextBox2 Tag property to 2 etc.
2. Set the RowSource of ComboBox1 to "MyRange"

Now put this code into the Private module of the UserForm:

Private Sub ComboBox1_Change()
Dim rFoundSource As Range
Dim strText As String
Dim tBox As Control

If ComboBox1.ListIndex > -1 Then

    strText = ComboBox1.Text
        Set rFoundSource = Range(ComboBox1.RowSource).Find(What:=strText, _
            After:=Range(ComboBox1.RowSource).Cells(1, 1), _
            LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=True)


    For Each tBox In Me.Controls
        If IsNumeric(tBox.Tag) Then
            tBox.Text = rFoundSource.Offset(0, tBox.Tag)
        End If
    Next tBox

End If

End Sub

This simple method will fill all 10 Textboxes with their appropriate data.

OK, that's it for this month - until next month - keep Excelling!

Kind regards

Dave Hawley

DavidH@OzGrid.com

Helpful Information