Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation.
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.
TimeZones
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
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:
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:
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:
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.
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:
SubToggleSheetX()
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!
FindMethod
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:
Range("A1:A2000").Find(What:="Cat")
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
End If
Next wsSheet
MsgBox "No match"
End Sub
The important parts of the code arethat:
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.
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
HelpfulInformation
Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation.