CurrentArray broken in xl2003? xl2007?

[Joe User]

According to VBA Help, the CurrentArray property should behave as follows:
"If the specified cell is part of an array, returns a Range object that
represents the entire array".

And it does exactly that when it is used in the testit macro below.

But it does not seem to behave that way when it is used in the same way in
the funcit function below.

Is CurrentArray broken in my revision of Excel, namely Excel 2003
(11.5612.5606) and VBA 6.3 (9969, 6.4.8869), part of MS Office 2003 Sm Busn
Ed?

Does funcit() behave like testit() in some earlier or later revisions of
Excel/VBA, e.g. Excel 2007?

Is there some reasonable way to make CurrentArray work in funcit() in my
revision of Excel? Or is there an alternative to CurrentArray that I could
use for the same purpose?


Details....

Suppose A1:B2 contains some array formula. Suppose C1 contains the formula
=funcit().

Be sure that C1 (or any cell other than one of A1:B2) is selected in Excel.
This is to demonstrate that CurrentArray is not limited to ActiveCell, which
is used in the Help page.

Execute the testit macro. The output, copy-and-pasted from the Immediate
Window, is:

testit: addr $A$1:$B$2, hasArray True, currArray $A$1:$B$2
addr $A$1, hasArray True, currArray $A$1:$B$2
addr $B$1, hasArray True, currArray $A$1:$B$2
addr $A$2, hasArray True, currArray $A$1:$B$2
addr $B$2, hasArray True, currArray $A$1:$B$2

Note that CurrentArray for each cell is indeed the entire array range.

Now, with C1 selected, press F2 and Enter to execute the funcit function.
My output is:

funcit: callcnt 1, addr $A$1:$B$2, hasArray True, currArray $A$1:$B$2
addr $A$1, hasArray True, currArray $A$1
addr $B$1, hasArray True, currArray $B$1
addr $A$2, hasArray True, currArray $A$2
addr $B$2, hasArray True, currArray $B$2

Note that CurrentArray for each cell is __not__ the entire array range.


Option Explicit
Private callcnt As Long


Sub testit()
Dim cell As Range, myStr As String, myrng As Range
Set myrng = Range("a1:b2")
With myrng
myStr = "testit: addr " & .Address & _
", hasArray " & .HasArray & _
", currArray " & .CurrentArray.Address
End With
For Each cell In myrng
With cell
myStr = myStr & Chr(10) & "addr " & .Address & _
", hasArray " & .HasArray & _
", currArray " & .CurrentArray.Address
End With
Next cell
Debug.Print myStr
MsgBox myStr
End Sub


Function funcit()
Dim cell As Range, myStr As String, myrng As Range
callcnt = callcnt + 1
Set myrng = Range("a1:b2")
With myrng
myStr = "funcit: callcnt " & callcnt & _
", addr " & .Address & _
", hasArray " & .HasArray & _
", currArray " & .CurrentArray.Address
End With
For Each cell In myrng
With cell
myStr = myStr & Chr(10) & "addr " & .Address & _
", hasArray " & .HasArray & _
", currArray " & .CurrentArray.Address
End With
Next cell
Debug.Print myStr
MsgBox myStr
End Function