Array Functions

To those, if any, who use the array functions in the freely downloadable
file at http://home.pacbell.net/beban:

I refreshed the file yesterday with changes I have been making during
the past month or so. Most of the changes were tidying things up,
including generalizing many of the functions so that they would better
handle non-variant arrays. I did add one new function, Assign, which
facilitates the "direct" assignment of a range to a non-Variant() array.
I.e., in Excel the following is the normal way to load a Variant()
array from a worksheet range

Dim arr() As Variant
arr = Range("a1:j10")

but it can't be used for non-Variant() arrays. The following now can (if
the array functions are available to your workbook):

Dim arr() As Integer
Assign Range("a1:j10"), arr

Alan Beban

"Alan Beban" wrote...
I.e., in Excel the following is the normal way to load a Variant()
array from a worksheet range

Dim arr() As Variant
arr = Range("a1:j10")

but it can't be used for non-Variant() arrays. The following now can (if
the array functions are available to your workbook):

Dim arr() As Integer
Assign Range("a1:j10"), arr

Odd bits.

If TypeName(InputArray) = "Object()" Then

You'd be assigning a range to an array of objects?! How do ranges hold
objects other than obviously as arrays of singe cells, each cell of which is
a range object in it's own right, but keeping it a multiple cell range would
make more sense than converting it into an array of individual cell range
objects.

ElseIf Not m = 1 Then

Got something against the operator?

Assign = "Success"

If the function should return success/failure, it's generally best to return
TRUE/FALSE and adopt a convention for whether TRUE represents success or
failure. Why so? Because boolean comparisons in IF statements are much more
efficient than string comparisons.

Thanks for taking the time to look at it.

Harlan Grove wrote:
Odd bits.

If TypeName(InputArray) = "Object()" Then

You'd be assigning a range to an array of objects?! How do ranges hold
objects other than obviously as arrays of singe cells, each cell of which is
a range object in it's own right, but keeping it a multiple cell range would
make more sense than converting it into an array of individual cell range
objects.

