I have a cell function:
Function x(ParamArray Params())
with which the user can pass a lot of miscellaneous junk:
=x("abc",123,y(999),z(y(999)), ...)
At VBA runtime, from *another* internal (not worksheet) function, I
want that ParamArray--i.e. "abc", "123", "y(999)", "z(y(999))", etc.
I'll take 'em any way I can get 'em--a string or variant array'd be
fine.
Since .Formula is just a stupid string, I'm currently--<cringe>--using
brute force trying to parse all the commas and parens. I'll tell ya,
it gets hairy with nesting. (And I suppose parsing multiple nesting
levels might require recursion.)
I want something like "Application.Caller.CellFunction.Params()" or
"Cell.FunctionCall.ParamArray()" or whatever.
In other words I want to "simulate" a call to x() to get its
ParamArray.
Or to use a Web analogy, I want to "pull" the ParamArray when it's not
being "pushed" to me.
Is there such an animal?
Thanks a-much.
***
--
Rick (MVP - Excel)
"Jim Luedke" <bao...@my-deja.com> wrote in message
news:40178938-08bc-41ff...@z30g2000yqz.googlegroups.com...
Thanks very much for reply.
I'm not sure how much better I can explain, but here goes:
Cell $A$1's formula is:
=x("abc",123,y(999),z(y(999)))
In VBA routine xx() (which is not a sheet function), what must I do to
get an array whose members are the params of x() living--granted, in
one big, undifferentiated string--in $A$1, namely the 4 strings "abc",
"123", "y(999)" and "z(y(999))"?
Excel knows how to do it because it does do it. It parses all those
commas and parens in a .Formula string to get x's params. It even
supports nesting (when a param is itself a function call).
The only thing is, x()'s ParamArray seems to be available only to code
lucky enough to reside in the tiny, gated community lying between the
statements "Function x(ParamArray Params())" and "End Function".
And, as you clearly say, that code ain't executing during your VBA
runtime. (Unless, as I said, there's some way to do a "calc" or
"simulated execute" on $A$1 to get its params.)
But just because x()'s *own* code isn't executing doesn't mean we
don't need those params elsewhere, at times.
So, does Excel publish its param-parsing intelligence?
It ought to. Because otherwise we poor programmers have to reverse-
engineer it.
Thanks.
***
My example was a bad one because it didn't highlight the difficulty of
parsing a formula string.
In fact in my example x()'s params were easily parsed. Just strip off
the leading "=" and outer parens, locate the commas, and you're done.
I.e.:
=x("abc",123,y(999),z(y(999)))
yields:
"abc"
"123"
"y(999)"
"z(y(999))"
The difficulty lies in **nested calls of functions with 2 or more
params**.
Using the same logic on this string:
=x("abc",123,y(999,"xyz"),z(101,y(999,"xyz")))
results in this mess:
"abc"
"123"
"y(999
"xyz)"
"z(101"
"y(999"
"xyz))"
or some such thing.
So it sher would be a great thing if Excel could do this for us.
***
Function GetXFunctionsParamArray(R As Range) As String
GetXFunctionsParamArray = Mid(R.Formula, 4, Len(R.Formula) - 4)
End Function
Note that the code is specific to the X function... it is not a general
solution for any function (namely because that function could be embedded in
other functions and would be difficult to locate.
--
Rick (MVP - Excel)
"Jim Luedke" <bao...@my-deja.com> wrote in message
news:af3d9660-509f-4a89...@37g2000yqm.googlegroups.com...
See for instance
http://ewbi.blogs.com/develops/2007/03/excel_formula_p.html
http://vangelder.orconhosting.net.nz/
I guess you already know that parameters involving expressions are already
evaluated when they are passed in the Paramarray to the calling function ...
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com
"Jim Luedke" <bao...@my-deja.com> wrote in message
news:c1bd22af-ac54-44c1...@e12g2000yqi.googlegroups.com...
Thanks very much for reply.
The more I think about it, the more I realize I prob. shouldn't have
gotten involved in what values come alive at what time; and might
instead have titled this thread, "Can Excel parse a function's param
string for you?"
Anyway, it's now moot. Who said necessity is the mother of invention?
(Frank Zappa?)
Because I've just written the code to parse a function's param string.
It wasn't easy, but it wasn't as difficult as I'd feared either. You
need about a half-dozen simple subroutines. No recursion seems to be
needed.
Unless I'm mistaken, there are 3 basic cases you have to deal with:
1. String w/out parens, and/or comma-delimited params only, a piece of
cake: abc,def,ghi
2. String with paren-delimited params but no CDP's, also pretty easy
(altho' your initial cut *cannot* of course be by commas. You must
locate the first left paren, then call find its balancing right paren,
and go from there): abc(def(ghi,jkl)),mno(pqr)
3. String with both paren- and comma-delimited params--not as easy,
but again, not that much more difficult than 2. All you do is, having
located the first left paren, which already did, you also see if it is
preceded by a comma. If so, then you know you've got CDP's preceding
your PDP's: abc,def,ghi(jkl(mno)),pqr
I will post it in this group as soon as it's tested a bit; hopefully
in a day or two.
Thanks again.
***
abc,def,ghi(jkl(mno,{3,4,5;6,7,8},xyz)),pqr
and possibly multi-area ranges (I would probably ignore these since they
don't work properly as function parameters in most Excel versions).
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com
"Jim Luedke" <bao...@my-deja.com> wrote in message
news:fd7168bb-ec3d-475a...@l13g2000yqb.googlegroups.com...
OK, I just submitted into this group code to parse a function's param
string into its params.
Thanks to all who responded:
***