How to get formula type in VBA

[Charlie]

Is there a property that returns whether or not the formula in a cell is an
array formula?

I can insert a formula or an array formula into a cell no problem, but
regardless of which way I insert it both the .Formula and the .FormulaArray
properties return identical strings. I would've thought that one or the
other would return empty.

The following Debug.Print statements show identical strings whether I insert
the formula string into .Formula or .FormulaArray

Range("C1").Formula = "=SUM(IF(A1:A5=1,B1:B5))"
Debug.Print Range("C1").FormulaArray
Debug.Print Range("C1").Formula

Range("C1").FormulaArray = "=SUM(IF(A1:A5=1,B1:B5))"
Debug.Print Range("C1").FormulaArray
Debug.Print Range("C1").Formula

Any ideas?
TIA

[Tom Ogilvy]

ActiveCell.HasArray is true if it is an array formula.

from help on HasArray

True if the specified cell is part of an array formula. Read-only Variant.

--
Regards,
Tom Ogilvy


"Charlie" <[email protected]> wrote in message
news:[email protected]...
> Is there a property that returns whether or not the formula in a cell is
an
> array formula?
>
> I can insert a formula or an array formula into a cell no problem, but
> regardless of which way I insert it both the .Formula and the
..FormulaArray
> properties return identical strings. I would've thought that one or the
> other would return empty.
>
> The following Debug.Print statements show identical strings whether I
insert
> the formula string into .Formula or .FormulaArray
>
> Range("C1").Formula = "=SUM(IF(A1:A5=1,B1:B5))"
> Debug.Print Range("C1").FormulaArray
> Debug.Print Range("C1").Formula
>
> Range("C1").FormulaArray = "=SUM(IF(A1:A5=1,B1:B5))"
> Debug.Print Range("C1").FormulaArray
> Debug.Print Range("C1").Formula
>
> Any ideas?
> TIA

[R.VENKATARAMAN]

I have anoher doubt . I get either from these newsgroups or web a fomrula.
the author ofthe formula forgot to indicate whether the formula is a
standard formula or array formula. is there any method to test the type of
formula except by actually hitting <enter> and if it fails click F2 and hit
control+shift+enter
thanks.




Tom Ogilvy <[email protected]> wrote in message
news:[email protected]...
> ActiveCell.HasArray is true if it is an array formula.
>
> from help on HasArray
>
> True if the specified cell is part of an array formula. Read-only Variant.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Charlie" <[email protected]> wrote in message
> news:[email protected]...
> > Is there a property that returns whether or not the formula in a cell is
> an
> > array formula?
> >
> > I can insert a formula or an array formula into a cell no problem, but
> > regardless of which way I insert it both the .Formula and the
> .FormulaArray
> > properties return identical strings. I would've thought that one or the
> > other would return empty.
> >
> > The following Debug.Print statements show identical strings whether I
> insert
> > the formula string into .Formula or .FormulaArray
> >
> > Range("C1").Formula = "=SUM(IF(A1:A5=1,B1:B5))"
> > Debug.Print Range("C1").FormulaArray
> > Debug.Print Range("C1").Formula
> >
> > Range("C1").FormulaArray = "=SUM(IF(A1:A5=1,B1:B5))"
> > Debug.Print Range("C1").FormulaArray
> > Debug.Print Range("C1").Formula
> >
> > Any ideas?
> > TIA
>
>

[Tom Ogilvy]

Possibly understanding the formula.

--
Regards,
Tom Ogilvy

"R.VENKATARAMAN" <vram26@vsnl$$$.net> wrote in message
news:%[email protected]...
> I have anoher doubt . I get either from these newsgroups or web a fomrula.
> the author ofthe formula forgot to indicate whether the formula is a
> standard formula or array formula. is there any method to test the type of
> formula except by actually hitting <enter> and if it fails click F2 and
hit
> control+shift+enter
> thanks.
>
>
>
>
> Tom Ogilvy <[email protected]> wrote in message
> news:[email protected]...
> > ActiveCell.HasArray is true if it is an array formula.
> >
> > from help on HasArray
> >
> > True if the specified cell is part of an array formula. Read-only
Variant.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Charlie" <[email protected]> wrote in message
> > news:[email protected]...
> > > Is there a property that returns whether or not the formula in a cell
is
> > an
> > > array formula?
> > >
> > > I can insert a formula or an array formula into a cell no problem, but
> > > regardless of which way I insert it both the .Formula and the
> > .FormulaArray
> > > properties return identical strings. I would've thought that one or
the
> > > other would return empty.
> > >
> > > The following Debug.Print statements show identical strings whether I
> > insert
> > > the formula string into .Formula or .FormulaArray
> > >
> > > Range("C1").Formula = "=SUM(IF(A1:A5=1,B1:B5))"
> > > Debug.Print Range("C1").FormulaArray
> > > Debug.Print Range("C1").Formula
> > >
> > > Range("C1").FormulaArray = "=SUM(IF(A1:A5=1,B1:B5))"
> > > Debug.Print Range("C1").FormulaArray
> > > Debug.Print Range("C1").Formula
> > >
> > > Any ideas?
> > > TIA
> >
> >
>
>

[R.VENKATARAMAN]

thanks. shall keep this in mind


Tom Ogilvy <[email protected]> wrote in message
news:#[email protected]...
> Possibly understanding the formula.
>
> --
> Regards,
> Tom Ogilvy
>
> "R.VENKATARAMAN" <vram26@vsnl$$$.net> wrote in message
> news:%[email protected]...
> > I have anoher doubt . I get either from these newsgroups or web a
fomrula.
> > the author ofthe formula forgot to indicate whether the formula is a
> > standard formula or array formula. is there any method to test the type
of
> > formula except by actually hitting <enter> and if it fails click F2 and
> hit
> > control+shift+enter
> > thanks.
> >
> >
> >
> >
> > Tom Ogilvy <[email protected]> wrote in message
> > news:[email protected]...
> > > ActiveCell.HasArray is true if it is an array formula.
> > >
> > > from help on HasArray
> > >
> > > True if the specified cell is part of an array formula. Read-only
> Variant.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "Charlie" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Is there a property that returns whether or not the formula in a
cell
> is
> > > an
> > > > array formula?
> > > >
> > > > I can insert a formula or an array formula into a cell no problem,
but
> > > > regardless of which way I insert it both the .Formula and the
> > > .FormulaArray
> > > > properties return identical strings. I would've thought that one or
> the
> > > > other would return empty.
> > > >
> > > > The following Debug.Print statements show identical strings whether
I
> > > insert
> > > > the formula string into .Formula or .FormulaArray
> > > >
> > > > Range("C1").Formula = "=SUM(IF(A1:A5=1,B1:B5))"
> > > > Debug.Print Range("C1").FormulaArray
> > > > Debug.Print Range("C1").Formula
> > > >
> > > > Range("C1").FormulaArray = "=SUM(IF(A1:A5=1,B1:B5))"
> > > > Debug.Print Range("C1").FormulaArray
> > > > Debug.Print Range("C1").Formula
> > > >
> > > > Any ideas?
> > > > TIA
> > >
> > >
> >
> >
>
>