Declaring arrays

[Alan Beban]

What might be the circumstances, if any, in which one might prefer to code:

Dim arr() As String
Redim arr(1 to 4)
arr(1)="ab"
arr(2)="cd"
arr(3)="ef"
arr(4)="gh"

rather than:

Dim arr() As Variant
arr=Array("ab", "cd", "ef", "gh")

TIA,
Alan Beban

 

[Jake Marx]

Hi Alan,

What might be the circumstances, if any, in which one might prefer to
code:

Dim arr() As String
Redim arr(1 to 4)
arr(1)="ab"
arr(2)="cd"
arr(3)="ef"
arr(4)="gh"

rather than:

Dim arr() As Variant
arr=Array("ab", "cd", "ef", "gh")

With either method, you can resize the array, check UBound and LBound, dump
them to Excel ranges directly, and use a For Each Next to step through each
element, so they are both functional enough. So for me, comes down to
efficiency.

Here's a test I just put together - on my machine, using an array of Strings
was over 3x faster than using a Variant to hold the array (test included
setting values, resizing, and adding another element).


'/ TEST CODE
Sub test1()
Dim arr() As String
Dim l As Long
Dim lTimer As Long

lTimer = Timer
For l = 1 To 5000000
ReDim arr(1 To 4)
arr(1) = "ab"
arr(2) = "cd"
arr(3) = "ef"
arr(4) = "gh"

ReDim Preserve arr(1 To 5)
arr(5) = "ij"
Next l

Debug.Print "test1: " & Format$(Timer - lTimer, "0.0000####")
End Sub

Sub test2()
Dim arr2() As Variant
Dim l As Long
Dim lTimer As Long

lTimer = Timer
For l = 1 To 5000000
arr2 = Array("ab", "cd", "ef", "gh")
ReDim Preserve arr2(4)
arr2(4) = "ij"
Next l

Debug.Print "test2: " & Format$(Timer - lTimer, "0.0000####")
End Sub


'/ DEBUG WINDOW RESULTS
test1: 9.46484375
test2: 32.04296875
test1: 8.79492188
test2: 33.79492188

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Harlan Grove]

......

How about

Dim rra As Variant
rra = Array("ab", "cd", "ef", "gh")

Note that rra is *NOT* declared as an array, just a variant scalar. Also note
that the arr=Array("ab", "cd", "ef", "gh") call throws an error in XL8/97, so
VBA 5.x.

'/ DEBUG WINDOW RESULTS
test1: 9.46484375
test2: 32.04296875
test1: 8.79492188
test2: 33.79492188

OK, but let's benchmark then torture test this. Also, I'd guess Alan asked this
to show that explicitly declared arrays of particular types are more efficient.
I don't dispute this. However, once these efficient data structures are exposed
to ill conceived procedure calls efficiency gains evaporate.

Here's a comparison with baseline performance of 3 different data types (array
of string, array of variant and scalar variant), then direct assignment of
Array(...) results to the scalar variant, then use of Alan's Assign procedure to
assign Array(...) results to the array of strings. Note that I had to change the
32nd line of Assign from

Dim tempArray()

to

Dim tempArray As Variant

since I'm using XL8/97 to test, and it doesn't support direct assignment to
uninitialized dynamic arrays. This may affect the relative timing results
compared to XL versions useing VBA 6.x. Note that this means Alan's array
function library isn't fully compatible with XL8/97 without modifications.


Sub testemhard()
Const MAXITER As Long = 2000000

Dim arr1() As String, arr2() As Variant, arr3 As Variant
Dim k As Long
Dim t As Long

t = Timer

For k = 1 To MAXITER

ReDim arr1(1 To 4)
arr1(1) = "ab"
arr1(2) = "cd"
arr1(3) = "ef"
arr1(4) = "gh"

ReDim Preserve arr1(1 To 5)
arr1(5) = "ij"

Next k

Debug.Print "test1: " & Format$(Timer - t, "0.0000####")


t = Timer

For k = 1 To MAXITER

ReDim arr2(1 To 4)
arr2(1) = "ab"
arr2(2) = "cd"
arr2(3) = "ef"
arr2(4) = "gh"

ReDim Preserve arr2(1 To 5)
arr2(5) = "ij"

Next k

Debug.Print "test2: " & Format$(Timer - t, "0.0000####")


t = Timer

For k = 1 To MAXITER

ReDim arr3(1 To 4)
arr3(1) = "ab"
arr3(2) = "cd"
arr3(3) = "ef"
arr3(4) = "gh"

ReDim Preserve arr3(1 To 5)
arr3(5) = "ij"

Next k

Debug.Print "test3: " & Format$(Timer - t, "0.0000####")


Erase arr3

t = Timer

For k = 1 To MAXITER

arr3 = Array("ab", "cd", "ef", "gh")
ReDim Preserve arr3(4)
arr3(4) = "ij"

Next k

Debug.Print "test4: " & Format$(Timer - t, "0.0000####")


Erase arr1

t = Timer

For k = 1 To (MAXITER / 50) '** NOTE iterating 2% as many times! **

Assign Array("ab", "cd", "ef", "gh"), arr1
ReDim Preserve arr1(4)
arr1(4) = "ij"

Next k

Debug.Print "test5: " & Format$(Timer - t, "0.0000####")

End Sub


And my Immediate window results,

test1: 7.18359375
test2: 8.515625
test3: 11.765625
test4: 18.05078125
test5: 22.26171875

Note that test5 was based on 2% of the iteration count of the other tests, so
the comparable iteration count result would be 1113.085938. So while assigning
the result of Array(...) to a scalar variant takes roughly 3 times as long as
item by item initialization of an array of strings, using Alan's Assign
procedure to initialize an array of strings takes almost 155 times as long.

Moral: if you're using arrays of particular types in order to wring as much
efficiency as possible out of your code, don't use procedure calls to do simple
things like initialization. Use inline code instead. If you find generating
inline code tedious, learn to use scripting languages or programmer's editors to
write code generators.

 

[Harlan Grove]

I suspected that XL version would have some effect on relative timing
results. It does. I reran the testemhard macro with the test5 For loop
changed to

For k = 1 To (MAXITER / 10) '** NOTE iterating 10% as many times! **

and Alan's Assign macro left as-is. The results were

test1: 9.5703125
test2: 10.9921875
test3: 15.7578125
test4: 26.78125
test5: 36.640625

Using Assign is still more than an order of magnitude slower than the
alternatives, but runs relatively much faster in VBA 6.x.

 

[Alan Beban]

...


. . . I'd guess Alan asked this
to show that explicitly declared arrays of particular types are more efficient.
I don't dispute this.

No. I asked it to see whether users might suggest a more functional
reason. E.g., that if the array were to be later changed or added to,
the type control furnished by, e.g., Dim arr As Integer, which might
have been a better example, might be useful. If so, then the user could
decide for him/herself whether that use of the Assign procedure to
support that usefulness, rather than using inline coding or learning to
use scripting languages or programmer's editors, was outweighed by the
loss of speed efficiency, which might for a particular user be a matter
of fractions of a millisecond.

. . . Note that I had to change the
32nd line of Assign from

Dim tempArray()

to

Dim tempArray As Variant

since I'm using XL8/97 to test, and it doesn't support direct assignment to
uninitialized dynamic arrays. This may affect the relative timing results
compared to XL versions useing VBA 6.x. Note that this means Alan's array
function library isn't fully compatible with XL8/97 without modifications.

I'm not sure whether the broad reference to "array function library" is
intended to mean anything other than the specifically described
incompatibility in the Assign procedure. In any event, that limited
incompatibility is being corrected and will be included in the next
update of the functions at the site.

Alan Beban

 

[Harlan Grove]

...
...

No. I asked it to see whether users might suggest a more functional
reason. E.g., that if the array were to be later changed or added to,
the type control furnished by, e.g., Dim arr As Integer, which might
have been a better example, might be useful. If so, then the user could
decide for him/herself whether that use of the Assign procedure to
support that usefulness, rather than using inline coding or learning to
use scripting languages or programmer's editors, was outweighed by the
loss of speed efficiency, which might for a particular user be a matter
of fractions of a millisecond.

...

Fine. Then to summarize the responses so far: the only thing that seems to be
gained by declaring and using dynamic arrays of specific types vs dynamic arrays
of variants or scalar variants holding dynamic arrays seems to be efficiency.
All are equally easy to redimension. Also, arrays of specific types do provide
stronger typing than the other alternatives. Whether that's useful or not in
practical usage is arguable. Evidence that it's not particularly useful is the
fact that very, very little widely used software is written in strongly typed
languages. As for VB[A], try

Sub foo()
Dim a() As String, b() As Double
ReDim a(1 To 5)
ReDim b(1 To 5)
a(1) = 1
b(1) = " 2.345 "
End Sub

Does this generate syntax (compile time) errors or semantic (run time) errors?
No? Then what benefits does sham strong typing provide other than storage and
execution speed efficiency?

Since you seem to dismiss efficiency, then do you really believe

Dim a() As String
Assign Array("foo", "bar"), a

is more convenient than

Dim a As Variant
a = Array("foo", "bar")

especially when one considers the necessity of keeping up with the frequent
revisions to the function library whence Assign comes?

If you're not dismissing efficiency, then how do you assess the run time
difference of using Assign vs inline array initialization? Under what
circumstances would using Assign make sense? Other than stroking your ego, I
don't see much point to it.

 

[Alan Beban]

. . .As for VB[A], try

Sub foo()
Dim a() As String, b() As Double
ReDim a(1 To 5)
ReDim b(1 To 5)
a(1) = 1
b(1) = " 2.345 "
End Sub

Does this generate syntax (compile time) errors or semantic (run time) errors?

Another of your characteristic straw men. No; but, e.g.:

Sub foo()
Dim a() As String, b() As Double
ReDim a(1 To 5)
ReDim b(1 To 5)
a(1) = 1
b(1) = "ok"
End Sub

does.

Since you seem to dismiss efficiency . . . .

And another. I don't dismiss it; I just think users can decide for
themselves how much weight to give it in their particular applications.

Alan Beban

 

[Harlan Grove]

. . .As for VB[A], try

Sub foo()
Dim a() As String, b() As Double
ReDim a(1 To 5)
ReDim b(1 To 5)
a(1) = 1
b(1) = " 2.345 "
End Sub

Does this generate syntax (compile time) errors or semantic (run time) errors?

Another of your characteristic straw men. No; but, e.g.:

Not a straw man. In C with warnings on,

void foo(void) {
double x;
x = "foobar";
}

does generate a compile time error. This is strong type checking.

Sub foo()
Dim a() As String, b() As Double
ReDim a(1 To 5)
ReDim b(1 To 5)
a(1) = 1
b(1) = "ok"
End Sub

does.

Picky: despite the error message, this isn't type checking per se. It's the
failure of implicit type conversion.

You're right. If b were declared as an array of variants, b(1) would happily
accept assignment of "ok". This can be a problem.

That said, far better to catch such errors at compile time rather than run time,
but that's not something VBA does. Declaring b as an array of doubles doesn't
prevent the VBA compiler from converting it into op codes and trying to run the
code. Dealing with such errors during run time requires using error trapping and
a lot of Err.Number checking. Easier & quicker to preceed all assignments with a
VarType check, and if that were done, variant type variables would be as safe as
specifically typed variables.

And another. I don't dismiss it; I just think users can decide for
themselves how much weight to give it in their particular applications.

