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屋!