Excel VBA - Different methods of declaring variables

Question

I'm trying to declare 7 variables as Ranges. I'm currently using this format

Dim Rg1 As Range
Dim Rg2 As Range
Dim Rg3 As Range
Dim Rg4 As Range
Dim Rg5 As Range
Dim Rg6 As Range
Dim Rg7 As Range

But i want to use this format

Dim Rg1, Rg2, Rg3, Rg4, Rg5, Rg6, Rg7 As Range

I thought there'd be no difference in this but apparently there is. Using the top format, the macro works perfectly. When i change and use the second format, i get the following error

Runtime error '424' Object Required

It makes this error when the range Rg4 is empty. It doesn't give an error when i use the first format though. Why?

You are declaring Rg1 through Rg6 as variants not ranges. The only variable that is being declared a range is the last one. You can do Dim Rg1 As Range, Rg2 As Range, ... — Scott Craner
– Scott Craner, Commented Nov 8, 2017 at 15:02
That is a curious error to get just from changing a variable declaration though. I suspect there is something wrong with your code, such as the omission of a Set statement. — Rory
– Rory, Commented Nov 8, 2017 at 15:30

user8753746 · Accepted Answer · 2017-11-08 15:06:25Z

2

If you declare your variables this way:

Dim Rg1, Rg2, Rg3, Rg4, Rg5, Rg6, Rg7 As Range

Only Rg7 will be Range, all others variables will be Variant If you want only one line, the only option will be

Dim Rg1 As Range, Rg2 As Range, Rg3 As Range, Rg4 As Range, Rg5 As Range, Rg6 As Range, Rg7 As Range

answered Nov 8, 2017 at 15:06

user8753746

Sign up to request clarification or add additional context in comments.

1 Comment

Pierre Over a year ago

precision: in VB.NET, both lines do the same, while VBA has this dumb behaviour

Vityata · Accepted Answer · 2017-11-08 15:23:24Z

Declaring all the values on one line works perfectly with VB.Net, but with VBA it is problematic. Thus, declaring Dim Rg1, Rg4, Rg7 as Range makes only the last declared value as Range and the others are Variant.

However, in your case, once you set the other Rg1 and Rg4 to ranges, they start acting like a range. E.g., you may ask for rg4.Address and you will get it, because the new Type is Variant/Object/Range and if you declare it explicitly it will be Range - see the VBA monitor for the types:

Furthermore, once you declare the values as Variant, they may change type easily. See the following code, where the types change from Integer to String once you redeclare it:

Public Sub TestMe()

    Dim rg1
    Dim rg2
    Dim bornRange   As Range

    Range("A1") = "TEST"

    Debug.Print VarType(rg1) = vbEmpty      'true
    Debug.Print VarType(rg2) = vbEmpty      'true

    Set rg1 = Range("A1")
    rg2 = 2

    Debug.Print VarType(rg1) = vbString     'true
    Debug.Print VarType(rg2) = vbInteger    'true

    Set rg2 = Range("A1")
    Debug.Print rg2.Address                 '$A$1

    Debug.Print VarType(rg2) = vbInteger    'false
    Debug.Print VarType(rg2) = vbString     'true    

End Sub

K.Dᴀᴠɪs · Accepted Answer · 2017-11-08 17:56:53Z

0

You could use an array of ranges, if you'd like:

Dim Rng(1 To 7) As Range

Set Rng(1) = ws.Range("A1")
Set Rng(2) = ws.Range("B1")
' Etc....

As others have stated, with your declaration:
Dim Rg1, Rg2, Rg3, Rg4, Rg5, Rg6, Rg7 As Range
Rg1-6 are type Variant, while Rg7 is type Range

answered Nov 8, 2017 at 17:56

K.Dᴀᴠɪs

10.1k13 gold badges36 silver badges46 bronze badges

Collectives™ on Stack Overflow

Excel VBA - Different methods of declaring variables

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related