Fine. Can you answer a simple question: under what circumstances does it make
sense to use Assign? It's fine & dandy that you say others can make up their
minds for themselves, but *YOU* must have had some intended situations in mind
when you wrote Assign, didn't you?

You've mentioned in other threads in the past that Assign could be used to
assign ranges to arrays of particular types. Since each cell in a range could
contain strings, doubles, booleans, dates or error values, assigning ranges to
arrays of particular types is likely to choke whenever an unexpected type that
can't be converted is encountered. For ranges, this wouldn't be a completely
unexpected contingency. Your Assign function uses error trapping to catch such
exceptions, and when they occur, Assign immediately returns False to indicate
failure (otherwise, when successful, it returns True). It's indicative how few
of your suggested uses of Assign in the ng check its return value. Since Assign
partially sets InputArray, without checking the return code, it's impossible to
tell whether InputArray is set correctly.

 

[Harlan Grove]

Harlan Grove wrote: ....

And another. I don't dismiss it; I just think users can decide for
themselves how much weight to give it in their particular
applications.

Users can decide for themselves.

Remember my ct function from a few months ago? I've revised it a bit.


Function ct( _
ByRef a As Variant, _
ByRef b As Variant, _
Optional c As Boolean = False _
) As Boolean
'------------------------------------------------------------------
'c argument controls whether proc continues to initialize b from a
'when any conversion errors occur: True = continue, False = return
'immediately -- return value True means conversion error(s) occurred,
'False means success
'-------------------------------------------------------------------
Dim i As Long, n As Long
Dim i1 As Long, i2 As Long, i3 As Long
Dim i4 As Long, i5 As Long, i6 As Long
Dim u1 As Long, u2 As Long, u3 As Long
Dim u4 As Long, u5 As Long, u6 As Long

'truncate range initializers
If TypeOf a Is Range Then a = a.Areas(1).Value

If Not (IsArray(a) Or IsArray(b)) Then 'both scalars -- FIXED!
'leave type conversion to VBA, let b obj but a not throw an error,
'let a obj but b not rely upon default obj to scalar conversion
On Error Resume Next
If IsObject(b) Then Set b = a Else b = a
ct = (Err.Number <> 0)
On Error GoTo 0

ElseIf IsArray(a) And IsArray(b) Then
'check that b is empty - necessary precondition
On Error Resume Next
u1 = UBound(b, 1)
If Err.Number = 0 Then Exit Function Else Err.Clear

'count number of dimensions of a while storing dim'n bounds
On Error GoTo EndCountDimensions

i1 = LBound(a, 1)
u1 = UBound(a, 1)
n = 1

i2 = LBound(a, 2)
u2 = UBound(a, 2)
n = 2

i3 = LBound(a, 3)
u3 = UBound(a, 3)
n = 3

i4 = LBound(a, 4)
u4 = UBound(a, 4)
n = 4

i5 = LBound(a, 5)
u5 = UBound(a, 5)
n = 5

i6 = LBound(a, 6)
u6 = UBound(a, 6)
n = 6

EndCountDimensions:
Err.Clear

On Error GoTo 0

Select Case n
Case 1:
ReDim b(i1 To u1)

For i1 = i1 To u1
If ct(a(i1), b(i1)) Then
ct = True
If Not c Then Exit Function
End If
Next i1

Case 2:
ReDim b(i1 To u1, i2 To u2)

For i1 = i1 To u1
For i2 = i2 To u2
If ct(a(i1, i2), b(i1, i2)) Then
ct = True
If Not c Then Exit Function
End If
Next i2
Next i1

Case 3:
ReDim b(i1 To u1, i2 To u2, i3 To u3)

For i1 = i1 To u1
For i2 = i2 To u2
For i3 = i3 To u3
If ct(a(i1, i2, i3), b(i1, i2, i3)) Then
ct = True
If Not c Then Exit Function
End If
Next i3
Next i2
Next i1

Case 4:
ReDim b(i1 To u1, i2 To u2, i3 To u3, i4 To u4)

