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 972003 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 972003 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 65636row limit hardcoded 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 XL2007compatible 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 972003 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 