Tuesday, 03 November 2009

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

Hi all

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

WhilstI haven't had time to add much new content to the site this month, I havebeen able to make available 3 new additions to the http://www.ozgrid.com/download/default.htmweb page.

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


For those of you that are not aware,  OzGrid.com is situated in Australind, Western Australia and so is on GMT+8. Thereason I mention this is because most of our clients are from outside ofAustralia and situated in the United States. With today's technologythis really makes very little difference, but there are times that a phone call isthe 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 <yourexit country code> 61 8 9725 8090 or if you prefer, emailme the time, date and your number and name and I'll ring you.

OtherNewsletters (friendlywarning)

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 thisone :o)) was showing how array formulascan be used. Now while there was nothing wrong with the actual content, therewas no mention of the downside to using them. Quite ironic considering thenormal content of this particular newsletter! This concerns me, so many people whenthey first discover arrays think they have found the solution to all theirproblems and use them quite liberally. They then find out the hard way that toomany array formulas cause Excels calculation, saving, opening and closing to slowdown to a crawl. This is because while array formulas are quite powerful theybasically 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!

Thereason I tell you all this is of no benefit to me, in fact I often haveclients 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, dynamicranges and Validation being used in place of their array formulas. See DFunctionsWithValidation.ziphere: http://www.ozgrid.com/download/default.htmfor some relatively easy examples. I must admit I get quite annoyed wheneverI see a so called experienced user suggest to a novice that they should use anarray formula, when really there is no need. The novice has come for help andgoes away thinking "Wow, this is great!" only to find out after monthsof adding array after array they must switch their Workbook to manualcalculation because it takes far to long to calculate otherwise. Up to 30minutes in cases working on a shared drive.

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


Microsoft Excel tips

Forthis months Excel tips I thought we would look at Conditional formattingand how to use it with a formula criteria. For those that are not familiarwith this feature, it allows Excel users to have some aspects of a cell or cellsformatting changed depending on the value in the cell. A very simple examplewould be if you wanted any number in a given range to have a background color ofred, but only if the number was greater than 100. Just so we are all on the samelevel 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.

Allvalues between 4 and 8 should now have a red background. This isExcel's Conditional formatting in a very simple form!

Usinga Formula

There isone basic rule that we must follow if we opt to use a formula as ourcriteria.  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

Providingcells B1:B10 are empty (or have a sum value of zero) our format condition will notbe 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 numberin any cell within B1:B10 and our condition will become True. Now typethe the same value again in another cell within B1:B10 but make it a negativenumber, eg if you typed 5 to start with, type -5 in any other cell withinB1:B10. Our condition has now become False again because the sum value ofB1:B10 is 0. This sort of condition is of no real use to anybody it's simply toshow you that any formula you use as the format condition must evaluate to Truefor 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 equalto the sum value of range B1:B10 (type any number between 1 and 10 in B1:B10). Itis very important to note here that we did not absolute cell A1, this isbecause we had the range A1:A10 selected (A1 being the only active cell) soExcel's Conditional Formatting will use the formula: =A2=SUM($B$1:$B$10) incell A2, =A3=SUM($B$1:$B$10) etc.

Let'snow 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.

Nowtoggle the CheckBox so that C1 goes from True to False, leave itas False. What we will do now is make this a additional condition in aConditional 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 wehave done here is told Conditional formatting that if the number is greater than4 but less than 8 and cell $C$1 (our CheckBox link) is Truehide the cells contents. You can see this quite easily by toggling the CheckBoxfrom False to True. I will often use this principal when I want tohide certain data on a cluttered spreadsheet so it's easier to read. Using this method and a bit of imaginationwe can make our spreadsheets interactive for the user without using any VBA atall!

Thissame type of principal can be used with many of the Forms toolbar controls tomake your spreadsheets far more user friendly and professional. I have another interactiveexample of this here: http://www.ozgrid.com/download/default.htmUnder ChangingCombo.zip at the very bottom of the page.

Microsoft Excel VBA tips

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

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 trickbut there's a lot of If's going on! We need at least one If to check theWorksheets 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 samebut is generally more efficient. 

Let's take this to one more level now and use a Loopgoing through a range of 2000 cells and bold all Even numbers, withoutusing any If or Select case statements. Truth be known, this is a very poorreason to use a loop but it's for illustration purposes only. Forthose of you that are not familiar with my methods I will avoid loops most timesas they are horribly slow!

Put the numbers 1 to 2000 in cellsA1:A2000, I suggest using the Fill Handle :o)   Now place this code in a moduleand 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 Modoperator 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 haveused the expression: 

(rCells.Value Mod 2) = 0

To return either True or Falseto:

rCells.Font.Bold = 

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


The Find method in Excel is one of myfavourite features (I often use it in place of loops!) because it is extremelyfast and very flexible. However, it can catch the novice Excel VBA coderout. I have often seen posts on Excel Newsgroups and Newsletters that the Findmethod is not reliable. This is simply not true, it's most likelybecause they haven't taken the time to learn VBA properly and/or they haven'tread the help on the Find Method. The explanation is most likely because they don't realize many ofthe 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 aftereach search are: LookIn, LookAt, SearchOrder,and MatchByte

So if you do not set these and usesomething like: 


Or even

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

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

Often when we do a search we may notknow which sheet (if any) the value we are looking for resides, or in whichcell. This means we must search each sheet within the Workbook and if our valueis 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 If
Next wsSheet

MsgBox "No match"
End Sub

The important parts of the code arethat:

  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/orRows from the Find method simply hide the Column or Row. The Find will not lookin hidden columns and rows.


The Find method is also very usefulwhen working with UserForm controls, in particular ComboBoxes and/or ListBoxes.Assume you have a UserForm that has 1 ComboBox and 10 Textboxes. The ComboBoxhas 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 TextBoxesfilled with the corresponding data from you data table. Here's how you could dothis.

  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 Privatemodule 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 10Textboxes with their appropriate data.

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

Kind regards

Dave Hawley

[email protected]


  • Have Excel open when reading the newsletter.
  • When copying and pasting formulas from newsletter (or Web pages) into Excel, copy the formula (Ctrl+C), select a cell then click within the formula bar (or push F2) then paste (Ctrl+V)
  • To get help on a specific formula push F1 and type the formula name then push Enter.
  • To get a reminder of a functions syntax, type = then the functions name, then push Ctrl+Shift+A
  • To default the Paste function (formula wizard) to a specific function type = then the functions name, then push Ctrl+A
  • To copy and paste any VBA code, open the Visual Basic Editor (Alt+F11), go to Insert>Module and paste in the code.
  • To run any code after copying and pasting it in, place your mouse insertion point anywhere within the code and go to Run>Run Sub/UserForm, or push F5
  • To easily access the Private module of the "ThisWorkbook" Object, while in Excel, right click on the Excel icon (top left next to File) and select "View Code".
  • To easily access the Private module of a Worksheet Object, while in Excel, right click on the Worksheet name tab and select "View Code".
  • If the VBA code is a Custom Function (i.e. begins with the word Function) after you have pasted the code into a Module, switch back to Excel (Alt+F11), go to Insert>Function... or push Shift+F3, scroll down to User Defined (under Function category:) then select the Function name from within the Function name: box.
  • To assign a shortcut key to any Macro go to Tools>Macro>Macros..., or push Alt+F8 then select the Macro name and click Options.




    Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation.