Saturday, 23 January 2010

OzGrid's Free Excel Newsletter

This edition looks at Excel lists andtables, good spreadsheet design, array formulas, volatile functions. 

See Also:Excel Best Practices &speed up vba code & Slow Macros &Excel/VBA Golden Rules &Excel Services Best Practices

The ExcelVBA sections covers Workbook and Worksheets events and using the Target.

See Also:worksheet change event & Excel Macros &Excel Formulas

Microsoft Excel tips

Forthis months Excel tips I thought we would go back to the very basics of Excel and spreadsheets in general and talk about the very important fundamentals of setting up and designing an efficient spreadsheet.  While it may seem quite easy to design your own spreadsheet once you have become reasonably proficient in using Microsoft Excel, it is unfortunately very rare that the proficient user for Excel will understand the necessities of designing and setting up a spreadsheet in a correct and efficient manner.  A spreadsheet which has been designed and set up properly should have no problems in performing recalculations, opening, saving and closing etc.  A poorly designed spreadsheet will cause constant frustration as it will take far too long for formulas to recalculate.  To make matters worse, if the spreadsheet has not been set up in the correct manner, it will become extremely difficult, if not impossible, to use many of Excel's built-in features that are ideal for working with spreadsheets.

Lists and Tables

When setting up a list or a table in Excel, you will be grateful later-on if you set your table or list up in the way Excel expects it to be set up.  By this I mean have your headings set-up going across the columns, and your data laid out in the rows directly underneath.  Avoid whenever possible including blank cells and/or rows and columns in your table and make your headings distinguishable in some way from your main data, eg bold.  Sort your table by at least one column of data.

Below are six basic rules which when adhered to can make your life much easier in the long run:

1. When putting in headings bold the font.  This will help Excel recognize them as headings when you use one of its functions eg; Data>Sort.
2. When putting data into the data area of your spreadsheet try to avoid blank rows and columns if possible. This is because a lot of Excels built-in features will assume a blank row or column is the end of your data. It also helps with a lot of Excels formulas.
3. Have your data sorted if possible. Excel is very rich in what is known as Lookup & Reference formulas and many of these rely on your data being sorted in a logical order.
4. Use real dates for headings and format them appropriately. By this I mean if you want the names of the months as headings type them in as 1/1/2001 , 1/2/2001 , 1/3/2001 etc then format them as "mmmm". This is a very simple procedure that is all too often overlooked by many. If you have real dates as headings life will be much easier further down the road when you need to use them in formulas.
5. Avoid merging cells if possible. Use "Centre across selection" instead. This can be found by going to Format>Cells/Alignment and select it from the Horizontal box. Merging cells can cause all sorts of problems later on that that will have you scratching your head.
6. Don't put in one cell what could go in more than one cell. Say you have the names of 100 people to put in your spreadsheet don�t put their full name in one cell. Instead put the First name in one cell then their surname in the next cell to the right. If you need to place them into one single cell at a later stage, this can be done very easily - the same cannot always be said for the reverse

Speeding up Re-calculations

A common problem with poorly designed spreadsheets is that they become painfully slow in recalculating.  Many people will suggest a solution to this problem is putting your calculations into Manual by going to Tools>Options>Calculations.  This is generally very poor advice and thwart with potential disasters.  A spreadsheet is all about formulas and calculations and the results that they produce.  If you are running a spreadsheet in manual calculation mode, sooner or later you will read some information off your spreadsheet which will not have been updated.  In other words your formulas will be returning old values and not the updated values.  This is because when you go into manual calculation you must force Excel to recalculate by pushing the F9 key.  As you can imagine, it is very easy to overlook doing this.

To prevent this from happening, it is very important to realize that designing a spreadsheet is often 80% planning and 20% implementation.  By this I mean it is not uncommon for a spreadsheet developer to spend 80% of his time deciding how the spreadsheet should be set up in an efficient manner for the results that are expected.  While this can seem extremely inefficient in the short run, I can guarantee you that the long term gain of this will far outweigh the short-term pain.  If you follow the above guidelines for lists and tables, you should generally be able to use all of Excel's built-in features to produce the results that you want.  This alone, can reduce your amount of formulas by a dramatic amount.  In particular, the user of Excel's pivot tables and sub-totals can often be used in place of potentially hundreds of formulas.  This has the added benefit of keeping the workbook to a manageable size and will speed up Excel's calculations greatly.