It's not clear to me that a user needs to be protected from assigning a
range to an array of objects because you or I think it doesn't make
sense to do so. As written it does permit the "direct" assignment of a
2-D Variant() array that contains objects (which myRange can be) to an
array of "Object()" type (notwithstanding that the explanation of the
function indicates that InputArray be "a dynamic array of a built-in
type other than "Object()""). In any event, it will be changed in due
course either to proscribe InputArray as Object() type, or expanded to
facilitate 1-D Variant() array assignment as well as 2-D, and the
explanation changed accordingly. At that time, a decision will be made
whether to prevent a user from assigning a range to an Object() type
array. In the meantime,

Dim myArray() As Whatever
Assign myRange, myArray

works as written if myRange is a single area multi-cell range and
Whatever is a built-in type (so long, of course, as the values in
myRange are such that an array of Whatever type will accept them).


ElseIf Not m = 1 Then

Got something against the operator?

Is there a reason for a preference? If so, it will be changed in due course.


Assign = "Success"

If the function should return success/failure, it's generally best to return
TRUE/FALSE and adopt a convention for whether TRUE represents success or
failure. Why so? Because boolean comparisons in IF statements are much more
efficient than string comparisons.

Thanks; it will be changed in due course.

Thanks again for reviewing it.

Alan Beban

"Alan Beban" wrote...
Harlan Grove wrote:
Odd bits.

If TypeName(InputArray) = "Object()" Then

You'd be assigning a range to an array of objects?! . . .
....
It's not clear to me that a user needs to be protected from assigning a
range to an array of objects because you or I think it doesn't make
sense to do so. . . .

As so often seems to be the case, you're missing the point. It's not your or
my sensibilities that matter. This just doesn't do anything useful. The only
object type a range can contain is other range objects. All your code does
is replace single area, multiple cell ranges with arrays of ranges in which
each entry corresponds to a single cell in the original range. What
conceivable benefit would be gained from this?

. . . As written it does permit the "direct" assignment of a
2-D Variant() array that contains objects (which myRange can be) to an
array of "Object()" type . . .
....

But your code is checking *only* that InputArray is an array of objects, not
that InputRange also contains object references. When VBA tries to set an
object variable to a nonobject reference, it throws a runtime error. You've
trapped runtime errors, so nothing catastrphic happens. However, since your
function provides other types of diagnostics for unsupported argument types,
why not check that when InputArray is an array of object references, that
InputRange must also be an array of objects?

Harlan Grove wrote:
"Alan Beban" wrote...

Harlan Grove wrote:

Odd bits.

If TypeName(InputArray) = "Object()" Then

You'd be assigning a range to an array of objects?! . . .

...

It's not clear to me that a user needs to be protected from assigning a
range to an array of objects because you or I think it doesn't make
sense to do so. . . .


As so often seems to be the case, you're missing the point. It's not your or
my sensibilities that matter. This just doesn't do anything useful. The only
object type a range can contain is other range objects. All your code does
is replace single area, multiple cell ranges with arrays of ranges in which
each entry corresponds to a single cell in the original range. What
conceivable benefit would be gained from this?

No, I didn't miss the point. Setting aside for the time being your
second comment below, what it does that might conceivably be useful is
provide for the "direct" assignment of a 2-D Variant() array that
contains objects (which myRange can be) to an array of Object type.
Unless this facility were otherwise provided for (a la, e.g., your
second suggestion, below), it would be lost by the simple removal of the
If Typename(InputArray) = "Object()" section to remove the facility for
the useless assignment of a range; though that facility might not make
sense, I don't see the harm in leaving it in--i.e., letting that section
apply to both ranges (though useless) and some arrays (conceivably useful).

[Y]our code is checking *only* that InputArray is an array of objects, not
that InputRange also contains object references. When VBA tries to set an
object variable to a nonobject reference, it throws a runtime error. You've
trapped runtime errors, so nothing catastrophic happens. However, since your
function provides other types of diagnostics for unsupported argument types,
why not check that when InputArray is an array of object references, that
InputRange must also be an array of objects?


The code does not provide *any* checking to see that InputRange contains
only values that are acceptable to an array of the type that InputArray
is. It does not, for example, check that InputRange does not contain any
"pure strings" (such as "OK") when InputArray is of type Integer or Long
or Single or Double. This did not, and does not, seem straightforward to
me and I left that to the errorhandler. I did not, for example, try to
work out code that would exclude InputRange dates when InputArray was of
Long type, or exclude InputRange integers when InputArray was of Date
type, or, as you suggest above, exclude InputRange non-objects when
InputArray is of Object type. I'm not sure what the code would look like
in this last case, so I can't be sure that working it out for the sole
purpose of removing a perhaps useless, but harmless, functionality of
permitting the assigning of ranges to Object type arrays would be worth it.

Thanks again for taking time. I do appreciate the substantive comments.

Alan Beban

The relevant code will go like this:

If TypeName(InputArray) = "Object()" Then
If Not TypeOf InputRange Is Range Then
'Load 1-D or 2-D Object() InputArray
Else
'message "Can't assign range to Object() array"
Exit Function
End if
Else
'Load 1-D or 2-D non-Object() array
End If
Assign = True
errorhandler:
End Function

Thanks for helping me think it through.

Alan Beban

Harlan Grove wrote:
"Alan Beban" wrote...

Harlan Grove wrote:

Odd bits.

If TypeName(InputArray) = "Object()" Then

You'd be assigning a range to an array of objects?! . . .

...

It's not clear to me that a user needs to be protected from assigning a
range to an array of objects because you or I think it doesn't make
sense to do so. . . .


As so often seems to be the case, you're missing the point. It's not your or
my sensibilities that matter. This just doesn't do anything useful. The only
object type a range can contain is other range objects. All your code does
is replace single area, multiple cell ranges with arrays of ranges in which
each entry corresponds to a single cell in the original range. What
conceivable benefit would be gained from this?


. . . As written it does permit the "direct" assignment of a
2-D Variant() array that contains objects (which myRange can be) to an
array of "Object()" type . . .

...

But your code is checking *only* that InputArray is an array of objects, not
that InputRange also contains object references. When VBA tries to set an
object variable to a nonobject reference, it throws a runtime error. You've
trapped runtime errors, so nothing catastrphic happens. However, since your
function provides other types of diagnostics for unsupported argument types,
why not check that when InputArray is an array of object references, that
InputRange must also be an array of objects?

"Alan Beban" wrote...
Harlan Grove wrote:
...
The code does not provide *any* checking to see that InputRange contains
only values that are acceptable to an array of the type that InputArray
is. It does not, for example, check that InputRange does not contain any
"pure strings" (such as "OK") when InputArray is of type Integer or Long
or Single or Double. This did not, and does not, seem straightforward to
me and I left that to the errorhandler. I did not, for example, try to
work out code that would exclude InputRange dates when InputArray was of
Long type, or exclude InputRange integers when InputArray was of Date
type, or, as you suggest above, exclude InputRange non-objects when
InputArray is of Object type. I'm not sure what the code would look like
in this last case, so I can't be sure that working it out for the sole
purpose of removing a perhaps useless, but harmless, functionality of
permitting the assigning of ranges to Object type arrays would be worth it.

OK, you're the one supposedly championing generality. An array of Variants could
contain scalars and objects. For example,


Sub xyz()
Dim v() As Variant
ReDim v(1 To 2)
v(1) = 5#
Set v(2) = ActiveCell
End Sub


Your stated concern for generality would be easier to believe if your code
looked like


For i = LBound(InputArray, 1) To UBound(InputArray, 1)

For j = LBound(InputArray, 2) To UBound(InputArray, 2)

If IsObject(InputRange(i, j)) Then
Set InputArray(i, j) = InputRange(i, j)

Else
InputArray(i, j) = InputRange(i, j)

End If

Next j

Next i


You didn't consider this possibility, did you?

So, object support only seems worthwhile when InputRange is an array of
Variants. In that case, wouldn't the BUILT-IN VBA syntax/semantics for assigning
an array of variants to a nonarray variant provide *exactly* the same
functionality as using Assign to assign an array of variants to another array of
variants? The former would be MUCH QUICKER because VBA itself would be doing
everything (and it would handle hierarchical arrays, any dimensions, and all the
other annoying possibilities Assign doesn't). So what's the benefit from doing
this? You keep repeating that providing this does no harm. Yes it does! It
wastes system resources adding limited, slow functionality which provides no
benefits. Dead and do-nothing code should be eliminated whenever possible.
You're doing no one any favors adding 'features' no sane person would ever use.

--
To top-post is human, to bottom-post and snip is sublime.

"Alan Beban" wrote...
Harlan Grove wrote:
...
. . . As I mentioned previously, by design no checking is
provided to insure that each element of InputRange is of a type that
will be accepted by an array of the type of InputArray; that is left to
errorhandler. One reason, among others, is that otherwise, as above,
an additional call is required within the loop for each element of
InputRange.
...

You're checking whether InputArray is an array of object references, but you see
no benefits to checking whether individual entries in InputRange are objects?
And you base this on the desirability of not checking the types of any entry in
InputRange.

As for the need for additional calls, if more calls provide more functionality,
it's a design trade-off. So you seem to be taking the position that the
functionality provided by Assign as you have it so far is ideal even though it
won't handle assigning an any valid InputRange (no more than 2 non-hierarchical
dimensions of variants) to an array of variants.

The only time Assign could do anything useful when InputArray is an array of
object references is when InputRange is also an array of object references.
Otherwise, the error handler would kick in. Even when both are arrays of object
references, when would Assign ever be preferable to


Dim InputArray As Variant, InputRange(...) As SomeObjectType
'initialize InputRange
InputArray = InputRange


?!!

You must have some idea when Assign would be useful when InputArray is an array
of object references. Don't you?


I don't understand your discussion above about built-in VBA
syntax/semantics (I'm not sure what syntax/semantics you're referring
to) being quicker than using Assign to assign an array of variants to
another array of variants. . . .

See above. Precisely *NOT* assignment to another *ARRAY* of variants but to a
variant, as in 'Dim x As Variant', not 'Dim x() As Variant'.

. . . The function isn't proposed for use in
assigning an array of variants to another array of variants; it's
proposed for assigning ranges and arrays to non-Variant() arrays. I.e.,
cases, admittedly not all, in which the "Can't assign to array" error
message is normally encountered. Indeed, to assign your Variant() array
v above to a Variant() array arr, all that's needed is arr = v; Assign
isn't relevant to that case. Unless once again, I'm missing some point.
If so, perhaps you could make the point more clearly, without so much
clutter.

OK, it seems your original conception was that Assign would handle assigning
ranges to arrays of nonvariant and nonobject type. For example, assigning a
range to an array of integers (along with all the implicit conversion and
rounding that may entail). Then you seem to have thought about adding support
for 1D and 2D arrays as well as ranges. Then you seem to have thought that such
arrays could contain object references. Classic feature creep.

Assigning anything to an array of variants is pointless compared to assinging
exactly the same thing to a single, non-array variant (see example above if
you're still unclear on the concept). In that sense, Assign provides no benefits
when InputArray is an array of variants. It also provides no clear benefits when
assigning arrays of objects to arrays of objects. Maybe I've been too sheltered,
but when I need arbitrary collections of objects, I use Collection objects
rather than arrays of objects. When are arrays of objects useful? How often do
you use them?

Anyway, FTHOI, here's how I'd do it. Note it doesn't allow objects at all, it
requires that the target array start off empty (up to the caller to Erase it if
necessary) and it does lightweight error checking by entry. Oh, and it also
handles 0D through 6D nonhierarchical arrays.


Function ct(ByRef a As Variant, ByRef b As Variant) As Boolean
Dim n As Long, x As Variant
Dim i1 As Long, i2 As Long, i3 As Long, i4 As Long, i5 As Long, i6 As Long

ct = True 'error exit status is TRUE, success is FALSE

If IsObject(a) Or IsObject(b) Then Exit Function

If Not (IsArray(a) And IsArray(b)) Then 'both scalars
Select Case TypeName(b)
Case "Boolean": b = CBool(a)
Case "Byte": b = CByte(a)
Case "Currency": b = CCur(a)
Case "Date": b = CDate(a)
Case "Decimal": b = CDec(a)
Case "Double": b = CDbl(a)
Case "Integer": b = CInt(a)
Case "Long": b = CLng(a)
Case "Single": b = CSng(a)
Case "String": b = CStr(a)
Case "Variant": b = CVar(a)
Case Else: Exit Function 'impossible condition - error
End Select

ct = False 'success if any built-in scalar type

ElseIf IsArray(a) And IsArray(b) Then
On Error Resume Next

x = UBound(b, 1)
If Err.Number = 0 Then
Exit Function 'b must be empty!!
Else
Err.Clear
End If

n = 1
Do 'forever
x = UBound(a, n + 1)
If Err.Number 0 Then Exit Do
n = n + 1
Loop
Err.Clear

On Error GoTo 0


Select Case n

Case 1:
ReDim b( _
LBound(a, 1) To UBound(a, 1) _
)

For Each x In b
Exit For
Next x

For i1 = LBound(a, 1) To UBound(a, 1)
If Not ct(a(i1), x) Then
b(i1) = x
Else
Exit Function 'error converting a(...)
End If
Next i1

Case 2:
ReDim b( _
LBound(a, 1) To UBound(a, 1), _
LBound(a, 2) To UBound(a, 2) _
)

For Each x In b
Exit For
Next x

For i1 = LBound(a, 1) To UBound(a, 1)
For i2 = LBound(a, 2) To UBound(a, 2)
If Not ct(a(i1, i2), x) Then
b(i1, i2) = x
Else
Exit Function 'error converting a(...)
End If
Next i2
Next i1

Case 3:
ReDim b( _
LBound(a, 1) To UBound(a, 1), _
LBound(a, 2) To UBound(a, 2), _
LBound(a, 3) To UBound(a, 3) _
)

For Each x In b
Exit For
Next x

For i1 = LBound(a, 1) To UBound(a, 1)
For i2 = LBound(a, 2) To UBound(a, 2)
For i3 = LBound(a, 3) To UBound(a, 3)
If Not ct(a(i1, i2, i3), x) Then
b(i1, i2, i3) = x
Else
Exit Function 'error converting a(...)
End If
Next i3
Next i2
Next i1

Case 4:
ReDim b( _
LBound(a, 1) To UBound(a, 1), _
LBound(a, 2) To UBound(a, 2), _
LBound(a, 3) To UBound(a, 3), _
LBound(a, 4) To UBound(a, 4) _
)

For Each x In b
Exit For
Next x

For i1 = LBound(a, 1) To UBound(a, 1)
For i2 = LBound(a, 2) To UBound(a, 2)
For i3 = LBound(a, 3) To UBound(a, 3)
For i4 = LBound(a, 4) To UBound(a, 4)
If Not ct(a(i1, i2, i3, i4), x) Then
b(i1, i2, i3, i4) = x
Else
Exit Function 'error converting a(...)
End If
Next i4
Next i3
Next i2
Next i1

Case 5:
ReDim b( _
LBound(a, 1) To UBound(a, 1), _
LBound(a, 2) To UBound(a, 2), _
LBound(a, 3) To UBound(a, 3), _
LBound(a, 4) To UBound(a, 4), _
LBound(a, 5) To UBound(a, 5) _
)

For Each x In b
Exit For
Next x

For i1 = LBound(a, 1) To UBound(a, 1)
For i2 = LBound(a, 2) To UBound(a, 2)
For i3 = LBound(a, 3) To UBound(a, 3)
For i4 = LBound(a, 4) To UBound(a, 4)
For i5 = LBound(a, 5) To UBound(a, 5)
If Not ct(a(i1, i2, i3, i4, i5), x) Then
b(i1, i2, i3, i4, i5) = x
Else
Exit Function 'error converting a(...)
End If
Next i5
Next i4
Next i3
Next i2
Next i1

Case 6:
ReDim b( _
LBound(a, 1) To UBound(a, 1), _
LBound(a, 2) To UBound(a, 2), _
LBound(a, 3) To UBound(a, 3), _
LBound(a, 4) To UBound(a, 4), _
LBound(a, 5) To UBound(a, 5), _
LBound(a, 6) To UBound(a, 6) _
)

For Each x In b
Exit For
Next x

For i1 = LBound(a, 1) To UBound(a, 1)
For i2 = LBound(a, 2) To UBound(a, 2)
For i3 = LBound(a, 3) To UBound(a, 3)
For i4 = LBound(a, 4) To UBound(a, 4)
For i5 = LBound(a, 5) To UBound(a, 5)
For i6 = LBound(a, 6) To UBound(a, 6)
If Not ct(a(i1, i2, i3, i4, i5, i6), x) Then
b(i1, i2, i3, i4, i5, i6) = x
Else
Exit Function 'error converting a(...)
End If
Next i6
Next i5
Next i4
Next i3
Next i2
Next i1

Case Else: Exit Function 'impossible condition - error

End Select

ct = False 'success if any built-in scalar type

'Else -- mixed references - unsupported - error

End If

End Function


Sample usage:


Sub foo()
Dim x() As Byte

If ct(Range("A1:C5").Value, b) Then
MsgBox "FUBAR!"
Exit Sub
End If

'other stuff using b

End Sub

--
To top-post is human, to bottom-post and snip is sublime.

"Harlan Grove" wrote...
...
Sub foo()
Dim x() As Byte

I love typos! Make that

Dim b() As Byte

Harlan Grove wrote:
Anyway, FTHOI, here's how I'd do it. Note it doesn't allow objects at all, it
requires that the target array start off empty (up to the caller to Erase it if
necessary) and it does lightweight error checking by entry. Oh, and it also
handles 0D through 6D nonhierarchical arrays.

With 1 through 9 in a1:c3, what's the call to assign the range to an
Integer() type array?

When I code

Sub foo()
Dim arr() As Integer
x = ct(Range("A1:C3"), arr)
Debug.Print x
End Sub

it fails because Range("A1:C3") is an object.

Alan Beban