extracting parameters from formula
 

Hi all

Is there an easy way to extract the parameter values used
in a function, e.g.
if a cell contains a formula "=sum(a1,a2,a3)", I'd like to
get an array with the values (a,b,c) ( =values of a1, a2,
a3) being used in the function. The function wizard in
excel takes the function apart in this way, but it looks
quite complex to try and do something similar. I am
tempted to take the formula apart as a string, going
for "(" and ",", but it doesn't look easy. Maybe there's a
ready made procedure for this?

Thanks for your input

extracting parameters from formula
 

Claude

The Precedents collection might help you here. You can loop through the
Precedents of cell and it will return a collection of Range objects from
which you can get the values. It doesn't return all the arguments, unless
they happen to be cell references, so it would work for your example, but
not

=Sum(A1,10,B2)

That would only return A1 and B2, not the scalar value 10. Here's an
example of how to use Precedents.

http://www.dicks-blog.com/excel/2004...recedents.html

If you need all arguments, not just cell references, then I think parsing
the Formula property is the only way to go. But it won't be easy.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Claude" wrote in message
...
Hi all

Is there an easy way to extract the parameter values used
in a function, e.g.
if a cell contains a formula "=sum(a1,a2,a3)", I'd like to
get an array with the values (a,b,c) ( =values of a1, a2,
a3) being used in the function. The function wizard in
excel takes the function apart in this way, but it looks
quite complex to try and do something similar. I am
tempted to take the formula apart as a string, going
for "(" and ",", but it doesn't look easy. Maybe there's a
ready made procedure for this?

Thanks for your input