Sunday, 01 November 2009

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

I hope all is well in all countries around the world at the moment, or at least as well as can be expected in light of recent world events. Recently my wife and I returned from an overseas trip to Bali, Indonesia and I don't mind telling you we were both a bit anxious about travelling to Indonesia. However, once there all our fears were soon dispelled as we were treated with the utmost respect and made to feel more than welcome. For those of you that are not familiar with Bali, it is a small Indonesian island that is extremely popular with Australians, both young and old. The cheap shopping there is a woman's dream come true and a husbands worst nightmare come true :o) All jokes aside though, the food and drink were fantastic, as were the people.

New Site

The biggest news for OzGrid this month is the imminent launch of our new Web site at www.ozgrid.com ! The site is now very near completion and should be finalized within a fortnight. At present www.microsoftexceltraining.com still points to our old site, but this should soon take you straight to www.ozgrid.com. If you haven't already, please browse around our new site and let us know what you think. While there, it would be great if you took five minutes to complete our training poll. This will enable us to better provide Microsoft Excel and VBA for Excel distance training and tutoring. A big thanks to all those that have taken the survey already!

I have also starting to send emails in HTML format (like this one) using stationary designed by Hans Stammel ofwww.avip.com.au. I have modified some of the HTML code supplied by Hans in this stationary, so if there are any problems at all it is most likely my fault, not Hans'! Should it get messed up on anyone's email program, please let me know.

Special Offer

As a special for the launch of our new site we will be offering a 20% discount off all Excel and VBA for Excel training courses for this month only - offer expires 10 December 2001 . These are normally AUD350.00, with the 20% discount this works out to AUD280.00. Convert this to US dollars and you are looking at about US150.00

You may also have noticed the Microsoft Excel and VBA for Excel Training Information (auto response) email link at the top of this newsletter, by clicking this link and sending the email generated, you will be sent an automated HTML email covering most of the details of our courses. You can also simply send an email to [email protected] and put the word TrainingInfo into the subject field.

Quick Survey

If you have a spare few minutes and as we now have our new web-site up and running, we would appreciate it if you would take 2 minutes and answer these 4 questions.  All answers will be confidential.

  1. What word(s) would you use to find training in Microsoft Excel or VBA for Excel on the internet?
  2. What method of training would you give preference to?
  3. Which search engine would you use?
  4. What word(s) in a search engine would you use to find a person or company to develop a spreadsheet.
Many thanks if you have time to answer these questions. Please send all answers to [email protected]
 
New Download
 
I had a request this month for an Excel template workbook that would automatically insert an incremented number each time it was opened. After writing some code to do this I though it would be a good one to share with everyone, so I have posted it here: http://www.ozgrid.com/download/default.htm under InvoiceAutoNumber.zip. Please feel free to download it, complete with code and information.
 
Let's move into the good bits now!

Microsoft Excel tips

I thought for this months Excel section I would share with you some of the answers I have supplied over the last month. These questions mainly come from people that have a nagging Excel problem they cannot solve. I am more than happy to try and supply answers to these questions, should my workload permit. I try to dedicate about 1 hour of each day to supplying answers to such questions, but as I've always said I must put my paying clients first. If you have sent me a question that has not been answered, please realize that I receive some 20 requests a day from people wanting help, and it is simply not possible to answer them all!

If you are part of an office team that uses Excel a lot you can benefit greatly by joining our help desk service. This will guarantee you will have an answer to your problems, if not you pay nothing! Let's face it, why spend hours trying (and often failing) to solve those Excel or VBA for Excel problems when we (OzGrid) probably know the answer. Please send me an email for full details.

One of the questions I answered this month was about having a Subtotal for 2 Columns of numbers. For those of you that received last month's newsletter, you may recall we looked at the SUBTOTAL function. I also suggested that you try the Subtotals... feature found under Data on the main menubar. For this instance though I suggested they try a very simple SUM formula. The reason I have included this in this months Newsletter is because (a) not only does it follow on well from last months newsletter and (b) it is also a great example of the right way and the wrong way to achieve something in Excel.

Let's suppose the 2 columns of numbers are in Columns "A" and "B" and cells A1 and B1 are headings. You could place this formula in cell C2 and copy down further than needed:

=IF(A2="","",SUM($A$1:B2))

This would certainly do the job, but is not a good method to use! To better understand why I will first show you the formula that should be used:

=IF(A2="","",SUM(A2:B2,C1))

Doesn't look much different really, does it? In fact there isn't much difference in the formula itself, but the first method is really very inefficient. To understand why, let's see what each formula looks like when copied down to say cell C1000

  1. =IF(A1000="","",SUM($A$1:B1000))
  2. =IF(A1000="","",SUM(A1000:B1000,C999))
In case you can't see the reason why number 2 is a far better choice, I'll tell you! Method 2 is telling Excel to sum 3 cells of numbers, while method 1 is forcing Excel to sum 2000 cells to get the same result. It is inefficiencies like this that can eventually slow a spreadsheet down to a crawl and blow out the file size.
 
Another question this month came from a friend of mine in London. He had the problem that he wanted to find the first occurrence of 3 zeros in a row within a row of data and then return the heading from the corresponding cell directly above the first of the 3 zeros. At first I thought this would need an array formula or a custom function written. But as I only use array formulas when absolutely necessary I came up with this.
What I suggested was, he insert a blank row below row 10 and then in this row, put this formula, starting from A11.
 
=IF(COUNTIF(A10:C10,0)=3,"X","")
 
From Excel Help.
 
IF(logical_test,value_if_true,value_if_false)
Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE.
Use IF to conduct conditional tests on values and formulas.
 
COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given criteria. From Excel Help.
 
End Of Excel Help.
 
Once this is copied all the way across to IV11 (last cell in row 11) it will produce the letter "X" below the first of any 3 zeros in a row. I then placed this formula in the result cell and hid row 11.
 
=INDEX($A$1:$IV$1,1,MATCH("X",$A$11:$IV$11,0))
 
From Excel Help.
 
INDEX(array,row_num,column_num)
returns the value of a specified cell or array of cells within array. From Excel Help.

MATCH(lookup_value,lookup_array,match_type)
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.
 
End Of Excel Help.
 
Although it is quite possible there would be more than one set of 3 cells in a row containing zeros (and hence more than one "X" in row 11) it doesn't matter as the optional match_type argument for the MATCH function is 0 (zero). This means that MATCH will look for the first value that is exactly equal to lookup_value ("X"). Once found it returns it's relative position in the row as a number. This number is then used as the column_num argument of the INDEX function. We already know the row number to supply to row_number argument of the INDEX function ie 1
 
So if our first cell of three zeros in a row is cell Z10 our result would be the content of cell Z1. MATCH would return the number 26 (column Z is the 26th column) and this would tell INDEX we want the 26th Column on the first row (1) within the range $A$1:$IV$1.
 
I mentioned previously that I also considered using a custom function, well I did write one and it is shown below in the Microsoft Excel VBA tips section.
 

Microsoft Excel VBA tips

 
Here the custom function way using VBA.
 
Function Find3XInaRow(xFindwhat As Variant, rLookin As Range, rReturnFrom As Range)
Dim rCell As Range
Dim vResult
'Look in each cell with the rLookin range argument supplied UNTIL _
 both the cell AND the 2 cells to it's right, contain zeros.
    For Each rCell In rLookin
        If rCell = xFindwhat And _
           rCell.Offset(0, 1) = xFindwhat And _
           rCell.Offset(0, 2) = xFindwhat Then
            vResult = rReturnFrom.Cells(1, rCell.Column)
           Exit For 'Condition met so leave the loop.
        End If
    Next rCell
 Find3XInaRow = vResult
End Function

The formula would be placed in a cell like this:

=Find3XInaRow(0,$A$11:$H$11,$A$1:$H$1)

It's syntax is:

Find3XInaRow(xFindwhat,rLookin,rReturnFrom)

Comparing Text and Case Sensitivity

In last months newsletter I mentioned that I would show you how you can change Excel default way it compares text, (i.e Binary) which can make comparing text give unexpected results. Often when we compare text VBA code we are not interested in the case of the letters (i.e capital or lower).

To see what I mean type the word Cat (upper case "C") in cell A1 and then type cat (lower case "c") in cell A2. Now run this code:

Sub CompareText1()
If Range("A1").Text = Range("A2").Text Then
   MsgBox True
Else
   MsgBox False
End If
End Sub

You will get a False message box! This is because as far as Excel in concerned C <> c. Change both letter "c" to upper or lower and you get the True message box! What we need to do is force Excel to see C = c by using the Option Compare Statement at the *Module level.

*Module level

Code placed in the Declarations section of a module. All code placed outside a procedure is module level code. Declarations must be listed first, followed by procedures.

So, to force Excel to see our 2 words (Cat and cat) as the same we simply use this:

Option Compare Text

Sub CompareText2()
If Range("A1").Text = Range("A2").Text Then
   MsgBox True
Else
   MsgBox False
End If
End Sub

It's that simple! Now to revert Excel back to it's default (Binary) we simply would use:

Option Compare Binary

Sub CompareText3()
If Range("A1").Text = Range("A2").Text Then
   MsgBox True
Else
   MsgBox False
End If
End Sub

Let's now take this text comparison one step further. Assume we want to compare text using wildcard characters. To do this we can use the Like statement. For this example we will see if the text in cell A1 contains the word cat. Type: The cat sat on the mat in cell A1, then run this code:

Sub CompareText4()
If Range("A1").Text Like ("*cat*") Then
   MsgBox True
Else
   MsgBox False
End If
End Sub


This will return True so long as you used a lower case "c" in A1 and the code! Even though we have used the Like statement it is (by default) still case sensitive. Unless of course you still have: Option Compare Text declared at the module level.

Naturally if A1 had the text cats are us! our message box would read False, because we have assumed there are characters before the word cat. To account for these sort of problems simply use:

Sub CompareText5()
If Range("A1").Text Like ("cat*") Or _
   Range("A1").Text Like ("*cat") Or _
   Range("A1").Text Like ("*cat*") Then
   MsgBox True
Else
   MsgBox False
End If
End Sub

Like the Select Case Statement we looked at in the last 2 newsletters, the Like Statement also automatically knows it's alphabet :o) To see what I mean by this type the single letter H in cell A1 then run both of these Procedures.

Sub CompareText6()
If Range("A1").Text Like "[A-I]" Then
   MsgBox True
Else
   MsgBox False
End If
End Sub

Sub CompareText7()
If Range("A1").Text Like "[A-G]" Then
   MsgBox True
Else
   MsgBox False
End If
End Sub

You should get True for Sub CompareText6() and False for Sub CompareText7().

Before I wind up this months newsletter, I would like to apologise to the person that sent me some nifty code for a Custom Function. I promised I would include it in this months newsletter, but I have lost the persons name. I didn't think it was fair to publish it without giving credit where credit is due. So if you could email again I will publish it in next months newsletter.


Until next month, keep Excelling!

Kind regards

Dave Hawley

[email protected]

 

Helpful Information

  • Have Excel open when reading the newsletter.
  • When copying and pasting formulas from newsletters (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.