Complex strings break 'Evaluate' UDF

[Nathan]

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,INDIRECT("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!

 

[Guest]

I think it's because you've gone over 256 characters.

 

[Nathan]

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,INDIRECT("bcs!
r2c"&30&":r2c256",0),0)+29&":r178c"&MATCH(L$11,INDIRECT("bcs!
r2c"&30&":r2c256",0),0)+29,0)))

217 characters.

 

[Nathan]

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!

 

[Charles Williams]

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]

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!