For i1 = i1 To u1
For i2 = i2 To u2
For i3 = i3 To u3
For i4 = i4 To u4
If ct(a(i1, i2, i3, i4), _
b(i1, i2, i3, i4)) Then
ct = True
If Not c Then Exit Function
End If
Next i4
Next i3
Next i2
Next i1

Case 5:
ReDim b(i1 To u1, i2 To u2, i3 To u3, i4 To u4, _
i5 To u5)

For i1 = i1 To u1
For i2 = i2 To u2
For i3 = i3 To u3
For i4 = i4 To u4
For i5 = i5 To u5
If ct(a(i1, i2, i3, i4, i5), _
b(i1, i2, i3, i4, i5)) Then
ct = True
If Not c Then Exit Function
End If
Next i5
Next i4
Next i3
Next i2
Next i1

Case 6:
ReDim b(i1 To u1, i2 To u2, i3 To u3, i4 To u4, _
i5 To u5, i6 To u6)

For i1 = i1 To u1
For i2 = i2 To u2
For i3 = i3 To u3
For i4 = i4 To u4
For i5 = i5 To u5
For i6 = i6 To u6
If ct(a(i1, i2, i3, i4, i5, i6), _
b(i1, i2, i3, i4, i5, i6)) Then
ct = True
If Not c Then Exit Function
End If
Next i6
Next i5
Next i4
Next i3
Next i2
Next i1

Case Else 'unsupported number of dim'ns
ct = True
Exit Function

End Select

Else 'unsupported mixed references
ct = True

End If

End Function


I put it into your ArrayFunctions library along with the following testing
macro.


Sub testemhard()
Const MAXITER As Long = 2000000

Dim arr1() As String, arr2() As Variant, arr3 As Variant
Dim k As Long
Dim t As Long

t = Timer

For k = 1 To MAXITER
ReDim arr1(1 To 4)
arr1(1) = "ab"
arr1(2) = "cd"
arr1(3) = "ef"
arr1(4) = "gh"

ReDim Preserve arr1(1 To 5)
arr1(5) = "ij"
Next k

Debug.Print "test1: " & Format$(Timer - t, "##0.00");
For k = LBound(arr1) To UBound(arr1)
Debug.Print " "; arr1(k);
Next k
Debug.Print ""


t = Timer

For k = 1 To MAXITER
ReDim arr2(1 To 4)
arr2(1) = "ab"
arr2(2) = "cd"
arr2(3) = "ef"
arr2(4) = "gh"

ReDim Preserve arr2(1 To 5)
arr2(5) = "ij"
Next k

Debug.Print "test2: " & Format$(Timer - t, "##0.00");
For k = LBound(arr1) To UBound(arr1)
Debug.Print " "; arr1(k);
Next k
Debug.Print ""


t = Timer

For k = 1 To MAXITER
ReDim arr3(1 To 4)
arr3(1) = "ab"
arr3(2) = "cd"
arr3(3) = "ef"
arr3(4) = "gh"

ReDim Preserve arr3(1 To 5)
arr3(5) = "ij"
Next k

Debug.Print "test3: " & Format$(Timer - t, "##0.00");
For k = LBound(arr1) To UBound(arr1)
Debug.Print " "; arr1(k);
Next k
Debug.Print ""


Erase arr3

t = Timer

For k = 1 To MAXITER
arr3 = Array("ab", "cd", "ef", "gh")
ReDim Preserve arr3(4)
arr3(4) = "ij"
Next k

Debug.Print "test4: " & Format$(Timer - t, "##0.00");
For k = LBound(arr1) To UBound(arr1)
Debug.Print " "; arr1(k);
Next k
Debug.Print ""


Erase arr1

t = Timer

For k = 1 To MAXITER
ct Array("ab", "cd", "ef", "gh"), arr1
ReDim Preserve arr1(4)
arr1(4) = "ij"
Next k

