Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 UDFs result in #REF! errors
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 UDFs result in #REF! errors
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.). |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 UDFs result in #REF! errors
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. -- Regards Roger Govier wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 UDFs result in #REF! errors
Too many columns to remember <vbg.
Roger Govier wrote: 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. -- Regards Roger Govier wrote in message ... 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 UDFs result in #REF! errors
Ah..obvious now (upon being enlightened!)
Many thanks all for the clarification, much appreciated! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 UDFs result in #REF! errors
In Excel 2007 VOL1 is a cell reference..........
-- Kind regards, Niek Otten Microsoft MVP - Excel wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 UDFs result in #REF! errors
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. -- Gary''s Student - gsnu2007k " wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Errors in Excel 2007 Calculation | Excel Discussion (Misc queries) | |||
excel 2007 getting errors with mround | Excel Worksheet Functions | |||
print errors excel 2007 | Excel Discussion (Misc queries) | |||
Excel 2007 charting errors with pivot tables | Charts and Charting in Excel | |||
Returning custom errors from UDFs | Excel Programming |