OzGrid Excel and VBA Newsletter

Search Tips

All Excel Templates Normally an 80% saving at $299.00. Super Special $175.00!

Check out this months super half price offer !

MAIN INDEX

Search Tips

What's New at OzGrid | 50% Off Offer | Excel Tips and Tricks | Excel VBA Tips and Tricks

What's New at OzGrid

February was another busy month at OzGrid, with the final touches being put to our book - "100 Excel Hacks".  We are currently in the process of proof-reading and checking, then re-checking all Hacks and making sure we have the right figures in the right places.  Hopefully we have picked up any outstanding errors as the book is off to the printers at the end of the month, it should be out on the shelves in April.

Apart from the book, we have been busy preparing lectures and assessments as the school year commenced this week and students have Start ed at College.  This will certainly keep us busy for the next few weeks while the students (and us) settle in.  Also keeping us busy is the Consulting Services side of OzGrid.com.  Business is steadily increasing in this area, due to the extra advertising we have undertaken, we see hope this will continue to grow in the future.  

Over the next few months we will Start updating our training lessons to reflect Microsoft Excel XP.  We will keep you informed as to when they will be available.

Seems like we have lots to keep us busy, so we must keep Excelling....

50% Off Offer

This month we are extremely happy to be able to offer to all our newsletter membersNeural Networks Signal For Microsoft Excel at a 50% discount. This is an extremely popular Excel add-in for traders. The offer is open to all newsletter members until 29 Feb 2004.

Excel Tips and Tricks Essential Spreadsheet Design

This month I thought I would share with you the most important (I cannot stress that enough) part of using Excel, data layout. Without doubt the single biggest mistake made by those who use Excel is their data layout. Out of every 10 Workbooks I see, at least 8 are set-up in the wrong manner. This then means that, 8 out 10 Workbooks are built on a very poor foundation. If your foundations are not correct you cannot expect to build a good solid spreadsheet.

Excel, although not a true database, often expects data to be laid out in a database fashion. This simply means that you should lay out your raw data in a standard table manner. Have your headings going across row 1 of your table and all associated data laid out directly underneath these headings. There should be no blank columns or rows at all in the table. See the two tables below to see what I mean.

Correct Layout of Raw Data

Incorrect Layout of Raw Data

Let's look at why the second table is just one of many incorrect ways to layout raw data.

1. No spare rows on top of table for features such as Advanced Filter.
2. Headings not bolded or differentiated from the data. Many of Excels features, such as Sort, Subtotals etc may not automatically detect the headings.
3. Numeric values and text values have had their default horizontal alignment changed. Numbers should be right aligned and text left aligned. This way you can tell at a glance if data is text or numeric. You will be surprised just how easy it is to think a cell has a number (such as dates) when it's really text.
4. Blank rows in the table at rows 4 & 5.
5. Blank cell left to indicate the repeat of Male and Female. Will make it hard to base a Pivot Table off the data.

Remember, this is for your raw data, not your final results. Each Workbook should have a Worksheet that houses raw data (and can be used for further data entry). Try to keep all related data in the one table and on the one Worksheet. As a general rule, the more Worksheets you use for raw data the harder it becomes to extract and return meaningful results.

Once you follow the above rules you will find you are able to do things with Excel that most will never know. Some of features that will become easy to use when/if following these rules are;

1. Pivot Tables
2. Subtotals
3. Group & Outline
4. Auto Filter
5. Advanced Filter
6. Lookup & Reference Functions
7. Custom Views
8. Report Manager
9. Database Functions
10. Dynamic Named Ranges

Too many people make the mistake of using their raw data as their only data and hence their final resulting data. There should rarely be a need for users to see the raw data. Users only need to see the results they are after. The raw data sheet can be completely hidden from view (Format>Sheet>Hide) and only shown when/if more data is to be added. Think of the raw data sheet as your database not your information.

The above information, in my opinion, is the most valuable bit of information I can pass on. To read more about spreadsheet design go to: Efficient Excel Spreadsheet Design

Excel VBA Tips and Tricks How many Mondays in that month

For those of us that use Excel a lot, both the interface and VBA, you will appreciate just how painful dates in Excel can be at times! This is particularly true for those of us that live outside the US. Excel has quite a rich selection of date and time functions and even more with the Analysis Collection Add-in installed. One function I would like to see in Excel would be one that can tell me how many specified days there are in any specified month of any specified year. There is probably a way to do this without VBA (never really attempted it) but I would imagine it would be one of those horribly long deeply nested functions. Anyway, I decided to set upon writing my own custom function (seems well suited) as VBA has even more date and time functions available. The reason I see this problem is well suited to a Custom Function, as opposed to several nested standard Excel functions, is because we should not have to worry about looping through and reading cells. This is what makes most Custom Functions so horribly slow, even compared to a deeply nested mega formula.

The function below can be used to determine how many days (e.g Mondays etc) there are in any specified month. For example

=HowManyDaysInMonth("1/12/03","wed")

Will return 5 as there are five Wednesdays in the month of December in 2003

=HowManyDaysInMonth("1/12/03","thu")

Will return 4 as there are four Thursdays in the month of December in 2003

To be able to use this custom function in a Workbook, you must first place the code below into a standard module.

1. Open the Workbook.
2. Go to Tools>Macro>Visual Basic Editor (Alt+F11).
3. Then to Insert>Module.
4. Paste in the code.
5. Click the top right X to return to Excel.

'The Code

Function HowManyDaysInMonth(FullDate As String, sDay As String) As Integer
Dim i As Integer
Dim iDay As Integer, iMatchDay As Integer
Dim iDaysInMonth As Integer
Dim FullDateNew As Date

iMatchDay = Weekday(FullDate)
    Select Case UCase(sDay)
       Case "SUN"
        iDay = 1
       Case "MON"
        iDay = 2
       Case "TUE"
        iDay = 3
       Case "WED"
        iDay = 4
       Case "THU"
        iDay = 5
       Case "FRI"
        iDay = 6
       Case "SAT"
        iDay = 7
    End Select
  iDaysInMonth = Day(DateAdd("d", -1, DateSerial _
            (Year(FullDate), Month(FullDate) + 1, 1)))
  FullDateNew = DateSerial(Year(FullDate), Month(FullDate), iDaysInMonth)
        For i = iDaysInMonth - 1 To 0 Step -1
          If Weekday(FullDateNew - i) = iDay Then
            HowManyDaysInMonth = HowManyDaysInMonth + 1
          End If
        Next i
End Function

Now simply enter the function into any cell as shown above.

If you are not familiar with DateAdd, DateSerial, Weekday, Day, Month and Year functions in VBA I strongly suggest reading up on them via the VBA help. They can make life working with dates (in particular the DateSerial) so much easier.

Until next month, keep Excelling!

You are more than welcome to pass on this newsletter to as many people as you wish. Or even upload it (as is) to your Web site!
To no longer receive our newsletter, send a NEW email with the exact words "action: Unsubscribe" in the body of the email, or click here .
Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation
Read this issue and past issues online here :