Saturday, 23 January 2010

OzGrid's Free Excel Newsletter

Getthe OzGrid Excel Plus Add-In! 8add-ins in one for less than you think. If you buy the add-in and are subscribedto our free newsletter, you will get the "File Size Reducer" for FREE!ManyOther Excel Add-ins HereOver 40 of them!

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

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

Hi all,

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

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

Online Lessons [Top]

Hopefullyour 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 ourMicrosoft Excel - Level 2 course as this is one of our most popularcourses.  If all goes how we hope it will, we will follow this with all ourother training courses.  People wishing to access on-line lessons will besupplied with a Username and password, so they can access lessons at theirconvenience.  We are hoping that many people will take advantage of this,as although we try to send lessons within one working day, many clients haveasked if the on-line feature would be available, so they can be totallyindependent of Ozgrid, and only contact us if they have anyquestions.  

Inlight of the on-line lessons becoming available, we will be reviewing our pricesover the next few months.  We will be offering a price to purchase trainingcourses with all questions answered, and a price for just supplying the lessonsonly, with no ability to ask questions.  We hope to publish these newprices in the September newsletter.

FiscalYear Templates [Top]

As itis the end of the financial year here, we thought that the following templatessupplied 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 tocheck them out, we would be interested to know what you think of them.

MicrosoftExcel Add-ins and Free Stuff [Top]

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

We have also created a page on oursite dedicated to other Excel Add-ins from all over the world.  At present thereare some 40 add-ins here that you can purchase them on-line immediately.You will find this page here: ManyOther 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 denyingthat we all love something for nothing! So I have decided to create apage on our site dedicated to nothing but FREEstuff 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 fromother sites and not just mine.

MicrosoftWord Expert Newsletter [Top]

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

Has recently started a FREEmonthly newsletter that gives subscribers lots of info on Microsoft Word as wellas 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 Ifeel this newsletter is the perfect compliment to our Excel newsletter.

CreditCard Facility [Top]

Thought I would just let all youfledgling businesses know that after the hassle of searching for and signing upwith a suitable company that supplies secure on-line Credit Card facilities, ithas proved to be extremely worthwhile.  We are using WorldPay for ourCredit Card facilities, and this facility has already paid for itself in theshort time that we have had it.  WorldPay are prompt with payment, alltheir 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 USdollars, so because of exchange rates etc., we wanted to keep the charges to aminimum.  I would estimate that already 70% of our customers are using ourcredit card facility, with more using it every day.  This saves us lots oftime in itself, because the bank charges for overseas cheques in a foreigncurrency, or telegraphic transfers are horrendous here in Oz.  Not tomention the fact that I no longer have to get in my car and drive to the bank todeposit cheques all the time, and I no longer have to que at the "foreigncurrency" teller at the bank, getting frustrated at their inadequacy, manytimes a week.  This has been cut right down to an acceptable level andmakes me a happy woman.  

NewAdditions to our Web Site [Top]

PagesWith New Content Added [Top]

 Microsoft Excel tips[Top]

I thought for this months Excelsection we would look at looking up values from within a table or list. As thisis such a big topic, this will be part one of three. Beforewe jump into the functions we can use for this I would first like to point outhow a table or list should be set up in Excel. If we following these guidelineswe 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 nothard and fast, but more like guidelines. As I always say "good habits arejust are hard to break as bad ones".

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

For all examples lets use a simple6 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 andcustom format as mmmm. Now right click on the bottom right corner of cell B6 (the fill handle), drag to cell F6, release and select "FillMonths". Now bold these headings. In cell A7, type: Monday nowuse the Fill Handle of A7 (left click this time) drag down to A13and release. Bold and Italic these. Now fill the table with the numbers, type 50 in cell B7, 75 in cell B8 and 225in cell C7 and 250 in cell C8. Select cells B7:C8and 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 thetable is still highlighted click in the Name box (left of Formula bar) and type: Data. Ideally we would use DynamicRange Names.

