Strange cell addresses behaviour for non-contiguou

问题:

I was trying to answer this question when I came across some bizarre VBA behaviour in Excel. I have written a very simple sub to demonstrate the issue:

Sub debugAddresses(rng As Range)
    Debug.Print "Whole range: " & rng.Address
    Dim i As Long
    For i = 1 To rng.Cells.Count
        Debug.Print rng.Cells(i).Address
    Next i
End Sub

I loop over each cell in a range object and print its address, simple right?

debugAddresses Range("B2:B3")  
' Result as expected: 
' >> Whole range: $B$2:$B$3
' >> $B$2
' >> $B$3

However, for non-contiguous ranges I get some strange behaviour:

debugAddresses Range("A1,B2")
' Strange behaviour when getting addresses of individual cells:
' >> Whole range: $A$1,$B$2
' >> $A$1
' >> $A$2

Can anyone shed any light on this please? Specifically why the Cells objects, which can be used for indexing of a contiguous range, seem to just extend the first selected Area.

---

Edit: It might be worth noting that using a For Each loop through the actual cell range objects gives the expected result*

Sub debugAddresses2(rng As Range)
    Debug.Print "Whole range: " & rng.Address
    Dim c As Range
    For Each c In rng
        Debug.Print c.Address
    Next c
End Sub

*^{See my answer for a comment on a more robust solution, as this (apparently) may not always give the expected result}

回答1:

Try using the modified Sub debugAddresses code below:

Sub debugAddresses(rng As Range)

    Dim RngA As Range
    Dim C As Range

    For Each RngA In rng.Areas
        For Each C In RngA.Cells
            Debug.Print C.Address
        Next C
    Next RngA

End Sub

回答2:

Here is your code "fixed". by just adding one more For Loop

Sub debugAddresses(rng As Range)

    Debug.Print "Whole range: " & rng.Address

    For Each r In rng ' this loops through the range even if separated cells

    Dim i As Long
        For i = 1 To r.Cells.Count 'changed to r instead of rng
            Debug.Print r.Cells(i).Address 'changed to r instead of rng
        Next i
    Next r

End Sub

So .Range works by entering the cell address ex. "B1", or by using R1C1 meaning row column ex. 1,2.

But you cant use just one R1C1 inside of .Range, since the range here is a span of cells. So to properly use R1C1 in .Range, you have to specify 2 of them.

So .Range("B5:B10") is equal to Range(Cells(5,2),Cells(10,2))

What you did was Specify a Range, then from that created another range using Cells. Very much like offset.

So Range("A1,B2") then adding Cells(1) then Cells(2) adds rows to the first range that is "A1" or offsets.

Sub selector()
Set Rng = Range("A1")
Rng.Select
Rng.Cells(4, 4).Select

End Sub

This offsets 4 colums and 4 rows from A1

回答3:

It appears that Florent's comment was in the correct direction, and that this method is extending the first Area within the range object.

In a contiguous range (e.g. "A1:B5", "C10:C100") the following method loops over each cell in the given range, rng.

Dim j As Long
For j = 1 To rng.Cells.Count
    Debug.Print rng.Cells(j).Address
Next j

However, in non-contiguous ranges it appears that this is equivalent (or shorthand for)

For j = 1 To rng.Cells.Count
    Debug.Print rng.Areas(1).Cells(j).Address
Next j

---

There doesn't appear to be any direct mention of this in the documentation but it is a sensible conclusion to draw by looking in the Locals browser of the VBA editor.

In the range object rng, there is a Cells property which only contains one "Item", which is the first Area. So it's reasonable to assume this one item is what .Cells(j) has access to.

In rng we can also see the Areas property, which contains 2 items (in this example) equal to the number of Areas in my non-contiguous range.

---

So rng.Cells(j) is accessing the jth element within the first area of rng. Because .Cells() can extend past the original size of rng, we see the addresses listed of cells outside rng.

---

The solution(s):

• Either ensure you directly loop through the range objects within rng using a For Each loop as shown in the question.
• Or loop over each area, and then each cell within that area.

The first option is more concise, but Shai points out that to be completely sure, the most robust method is to do the two For Each loops as there may be more complicated edge cases which aren't captured with the single loop.