How to get variable from a formula

[pontitt]

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

 

[Gary''s Student]

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

 

[Dave Peterson]

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]

Tricky, good as base approach, thanks

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

 

[Rick Rothstein \(MVP - VB\)]

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