Prev: List Box + Sharepoint Navigation
Next: Hide subsequent rows based on pull down list choice - macro
From: Greg Lovern on 11 May 2010 16:02
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
From: Tom Hutchins on 11 May 2010 17:09
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" wrote:

> 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
> .
>
From: Greg Lovern on 11 May 2010 17:35
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




On May 11, 2:09 pm, Tom Hutchins
<TomHutch...(a)discussions.microsoft.com> wrote:
> 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" wrote:
> > 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
> > .- Hide quoted text -
>
> - Show quoted text -

From: Greg Lovern on 11 May 2010 17:54
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


On May 11, 2:35 pm, Greg Lovern <gr...(a)gregl.net> wrote:
> 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
>
> On May 11, 2:09 pm, Tom Hutchins
>
>
>
> <TomHutch...(a)discussions.microsoft.com> wrote:
> > 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" wrote:
> > > 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
> > > .- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

From: Tom Hutchins on 11 May 2010 22:36
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

"Greg Lovern" wrote:

> 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
>
>
> On May 11, 2:35 pm, Greg Lovern <gr...(a)gregl.net> wrote:
> > 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
> >
> > On May 11, 2:09 pm, Tom Hutchins
> >
> >
> >
> > <TomHutch...(a)discussions.microsoft.com> wrote:
> > > 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" wrote:
> > > > 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
> > > > .- Hide quoted text -
> >
> > > - Show quoted text -- Hide quoted text -
> >
> > - Show quoted text -
>
> .
>
 |  Next  |  Last
Pages: 1 2 3
Prev: List Box + Sharepoint Navigation
Next: Hide subsequent rows based on pull down list choice - macro