Wednesday, 12 November 2008

OzGrid's Free Excel Newsletter

Get the OzGrid Excel Plus Add-In! 8 add-ins in one for less than you think. If you buy the add-in and are subscribed to our free newsletter, you will get the "File Size Reducer" for FREE! Many Other Excel Add-ins Here Over 40 of them!

All past issues online here: http://www.ozgrid.com/News/Archive.htm

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 "action: Unsubscribe" in the body of the email, 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 [Top]

Hi all,

Here we are in July already.  Well, it has been the end of the financial year here in Australia (30 June) so as you can imagine, we have been extremely busy fulfilling client obligations.  Hopefully this hectic period is coming to an end and we can now concentrate on getting our books in order to please the tax man, before returning to our normal "really busy" state, as opposed to our "flat out like a lizard drinking" state as they say in Oz.

A huge congratulations to our Brazilian subscribers for winning the World Cup (again). While the sport is not that big in Australia, I think it gets bigger each time the World Cup comes around. I guess we Australians are a bit like the Americans in that once (we will one day) we finally make it to the Cup the sport here in Australia will get the recognition it deserves.

On line Lessons [Top]

Hopefully our on-line lessons will be up and ready to go at the end of this month.  We have spent lots of time over the last few weeks checking and updating things, and working with our web developer to get the lessons into an easy-to-use, user-friendly format.  The first package we will be putting on line is our Microsoft Excel - Level 2 course as this is one of our most popular courses.  If all goes how we hope it will, we will follow this with all our other training courses.  People wishing to access on-line lessons will be supplied with a Username and password, so they can access lessons at their convenience.  We are hoping that many people will take advantage of this, as although we try to send lessons within one working day, many clients have asked if the on-line feature would be available, so they can be totally independent of Ozgrid, and only contact us if they have any questions.  

In light of the on-line lessons becoming available, we will be reviewing our prices over the next few months.  We will be offering a price to purchase training courses with all questions answered, and a price for just supplying the lessons only, with no ability to ask questions.  We hope to publish these new prices in the September newsletter.

Fiscal Year Templates [Top]

As it is the end of the financial year here, we thought that the following templates supplied by Microsoft may aid some of our readers.  Please be aware though, we have not checked them out personally at this stage.  If you decided to check them out, we would be interested to know what you think of them.

Microsoft Excel Add-ins and Free Stuff [Top]