Debug.Print "test5: " & Format$(Timer - t, "##0.00");
For k = LBound(arr1) To UBound(arr1)
Debug.Print " "; arr1(k);
Next k
Debug.Print ""


Erase arr1

t = Timer

For k = 1 To (MAXITER / 10) '** iterating 10% as many times! **
Assign Array("ab", "cd", "ef", "gh"), arr1
ReDim Preserve arr1(4)
arr1(4) = "ij"
Next k

Debug.Print "test6: " & Format$(Timer - t, "##0.00");
For k = LBound(arr1) To UBound(arr1)
Debug.Print " "; arr1(k);
Next k
Debug.Print ""

End Sub


and this testing macro gave the following results.

test1: 10.98 ab cd ef gh ij
test2: 11.68 ab cd ef gh ij
test3: 15.44 ab cd ef gh ij
test4: 28.56 ab cd ef gh ij
test5: 28.18 ab cd ef gh ij
test6: 34.78 ab cd ef gh ij

which means that ct appears to run more than 11 times faster than Assign,
and ct appears comparable to assigning arrays to scalar variants
(surprising!). Explicit item-by-item initialization to arrays of specific
type is still fastest, though. But you can ponder whether Assign should be
as slow as it appears to be.

 

[Alan Beban]

Users can decide for themselves.

Remember my ct function from a few months ago? I've revised it a bit. . . .

I think it was named rta or rtoa. I haven't played with ct yet, but did
note that, unlike Assign, it doesn't handle multi-area input ranges.

Alan Beban

 

[Alan Beban]

. . .
Remember my ct function from a few months ago? I've revised it a bit.

With non-zero integers in A18

Dim arr() As Integer
ct Range("a1:d8"), arr
Debug.Print ct(Range("a1:d8"), arr)
4: Debug.print arr(4, 2)

prints False
0

If the first line of ct is changed to

If TypeOf a Is Range Then Set a = a.Areas(1),

the above prints True and returns a Subscript out of range error for line 4.

Alan Beban

 

[Harlan Grove]

I think it was named rta or rtoa. I haven't played with ct yet,
but did note that, unlike Assign, it doesn't handle multi-area
input ranges.

Assign seems to assign multiple area ranges to 1D arrays rather than to
sparse 3D arrays, so the structure of the areas within the ranges is lost.
Myself, I see as little point to supporting multiple area ranges as
hierarchical arrays, which are always arrays of variants at the highest
level.

 

[Harlan Grove]

With non-zero integers in A18

Dim arr() As Integer
ct Range("a1:d8"), arr
Debug.Print ct(Range("a1:d8"), arr)
4: Debug.print arr(4, 2)

prints False
0

....

Yup. I was too sparing with local variables. Your Assign has the proper
amount. Here's ct with this problem fixed. Note: it still doesn't support
multiple area range. It truncates such ranges to their first area, just the
same as

Dim x As Variant
x = Range("A1:B20,C3150")

If users wanted multiple area ranges converted to single area ranges of
specific types, they could use

chk = Assign(MakeArray(MultipleAreaRange), arr)


Function ct( _
ByRef a As Variant, _
ByRef b As Variant, _
Optional c As Boolean = False _
) As Boolean
'------------------------------------------------------------------
'c argument controls whether proc continues to initialize b from a
'when any conversion errors occur: True = continue, False = return
'immediately -- return value True means conversion error(s) occurred,
'False means success
'-------------------------------------------------------------------
Dim n As Long
Dim i1 As Long, i2 As Long, i3 As Long
Dim i4 As Long, i5 As Long, i6 As Long
Dim lb1 As Long, lb2 As Long, lb3 As Long
Dim lb4 As Long, lb5 As Long, lb6 As Long
Dim ub1 As Long, ub2 As Long, ub3 As Long
Dim ub4 As Long, ub5 As Long, ub6 As Long

'truncate range initializers
If TypeOf a Is Range Then a = a.Areas(1).Value

