Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Logic If formula - multi-parameters can not get to work?? | Excel Worksheet Functions | |||
SUBSTITUTE formula -- variable spacing between parameters?? | Excel Worksheet Functions | |||
Parameters for an excel formula | Excel Discussion (Misc queries) | |||
formula to check balances within parameters | Excel Worksheet Functions | |||
MID formula not extracting what I want. | Excel Discussion (Misc queries) |