Weird Little Traps: Excel Going Wrong from Day 1…

While our VBA blog series takes a little hiatus (it’s coming back soon, we promise!), we thought we’d share with you a problem that raises its ugly little head from time to time. Don’t fall foul of it!

As many of you are aware, range names are created by selecting your range of cells and then either typing directly into the Name Box (circled, below),

or else by using the keyboard shortcut CTRL + F3, and then clicking on the ‘New’ button in the ‘Name Manager’ dialog box), viz.

Since Excel 2007 came about, many users have fallen into a trap that previously harmless range names seemed to invoke the wrath of Excel:

Believe it or not, Day1 is a common range name used in spreadsheets. While Day_1, Day01 and Days1 all seem fine, Excel proclaimed that Day1 was a reserved name. And guess what? Indeed, it is!

Since Excel 2007, the spreadsheet has grown to 16,384 columns (A to XFD) and 1,048,576 rows. Since range names cannot be called potential cell references (can you imagine how much fun that would be – we’d call cell A1 ‘F5’ and so on), this means many three-letter range names are not available. DAY1 is in fact a cell reference:

So be careful! At least we now have the answer to the age-old question, CAR54 where are you?

The VBA blog series returns soon. We’ve just got stick in an iterative loop!