Moving!

Just letting you know that I'm moving my site over to WordPress.com. The new address is:
http://dotxls.wordpress.com/

I look forward to seeing you there :)

References

There are a lot of people who write add-ins for Excel, myself included.  On several occasions I wondered if I could use those add-ins within the VBA of another spreadsheet.  And of course you can using - you guessed it - references!  And frankly I think references are one of the most criminally under-documented features of VBA in general.

I think of references essentially as "VBA add-ins".  However references are somewhat more powerful add-ins because there is such a variety of things you can use, including things that come built in with Windows or Office.  I'll give you a couple of examples in a bit.

Firstly to have a look at references you need to remember the Tools - References menu option in VBE. This will bring up a huge list of available things you can reference.  In any spreadsheet there are a few standard ones that are ticked at the very top like the "Microsoft Excel Object Library". I'd recommend that you don't remove any of these standard ones as they are fundamental to smooth operation.

All the references that you've selected will appear at the top of this huge list, with ticks next to them. So you won't ever need to search the whole list for an elusive tick!

What references might you want?  Well one that I use regularly is the Microsoft Scripting Runtime. This gives you access to the Visual Basic Scripting Object Model, with notable inclusions the Dictionary object and the FileSystemObject.

• Dictionary objects are very similar to Collections  in VBA but with some useful additional features.  If you want to check if an item exists in your Dictionary object then use the Dictionary.Exists method.  And Dictionaries also provide you with a list of the unique IDs of its elements using the Dictionary.Keys property.
• FileSystemObject is a very powerful tool in scripting. You can recurse through Files in a folder or subfolders of a parent folder, create and delete folders and files, check if folders and files exist and so on.  It's an extremely powerful tool - I may well do a future post dedicated to it.

 Another reference I've used recently is the CAPICOM Type Library.  I needed a hash function to help me validate some data, so decided to use the MD5 algorithm.  (If you don't know what a hash function is, it's essentially just a fancy checksum but it works on text data as well.)  Adding the CAPICOM reference gives you the HashedData object, which can be used to generate a variety of hash functions.

As mentioned at the top, you can even create references to other Excel addins, giving you access to all the same functions and macros.  This can make for some helpful ways to organise and simplify code.

All in all references are an extremely powerful tool and well worth getting acquainted with.  If you're interested in investigating the Microsoft Scripting Runtime further then I'd also recommend checking out the VB script pages at www.devguru.com for a handy reference.

ByVal and ByRef

A few people have asked me what the difference between ByVal and ByRef actually is. In a nutshell it's the difference between creating a new variable (ByVal) and rebadging an existing one (ByRef).  Here's an example of some code you can run:

Option Explicit

Public Sub Test()
    Dim x As Double
   
    x = 1
   
    AddTwoByVal x
    Debug.Print x
   
    AddTwoByRef x
    Debug.Print x
End Sub

Private Sub AddTwoByVal(ByVal Value As Double)
    Value = Value + 2
End Sub

Private Sub AddTwoByRef(ByRef Value As Double)
    Value = Value + 2
End Sub

