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" wrote in message
...
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.

***