Range or Array?

[Guest]

Does anyone know an easy way of telling whether the input argument of a UDF
is an Excel range, a VBA array or a variant? IsArray doesn't work because it
returns a True for all cases. Thanks.

 

[Bob Phillips]

If TypeName(rng) = "Range" Then
MsgBox "Range"
ElseIf TypeName(rng) = "Variant()" Then
If IsArray(rng) Then
MsgBox "Array"
End If
End If


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Arvi Laanemets]

Hi

An example from my UDF

Public Function EnchWorkdaysN(...,
Optional Holidays As Variant = Nothing,
...)

....


' When parameter Holidays is omitted, or Null, or not a positive numeric
(date) value,
' or not an array or cell range with numeric values, then no holidays
' are left out from day's count.

....

' Initialize ArrayH
If TypeName(Holidays) = "Variant()" Then
...
ElseIf (VarType(Holidays) >= 8192 And VarType(Holidays) <= 8199) Or _
VarType(Holidays) = 8204 Then
...
ElseIf VarType(Holidays) < 8 Then
...
Else
...
End If
.....

 

[Alan Beban]

I take it that by "variant" you mean a Variant variable containing an
array. If so, in what circumstances do you need to distinguish between a
Variant() type array and an array contained within a Variant variable?

Alan Beban

 

[Alan Beban]

Consider:

If IsArray(rng) Then
If TypeOf rng Is Range Then
MsgBox "Range"
Else
MsgBox "Array"
End If
End If

But neither this nor Bob Phillips's version below distinguishes between
a Variant() array and an array contained within a Variant Variable. I
don't know of a way to do that if the array is passed to a sub procedure
or function, but I have difficulty seeing why one would care.

In the abstract, one can see it in the Declaration statement. E.g.,

after Dim myArray1 As Variant, myArray2() As Variant

myArray1 is a Variant variable, myArray2 is a Variant() type array.

Alan Beban

 

[Bob Phillips]

Consider:

If IsArray(rng) Then
If TypeOf rng Is Range Then
MsgBox "Range"
Else
MsgBox "Array"
End If
End If

I decided to cater for other types passed as well.

 

[Alan Beban]

More precisely, I believe that if the array has been passed to a sub
procedure or a function, it will always be an array contained within a
Variant variable; I believe there is no syntax equivalent to:

Function junk1(myInput As Variant()). So myInput must always be declared as

Function junk1(myInput As Variant) or its equivalent

Function junk1(myInput)

in order to accept an array. So whatever array is passed to the
function, whether it starts out as an array within a Variant variable,
or a Variant() type of array, or an Integer() type of array, or
whatever, it ends up in the function as an array contained within the
Variant variable (in this case, myInput).

Alan Beban

 

[Alan Beban]

I decided to cater for other types passed as well.

But what happens to:

Sub abtest2()
Dim rng() As Integer
If TypeName(rng) = "Range" Then
MsgBox "Range"
ElseIf TypeName(rng) = "Variant()" Then
If IsArray(rng) Then
MsgBox "Array"
End If
End If
End Sub

In my code, the second line should be

If Typename(rng) = "Range" Then

Alan Beban

 

[Chip Pearson]

Try something like

Sub AAA()
Dim A(1 To 4, 1 To 3) As Variant
Dim R As Range
Dim V As Variant
V = Range("A1:C4")
Set R = Range("A1:C4")
Debug.Print "V: " & IsRange(V)
Debug.Print "R: " & IsRange(R)
Debug.Print "A: " & IsRange(A)
End Sub

Function IsRange(V As Variant) As Boolean
If IsObject(V) = True Then
If TypeOf V Is Excel.Range Then
IsRange = True
Exit Function
End If
End If
End Function


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Alan Beban]

Like the previous solutions, this doesn't distinguish between a Variant
type array and an array contained within a Variant variable. But since
the OP has not bothered to repost suggesting what conceivable need
he/she could have for so distinguishing, no loss.

Alan Beban

 

[Guest]

Hi - back again after other projects took a chunk out of my time! Thanks
everyone for their contributions to this lively thread. As Alan points out,
the problem area is in distinguishing between a variant variable and a
variant array - I want my UDF to be flexible enough to accept either which
means, once I've ruled out the input being a range, I need to know whether I
just have a single value or an array to loop through. I guess I'll just have
to use some error trapping on my existing code. Curious there isn't some
simpler way to spot this!

 

[Alan Beban]

When I first responded I stated "I take it that by "variant" you mean a
Variant variable containing an array." It now appears that that isn't
what you mean at all. Perhaps you could provide an illustration of
IsArray returning True for a variant variable.

Alan Beban

 

[Alan Beban]

Well, there seems to be some continuing confusion in your thinking; or
at least in your presentation of "the problem".

IsArray(iVar) will return True if iVar is a multi-celled range or an
array, whether the array was declared as a true Variant() array (or an
array of any other built-in type) or is an array contained within a
Variant variable (whether the array so contained is of type Variant(),
Integer(), String(), or whatever).

So once IsArray has returned True and a range has been ruled out, the
only way for iVar to be a single value is if it is an array (whether or
not contained within a Variant variable) containing a single element--if
it were otherwise a single value, i.e., simply a single value rather
than an array containing a single element, IsArray would have returned
False.

So if you are trying to rule out a single element array (though it's not
clear why--looping from LBound(arr,n) to UBound(arr,n) in each dimension
will deal with both a single element array or a multi-element array),
you can check for that directly.

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, you can
check for it with ArrayCount(iVar), which will return the number of
elements in the array.

If those functions are not so available then the ArrayCount function is
included below.

As an aside, there are a couple of ways to determine whether an array
was declared as true array or as an array contained within a Variant
variable, but it is not clear to me why that should ever be necessary or
desirable.

Function ArrayCount(InputArray)
'This function counts NOT the number of
'non-blank values in the array, but the
'number of available slots for values,
'whether the slots contain anything or not.
'It's similar to the Count Property [e.g.,
'Range("a1:c3").Count]

Dim j As Long, k As Long

'Convert range to array
'InputArray = InputArray

If IsArray(InputArray) Then
If Not TypeOf InputArray Is Range Then

j = 1: k = 1

On Error Resume Next

Do
k = k * (UBound(InputArray, j) - _
LBound(InputArray, j) + 1)
j = j + 1
Loop While Err.Number = 0

ArrayCount = k
Else
If TypeOf Application.Caller Is Range Then
ArrayCount = "#ERROR! This function accepts only arrays."
Else
MsgBox "#ERROR! The ArrayCount function accepts only arrays.", 16
End If
End If

Else
If TypeOf Application.Caller Is Range Then
ArrayCount = "#ERROR! This function accepts only arrays."
Else
MsgBox "#ERROR! The ArrayCount function accepts only arrays.", 16
End If
End If

End Function

Alan Beban