Paste this into a blank module and run the "Test" macro.  You should see the following output in the Immediate Window (press CTRL+G to bring it up if it's hidden):

1
3

But what's going on?

Using ByVal creates a whole new variable.  VBA takes the value of x and stores it in a new variable called "Value".  When AddTwoByVal finishes the variable Value is thrown away along with everything you've done to it, so there's no effect on "x" in Test.

Using ByRef effectively relabels an existing variable.  So in AddTwoByRef you are actually using the same variable as in Test but, figuratively speaking, you've stuck it in a box labelled "Value".  Therefore when AddTwoByRef finishes the changes to "Value" will also be reflected in "x".

If you don't specify ByVal or ByRef then VBA assumes ByRef.

You can use this in all sorts of ways. One common application is to address some of the weaknesses of Functions. Functions return a single value (which could be an object or  user-defined type) but what if you want to amend 2 or more values?  Simply create a Sub with the "answers" as arguments passed ByRef - neat trick!

Long Formulae

My number one rule for spreadsheets is a simple one:

A long formula is a bad formula.

Long formulae are the bane of spreadsheets. On more than 1 occasion I have been tempted to write to Microsoft and ask them to impose a 100 character limit on formula length (or at least provide such an option). But what is the problem?

Let's look at this the other way round for a moment. How many short formulae do you see that are difficult to understand after a little thought? I'll bet not many. Maybe there's a new function you need to look up in Excel help but that's about all. A long formula can suggest many things:

• There are lots of parts to a single calculation.
• A simple calculation is being performed in an overly complicated way.
• The author is basically showing off (frankly the worst crime of all!)

For sake of illustration I'm going to calculate a person's Body Mass Index (BMI).  BMI is defined as:

BMI = (Weight in kilograms) / (Height in metres) ^ 2

My spreadsheet is reasonably flexible. It allows people to choose the units they use for entering height and weight. Height can be entered in metres or feet. Weight can be entered in kilograms, stones or pounds. (I'm assuming that users are capable of entering fractions, e.g. 10 stones 6 pounds would be entered using =10+6/14.)

B1 = height value
C1 = height units ("metres" or "feet")


B2 = weight value
C2 = weight units ("kilograms", "stones" or "pounds")

To calculate BMI in one go you could use this formula:

=B2*IF(C2="kilograms",1,IF(C2="stones",1/0.1575,1/2.205))/((B1*IF(C1="feet",1/3.281,1))^2)

This isn't too bad but it could definitely be easier to follow! Think about how you would check it. You would probably have to break it down as follows and check each part somehow (e.g. using 'Evaluate Formula' and stepping through a couple of example calcs):

1. Convert height into metres
2. Convert weight into kilograms
3. Calculate BMI

Surely it would be clearer if these steps were shown separately?

Let's say we put the height in metres in cell B4. To convert to metres we just pick out this part:
=B1*IF(C1="feet",1/3.281,1)

Now let's put weight in kilograms in cell B5. Now we just pick out this part:
=B2*IF(C2="kilograms",1,IF(C2="stones",1/0.1575,1/2.205))

Finally the BMI formula goes from that first formula above to something that resembles the definition much more closely:
=B5/B4^2

Notice how much easier this is to check too.  You can easily check that each conversion is working correctly. Then checking the BMI formula itself is simplicity itself!

IFERROR - a new(ish) way to handle errors

Oftentimes I see formulae that attempt to deal with errors, such as division by zero or failed VLOOKUPs.  Something like this for example:

=IF(ISERROR([something]),[something else],[something])

I never liked this because Excel actually has to do the [something] bit twice. Worse yet you may get one right and one wrong, e.g. due to updating one but not the other. Excel 2007 introduced a new function that addresses these points, called IFERROR. The following formula does exactly the same as the above but more robustly:

=IFERROR([something],[something else])

If [something] returns a valid answer then IFERROR returns it! If it's an error then IFERROR will return the result of the [something else] calculation instead. Notice how much shorter the formula can be, especially if [something] is quite long. Plus you only have to type each part once so there's less risk of accidents.

As a final point do beware of sending the spreadsheet to anyone using an earlier version of Excel. If you do then this function won't be recognised and you'll just get errors! In such cases you may need to rewrite these formulae using the original format shown above. Alternatively you can easily recreate the IFERROR function in VBA instead - just paste the following into a module:

Public Function IfError(Value, OtherValue)
'Do not use this if saving in Excel 2007 format or later!
    If IsError(Value) Then IfError = OtherValue Else IfError = Value
End Function

Use Print Preview!

Easy one, this! To put it succinctly, get into the habit of looking at Print Preview.

You've written a spreadsheet and next job is to pass it on to a colleague to have a look. Before you do anything else, have a quick look at Print Preview.  A lot of people will automatically print everything out that you send to them - documents, pictures, spreadsheets, you name it.  Now I'm not going to be an eco-warrior here - the rights and wrongs of this are not the point.  The point is that in the real world and that's what (some) people do.

Now I don't know about you but I'm certain that my boss wouldn't be very happy if I handed him a spreadsheet and he ended up sending 100 pages to the printer, 90 of which were junk.  It really is that simple.  A quick look a Print Preview can save you a lot of (easily avoided) headaches!

On top of that Excel provides something called "Page Break Preview", which appears on the "View" tab in Excel 2007.  This gives you a visual guide to where all the page breaks will appear when you print it.  The dashed lines are where Excel has put a page break for you, the solid ones are added by hand.  You can drag these around as needed.  When you've finished switch back to "Normal" view and you're done.  A small investment of time goes a long way!

Overwritten Cells

Very often where I work I come across spreadsheets of a certain type, characterised as follows:

• 1 row containing headings (of varying levels of helpfulness) ...
• followed by 1 row containing formulae ...
• all copied down for several hundred rows or more.

This is the kind of thing that spreadsheets are really good at. Write your calculations for 1 record and copy them as many times as you need. Brilliant.

That's all fine as long as people don't mess it up! All too often I see situations where the formula in cell AZ653 doesn't quite work due to a quirk in that record. Rather than think about the wider implications the user simply types the right value in instead and carries on regardless. There are numerous problems with this but I'll concentrate on the poor person whose job it is to check this spreadsheet. It is very possible that this change will be missed, or perhaps just become overwritten, e.g. if someone copies all the formulae right the way down again. We need an easy way to spot it.

Enter column differences. First select the range containing all the formulae but leave out the header row(s). In Excel 2007 the command is found here:

• Home tab
• Find & Select
• Go To Special
• Select the "Column differences" option
• Press OK.

(A slightly quicker route is to press the F5 key followed by the "Special" button.) Now Excel will only select those cells where the formulae don't match with those in the first row. You can cycle through these cells by pressing the TAB key. You can even colour the cells in using the normal fill control.

This works equally well when you have lots of formulae in a column copied across to the right. Only this time - you guessed it - you would select "Row differences" instead.

These range differences are powerful tools that can be a real life saver. If you ever come across spreadsheets like the ones I've described then I heartily recommend getting used to using these tools.