Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range or Array?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range or Array?
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) "Smallweed" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range or Array?
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 ..... -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "Smallweed" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range or Array?
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 Smallweed wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range or Array?
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 wrote: If TypeName(rng) = "Range" Then MsgBox "Range" ElseIf TypeName(rng) = "Variant()" Then If IsArray(rng) Then MsgBox "Array" End If End If |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range or Array?
"Alan Beban" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range or Array?
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 wrote: . . . 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range or Array?
Bob Phillips wrote:
"Alan Beban" wrote in message ... 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. 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range or Array?
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" wrote: 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 Chip Pearson wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range or Array?
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 Smallweed wrote: 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! |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range or Array?
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 Smallweed wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to fix an array or range | Excel Discussion (Misc queries) | |||
Range to Array | Excel Programming | |||
copy one array formula to an array range | Excel Programming | |||
Tricky array formula issue - Using array formula on one cell, then autofilling down a range | Excel Programming | |||
Array <--- Range | Excel Programming |