“Range”，“Range.Cells”，“Range.Rows”等之间的区别，“子类型” [英] Difference between `Range`, `Range.Cells`, `Range.Rows`, etc., "subtypes"
问题描述
编辑： Sub dump_range 下面显示地址不完全定义一个 Range （as far与其内容一样操作），我发现令人惊讶的，在MS文档中没有明确说明。还有别的东西，一个子类型。似乎无法通过 Count 间接查询子类型。这一点的实际意义在于，如果一个 Sub （或 Function ）定义一个 Range 作为参数，应该考虑到这一点，以编码 Sub （我个人没有做的），以避免潜在的错误。
Sub dump_range below shows that the address does not completely define a Range (as far as operating with its contents is concerned), something that I found surprising, and not clearly stated in MS documentation. There is something else, a "subtype". It appears that one cannot inquire about the "subtype", but only indirectly, via Count. The practical relevance of this point is that, if one defines a Sub (or Function) taking a Range as an argument, one should bear this in mind to code the Sub (something that I personally did not do) to avoid potential errors.
在代码中
Dim rng As Range, rng2 As Range, rng3 As Range
Set rng = Selection
Set rng2 = rng.Cells
Set rng3 = rng.Rows
（问题1 ）对象 rng ， rng2 等之间的区别是什么？
(Question 1) what are the differences between objects rng, rng2, etc.?
我不是仅仅用言语解释，而是根据指定什么方法/属性和结果可用于ea ch，并弥补亚型之间的差异。
应该有一些，因为VBA对它们有不同的作用。但是我没有找到规格。
实际上，对于Range.Cells属性的Excel帮助令我感到困惑：返回表示指定范围内的单元格的Range对象。它向我看，它应该返回与调用者相同的对象。
权威文件的链接将支持解释。
I do not mean to get it explained only in words, but in terms of specification of what methods/properties and results are available for each, and make up the difference among the "subtypes". There should be some, as VBA works differently on them. But I do not find the specification. Actually, the Excel help for the Range.Cells Property is confusing to me: "Returns a Range object that represents the cells in the specified range." It looks to me that it should return the same object as the caller. Links to authoritative documentation will support an explanation.
可以看到，使用
Call dump_range(rng) ' -> Range $A$1:$B$6, count = 12
Call dump_range(rng2) ' -> Range $A$1:$B$6, count = 12
Call dump_range(rng3) ' -> Range $A$1:$B$6, count = 6
与
Sub dump_range(ByRef rng As Range)
Debug.Print "Range " & rng.Address & ", count = " & rng.Count
End Sub
（问题2 ）
我还想知道是否可以测试一个 Range 对象，子类型是它。
例如，在不检查代码的情况下，如何辨别对象 rng 和 rng2 的子类型 ？
即可在运行时获取这些信息。
(Question 2)
I also want to know if one can test a Range object for which "subtype" it is.
E.g., how would one discern the "subtypes" of objects rng and rng2, without inspecting the code?
I.e., obtaining such information at run-time.
（问题3 ）
是否有一个规范的字子类型？
(Question 3) Is there a canonical word for "subtype"?
推荐答案
问题1）
相同的类或对象类型（Range Object）。
Question 1)
They are all the same class or object type (Range Object).
范围对象是可以是单元格，行或列等的其他事物的集合。其中每一个都是范围对象。
A range object is a collection of other "things" which can be cells, rows or columns etc. Each one of these is a range object.
例如，给定范围R：
R.cells是范围对象由所有单元格（也包括范围对象）组成，即{cell1，cell2，cell3，...}
R.cells is a range object made up off all the cells (also range objects) i.e. {cell1, cell2, cell3, …}
R.rows是由所有行（也是范围对象）即{row1，row2，row3，...} - >（类似地，row1是范围对象由所有单元格组成，即{row1Cell1，row1Cell2，...}）
R.rows is a range object made up of all the rows (also range objects) i.e. {row1, row2, row3, …} --> (similarly row1 is a range object is made up of all the cells i.e. {row1Cell1, row1Cell2, …})
并且选择等效于selection.cells，所以在你的例子中，rng相当于rng2。
And selection is equivalent to selection.cells, so in your example rng is equivalent to rng2.
再次，
行，单元格，列都是范围对象，因此它们都具有相同的属性/方法。您得到不同数量的原因不是因为它们是不同类型的具有不同属性/方法的对象。它们是相同类型的对象，但不是等效的，因此一些属性可能不同。
Rows, cells, columns are all range objects, so they all have the same properties/methods. The reason you are getting a different count is not because they are different types of objects with different properties/methods. They are the same type of object, but not equivalent and thus some properties may be different.
要查看完整列表或道具+方法，请参阅 here 。
To see a full list or props + methods, see here.
子类型在这个意义上不是正确的词。你的例子都是一样的，没有亚型。他们是不等同的。为了测试等同（在你所寻求的意义上），你可以循环遍历范围对象中的每个对象，并递归地测试每个对象的等同（比较地址字符串），直到你到达一个零的范围对象，请参见下文：
Subtype isn’t the correct word in this sense. Your examples are all the same type, there is no subtype. What they are is not equivalent. To test for "equivalence" (in the sense that you seek) you can loop through each object in the range object and recursively test equality (comparing the address strings) of every object until you get to a range object with a count of zero, see below:
'should return "Rows"
getRangeType(selection.Rows)
Function getRangeType(inputRange As Range) As String
If (testRangeEquality(inputRange, inputRange.Rows)) Then
getRangeType = "Rows"
Exit Function
End If
If (testRangeEquality(inputRange, inputRange.columns)) Then
getRangeType = "Columns"
Exit Function
End If
If (testRangeEquality(inputRange, inputRange.Cells)) Then
getRangeType = "Cells"
Exit Function
End If
End Function
Function testRangeLevelEquality(range1 As Range, range2 As Range) As Boolean
If (range1.Count <> range2.Count) Then
testRangeLevelEquality = False
Exit Function
End If
IsEqual = True
For i = 1 To range1.Count
If (range1(i).Address <> range2(i).Address) Then
IsEqual = False
End If
Next i
testRangeLevelEquality = IsEqual
End Function
Function testRangeEquality(range1 As Range, range2 As Range) As Boolean
Equality = True
If (testRangeLevelEquality(range1, range2)) Then
If (range1.Count = 1) Then
Equality = True
Else
For i = 1 To range1.Count
If (testRangeEquality(range1(i), range2(i)) = False) Then
Equality = False
Exit For
End If
Next i
End If
Else
Equality = False
End If
testRangeEquality = Equality
End Function
问题3）
再次，在这个意义上，没有一个子类型的概念，只是同一个类的单独的，非等价的实例。但是，我认为继承可能是您正在寻找的术语。
Question 3)
Again, there is not a notion of a subtype in this sense, just separate, nonequivalent instances of the same class. But, I think that inheritance may be the term you were looking for.
这篇关于“Range”，“Range.Cells”，“Range.Rows”等之间的区别，“子类型”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