Ok, onto the extracting of datafrom the table.  In most cases we can use the VLOOKUP function, which will return aresult by looking in the first Column of a table for a specified value andreturn 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 coulduse:

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

Syntax

=vlookup(lookup_value,table_array,col_index_num,range_lookup)

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

=Tuesday March

This holds a few advantages on theVLOOKUP with these being:

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

LookLeft

While the VLOOKUP is arguably the mostpopular Lookup function in Excel, it lacks the alibility to look to the left ofit's table_array  thatit references. This is no problem for us to do in Excel though as we can use acombination 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 thefirst 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 positionof an item in an array that matches a specified value in a specified order. UseMATCH instead of one of the LOOKUP functions when you need the position of anitem in a range instead of the item itself. 

So what we will do is use the MATCH function tosupply the argument of the row_num withinthe INDEX function. So if we use the same table again we can now look in ANYcolumn of our table and return the result from the corresponding row in theColumn 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 450and have our formula return the day of the week this value corresponds to. By changingthe very last argument  column_num canmake it return the corresponding result from any column in the table. It isimportant to note the 0 usedas the  match_type argument inthe MATCH function. This tells MATCH to find an exact match for 450 in the MarchColumn. We must use +1so 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 columnwe want our corresponding result from. All we know is we want the correspondingresult for January, but we do not know which Column January is. The formulabelow will do this for us:

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

Beforethis 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 ourheadings a true dates and not just text. If you are not sure what I mean by this thenI would urge you to read this page (and the links from it) http://www.ozgrid.com/Excel/ExcelDateandTimes.htmas Dates and Times are a very important part of Excel. Once you understand howexcel sees dates and times, you should have no more problems with them. We alsoneed to use +1 afterthe second MATCH because the named range "FirstMonths" has one lessColumn than the named range "Data".

Download the workbook here.

Microsoft Excel VBA tips[Top]

As we have just covered the use ofExcel's Lookup functions in our Excel section, it would be a good time to pointout a common mistake made by some programmers. If you wish to perform a Lookupin VBA it seems that many opt to use the Vlookup WorksheetFunction in theircode. While this can be done, it is a slow means of looking up data. This isbecause 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 coupleof 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 youdon’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 Finddialog box, and changing the settings in the Find dialog box changes thesaved values that are used if you omit the arguments. To avoid problems, setthese arguments explicitly each time you use this method.

If we do not remember this we are heading into possible problems, bigtime! I cannot tell you how many times I see the Find Method usedincorrectly due to this. By far the best way to get the code needed for the FindMethod 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 valuefrom 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 CountIffunction to first find out whether the name even exits. The code does assume thenamed range Data exists on Sheet1 (CodeName). You will find this method, whileit looks slower, is many times faster than VlookUp. It is also very flexible aswe can offset to the right or left, depending on where we find the name. On thepoint of 'shorter code', it is a common misconception that the shorter the codethe more efficient it must be. Nothing could be further from the truth! Oftenshort code accounts for no errors and doesn't make use of Excel's built infeatures.

Let's now make this a bit more complicated bysaying 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 whatwe want. Let's assume we want the name Billy Brown and it's correspondingresult on the same row, 3 columns to the right BUT only if the value in the cellimmediately 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 lengthyfor what it does I feel confident in saying there is no faster way. (now I'll beflooded with emails!!)

The important parts of this code arethe use of CountIf to limitour loop and the use of Exit For Notealso the use of the IsEmptyFunction which returns a Boolean value indicating whether a variable has beeninitialized.

SomeShort 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!

Getthe OzGrid Excel Plus Add-In! 8add-ins in one for less than you think. If you buy the add-in and are subscribeto our free newsletter, you will get the "File Size Reducer" for FREE!ManyOther Excel Add-ins HereOver 40 of them!

 

Kind regards

Dave Hawley

[email protected]

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

     

    OrderTraining Online

    HireOzGrid For Excel/VBA

     

    Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation.