PC Review


Reply
Thread Tools Rate Thread

CurrentArray broken in xl2003? xl2007?

 
 
Joe User
Guest
Posts: n/a
 
      4th Nov 2009
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

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
XL2007 and XL2003 LeeCC Microsoft Excel Misc 2 18th Dec 2008 04:44 PM
Conditional formatting different on XL2007 than XL2003 Stan Microsoft Excel Misc 0 16th Jul 2008 06:24 PM
Opening XLS with XL2003, not XL2007 jg70124@gmail.com Microsoft Excel Discussion 2 21st Sep 2007 01:34 AM
XL2007 vs XL2003 =?Utf-8?B?QXJ0?= Microsoft Excel Programming 13 21st Apr 2007 04:32 PM
XL2003 inconsistency fixed in XL2007? joeu2004 Microsoft Excel Worksheet Functions 2 5th Mar 2007 05:59 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 08:52 AM.