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.

GETPIVOTDATA

GETPIVOTDATA is a fabulous little function! Try it out, get used to it, learn its quirks and then use it. Frankly it's about as exciting as worksheet functions get. Check it out for yourself!

You've built a good spreadsheet, maybe drawn a few graphs and then added a handy pivot table summarising all the stuff you need to know. Then you realise you want to pull some numbers out of that pivot table. So you select an empty cell, press the equals sign and then click on the cell in the pivot table that you want to use. What happens? You end up with a formula like this ..

=GETPIVOTDATA("Sales",$A$3,"Month","Jul")

It's strange but people seem scared of this. Even without seeing the spreadsheet you can probably guess that it's looking up sales for the month of July! However people seem to prefer something like this instead:

=B5

..where B5 is the current cell in the pivot table containing sales for July. So why is the long-winded version so much better? Well a few reasons really:

1. Pivot tables are very user-friendly. It's very easy for people to play with them. That means users will reorganise columns and rows, add things and take things away. In fact you can almost guarantee that whatever it was you were originally interested in is going to move. With GETPIVOTDATA that doesn't matter.
2. Pivot tables can be filtered. If I used filters to remove "Jul" from my pivot table in the above example then I would want to be warned that this data wasn't available any more. GETPIVOTDATA does that too with a #REF! error. If your formula is =B5 then whatever is in B5 will be used, whether it's right or not.
3. Then when you amend the filter to put "Jul" back in, how do you know your answer is going to appear in B5 again? What if the user has added some more data? Guess what - GETPIVOTDATA deals with it.
4. You can add multiple conditions in GETPIVOTDATA. I often see people creating all sorts of unique identifiers by tacking things like surname, initials, date of birth, etc together into one "word". Why bother when GETPIVOTDATA makes it so easy to express in a logical way?

Get to know GETPIVOTDATA. It can be a very good friend.

VBA Tip - Screen Updating

Say you've written some VBA code that does various things with a spreadsheet. It does exactly what you want so you're essentially happy! However it may seem a bit slow or it just jumps around a lot on screen while it's running. In short it does the job but it doesn't look very professional.

Let's face it - you really don't want to change your code. It works! You just wish you could hide what was going on so that the user doesn't get distracted (or worse confused) by things jumping around on screen. If the user could switch the screen off while the code ran then that would be great! That's not going to happen but you can do the next best thing. Insert this line at the top of your lovely bit of code:

Application.ScreenUpdating = False

When Excel does something with a spreadsheet it redraws what's shown on screen. This simple line of code prevents that happening. The rest of your code is still doing all the same things as before but Excel doesn't draw any of it. This gives a really nice clean feel to a lot of processes.

At the end of your lovely code just remember to switch it back on again! Excel should do this for you but it's a good habit to get into just in case something doesn't quite go according to plan. (You can really confuse users if you forget!) The command is:

Application.ScreenUpdating = True

There is one added bonus, which you may have already guessed. Because Excel has got less work to do (no redrawing what's on screen) you may actually find that the same bit of code runs faster. Which is always nice!

VBA Tip - DoEvents

Say you're running a macro that's hogging your PC and you're unable to do anything else while it's running. You can't even break into the code to check it's running OK. Throw in "DoEvents" every now and then and the code will briefly pause while Excel checks what else is going on, e.g. you pressing the Escape key! These pauses are barely noticeable in real time but can spare the user much aggravation.

E.g. change this:
For i = 1 To 1000
...do stuff
Next

to this:
For i = 1 To 1000
...do stuff
If i Mod 10 = 0 Then DoEvents
Next

In this example the code will briefly pause on every 10th iteration, while Excel checks for things like key presses. The "Mod" condition allows you to control how often it checks. If each iteration is slow then you may want to check more frequently, in which case you would replace 10 with a smaller number and vice versa.

To Declare or Not To Declare

If you're not familiar with declaring variables then please look up "Option Explicit" in VBA help or on your favourite web search engine first. This post is not about what that does - rather it concerns the choice of whether or not to use it.

A popular source of debate between VBA programmers is the choice of whether to force people to declare variables (using the Dim command) or not.  There are many people in both camps.

If you ask me then I will say "Yes, do declare variables".  Why?  Well several reasons really.

1. When you declare a variable, it's an ideal place to add a comment describing what that variable actually is (in plain language).  This is potentially valuable information for the next person who has to look at your code.
2. If you use camel case for your variable names, e.g. MyVariable, then by declaring them the VB editor will automatically capitalise the appropriate letters when you press ENTER at the end of a line of code. This gives you an instant visual check - if something doesn't capitalise correctly then you've done something wrong!
3. If you don't declare variables then typos are your worst enemy. Say you spell a variable name incorrectly somewhere in your code. VBA will automatically think you wanted a new variable, create it and then carry on. Often it will be obvious from the output that something is wrong, but tracking it down in the code can be a pain! And all because of a simple typo.
4. If you ever decide to pick up another programming language then there's a good chance you'll be forced to declare all variables there.  In my opinion it's worth getting into the habit as soon as you can.
5. If you don't declare a variable then any variable you use will have Variant type. Some people will argue that this wastes memory. It does but these days we've got gigabytes of memory so - realistically - we aren't often going to run out of space. However if something should always be an integer then why not force it to be one?  Wouldn't you want to know if you were expecting a whole number but the variable actually contained something silly, like "fish"?!

Counter-arguments tend to focus on ease of use. E.g. why bother declaring when you don't have to? Personally I think it's a good discipline to get used to and once you've got it you'll actually find it's quite useful.

Welcome to .xls

Hi and welcome to my Excel Blog. Clearly there are masses of resources out and about on the interweb for Excel and VBA developers so why one more?  Well I've only recently started doing blogging (of sorts) on our company intranet and people seemed interested. So I figured I'd try a real blog out.

Sign up if you're interested and I'll share some thoughts and ideas with you.  I'm happy to discuss queries so fire away.  I can't promise to know all the answers but I will try :)