Below is a list of what I believe to be the worst offenders for slowing down recalculations of spreadsheets.

1. Array formulas
2. UDFs (User-defined functions)
3. Volatile functions
4. Using range references in formulas that are beyond the required data scope.

Let's look at each of these now in turn, and see what alternatives we can have for them.

Array Formulas

A possible alternative for array formulas, are Excel's database functions.  The Excel help has some very good examples on how these formulas can be used on large tables of data and are able to return results based on multiple criteria.  In fact up to 256 different criteria.   Another alternative which is too often overlooked is the use of Excel's pivot table feature.  While these may seem very daunting when first encountered, I highly recommend that you familiarize yourself with this powerful Excel feature as once you master them, you will wonder how you survived without them!

In a nutshell, array formulas are a very superficial solution and as far as I am concerned are nothing more than a quick fix that can lead you on a slippery slope to nowhere.

UDFs Or Custom Functions

UDFs for those of you that are not aware, are User-defined Functions that are written with Excel VBA which can then be used in a Worksheet in the same way as any one of Excel's built-in worksheet functions.  Unfortunately, no matter how good the person is at VBA who has written the UDF, it is very unlikely that it will perform at the same speed as one of Excel's built-in functions or even if it would be necessary to use several nested functions to get the same result.  In my opinion, a well written and efficient UDF function will incorporate the user of Excel's worksheet functions.  By this, I mean you may decide to write a UDF to replace a function which otherwise would require you to use several worksheet functions nested within each other.  The most efficient way to replace the use of several nested worksheet functions would be to write a UDF that would incorporate the use of all of the worksheet functions necessary.  This way instead of having to repeatedly nest the functions within each other to get the desired result, you could in effect nest the functions within VBA which would then allow you to use this function in the interface without the need to repeat the nesting.

Volatile Functions

Volatile functions are simple functions that will recalculate each time a change of any sort occurs in any cell on any worksheet.  Most functions will only recalculate if a cell which they are referencing has changed.  Some of the most common volatile functions used are undoubtedly the NOW() and TODAY() functions.  If you are going to be using the result of these functions frequently throughout your spreadsheet, avoid the temptation of nesting these functions within other functions to get your desired result.  Instead, simply type the volatile function into a single cell on your spreadsheet and reference that cell from within other functions.  This alone can potentially cut down on the amount of volatile functions by hundreds, if not thousands at times.

Using Range References

Possibly one of the most common mistakes made when designing a spreadsheet is using your formulas to reference entire columns of data.  This forces Excel to look through potentially thousands, if not millions or cells which it need not be concerned with at all.  Assume, for example you have a table of data ranging from cell A1 down to H1000.  You may decide that you want to use one or more of Excel's look-up formulas to extract required information.  As your table may be growing and as new data is added, it is common to reference the entire table incorporating all rows and all columns.  In other words, your reference may look something like "A:H", or possibly A1:H65536.  The reason this is done is so that when new data is added to the table it will automatically be referenced in the formulas.  This is a very bad habit to form and should be avoided in most cases.  This obviously begs the question that if you do not do this you will be constantly needing to update your formula references to incorporate the new data as it is added to the table.  Possibly one of the very best ways to overcome this is to familiarize yourself with the use of dynamic named ranges. If you are not familiar with these, check them out here: Dynamic Ranges

Microsoft Excel VBA tips

For this months Excel VBA section Ithought it would be a good idea to look into some of Excel Object Events. Thetwo most popular of these are Worksheet Event and WorkbookEvents. Both the Worksheets and Workbook have their own PrivateModules, much like a UserForm has its own Private Module.  As do Controls placedon UserForms (and the UserForm itself) have Events, eg Click, Initialize,Activate etc so does the Worksheet and WorkbookObjects. Perhaps the most commonly used Worksheet Event isthe Change Event and the most commonly used Workbook Event is theOpen Event. In versions of Excel prior to Excel 97 the only way to have a macrorun upon opening a Workbook was to name the macro "Auto_Open()" and place it in a standard module. This has now been replaced with the "Workbook_Open()"placed in the Private module of the Workbook Object, or "ThisWorkbook".  If you are still using Auto_Open (still available, but only for backwardcompatability) it's time to move ahead and use Workbook_Open instead.

