B
Bob S.
The Variant containing an Array is an argument to VBA function.
I have a VBA function MyFunc(X). I use it directly in an Excel worksheet by
typing
=MyFunc(A1:A4) right into a cell. The argument comes into the function as a
Range. That's fine.
Excel also lets you type something like {=MyFunc(ABS(A1:A4))} (using
ctrl-shift-Enter), and in this case the argument comes into the function as a
Variant containing an array. The array is dimensioned X(4, 1). That's fine
too. I can tell if this is the case by looking at the Typename of the
argument to see if it's a Range or a Variant.
Trouble comes if the user types a _Horizontal_ range instead of a vertical
one, as in
{=MyFunc(ABS(A11))} . In that case the array comes in dimensioned as
X(4), with only _One_ subscript. I can't figure out how to identify this
case, so the function crashes if I use the wrong number of subscripts to
refer to the array elements.
Driving me crazy.
Thanks in advance.
Bob
I have a VBA function MyFunc(X). I use it directly in an Excel worksheet by
typing
=MyFunc(A1:A4) right into a cell. The argument comes into the function as a
Range. That's fine.
Excel also lets you type something like {=MyFunc(ABS(A1:A4))} (using
ctrl-shift-Enter), and in this case the argument comes into the function as a
Variant containing an array. The array is dimensioned X(4, 1). That's fine
too. I can tell if this is the case by looking at the Typename of the
argument to see if it's a Range or a Variant.
Trouble comes if the user types a _Horizontal_ range instead of a vertical
one, as in
{=MyFunc(ABS(A11))} . In that case the array comes in dimensioned as
X(4), with only _One_ subscript. I can't figure out how to identify this
case, so the function crashes if I use the wrong number of subscripts to
refer to the array elements.
Driving me crazy.
Thanks in advance.
Bob