Excel 2007 UDFs result in #REF! errors

[chris_j_adams]

I get strange results when I try to create UDFs in Excel 2007 (using
Vista).

For example, if I name the function any of the following, I get a
#REF! error.
add1
sum1
vol1

However, I don't get such an error if I use
addone
sumone
volone

I never noticed this before in earlier versions of Excel and it was a
pain until I worked out what appeared to be going on.

Any comments about this behaviour (and anything else that may be
associated)? For example, I presume this has been done to preserve
key names, which suggests any key name with just an integer after it
could be suspect.

Thanks,
Chris

 

[chris_j_adams]

Just a couple of other points:

1. This behaviour is observed when calling the function from the
spreadsheet. It seems to work fine when the function is called from a
sub.

2. Reason I noticed this behaviour was an old spreadsheet was no
longer working (it was using vol1, vol2 etc.).

 

[Roger Govier]

Hi Chris

With XL2007 there are 16000+ columns, with the last column being XFD
All three letter combinations from AAA up to XFD, with a 1 following, will
be a cell reference, and therefore not allowed for a UDF name.

Try using Add_1, Sum_1 etc for your names instead.

 

[Niek Otten]

In Excel 2007 VOL1 is a cell reference..........

 

[Gary''s Student]

Excel thinks your UDF is actually a cell reference!?!

For example:

Function a1(r As Range) As String
a1 = "X"
End Function

will fail even in ancient version of Excel.

Avoid assigning a name to a function that Excel can mis-interpret.

 

[Dave Peterson]

Do you have any workbook/worksheet names that look like add1, sum1, vol1?

How about the module names for those functions? You didn't make them the same
name did you? If you did, rename the modules (ModAdd1, ModSum1, ...).

If you start a new workbook from scratch, can you create the UDFs with those
names?

 

[Dave Peterson]

Too many columns to remember <vbg>.

Hi Chris

With XL2007 there are 16000+ columns, with the last column being XFD
All three letter combinations from AAA up to XFD, with a 1 following, will
be a cell reference, and therefore not allowed for a UDF name.

Try using Add_1, Sum_1 etc for your names instead.

 

[chris_j_adams]

Ah..obvious now (upon being enlightened!)

Many thanks all for the clarification, much appreciated!