Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CurrentArray broken in xl2003? xl2007?
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
XL2003 toolbar macros in XL2007 | Excel Programming | |||
XL2007 and XL2003 | Excel Discussion (Misc queries) | |||
Conditional formatting different on XL2007 than XL2003 | Excel Discussion (Misc queries) | |||
Upgrade options from XL2003/VBA/VB6 to XL2007/VB.NET ... | Excel Programming | |||
XL2007 vs XL2003 | Excel Programming |