Worksheet.Columns属性要求我提供RowIndex吗? [英] Worksheet.Columns property asks me for a RowIndex?
问题描述
如果我在VBA编辑器中编写此代码:
If I write this in the VBA editor:
Dim ws As Worksheet: set ws = ActiveSheet
ws.Columns(
IntelliSense 向我展示了一个看似无关的工具提示:
IntelliSense shows me a seemingly unrelated tooltip:
_Default( [RowIndex] ，[ColumnIndex])
_Default([RowIndex], [ColumnIndex])
据我在那我为什么要问一个 RowIndex ?为什么它指的是 _Default (以及它是什么)?
So why am I asked for a RowIndex? Why does it refers to _Default (and what is it)?
推荐答案
据我所知，Worksheet.Columns属性仅接受索引(列)号.
The Worksheet.Columns property only accepts the index (column) number as far as I can see in the documentation.
在文档中的任何地方都没有说 Columns 属性带有参数，实际上，提及它是错误的，因为它没有任何参数:
Nowhere in the documentation does it say the Columns property takes a parameter, and indeed, it would be wrong to mention that, because it doesn't have any:
类似于 Worksheet.Rows ， Worksheet.Columns 会产生一个 Range 对象.因此，当您对其进行参数化"时，实际上是这样的:
Like Worksheet.Rows, Worksheet.Columns yields a Range object. So when you "parameterize" it, what's really happening is this:
Set foo = ws.Columns.[_Default](value)
您提供的任何参数都会被解释为针对 Range 对象的隐式默认成员调用的参数，该对象通过对 Columns 返回>.
Any argument you provide, get interpreted as arguments to an implicit default member call against the Range object that was returned by the call to Columns.
您可能已经读到某个地方，其中 Range 的默认成员是其 Value -这是不正确的. Range 的默认成员是一个名为 [_ Default] 的隐藏属性(如果要显式调用它，则在VBA中必须使用方括号，因为不能以合法的VBA标识符开头带有下划线)，它带有两个可选参数:
You may have read somewhere that the default member of a Range is its Value - and that is not true. The default member of a Range is a hidden property named [_Default] (square brackets are required in VBA if you want to invoke it explicitly, because no legal VBA identifier can begin with an underscore), that takes two optional parameters:
当您在不提供任何参数的情况下读取(获取")此默认属性时，此默认属性会为您提供 Range.Value (即单个单个单元格的变体值，或多个单元格的2D Variant 数组).分配给该默认属性时，您是分配 Range.Value .
When you read ("get") this default property without providing any arguments, this default property does get you the Range.Value (i.e. a single Variant value for a single cell, or a 2D Variant array for multiple cells). When you assign to this default property, you are assigning the Range.Value.
但是，当在读取(获取")此默认属性时提供任何参数时，您得到的是对非常标准的 Range.Item 索引器属性的调用:
But when any arguments are provided when reading ("get") this default property, what you get is a call to the very standard Range.Item indexer property:
那么 Columns 的作用就是简单地输入您的输入范围，并生成一个 Range 对象，其布局方式使得可以使用进行访问RowIndex 参数-我们可以使用命名参数来证明这一点，这表明该代码是非法的:
So what Columns does, is simply take your input range, and yield a Range object laid out in such a way that it can be accessed using a RowIndex argument - we can prove this using named arguments, which show that this code is illegal:
?Sheet1.Range("A1:C1").Columns.Item(ColumnIndex:=2).Address
>> "wrong number of arguments"
这是等效的代码:
?Sheet1.Range("A1:C1").Columns(ColumnIndex:=2).Address
>> "error 1004"
请注意， _Default 属性产生一个 Variant ，因此上述 .Address 成员调用只能在运行时解决(并且您不会获得任何智能感知，即使指定了 Option Explicit ，编译器也不会出现任何错字-尽管您在运行时 会收到错误438).
Note that the _Default property yields a Variant, so the above .Address member call can only be resolved at run-time (and you don't get any intellisense for it, and the compiler will not flinch at any typo, even with Option Explicit specified - you will get error 438 at run-time though).
最好坚持安全的早期绑定土地，然后将返回的对象引用拉入局部变量:
Best stick to safe early-bound land, and pull the returned object reference into a local variable:
Dim foo As Range
Set foo = ws.Columns(1)
Debug.Print foo.Address '<~ early-bound w/intellisense & compile-time validation
TL; DR:系统会提示您输入 RowIndex 参数，因为您正在进行调用(尽管是隐式)隐藏的 _Default 属性，该属性接受 RowIndex 参数.
TL;DR: You're being prompted for a RowIndex argument because you are making a call (albeit an implicit one) to a hidden _Default property that accepts a RowIndex argument.
这篇关于Worksheet.Columns属性要求我提供RowIndex吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
