CODEWITHSUNDEEP
vbaexcel
user764357
user764357

Reputation:

Why should I use the DIM statement in VBA or Excel?

So there is a question on what DIM is, but I can't find why I want to use it.

As far as I can tell, I see no difference between these three sets of code:

'Example 1
myVal = 2

'Example 2
DIM myVal as Integer
myVal = 2

'Example 3
DIM myVal = 2

If I omit DIM the code still runs, and after 2 or 3 nested loops I see no difference in the output when they are omitted. Having come from Python, I like to keep my code clean*.

So why should I need to declare variables with DIM? Apart from stylistic concerns, is there a technical reason to use DIM?

* also I'm lazy and out of the habit of declaring variables.

Upvotes: 6

Views: 18518

Answers (3)

phd443322
phd443322

Reputation: 503

Moderators, I'm making an effort, assuming you'll treat me with due respect in thefuture.

All local variables are stored on the stack as with all languages (and most parameters to functions). When a sub exits the stack is returned to how it was before the sub executed. So all memory is freed. Strings and objects are stored elsewhere in a object manager or string manager and the stack contains a pointer but vb looks after freeing it. Seting a vbstring (a bstr) to zero length frees all but two bytes. That's why we try to avoid global variables.

In scripting type programs, typeless programming has many advantages. Programs are short and use few variables so memory and speed don't matter - it will be fast enough. As programs get more complex it does matter. VB was designed for typeless programming as well as typed programming. For most excel macros, typeless programming is fine and is more readable. Vbscript only supports typeless programming (and you can paste it into vba/vb6).

Upvotes: 5

chris neilsen
chris neilsen

Reputation: 53137

Any variable used without declaration is of type Variant. While variants can be useful in some circumstances, they should be avoided when not required, because they:

  1. Are slower
  2. Use more memory
  3. Are more error prone, either through miss spelling or through assigning a value of the wrong data type

Upvotes: 11

Matt Coubrough
Matt Coubrough

Reputation: 3829

Using Dim makes the intentions of your code explicit and prevents common mistakes like a typo actually declaring a new variable. If you use Option Explicit On with your code (which I thoroughly recommend) Dim becomes mandatory.

Here's an example of failing to use Dim causing a (potentially bad) problem:

myVar = 100

' later on...

myVal = 10      'accidentally declare new variable instead of assign to myVar

Debug.Print myVar     'prints 100 when you were expecting 10

Whereas this code will save you from that mistake:

Option Explicit

Dim myVar as Integer
myVar = 100

' later on...

myVal = 10    ' error: Option Explicit means you *must* use Dim

More about Dim and Option Explicit here: http://msdn.microsoft.com/en-us/library/y9341s4f.aspx

Upvotes: 13

Related Questions