Those of you that were subscribed last month will recall the launch of our Excel add-ins Excel Plus Add-In! and File Size Reducer ( http://www.ozgrid.com/Services/AddinExamples.htm ). We have some fantastic feedback on both these add-ins, not only are you getting 9 add-ins for only $20.00 but the "File Size Reducer" comes with it Free.

We have also created a page on our site dedicated to other Excel Add-ins from all over the world.  At present there are some 40 add-ins here that you can purchase them on-line immediately. You will find this page here: Many Other Excel Add-ins Here (http://www.ozgrid.com/Services/ExternalAddIns.htm). This page is very LARGE so it can take a while to download.

There is no denying that we all love something for nothing! So I have decided to create a page on our site dedicated to nothing but FREE stuff for Excel (http://www.ozgrid.com/Services/ExternalFree.htm) I will be adding to this site as I come across and develop free Excel stuff. Please take note of the "Credit To" links as most of these are from other sites and not just mine.

Microsoft Word Expert Newsletter [Top]

The Word Expert: http://www.thewordexpert.com/ Anne, or Dreamboat as she is known on the WWW

Has recently started a FREE monthly newsletter that gives subscribers lots of info on Microsoft Word as well as other Office Applications.  If you want to check out this months issue Click here for the July Newsletter!  The author, Dreamboat is genuine, and the newsletter worthwhile. Personally I feel this newsletter is the perfect compliment to our Excel newsletter.

Credit Card Facility [Top]

Thought I would just let all you fledgling businesses know that after the hassle of searching for and signing up with a suitable company that supplies secure on-line Credit Card facilities, it has proved to be extremely worthwhile.  We are using WorldPay for our Credit Card facilities, and this facility has already paid for itself in the short time that we have had it.  WorldPay are prompt with payment, all their statements and correspondence are well laid out and easy to understand, and most of all, their charges are reasonable.  This was a big thing for us, because even though we are based in Australia, our costs and charges are in US dollars, so because of exchange rates etc., we wanted to keep the charges to a minimum.  I would estimate that already 70% of our customers are using our credit card facility, with more using it every day.  This saves us lots of time in itself, because the bank charges for overseas cheques in a foreign currency, or telegraphic transfers are horrendous here in Oz.  Not to mention the fact that I no longer have to get in my car and drive to the bank to deposit cheques all the time, and I no longer have to que at the "foreign currency" teller at the bank, getting frustrated at their inadequacy, many times a week.  This has been cut right down to an acceptable level and makes me a happy woman.  

New Additions to our Web Site [Top]

Pages With New Content Added [Top]

 Microsoft Excel tips [Top]

I thought for this months Excel section we would look at looking up values from within a table or list. As this is such a big topic, this will be part one of three. Before we jump into the functions we can use for this I would first like to point out how a table or list should be set up in Excel. If we following these guidelines we can easily extract data from any table or list.

  1. Always use headings and bold them or make them different in some way than your data. Many of Excels standard features automatically look for this and act accordingly.
  2. Avoid the use of entire blank Rows or Columns, in fact I would suggest avoiding any blank cells within the table or list whenever possible. Excel will often see a blank Row or Column as the end of your data.
  3. If month names are to be used as headings type a true date (25/12/2002) and custom format as "mmm" or "mmmm". This can make life much easier later on in Excels functions.
  4. If two tables or lists are NOT related keep them on separate sheets. This way if you Filter down one table or list you are not accidentally hiding the rows of another. If the data is related, keep it within the same table or list.  Rather than have a copy of your headings in the first spare row, use =A1 etc. This way any changes to the headings are reflected in your criteria headings.
  5. Leave at least 3 blank rows at the top of your table, this can be invaluable for Advanced Filtering etc. I normally opt for at least 6 and then simply hide them.
  6. Make use of Excels brilliant Validation feature.
  7. Try and keep one table simply for data entry then use this to produce a Pivot Table etc. Avoid mixing data entry tables with result tables.
  8. Aim to use Columns for headings and Rows for data entry, not vice versa. Remember there are only 256 Columns and 65536 rows. Also most  of Excels features are designed to work with tables set up this way.

Now of course these rules are not hard and fast, but more like guidelines. As I always say "good habits are just are hard to break as bad ones".

If you would prefer all the below was done for you then Download the workbook here.

For all examples lets use a simple 6 row by 5 Column table of data. In row B6 (yes leave rows 1 to 5 blank) put the first 5 months of the year. In cell B6 type: 1/1/2002 and custom format as mmmm. Now right click on the bottom right corner of cell B6 (the fill handle), drag to cell F6, release and select "Fill Months". Now bold these headings. In cell A7, type: Monday now use the Fill Handle of A7 (left click this time) drag down to A13 and release. Bold and Italic these. Now fill the table with the numbers, type 50 in cell B7, 75 in cell B8 and 225 in cell C7 and 250 in cell C8. Select cells B7:C8 and using the Fill Handle drag across to Column F, then down to Row 13. 

Now select the entire table (A6:F13), go to Insert>Name>Create check both "Top row" and "Left column" and click OK. While the table is still highlighted click in the Name box (left of Formula bar) and type: Data. Ideally we would use Dynamic Range Names.

Ok, onto the extracting of data from the table.  In most cases we can use the VLOOKUP function, which will return a result by looking in the first Column of a table for a specified value and return the result from the corresponding row in a specified column to the right. Lets say we want to know the figure for Tuesday in March, we could use:

=VLOOKUP("Tuesday",Data,4,FALSE)

Syntax

=vlookup(lookup_value,table_array,col_index_num,range_lookup)

Very easy to use and very handy in a lot of cases. However the VLOOKUP relies very entirely on us knowing the position of the column headed "March". With a table like this we could also simply use:

=Tuesday March

This holds a few advantages on the VLOOKUP with these being:

  1. Easier to read
  2. Shorter to type
  3. No need to know the Column position of "March"

Look Left

While the VLOOKUP is arguably the most popular Lookup function in Excel, it lacks the alibility to look to the left of it's table_array  that it references. This is no problem for us to do in Excel though as we can use a combination of 2 other Excel Lookup functions, INDEX and MATCH

Syntax

=index(array,row_num,column_num)

=Match(lookup_value,lookup_array,match_type)

There are 2 INDEX functions in Excel and it's the first one we are going to use. It returns the value of a specified cell or array of cells within array.

The MATCH Function 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. 

So what we will do is use the MATCH function to supply the argument of the row_num within the INDEX function. So if we use the same table again we can now look in ANY column of our table and return the result from the corresponding row in the Column to the left or right! So if we used:

=INDEX(Data,MATCH(450,March,0)+1,1)

We could look in the March column for the value 450 and have our formula return the day of the week this value corresponds to. By changing the very last argument  column_num can make it return the corresponding result from any column in the table. It is important to note the 0 used as the  match_type argument in the MATCH function. This tells MATCH to find an exact match for 450 in the March Column. We must use +1 so we account for the headings. It can also take the value of 1 or -1. See text below from Excel help

Let's assume now that we do not know which column we want our corresponding result from. All we know is we want the corresponding result for January, but we do not know which Column January is. The formula below will do this for us:

=INDEX(Data,MATCH(450,March,0)+1,MATCH(VALUE("1/Feb/2002"),FirstMonths,0)+1)

Before this will work however, we must name the range that holds are headings (B6:F6) FirstMonths. Note also that we have used VALUE("1/Feb/2002") as the argument for the second Match function. We must use this as our headings a true dates and not just text. If you are not sure what I mean by this then I would urge you to read this page (and the links from it) http://www.ozgrid.com/Excel/ExcelDateandTimes.htm as Dates and Times are a very important part of Excel. Once you understand how excel sees dates and times, you should have no more problems with them. We also need to use +1 after the second MATCH because the named range "FirstMonths" has one less Column than the named range "Data".

Download the workbook here.

Microsoft Excel VBA tips [Top]

As we have just covered the use of Excel's Lookup functions in our Excel section, it would be a good time to point out a common mistake made by some programmers. If you wish to perform a Lookup in VBA it seems that many opt to use the Vlookup WorksheetFunction in their code. While this can be done, it is a slow means of looking up data. This is because when we are in VBA we can use the extremely fast Find Method. However, before I show an example I fell it is extremely important to point out a couple of very often overlooked parts of this Method. See below from the Excel Help:

Remarks

The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you don’t specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.

If we do not remember this we are heading into possible problems, big time! I cannot tell you how many times I see the Find Method used incorrectly due to this. By far the best way to get the code needed for the Find Method is to use the Macro Recorder then modify the code.

Let's assume we have a table of data on Sheet1 within a range named Data and We wish to find the name Billy Brown obtain the value from 3 columns to the right on the correspoding row.

Sub FindBillyBrown()
Dim vOurResult
' FindBillyBrow Macro
' Using the Find Method over Vlookup
'
If WorksheetFunction.CountIf(Sheet1.Range("Data"), "Billy Brown") > 0 Then
    With Sheet1.Range("Data")

        vOurResult = .Find(What:="Billy Brown", After:=.Cells(1, 1), _
            LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False).Offset(0, 3)
End With

MsgBox vOurResult
End If
End Sub

Note that we have used the CountIf function to first find out whether the name even exits. The code does assume the named range Data exists on Sheet1 (CodeName). You will find this method, while it looks slower, is many times faster than VlookUp. It is also very flexible as we can offset to the right or left, depending on where we find the name. On the point of 'shorter code', it is a common misconception that the shorter the code the more efficient it must be. Nothing could be further from the truth! Often short code accounts for no errors and doesn't make use of Excel's built in features.

Let's now make this a bit more complicated by saying we might well have more than one occurrence of the name Billy Brown. The code as it is will only find the very first occurrence, which may not be what we want. Let's assume we want the name Billy Brown and it's corresponding result on the same row, 3 columns to the right BUT only if the value in the cell immediately to its left is 35 (the persons age).

Sub FindBillyBrown35()
Dim vOurResult
Dim rFoundIt As Range
Dim iLoop As Integer
' FindBillyBrow Macro
' Using the Find Method over Vlookup
'
    With Sheet1.Range("Data")
        'Set variable to start search from
         Set rFoundIt = .Cells(1, 1)
            'Loop no more times that the name occurs
             For iLoop = 1 To WorksheetFunction.CountIf _
                                (Sheet1.Range("Data"), "Billy Brown")

                'ReSet variable to found occurence of name. Next loop search _
                will start AFTER this Set cell.
                Set rFoundIt = .Find(What:="Billy Brown", After:=rFoundIt, _
                LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, MatchCase:=False)

               'Check if it's the right one.
                If rFoundIt.Offset(0, -1).Value = 35 Then
                    vOurResult = rFoundIt.Offset(0, 3)
                    Exit For 'Leave loop
                End If

            Next iLoop
    End With 'Sheet1.Range("Data")

    If Not IsEmpty(vOurResult) Then 'Variable holds a value
        MsgBox vOurResult
    End If
End Sub

Again while this code is quite lengthy for what it does I feel confident in saying there is no faster way. (now I'll be flooded with emails!!)

The important parts of this code are the use of CountIf to limit our loop and the use of Exit For Note also the use of the IsEmpty Function which returns a Boolean value indicating whether a variable has been initialized.

Some Short and Sweet VBA Tips

  1. To name a Module. Select it, push F4 and type the new name in the Properties Window.
  2. To stop users scrolling about a Worksheet. Select the sheet from the "Project Explorer", push F4 and type in A1:H20, or whatever range you like. The code Sheet1.ScrollArea = "A1:H20" could also be used in the Activate Event of the Sheet Object. Use Sheet1.ScrollArea = "" to set it back to normal.
  3. Destroy ALL variables. Use the End Statement.
  4. Prevent Procedure being available via Tools>Macro>Macros (Alt+F8). Use Option Private Module at the very top of the Module.
  5. Prevent the use of Esc or Ctrl+Break to halt code. Use Application.EnableCancelKey = xlDisabled RemarksUse this property very carefully. If you use xlDisabled, there's no way to interrupt a runaway loop or other non – self-terminating code. Likewise, if you use xlErrorHandler but your error handler always returns using the Resume statement, there's no way to stop runaway code.The EnableCancelKey property is always reset to xlInterrupt whenever Microsoft Excel returns to the idle state and there's no code running. To trap or disable cancellation in your procedure, you must explicitly change the EnableCancelKey property every time the procedure is called.

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

Get the OzGrid Excel Plus Add-In! 8 add-ins in one for less than you think. If you buy the add-in and are subscribe to our free newsletter, you will get the "File Size Reducer" for FREE! Many Other Excel Add-ins Here Over 40 of them!

 

Kind regards

Dave Hawley

DavidH@OzGrid.com

Helpful Information [Top]

  • 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.
    •

     

    Order Training Online

    Hire OzGrid For Excel/VBA

     

    Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation.