Excel 2007: UDF can't reference row below 65536

[Greg Lovern]

In Excel 2007 (I'm on SP2), if I enter this formula:

=SUM(A65537)

It works fine. But if I enter this formula:

=MYUDF(A65537)

Excel turns it into this:

=MYUDF(#REF!)


That isn't the UDF's fault' Excel is doing that before the UDF has the
opportunity to do anything at all. Is there a workaround or fix?


Thanks,

Greg

 

[Tom Hutchins]

It sounds like you are working in Compatibility Mode. How many rows does your
workbook have on a sheet? I created a simple MyUDF function, and it accepts
any range up to 1048576 unless the workbook calling it was saved in an
earlier Excel format (.xls instead of .xlsx).

Hope this helps,

Hutch

 

[Greg Lovern]

Hi Tom,

I'm in a .xlsx saved out from Excel 2007 as a standard Excel .xlsx
file. The last cell is XFD1048576.

I've been working with it more and found that the problem was not as
simple as I'd thought.

The problem seems to require that the UDF have two arguments (both
required args). Then, the problem happens even if only one arg is
passed, which is invalid for the UDF but even so, the reference in the
formula shouldn't change to #REF!.

So to get a simple repro, change the UDF to require two args, then
reference cells below row 65536 in those args:

MYUDF(A65537,A65537)


Result: Excel changes the formula to:

MYUDF(#REF!,A65537)


Any thoughts?


BTW I've been working with UDFs in an .xla that has stubs that call an
ActiveX DLL. I'll see if I get the same problem with a simpler
scenario. Come to think of it, the .xla is in Excel 97-2003 format
(for compatibility with those Excel versions).


Thanks,

Greg

 

[Greg Lovern]

I've found that if I go into the .xla and comment out the call to the
DLL, and just add the two references together, it works fine. So I'm
going to have to step through the DLL next, which I don't have with me
at the moment.

No matter what the DLL might be doing wrong, Excel shouldn't change
the formula like that. Any error should be returned as the return
value, not as a change to the formula itself. Are there any
circumstances under which Excel is expected to change the formula when
just calculating the formula?


Thanks,

Greg

 

[Tom Hutchins]

You hadn't mentioned any .DLL or .xla files. I think that one (or both) of
them must be based on an Excel 2003 or earlier format. Is the 65636-row limit
hard-coded in the DLL? Even though your current workbook is in Excel 2007
format, the UDF (and it sounds like the .DLL is the culprit) still has the
limitations of XL2003. The .DLL and .xla files need to be rewritten/recreated
in an XL2007-compatible format.

Hutch

 

[Ron Rosenfeld]

BTW I've been working with UDFs in an .xla that has stubs that call an
ActiveX DLL. I'll see if I get the same problem with a simpler
scenario. Come to think of it, the .xla is in Excel 97-2003 format
(for compatibility with those Excel versions).

Try it in an .xlam, which is the Excel 2007 version of the .xla

FWIW, your problem does not reproduce in my Excel 2007.

--ron

 

[Greg Lovern]

When I got home last night, I tried it again, to work on a workaround
until I have a chance to find and fix the bug. To my surprise, it did
NOT repro on my home computer! When I got back here this morning, I
verified that it does repro here.

On both computers, I'm running Excel 2007 SP2, and I was using the
same version of my add-in, with the xla and dll both installed by the
same release package. To repro the problem, in both cases I was using
the same sample .xlsx file.

I have no idea why it works on one computer and not the other.


Greg

 

[Greg Lovern]

Sorry, I forgot to add:

If the problem only occured on one computer, I'd be inclined to think
it was a problem with the Excel installation on that computer. But the
problem exists on at least two computers -- the problem was originally
reported to me by a customer, and it was his sample .xlsx file I used
on both of my computers to repro.

So the problem happens on his computer and one of mine, but not on
another computer of mine.

Greg

 

[Ron Rosenfeld]

When I got home last night, I tried it again, to work on a workaround
until I have a chance to find and fix the bug. To my surprise, it did
NOT repro on my home computer! When I got back here this morning, I
verified that it does repro here.

On both computers, I'm running Excel 2007 SP2, and I was using the
same version of my add-in, with the xla and dll both installed by the
same release package. To repro the problem, in both cases I was using
the same sample .xlsx file.

I have no idea why it works on one computer and not the other.


Greg

Try saving the file as an .xlsm file.
--ron

 

[Ron Rosenfeld]

Sorry, I forgot to add:

If the problem only occured on one computer, I'd be inclined to think
it was a problem with the Excel installation on that computer. But the
problem exists on at least two computers -- the problem was originally
reported to me by a customer, and it was his sample .xlsx file I used
on both of my computers to repro.

So the problem happens on his computer and one of mine, but not on
another computer of mine.

Greg

I think it has to do with compatibility mode incompatibilities.
--ron

 

[Greg Lovern]

It stopped reproducing here after I restarted Excel. I tried several
different ways but haven't been able to get the problem to happen
again. I'll pick it up again if I can get a repro. Also, when I can
get the time I'll step through the DLL function to see if I can find
the problem. But the .xla is just passing values to the DLL, not range
references.

Greg

 

[Wilfred Krol]

I encountered the same problem, and have found a solution for it.

I am maintaining an Excel 2003 add-in (.xla) that contains some UDFs. The add-in is supposed to be able to be used in newer versions of Office as well. However, a customer reported problems when using this add-in from Excel 2007 and 2010: If any of these UDFs is invoked with one or more references to cells outside of the Excel 2003 limits (i.e. 65536 rows and 256 columns) as parameters, these references are sometimes considered ‘invalid’ by Excel, i.e. they are replaced by #REF!, and as a result, the cell’s value is displayed as #VALUE!.

I sometimes succeeded in reproducing this problem, and sometimes I didn’t, until I discovered the following method to reproduce it consistently:

1. In Excel 2007 or 2010 (with the .xla installed), create a fresh workbook that invokes one or more of the UDFs in the .xla. (At this point, it doesn’t matter what values you pass as parameters. Even references to cells outside of the Excel 2003 limits are OK.)

2. Save the workbook (as .xlsx), and close Excel.

3. Reopen the workbook. From now on, if you add an invocation of one of the UDFs with a reference to a cell outside of the Excel 2003 limits as a parameter, the reference is immediately changed to #REF!. (Note: If you added such invocations before reopening the workbook, these cell references were, and still are OK.)

This looks like a bug in Excel 2007/2010: The presence of an Excel 2003 add-in seems to put Excel into a ‘Partial Compatibility Mode’.

The solution is to turn the add-in into an Excel 2007 or 2010 add-in (.xlam). This can be done simply by saving it as such. (This can be done even from Excel 2003, but you cannot use it from that version of Excel).

The sad conclusion is that it apparently is not possible to have a single version of the add-in for Excel 2003 and all newer versions.

Wilfred.