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 found this using Google:

The number of dimensions of an array
http://www.devx.com/vb2themax/Tip/18265
Francesco Balena

Function ArrayDims(arr As Variant) As Integer
Dim i As Integer, bound As Long
On Error Resume Next
For i = 1 To 60
bound = LBound(arr, i)
If Err Then
ArrayDims = i - 1
Exit Function
End If
Next
End Function

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Bob S." <(E-Mail Removed)> wrote in message
news:0C7399A6-3001-4FBF-B689-(E-Mail Removed)...
> 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

Jon Peltier wrote:
> I found this using Google:
>
> The number of dimensions of an array
> http://www.devx.com/vb2themax/Tip/18265
> Francesco Balena
>
> Function ArrayDims(arr As Variant) As Integer
> Dim i As Integer, bound As Long
> On Error Resume Next
> For i = 1 To 60
> bound = LBound(arr, i)
> If Err Then
> ArrayDims = i - 1
> Exit Function
> End If
> Next
> End Function
>
> - Jon

For effixiency you might want to use Dim i As Byte; and perhaps even Dim
bound As Byte, though that's more problematic.

Alan Beban

This will be a more efficient way to get the number of dimensions of an
array:

Option Explicit
Declare Sub CopyMemory Lib "kernel32" Alias _
"RtlMoveMemory" (pDst As Any, _
pSrc As Any, _
ByVal ByteLen As Long)

Sub testing()

Dim arr(1 To 1, 1 To 10)

MsgBox ArrayTester(arr)

End Sub


Function ArrayTester(arr As Variant) As Integer

'-----------------------------------------------------------------
' will return:
' -1 if not an array
' 0 if an un-dimmed array
' 1 or more indicating the number of dimensions of a dimmed array
'-----------------------------------------------------------------
Dim ptr As Long
Dim VType As Integer

Const VT_BYREF = &H4000&

'get the real VarType of the argument
'this is similar to VarType(), but returns also the VT_BYREF bit
CopyMemory VType, arr, 2

'exit if not an array
If (VType And vbArray) = 0 Then
ArrayTester = -1
Exit Function
End If

'get the address of the SAFEARRAY descriptor
'this is stored in the second half of the
'Variant parameter that has received the array
CopyMemory ptr, ByVal VarPtr(arr) + 8, 4

'see whether the routine was passed a Variant
'that contains an array, rather than directly an array
'in the former case ptr already points to the SA structure.
'Thanks to Monte Hansen for this fix
If (VType And VT_BYREF) Then
' ptr is a pointer to a pointer
CopyMemory ptr, ByVal ptr, 4
End If

'get the address of the SAFEARRAY structure
'this is stored in the descriptor

'get the first word of the SAFEARRAY structure
'which holds the number of dimensions
'...but first check that saAddr is non-zero, otherwise
'this routine bombs when the array is uninitialized
'(Thanks to VB2TheMax aficionado Thomas Eyde for
' suggesting this edit to the original routine.)
If ptr Then
CopyMemory ArrayTester, ByVal ptr, 2
End If

End Function


RBS



"Jon Peltier" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I found this using Google:
>
> The number of dimensions of an array
> http://www.devx.com/vb2themax/Tip/18265
> Francesco Balena
>
> Function ArrayDims(arr As Variant) As Integer
> Dim i As Integer, bound As Long
> On Error Resume Next
> For i = 1 To 60
> bound = LBound(arr, i)
> If Err Then
> ArrayDims = i - 1
> Exit Function
> End If
> Next
> End Function
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
>
> "Bob S." <(E-Mail Removed)> wrote in message
> news:0C7399A6-3001-4FBF-B689-(E-Mail Removed)...
>> 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
>
>