Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex strings break 'Evaluate' UDF
Hi,
I'm struggling to get a complex formula to be calculated by the Evaluate method, called from a UDF. Something simple, like "=sum(1,2,3)" works fine, but my complex formula returns a #VALUE error. I have a formula that calculates to the following string: =IF(OR($A12="",ProductName=""),"",SUMIF(BCs!$T$2:$ T$178,'Product Detail'!$A12&"*",INDIRECT("BCs!r2c"&MATCH(L$11,IND IRECT("bcs! r2c"&23+VLookupCode&":r2c256",0),0)+22+VLookupCode &":r178c"&MATCH(L $11,INDIRECT("BCs!r2c"&23+VLookupCode&":r2c256",0) ,0)+22+VLookupCode, 0))) When plugged into a cell, the formula works fine. When I use my UDF (eval(etc.)), it fails. I've tried putting the string directly into VBA with extra quotes as necessary--I can tell by using the 'Watch' window that it's entered exactly the same as I'm entering it in the spreadsheet. I had a similar problem yesterday on another Evaluate and was able to solve it by doing a more 'local' Evaluate (something like Range("A1").Application.Evaluate(Range("A1").Value )... worked, while just Evaluate(Range("A1").Value) did not). I'm using Excel 2003 on WinXP. Thanks in advance for any ideas! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex strings break 'Evaluate' UDF
I think it's because you've gone over 256 characters.
"Nathan" wrote: Hi, I'm struggling to get a complex formula to be calculated by the Evaluate method, called from a UDF. Something simple, like "=sum(1,2,3)" works fine, but my complex formula returns a #VALUE error. I have a formula that calculates to the following string: =IF(OR($A12="",ProductName=""),"",SUMIF(BCs!$T$2:$ T$178,'Product Detail'!$A12&"*",INDIRECT("BCs!r2c"&MATCH(L$11,IND IRECT("bcs! r2c"&23+VLookupCode&":r2c256",0),0)+22+VLookupCode &":r178c"&MATCH(L $11,INDIRECT("BCs!r2c"&23+VLookupCode&":r2c256",0) ,0)+22+VLookupCode, 0))) When plugged into a cell, the formula works fine. When I use my UDF (eval(etc.)), it fails. I've tried putting the string directly into VBA with extra quotes as necessary--I can tell by using the 'Watch' window that it's entered exactly the same as I'm entering it in the spreadsheet. I had a similar problem yesterday on another Evaluate and was able to solve it by doing a more 'local' Evaluate (something like Range("A1").Application.Evaluate(Range("A1").Value )... worked, while just Evaluate(Range("A1").Value) did not). I'm using Excel 2003 on WinXP. Thanks in advance for any ideas! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex strings break 'Evaluate' UDF
I actually had already shortened the formula below 256 characters...
posted the long version, sorry. No better results with this formula, although it still works fine as a normal formula in Excel: IF(OR($A12="",ProductName=""),"",SUMIF(BCs!$T$2:$T $178,'Product Detail'!$A12&"*",INDIRECT("BCs!r2c"&MATCH(L$11,IND IRECT("bcs! r2c"&30&":r2c256",0),0)+29&":r178c"&MATCH(L$11,IND IRECT("bcs! r2c"&30&":r2c256",0),0)+29,0))) 217 characters. On Oct 23, 11:18 am, Smallweed wrote: I think it's because you've gone over 256 characters. "Nathan" wrote: Hi, I'm struggling to get a complex formula to be calculated by the Evaluate method, called from a UDF. Something simple, like "=sum(1,2,3)" works fine, but my complex formula returns a #VALUE error. I have a formula that calculates to the following string: =IF(OR($A12="",ProductName=""),"",SUMIF(BCs!$T$2:$ T$178,'Product Detail'!$A12&"*",INDIRECT("BCs!r2c"&MATCH(L$11,IND IRECT("bcs! r2c"&23+VLookupCode&":r2c256",0),0)+22+VLookupCode &":r178c"&MATCH(L $11,INDIRECT("BCs!r2c"&23+VLookupCode&":r2c256",0) ,0)+22+VLookupCode, 0))) When plugged into a cell, the formula works fine. When I use my UDF (eval(etc.)), it fails. I've tried putting the string directly into VBA with extra quotes as necessary--I can tell by using the 'Watch' window that it's entered exactly the same as I'm entering it in the spreadsheet. I had a similar problem yesterday on another Evaluate and was able to solve it by doing a more 'local' Evaluate (something like Range("A1").Application.Evaluate(Range("A1").Value )... worked, while just Evaluate(Range("A1").Value) did not). I'm using Excel 2003 on WinXP. Thanks in advance for any ideas!- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex strings break 'Evaluate' UDF
Breaking it into a piecemeal equation, although Indirect in general
does fine with Evaluate, doing a match on an indirect range causes an error. I.e. MATCH(L$11,INDIRECT("BCs!r2c"&30&":r2c256",0),0) can't be evaluated (occasionally seems to throw off a wrong answer rather than a #VALUE error), but MATCH(L$11,BCs!AD2:IV2,0) works fine in the eval function. Any ideas on how to work around this problem? I can't avoid the indirects. Thanks again! On Oct 23, 11:29 am, Nathan wrote: I actually had already shortened the formula below 256 characters... posted the long version, sorry. No better results with this formula, although it still works fine as a normal formula in Excel: IF(OR($A12="",ProductName=""),"",SUMIF(BCs!$T$2:$T $178,'Product Detail'!$A12&"*",INDIRECT("BCs!r2c"&MATCH(L$11,IND IRECT("bcs! r2c"&30&":r2c256",0),0)+29&":r178c"&MATCH(L$11,IND IRECT("bcs! r2c"&30&":r2c256",0),0)+29,0))) 217 characters. On Oct 23, 11:18 am, Smallweed wrote: I think it's because you've gone over 256 characters. "Nathan" wrote: Hi, I'm struggling to get a complex formula to be calculated by the Evaluate method, called from a UDF. Something simple, like "=sum(1,2,3)" works fine, but my complex formula returns a #VALUE error. I have a formula that calculates to the following string: =IF(OR($A12="",ProductName=""),"",SUMIF(BCs!$T$2:$ T$178,'Product Detail'!$A12&"*",INDIRECT("BCs!r2c"&MATCH(L$11,IND IRECT("bcs! r2c"&23+VLookupCode&":r2c256",0),0)+22+VLookupCode &":r178c"&MATCH(L $11,INDIRECT("BCs!r2c"&23+VLookupCode&":r2c256",0) ,0)+22+VLookupCode, 0))) When plugged into a cell, the formula works fine. When I use my UDF (eval(etc.)), it fails. I've tried putting the string directly into VBA with extra quotes as necessary--I can tell by using the 'Watch' window that it's entered exactly the same as I'm entering it in the spreadsheet. I had a similar problem yesterday on another Evaluate and was able to solve it by doing a more 'local' Evaluate (something like Range("A1").Application.Evaluate(Range("A1").Value )... worked, while just Evaluate(Range("A1").Value) did not). I'm using Excel 2003 on WinXP. Thanks in advance for any ideas!- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex strings break 'Evaluate' UDF
Not sure exactly why this fails but here are some possibilities:
When you use Application.Evaluate any unqualified range references (like $A12) defaults to whatever is the active sheet. Its better to either use fully qualified references (like Sheet1!$A12) or use Worksheet("Sheet1").Evaluate to make all unqualified references refer to sheet 1. There are some other limitations of EVALUATE which you should be aware of: see http://www.decisionmodels.com/calcsecretsh.htm Secondly you are mixing R1C1 and A1 references in one formula which I would have thought was not a good thing. Thirdly the 256 character limit usually applies to the formula after all references have been fully qualified by sheet names and the formula has been internally converted to R1C1. Not sure if this applies to Evaluate ... Charles ______________________ Decision Models FastExcel 2.3 now available Name Manager 4.0 now available www.DecisionModels.com "Nathan" wrote in message ups.com... I actually had already shortened the formula below 256 characters... posted the long version, sorry. No better results with this formula, although it still works fine as a normal formula in Excel: IF(OR($A12="",ProductName=""),"",SUMIF(BCs!$T$2:$T $178,'Product Detail'!$A12&"*",INDIRECT("BCs!r2c"&MATCH(L$11,IND IRECT("bcs! r2c"&30&":r2c256",0),0)+29&":r178c"&MATCH(L$11,IND IRECT("bcs! r2c"&30&":r2c256",0),0)+29,0))) 217 characters. On Oct 23, 11:18 am, Smallweed wrote: I think it's because you've gone over 256 characters. "Nathan" wrote: Hi, I'm struggling to get a complex formula to be calculated by the Evaluate method, called from a UDF. Something simple, like "=sum(1,2,3)" works fine, but my complex formula returns a #VALUE error. I have a formula that calculates to the following string: =IF(OR($A12="",ProductName=""),"",SUMIF(BCs!$T$2:$ T$178,'Product Detail'!$A12&"*",INDIRECT("BCs!r2c"&MATCH(L$11,IND IRECT("bcs! r2c"&23+VLookupCode&":r2c256",0),0)+22+VLookupCode &":r178c"&MATCH(L $11,INDIRECT("BCs!r2c"&23+VLookupCode&":r2c256",0) ,0)+22+VLookupCode, 0))) When plugged into a cell, the formula works fine. When I use my UDF (eval(etc.)), it fails. I've tried putting the string directly into VBA with extra quotes as necessary--I can tell by using the 'Watch' window that it's entered exactly the same as I'm entering it in the spreadsheet. I had a similar problem yesterday on another Evaluate and was able to solve it by doing a more 'local' Evaluate (something like Range("A1").Application.Evaluate(Range("A1").Value )... worked, while just Evaluate(Range("A1").Value) did not). I'm using Excel 2003 on WinXP. Thanks in advance for any ideas!- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex strings break 'Evaluate' UDF
Charles,
Thanks for the response! I do want those references to be to the active worksheet. However, even full references don't correct the issue. Also, I don't think I'm up against the character limitation, since the adjusted formula is fairly small. The R1C1 piece is a feature of the indirect formula and helps make it easier to build these particular formula strings, and it works fine in Excel. I also tried changing the formula to all A1 referencing (substitute AD2:IV2 for the R1C1 piece, and get rid of the 0 on the indirect formula), which didn't accomplish anything. I thought that this must be a limitation of evaluate, except that I got something similar working on another worksheet. I have a data validation box with the function: =INDIRECT("BCs!O" & VLOOKUP(A4,INDIRECT("BCs!Div"),3,0)+1 & ":O" & VLOOKUP(A4,INDIRECT("BCs!Div"),4,0)) I can evaluate that in Excel like this: validationList = Range("MSelector").Validation.Application.Evaluate (Range("MSelector").Validation.Formula1) I tried using range names on my function to make it work, but that also made no difference. I also tried cycling through it as an array to see if that was the difference, but that also didn't help. I don't get it! On Oct 23, 12:21 pm, "Charles Williams" wrote: Not sure exactly why this fails but here are some possibilities: When you use Application.Evaluate any unqualified range references (like $A12) defaults to whatever is the active sheet. Its better to either use fully qualified references (like Sheet1!$A12) or use Worksheet("Sheet1").Evaluate to make all unqualified references refer to sheet 1. There are some other limitations of EVALUATE which you should be aware of: seehttp://www.decisionmodels.com/calcsecretsh.htm Secondly you are mixing R1C1 and A1 references in one formula which I would have thought was not a good thing. Thirdly the 256 character limit usually applies to the formula after all references have been fully qualified by sheet names and the formula has been internally converted to R1C1. Not sure if this applies to Evaluate ... Charles ______________________ Decision Models FastExcel 2.3 now available Name Manager 4.0 now availablewww.DecisionModels.com "Nathan" wrote in message ups.com... I actually had already shortened the formula below 256 characters... posted the long version, sorry. No better results with this formula, although it still works fine as a normal formula in Excel: IF(OR($A12="",ProductName=""),"",SUMIF(BCs!$T$2:$T $178,'Product Detail'!$A12&"*",INDIRECT("BCs!r2c"&MATCH(L$11,IND IRECT("bcs! r2c"&30&":r2c256",0),0)+29&":r178c"&MATCH(L$11,IND IRECT("bcs! r2c"&30&":r2c256",0),0)+29,0))) 217 characters. On Oct 23, 11:18 am, Smallweed wrote: I think it's because you've gone over 256 characters. "Nathan" wrote: Hi, I'm struggling to get a complex formula to be calculated by the Evaluate method, called from a UDF. Something simple, like "=sum(1,2,3)" works fine, but my complex formula returns a #VALUE error. I have a formula that calculates to the following string: =IF(OR($A12="",ProductName=""),"",SUMIF(BCs!$T$2:$ T$178,'Product Detail'!$A12&"*",INDIRECT("BCs!r2c"&MATCH(L$11,IND IRECT("bcs! r2c"&23+VLookupCode&":r2c256",0),0)+22+VLookupCode &":r178c"&MATCH(L $11,INDIRECT("BCs!r2c"&23+VLookupCode&":r2c256",0) ,0)+22+VLookupCode, 0))) When plugged into a cell, the formula works fine. When I use my UDF (eval(etc.)), it fails. I've tried putting the string directly into VBA with extra quotes as necessary--I can tell by using the 'Watch' window that it's entered exactly the same as I'm entering it in the spreadsheet. I had a similar problem yesterday on another Evaluate and was able to solve it by doing a more 'local' Evaluate (something like Range("A1").Application.Evaluate(Range("A1").Value )... worked, while just Evaluate(Range("A1").Value) did not). I'm using Excel 2003 on WinXP. Thanks in advance for any ideas!- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Evaluate text strings as a formula | Excel Programming | |||
Finding text strings in complex situations | Excel Discussion (Misc queries) | |||
Break up strings into separate cells | Excel Programming | |||
Combining Text Strings - Complex | Excel Worksheet Functions | |||
Complex Text Strings|Variables | Excel Programming |