在VBA中声明和使用范围 [英] Declare and use range in vba

问题描述

我对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")  

因为:

1. 您明确声明rg为Range对象.
2. 您正在使用Set关键字正确分配对象引用.

1. You're explicitly declaring rg as being a Range object.
2. 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中声明和使用范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！