extracting parameters from formula

[Claude]

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

 

[Dick Kusleika]

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/06/cell_precedents.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.