在VBA中，“行"属性具有奇怪的行为 [英] In VBA the Rows property has a weird behavior

问题描述

我试图弄清楚如何在很大范围内的特定行上工作.但是，似乎用rows属性创建的范围的行为与简单范围不同.检查以下代码，首次定义变量SpecificRow时，无法选择特定的单元格.但是，通过重新定义范围的怪异解决方法，它可以正常工作.您是否知道为什么以及如何以更优雅的方式定义范围?

I am trying to figure out how to work on a specific row among a big range. However it appears that a range created with the rows property does not behave the same as a simple range. Check the following code, the first time the variable SpecificRow is defined, it is not possible to select a specific cell. However with a weird workaround that redefines the range, it works fine. Do you have an idea why and how could I define the range with a more elegant way?

'The following shows the weird behavior of Rows property
Dim SourceRng As Range
Dim SpecificRow As Range
Dim i As Long

i = 3

Set SourceRng = Range("A1:D20")
Set SpecificRow = SourceRng.Rows(i)

'This will show the address of the selected row ("A3:D3")
MsgBox SpecificRow.Address

'Unexplicable behavior of the range when trying to select a specific cell
'where it will instead consider the whole row (within the limits of SourceRng)
MsgBox SpecificRow(1).Address
MsgBox SpecificRow(2).Address

'This would send an error
'MsgBox SpecificRow(1, 1).Address

'Workaround
Set SpecificRow = Intersect(SpecificRow, SpecificRow)

'The following will select the same address than before
MsgBox SpecificRow.Address

'However, now it has a correct behavior when selecting a specific cell
MsgBox SpecificRow(1).Address
MsgBox SpecificRow(2).Address

推荐答案

我找不到关于此的任何适当文档，但是这种观察到的行为实际上似乎非常合乎逻辑.

I cannot find any proper documentation on this, but this observed behaviour actually appears to be very logical.

Excel中的Range类具有两个重要属性:

The Range class in Excel has two important properties:

• Range的单个实例足以代表工作表上的任何可能范围
• 这是可迭代的(可以在For Each循环中使用)

• A single instance of Range is enough to represent any possible range on a sheet
• It is iterable (can be used in a For Each loop)

我相信为了实现逻辑上可观的可迭代性并避免创建不必要的实体(例如，单独的类，如CellsCollection，RowsCollection和ColumnsCollection)，Excel开发人员想出了一种设计，其中每个Range实例都具有一个private属性，该属性告诉它要以哪种单位进行计数(因此，一个范围可以是行集合"，而另一个范围可以是单元格集合" ).

I believe that in order to achieve logically looking iterability and yet avoid creating unnecessary entities (i.e. separate classes like CellsCollection, RowsCollection and ColumnsCollection), the Excel developers came up with a design where each instance of Range holds a private property that tells it in which units it is going to count itself (so that one range could be "a collection of rows" and another range could be "a collection of cells").

通过Rows属性创建范围时，此属性设置为(例如)"rows"；通过Columns属性创建范围时，此属性设置为(例如)"columns" "cells"以其他任何方式创建范围时.

This property is set to (say) "rows" when you create a range via the Rows property, to (say) "columns" when you create a range via the Columns property, and to (say) "cells" when you create a range in any other way.

这使您可以做到这一点，而不会感到不必要的惊讶:

This allows you to do this and not become unnecessarily surprised:

For Each r In SomeRange.Rows
  ' will iterate through rows
Next

For Each c In SomeRange.Columns
  ' will iterate through columns
Next

Rows和Columns都返回相同的类型Range，该类型引用完全相同的工作表区域，但是For Each循环在第一种情况下通过行进行迭代，而在第二种情况下通过列进行迭代，就像Rows和Columns返回两种不同的类型(RowsCollection和ColumnsCollection)一样.

Both Rows and Columns here return the same type, Range, that refers to the exactly same sheet area, and yet the For Each loop iterates via rows in the first case and via columns in the second, as if Rows and Columns returned two different types (RowsCollection and ColumnsCollection).

以这种方式进行设计是有道理的，因为For Each循环的重要属性是它无法为Range对象提供多个参数以获取下一项(单元格，行或列) ).实际上，For Each根本不能提供任何参数，它只能询问请输入下一个".

It makes sense that it was designed this way, because the important property of a For Each loop is that it cannot provide multiple parameters to a Range object in order to fetch the next item (cell, row, or column). In fact, For Each cannot provide any parameters at all, it can only ask "Next one please."

为了支持这一点，即使范围是二维的并且需要两个坐标来获取某物"，Range类也必须能够给出不带参数的下一个某物".这就是为什么Range的每个实例都必须记住自己将以什么单位计数的原因.

To support that, the Range class had to be able to give the next "something" without parameters, even though a range is two-dimensional and needs two coordinates to fetch the "something." Which is why each instance of Range has to remember in what units it will be counting itself.

该设计的一个副作用是，在仅提供一个坐标的Range中查找某物"非常好.这正是For Each机制所要做的，我们只是直接跳到了i项.
当遍历(或索引到)Rows返回的范围时，我们将从上到下获得第i行；对于Columns返回的范围，我们从左到右获得第i列；对于Cells或通过任何其他方法返回的范围，我们将获得第i个单元格，从左上角到右侧再到底部.

A side effect of that design is that it is perfectly fine to look up "somethings" in a Range providing only one coordinate. This is exactly what the For Each mechanism would do, we are just directly jumping to the ith item.
When iterating over (or indexing into) a range returned by Rows, we're going to get the ith row, from top to bottom; for a range returned by Columns we're getting the ith column, from left to right; and for a range returned by Cells or by any other method we're going to get the ith cell, counting from top left corner to the right and then to the bottom.

该设计的另一个副作用是可以以有意义的方式跳出"范围.也就是说，如果您有三个单元格的范围，而您要求第四个单元格，您仍然会得到它，它将是范围的形状和它自己计算的单位所决定的单元格:

Another side effect of this design is that can "step out" of a range in a meaningful way. That is, if you have a range of three cells, and you ask for the 4th cell, you still get it, and it will be the cell dictated by the shape of the range and the units it's counting itself in:

Dim r As Range
Set r = Range("A1:C3")          ' Contains 9 cells

Debug.Print r.Cells(12).Address ' $C$4 - goes outside of the range but maintains its shape

---

因此，您对Set SpecificRow = Intersect(SpecificRow, SpecificRow)的变通办法会将特定的Range实例的内部计数模式从(例如)"rows"重置为(例如)"cells".

---

So your workaround of Set SpecificRow = Intersect(SpecificRow, SpecificRow) resets the internal counting mode of that specific Range instance from (say) "rows" to (say) "cells".

您本可以通过

Set SpecificRow = SpecificRow.Cells
MsgBox SpecificRow(1).Address

但是最好使Cells靠近使用点，而不是范围创建点:

But it's better to keep the Cells close to the point of usage rather than the point of range creation:

MsgBox SpecificRow.Cells(1).Address

这篇关于在VBA中，“行"属性具有奇怪的行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！