Either this is so simple & stupid that it's staring me in the face, or
it's a real challenge:

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.

***

I don't quite understand your question. Your X function doesn't "have" a
ParamArray of its own, it has what is passed into it. If you call the
function X, you would need to pass the elements that will be assigned to the
ParamArray within your call to the function which would mean you *know* the
elements being passed and would not have to "ask" the X function for them.
Perhaps if you give us some detail as to what you have set up and what you
are trying to do with that setup, then maybe we can be of more help.

--
Rick (MVP - Excel)


"Jim Luedke" <(E-Mail Removed)> wrote in message
news:40178938-08bc-41ff-a720-(E-Mail Removed)...
> Either this is so simple & stupid that it's staring me in the face, or
> it's a real challenge:
>
> 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:

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.

***

Okay, I see what you want. The property you are looking for is the Formula
property. Here is a function that returns the parameter list for the X
function you posted...

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" <(E-Mail Removed)> wrote in message
news:af3d9660-509f-4a89-83db-(E-Mail Removed)...
> Rick:
>
> 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.
>
> ***

P.S.

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.

***

There are some Excel formula syntax parsers around, although I have not seen
one that copes with Excel 2007 and Excel 2010 Table syntax.

See for instance
http://ewbi.blogs.com/develops/2007/...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" <(E-Mail Removed)> wrote in message
news:c1bd22af-ac54-44c1-a9c7-(E-Mail Removed)...
> P.S.
>
> 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.
>
> ***
>

Rick, Charles:

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.

***

You might also need to handle array constants:

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" <(E-Mail Removed)> wrote in message
news:fd7168bb-ec3d-475a-98fc-(E-Mail Removed)...
> Rick, Charles:
>
> 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.
>
> ***
>

All:

OK, I just submitted into this group code to parse a function's param
string into its params.

Thanks to all who responded:

http://groups.google.com/group/micro...d1945265193fe5

***