How to get variable from a formula
 

Hi,

I would like to ask, whether it is possible to get information of a variable
in a formula, I mean:

Cell B1 contains formula:

=SPEC("Green","Apple",98),

is that possible somehow to reach the middle parameter of the function, that
is "Apple"?

Is there any VBA function like:

needed = Cells(1,2).Formula.Parameter(2)

Thanks in advance

How to get variable from a formula
 

Sub marine()
dq = Chr(34)
MsgBox (Split(Range("B1").Formula, dq)(1))
End Sub

--
Gary''s Student - gsnu200794


"pontitt" wrote:

Hi,

I would like to ask, whether it is possible to get information of a variable
in a formula, I mean:

Cell B1 contains formula:

=SPEC("Green","Apple",98),

is that possible somehow to reach the middle parameter of the function, that
is "Apple"?

Is there any VBA function like:

needed = Cells(1,2).Formula.Parameter(2)

Thanks in advance

How to get variable from a formula
 

You could user .formula to return the formula string.

Then parse that formula to look between the initial open paren and closing paren
to get the parms that you're passing.

Then you could use split (in xl2k and higher) to dump that in an array
(separated by commas).



pontitt wrote:

Hi,

I would like to ask, whether it is possible to get information of a variable
in a formula, I mean:

Cell B1 contains formula:

=SPEC("Green","Apple",98),

is that possible somehow to reach the middle parameter of the function, that
is "Apple"?

Is there any VBA function like:

needed = Cells(1,2).Formula.Parameter(2)

Thanks in advance

--

Dave Peterson

How to get variable from a formula
 

Tricky, good as base approach, thanks

"Gary''s Student" wrote:

Sub marine()
dq = Chr(34)
MsgBox (Split(Range("B1").Formula, dq)(1))
End Sub

--
Gary''s Student - gsnu200794


"pontitt" wrote:

Hi,

I would like to ask, whether it is possible to get information of a variable
in a formula, I mean:

Cell B1 contains formula:

=SPEC("Green","Apple",98),

is that possible somehow to reach the middle parameter of the function, that
is "Apple"?

Is there any VBA function like:

needed = Cells(1,2).Formula.Parameter(2)

Thanks in advance

How to get variable from a formula
 

I think you meant to type 44 (for a comma), not 34 (for a quote mark), in
your first statement (otherwise you get Green instead of "Apple"); although
you really do not need to use a variable to handle this...

MsgBox Split(Range("C1").Formula, ",")(1)

Rick


"Gary''s Student" wrote in message
...
Sub marine()
dq = Chr(34)
MsgBox (Split(Range("B1").Formula, dq)(1))
End Sub

--
Gary''s Student - gsnu200794


"pontitt" wrote:

Hi,

I would like to ask, whether it is possible to get information of a
variable
in a formula, I mean:

Cell B1 contains formula:

=SPEC("Green","Apple",98),

is that possible somehow to reach the middle parameter of the function,
that
is "Apple"?

Is there any VBA function like:

needed = Cells(1,2).Formula.Parameter(2)

Thanks in advance