Excel-VBA: Variable declaration necessary?

Question

Would it be wrong if write the following code

Sub Something()
 Dim i As integer
 Dim xRange As Range
 Dim yRange As Range

 Set xRange= Range("x_table")
 Set yRange= Range("y_table")

 For i = 1 To xRange.Columns.Count
    xRange.Columns(i) = Application.Sum(y_table.Columns(i))
 Next i
End Sub

without specifically declaring each of the variables? Like bellow;

Sub Something()
 Set xRange= Range("x_table")
 Set yRange= Range("y_table")

 For i = 1 To xRange.Columns.Count
    xRange.Columns(i) = Application.Sum(y_table.Columns(i))
 Next i
End Sub

NoAlias · Accepted Answer · 2010-11-17 14:52:59Z

14

If Option Explicit isn't turned on you can do it that way, but I wouldn't recommend it because then you're relying on the framework to guess at the type of variable it is dealing with, which could cause unexpected results.

answered Nov 17, 2010 at 14:52

NoAlias

9,2032 gold badges30 silver badges46 bronze badges

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

9 Comments

Carlos Over a year ago

@DaMartyr, thanks! if you will be a uni professor, will you mark me down for not declaring?

MikeD Over a year ago

start typing "xrange." and watch the methods and properties expanding on the dot in your 1st example while it does not in your 2nd example - this will give you help and confidence when you code - that's why I always qualify my objects first. There is hardly any case where you must use late binding

Dr. belisarius Over a year ago

Yep. It is a bad practice. In medium/large projects you may enter deep waters without Option Explicit.

Dr. belisarius Over a year ago

@Carlos Dim i, j, k As Integer ... logicamente

RolandTumble Over a year ago

@belisarius--no, No, NO, a thousand times NO! That's one of the traps of VB-based languages (VB & VBA). "Dim i, j, k As Integer" will get you ONE integer (k) and two Variants (i & j). You can do it all on one line, thus: "Dim i As Integer, j As Integer, k As Integer"

|

BradC · Accepted Answer · 2010-11-17 15:33:25Z

7

It works fine, until it doesn't.

Your examples are pretty simple, but its entirely possible to come up with situations that cause problems.

Better to declare all so that you don't risk running into ambiguity at runtime.

I'm also partial to MikeD's comment regarding autocomplete.

answered Nov 17, 2010 at 15:33

BradC

40.3k14 gold badges77 silver badges91 bronze badges

Collectives™ on Stack Overflow

Excel-VBA: Variable declaration necessary?

2 Answers 2

9 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related