HowTo: detect a cell contains an ArrayFormula in vba?

[AC]

Hi

I would like to know how to decide/detect if a cell contains an array
formula.

I found the property .FormulaArray but it seems to return a value for
regular formalas (in fact it returns a value even if the cell contains
just a value and not even a formula)

Take the following dummy example <i made up the array formula on the
fly, may have a mistake>:
A1 = "Hello World", A2 = "=sum(1 + 2)", A3 is blank but has yellow
fill, and A4 = "{sum(if(B1:B10<10,1,0))}"

i would like to be able to detect the fact A4 contains an array
formula, and that all the other cells done.

When I write the code:
msgbox Cells("Ax").formulaArray [where x is the cell number]

I get values returned for A1, A2 and A4, namely "Hello World", "=sum
(1+2)" and "=sum(if(B1:B10<10,1,0))" respectively

I only want A4 to be detected, as it has an array formula.

Maybe there is some other property i have not found?

Excel 2003 SP3 running on xp


Thanks
AndyC

please cc replies to my email if possible

 

[Harlan Grove]

Try the .HasArray property of the Range class.