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!