How to get formula type in VBA
 

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

How to get formula type in VBA
 

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" wrote in message
...
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

How to get formula type in VBA
 

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 wrote in message
...
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" wrote in message
...
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

How to get formula type in VBA
 

Possibly understanding the formula.

--
Regards,
Tom Ogilvy

"R.VENKATARAMAN" wrote in message
...
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 wrote in message
...
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" wrote in message
...
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

How to get formula type in VBA
 

thanks. shall keep this in mind


Tom Ogilvy wrote in message
...
Possibly understanding the formula.

--
Regards,
Tom Ogilvy

"R.VENKATARAMAN" wrote in message
...
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 wrote in message
...
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" wrote in message
...
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