As I have mentioned above theseEvents are for these Objects and as such MUST be placed within the PrivateModule of the Object. See points 8 and 9under Helpful Information at the end of this, and all newsletters foreasy access to these. The Workbook_Open event is very much as it says, itfires any code within it whenever the Workbook is opened. The opposite ofthis is the "Workbook_BeforeClose(Cancel As Boolean)"is fired immediately before the Workbook closes. You willnotice that this Event also has what is known as an argument "(CancelAs Boolean)".  If you set this to True (the default, or if omittedis False) as in the Procedure below:

Private SubWorkbook_BeforeClose(Cancel As Boolean)
    Cancel = True
End Sub

You are in effect preventing theWorkbook from closing by Cancelling the operation. Why would you need this?There are basically 2 instances when this would be done.

1. You wish to run a check on something in your spreadsheet and if what you need has not been done you could set the Cancel to True and prevent the Workbook closing until such time as it's been done. Remember though you should display a simple message informing the user of why they cannot close and what they must do before they can.
2. You have a Custom Toolbar which is attached to your Workbook and you want to Delete it each time the Workbook closes. Be aware that you should only Delete a Custom Toolbar if it is attached! If your toolbar should only be available while in a specific Workbook you should attach the toolbar to it. To attach a Custom toolbar, go to View>Toolbars>Customize | Toolbar select your Custom toolbar from the list, click "Attach" and copy it over. By doing this (and deleting it on Closing) any changes another user makes to your toolbar will not stick! To make changes stick you must first go to View>Toolbars>Customize | Toolbar select your Custom toolbar from the list, click "Attach" and then "Delete", make your changes re-attach it and then Save.

What we have done in this case ispassed the result of the Cancel argument to a Boolean variable. This means thatshould they click Cancel at the Save changes stage we will not delete ourcustom toolbar too early. If they do not choose Cancel and click Yesor No, the opposite of the Cancel (True) will be passed toour Boolean variable, our Workbook would then deactivate and fire the Workbook_Deactivate()Event. From here our If statement will determine the value of ourBoolean variable (True) and Delete it only if they are closing. If they aresimply leaving our Workbook open and activating another, our toolbar will onlybe hidden. This of course means we must also display our toolbar again oncethey re-activate the Workbook, no problems there, just use:

Private Sub Workbook_Activate()

On Error Resume Next
Application.CommandBars("MyCustomToolbar").Visible = True
On Error GoTo 0

End Sub

The use of the On Error Resume Next is just in case the PC crashes and things don't go as planned. To get detailon all Workbook Events see "Workbook Object Events" in theExcel VBE help.

Lets now look briefly at the "Private Sub Worksheet_Change(ByVal Target As Range)"Event. See points 8 and 9 under HelpfulInformation at the end of this, and all newsletters for easy access to this.According to the Excel help the Change Event: Occurswhen cells on the worksheet are changed by the user or by an external link.You will of course notice that this Event also takes an argument, which is theTarget (The changed range. Can be more than one cell.)This allows us to run our code only if the Change has occurred within a specificrange. We can do this in one of 2 ways.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$A$1" Then
     'Do something
  End If
End Sub

Or

Private Sub Worksheet_Change(ByVal Target As Range)
 If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
   'Do something
 End If
End Sub

If you are not familiar with theIntersect Method you can read all about it in issue 10 here:  Issue 10-

The first method will simply tell ourcode to fire only if the changed cell is A1, while the secondmethod will fire only the the changed cell is within the range A1:A10.If you ranges are non-contiguous you could use:

Intersect(Target, Range("A1:A10, B5:D15, H1:G10"))

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

Kind regards

Dave Hawley

HelpfulInformation