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

score 14 · Accepted Answer · answered Nov 17 '10 at 14:52

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 '10 at 14:52

NoAlias

9,218
2
27
46

@DaMartyr, thanks! if you will be a uni professor, will you mark me down for not declaring? – Carlos Nov 17 '10 at 14:58
5

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 – MikeD Nov 17 '10 at 14:58
@MikeD, that is true, i also declare them to write the program but then i delete them once i finish, it just makes a 3 line `sub` become a cake of code – Carlos Nov 17 '10 at 14:59
3

Yep. It is a bad practice. In medium/large projects you may enter deep waters without Option Explicit. – Dr. belisarius Nov 17 '10 at 15:01
@belisarius, then, is there a shortcut to initializing variables in VBA. Something like in java `int i, j, k`? – Carlos Nov 17 '10 at 15:29
2

@Carlos Dim i, j, k As Integer ... logicamente – Dr. belisarius Nov 17 '10 at 15:31
5

@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" – RolandTumble Nov 17 '10 at 17:24
the two code blocks behave different: in (1) "i" is and remains an Integer, in case 2 "i" is Variant/Empty in the beginning, and changes to Variant/Long on implicit initialisation at the For statement. Being a Long it has a different range and occupies more space - it will maybe hide overflows or accidental assignment of wrong Vartypes (like String) you would like to detect at developing time before s..t happens during live operation. This is particularly important when you use such var's as parameters to call functions/subs. – MikeD Nov 18 '10 at 08:37
try to set i="x" in both examples: in (1) you get a "Type mismatch" right on the spot where s..t happens; in (2) you get an error only at the Next statement. In a large program with nested Subs, Functions and Classes it can take you ages to find the root cause. – MikeD Nov 18 '10 at 08:42

score 7 · Answer 2 · answered Nov 17 '10 at 15:33

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.

Excel-VBA: Variable declaration necessary?

2 Answers2

Linked