奇怪的单元格地址行为为不连续的范围：VBA [英] Strange cell addresses behaviour for non-contiguous ranges: VBA

问题描述

当我遇到一些奇怪的VBA行为时，我正在尝试回答

在 rng 中，我们还可以看到 Areas 属性，其中包含2个项目（在本示例中）等于我的非连续范围内 Areas 的数量。

---

所以 rng.Cells（j）正在访问的第一个区域内的 j RNG 。因为 .Cells（）可以延伸超过 rng 的原始大小，我们看到 RNG 。

---

解决方案：

• 确保您直接循环使用 rng 中的范围对象，使用 For Each 循环，如题。


• 或循环遍历每个区域，然后循环该区域内的每个单元格。

第一个选项更简洁，但是Shai指出要完全确定，最强大的方法是做两个对于每个循环，因为可能会出现更复杂的边缘情况，而不是单循环捕获。

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}

解决方案

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.

这篇关于奇怪的单元格地址行为为不连续的范围：VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！