Declare and use range in vba

Question

I am quite new to VBA,
Today developing a macro I noticed something funny.

Using Range like this is working :

Dim rg As Range     
Set rg = ActiveSheet.Range("A1:B2")

Using Range like this does not work and result in error "Object variable not set" :

Dim rg As Range   
rg = ActiveSheet.Range("A1:B2")

but using Range like this is working :

Dim rg,rg2 As Range  
rg = ActiveSheet.Range("A1:B2")

How is it possible?

I believe you'll find that it doesn't "really" work it just doesn't trigger the compiler error when compiling. For example, add `Debug.Print rg.Address` at the end and it will only work when you use `Set` — Automate This, Dec 10 '14 at 14:58
Almost a duplicate of http://stackoverflow.com/a/17877644/11683, with the addition of http://stackoverflow.com/a/19234210/11683. — GSerg, Dec 10 '14 at 15:00

score 11 · Accepted Answer · answered Dec 10 '14 at 15:01

11

You are discovering Variant and object references.

A Range is an object - a Variant can be anything including an object.

This is the correct way to go about it:

Dim rg As Range     
Set rg = ActiveSheet.Range("A1:B2")

Because:

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

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

And VBA will happily let you assign a Variant with just about anything... but things will blow up at run-time.

answered Dec 10 '14 at 15:01

Mathieu Guindon

3

+ 1 - To confirm your reasoning here is a link to cpearson, scroll to [Pay Attention To Variables Declared With One Dim Statement](http://www.cpearson.com/excel/declaringvariables.aspx) – Automate This Dec 10 '14 at 15:06
2

May be everybody knows it but anyway ... if ```rg``` is declared as ```Variant``` then ```rg = ActiveSheet.Range("A1:B2")``` assigns values of "A1:B2" to rg (no error occures). ```rg``` is then array of Varaints ```(1 to 2, 1 to 2)```. – Daniel Dušek Dec 10 '14 at 15:46
@retailcoder yeah it is fun :-). I checked it in object browser and ```Value``` is not the default property of ```Range``` ... at least in my Excel version which is 2007. http://stackoverflow.com/questions/17877536/vba-when-one-should-use-set-e-g-for-specialcells-return-value/17877644#17877644 – Daniel Dušek Dec 11 '14 at 08:46
@MathieuGuindon you are right, let me rephrase (I deleted my previous comment): multiple variables declaration (including the type definition) can be done in "the same instruction" using the correct syntax. I expanded on that in a [separate answer](https://stackoverflow.com/a/57942548/2089396). – Albin Sep 15 '19 at 22:02

score 2 · Answer 2 · edited Aug 18 '20 at 17:31

2

Expanding on Mathieu Guidon's answer:

If you want to specify two objects in the same instruction (one line), you should use the following syntax:

Dim rg as Range, rg2 As Range

This will correctly assign both rg and rg2 as a range object.

Using Dim rg, rg2 As Range, only rg2 is assigned as a range object (rg becomes a Variant), as Mathieu Guidon correctly explains.

edited Aug 18 '20 at 17:31

TinMan

answered Sep 15 '19 at 08:50

Albin

2 Answers2