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 |
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.). |
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 |
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 |
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 |
Excel 2007 UDFs result in #REF! errors
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? 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 -- Dave Peterson |
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 |
Excel 2007 UDFs result in #REF! errors
Ah..obvious now (upon being enlightened!)
Many thanks all for the clarification, much appreciated! |
All times are GMT +1. The time now is 12:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com