If Not (IsArray(a) Or IsArray(b)) Then 'both scalars -- FIXED!
'leave type conversion to VBA, let b obj but a not throw an error,
'let a obj but b not rely upon default obj to scalar conversion
On Error Resume Next
If IsObject(b) Then Set b = a Else b = a
ct = (Err.Number <> 0)
On Error GoTo 0

ElseIf IsArray(a) And IsArray(b) Then
'check that b is empty - necessary precondition
On Error Resume Next
ub1 = UBound(b, 1)
If Err.Number = 0 Then Exit Function Else Err.Clear

'count number of dimensions of a while storing dim'n bounds
On Error GoTo EndCountDimensions

lb1 = LBound(a, 1)
ub1 = UBound(a, 1)
n = 1

lb2 = LBound(a, 2)
ub2 = UBound(a, 2)
n = 2

lb3 = LBound(a, 3)
ub3 = UBound(a, 3)
n = 3

lb4 = LBound(a, 4)
ub4 = UBound(a, 4)
n = 4

lb5 = LBound(a, 5)
ub5 = UBound(a, 5)
n = 5

lb6 = LBound(a, 6)
ub6 = UBound(a, 6)
n = 6

EndCountDimensions:
Err.Clear

On Error GoTo 0

Select Case n
Case 1:
ReDim b(lb1 To ub1)

For i1 = lb1 To ub1
If ct(a(i1), b(i1)) Then
ct = True
If Not c Then Exit Function
End If
Next i1

Case 2:
ReDim b(lb1 To ub1, lb2 To ub2)

For i1 = lb1 To ub1
For i2 = lb2 To ub2
If ct(a(i1, i2), b(i1, i2)) Then
ct = True
If Not c Then Exit Function
End If
Next i2
Next i1

Case 3:
ReDim b(lb1 To ub1, lb2 To ub2, lb3 To ub3)

For i1 = lb1 To ub1
For i2 = lb2 To ub2
For i3 = lb3 To ub3
If ct(a(i1, i2, i3), b(i1, i2, i3)) Then
ct = True
If Not c Then Exit Function
End If
Next i3
Next i2
Next i1

Case 4:
ReDim b(lb1 To ub1, lb2 To ub2, lb3 To ub3, _
lb4 To ub4)

For i1 = lb1 To ub1
For i2 = lb2 To ub2
For i3 = lb3 To ub3
For i4 = lb4 To ub4
If ct(a(i1, i2, i3, i4), _
b(i1, i2, i3, i4)) Then
ct = True
If Not c Then Exit Function
End If
Next i4
Next i3
Next i2
Next i1

Case 5:
ReDim b(lb1 To ub1, lb2 To ub2, lb3 To ub3, _
lb4 To ub4, lb5 To ub5)

For i1 = lb1 To ub1
For i2 = lb2 To ub2
For i3 = lb3 To ub3
For i4 = lb4 To ub4
For i5 = lb5 To ub5
If ct(a(i1, i2, i3, i4, i5), _
b(i1, i2, i3, i4, i5)) Then
ct = True
If Not c Then Exit Function
End If
Next i5
Next i4
Next i3
Next i2
Next i1

Case 6:
ReDim b(lb1 To ub1, lb2 To ub2, lb3 To ub3, _
lb4 To ub4, lb5 To ub5, lb6 To ub6)

For i1 = lb1 To ub1
For i2 = lb2 To ub2
For i3 = lb3 To ub3
For i4 = lb4 To ub4
For i5 = lb5 To ub5
For i6 = lb6 To ub6
If ct(a(i1, i2, i3, i4, i5, i6), _
b(i1, i2, i3, i4, i5, i6)) Then
ct = True
If Not c Then Exit Function
End If
Next i6
Next i5
Next i4
Next i3
Next i2
Next i1

Case Else 'unsupported number of dim'ns
ct = True

End Select

Else 'unsupported mixed references
ct = True

End If

End Function


