Hi
How can I write an array formula in VBA? I know how to put an array formula
into a worksheet but I'd like to get the result without having to do that.

More specifically I'd like to get the result from the following formula and
assign it to a variable without writing it to a cell

{=max(len(a1:a1000))}

TIA
;-)

evaluate("=MAX(LEN(A1:A1000))")

--

HTH

RP

"Loomah" <bellm AT globalnet dot co dot uk wrote in message
...
Hi
How can I write an array formula in VBA? I know how to put an array
formula
into a worksheet but I'd like to get the result without having to do that.

More specifically I'd like to get the result from the following formula
and
assign it to a variable without writing it to a cell

{=max(len(a1:a1000))}

TIA
;-)

Just a caveat: In the past, I found evaluating array formulas in VBA to be
"flaky". One day the formula will work, the next day it doesn't, though there
have been no changes to the code in the interim. I haven't tried them in the
last couple of versions, so the problem may have been remedied.

On Wed, 29 Sep 2004 10:32:12 +0100, "Loomah" <bellm AT globalnet dot co dot
uk wrote:

Hi
How can I write an array formula in VBA? I know how to put an array formula
into a worksheet but I'd like to get the result without having to do that.

More specifically I'd like to get the result from the following formula and
assign it to a variable without writing it to a cell

{=max(len(a1:a1000))}

TIA
;-)