问题描述
我对VBA还是很陌生,
今天开发一个宏,我注意到一些有趣的事情.
I am quite new to VBA,
Today developing a macro I noticed something funny.
像这样使用Range
可以正常工作:
Using Range
like this is working :
Dim rg As Range
Set rg = ActiveSheet.Range("A1:B2")
像这样使用Range
无效,并导致错误未设置对象变量":
Using Range
like this does not work and result in error "Object variable not set" :
Dim rg As Range
rg = ActiveSheet.Range("A1:B2")
,但是像这样使用Range
可以正常工作:
but using Range
like this is working :
Dim rg,rg2 As Range
rg = ActiveSheet.Range("A1:B2")
怎么可能?
推荐答案
您正在发现Variant
和对象引用.
You are discovering Variant
and object references.
Range
是对象-Variant
可以是任何 包括对象.
这是正确的解决方法:
Dim rg As Range
Set rg = ActiveSheet.Range("A1:B2")
因为:
- 您明确声明
rg
为Range
对象. - 您正在使用
Set
关键字正确分配对象引用.
- You're explicitly declaring
rg
as being aRange
object. - You're correctly assigning the object reference with the
Set
keyword.
如果您不使用Set
关键字,则使用VBA语法为值分配分配对象引用,这是一个错误:
If you don't specity the Set
keyword, you're assigning an object reference using the VBA syntax for values assignments, and that's an error:
rg = ActiveSheet.Range("A1:B2")
如果在同一条指令中声明多个变量,并且仅为最后一个变量指定类型,则rg
在此处为Variant
:
If you declare multiple variables in the same instruction, and only specify a type for the last one, then rg
is a Variant
here:
Dim rg,rg2 As Range ' this is like doing Dim rg As Variant, rg2 As Range
rg = ActiveSheet.Range("A1:B2")
VBA会很高兴地让您为Variant
分配几乎所有 ...但是事情会在运行时崩溃.
And VBA will happily let you assign a Variant
with just about anything... but things will blow up at run-time.
这篇关于在VBA中声明和使用范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!