I've also done some testing with Assign. The ArrayDimensions call and
running the entire function with error trapping enabled appear to be the
main causes of slower execution time.

 

[Alan Beban]

. . .
If users wanted multiple area ranges converted to single area ranges of
specific types, they could use

chk = Assign(MakeArray(MultipleAreaRange), arr) . . .

Or simply Assign MultipleAreaRange, arr

Perhaps you meant
chk = ct(MakeArray(MultipleAreaRange, 1), arr)

. . . I've also done some testing with Assign. The ArrayDimensions call and
running the entire function with error trapping enabled appear to be the
main causes of slower execution time. . . .

Thanks,
Alan Beban

 

[Alan Beban]

. . .
I've also done some testing with Assign. The ArrayDimensions call and
running the entire function with error trapping enabled appear to be the
main causes of slower execution time. . . .

I substituted out the ArrayDimensions call, replacing it with the structure

On Error GoTo EndDims

lb1 = LBound(InputRange,1)
ub1 = UBound(InputRange,1)
NumDims = 1

lb2 = LBound(InputRange,2)
ub2 = UBound(InputRange,2)
NumDims = 2
..
..
..
EndDims: Err.Clear
On Error GoTo 0

and turned off all other On Error commands. It still ran an order of
magnitude slower than ct (15 times slower).

I'll study some more the looping to load the array. I don't yet have my
head around the "flow diagram" implicit in ct.

Alan Beban

 

[Alan Beban]

. . .

What's happening with:

Dim arr() As Object, chk As Boolean
chk = ct(Range("A18"), arr)
Debug.Print chk' <---Prints False; what did the function do?

Ditto for

Dim arr() As Integer, chk As Boolean
chk = ct(Range("A18"), Range("A18"))
Debug.Print chk' <---Prints False; what did the function do?

Alan Beban

 

[Harlan Grove]

Harlan Grove wrote: ...
Or simply Assign MultipleAreaRange, arr

Perhaps you meant
chk = ct(MakeArray(MultipleAreaRange, 1), arr)

...

Either one. The point is that there's another, more explicit way to convert
multiple area ranges to 1D arrays than hiding the semantics in Assign or ct.

 

[Harlan Grove]

...
...

I substituted out the ArrayDimensions call, replacing it with the structure

On Error GoTo EndDims

lb1 = LBound(InputRange,1)
ub1 = UBound(InputRange,1)
NumDims = 1

...

Odd. This produced much faster results on my PC. Perhaps this is a case of
interaction between instructions, data objects and CPU cache.

I'll study some more the looping to load the array. I don't yet have my
head around the "flow diagram" implicit in ct.

Flow through a recursively called function?

 

[Harlan Grove]

What's happening with:

Dim arr() As Object, chk As Boolean
chk = ct(Range("A18"), arr)
Debug.Print chk' <---Prints False; what did the function do?

Prints True for me. Are you using the revision in


?

Ditto for

Dim arr() As Integer, chk As Boolean
chk = ct(Range("A18"), Range("A18"))
Debug.Print chk' <---Prints False; what did the function do?

The problem is in


On Error Resume Next
ub1 = UBound(b, 1)
If Err.Number = 0 Then Exit Function Else Err.Clear


In this case, b starts off as a range object, but the UBound call throws an
error. b isn't implicitly converted to b.Value in the function call. This is
something I hadn't anticipated). Also, if there were errors, ct wouldn't be set
to True, so two bugs. Replace this bit with


On Error Resume Next
ub1 = UBound(b, 1)
ct = (Err.Number <> 9) 'only subscript out of range error
If ct Then Exit Function Else Err.Clear

 

[Alan Beban]

...



Prints True for me. Are you using the revision in

news:[email protected]

I tried that link in Google and it said not found. I'm using the one you
posted May 10th at 1:58am. I went back and repasted it, tried again,
and it printed False. A18 has non-zero positive integers in it.

???
Alan Beban