DIM statement in VBA

Hi Phantom


The word Dim is short for dimmension and is used to declare variables. Now, while Excel can still work quite happily if we omit the Dim (as long as we do not have Option Explicit), it comes at a price. Excel MUST store all undeclared variables as a Variant, meaning it can accept any data type (String, Iteger, Range etc). This forces Excel to reserve much more memory than really needed. So this is why 'good' code has all variables dimmensioned correctly.

There are 3 basic levels of Dim Statements.

1. Procedure Level
2. Module Level
3. Project Level

1. Procedure Level
Must be used inside of a Procedure between

Sub MacroName()
Dim iInt as Integer

End Sub

This will ONLY retain the value passed to it while the Procedure is running, it is then destoyed.


2. Module Level
Must be placed at the very top of a Module and will then be available to ALL Procedures within that module.


3.Project Level
Must be placed at the very top of a Standard Module using the Public Key word, eg


Public iInt as Integer

This is then available to ALL Procedures in all modules at ALL times. Until we either:

1.Destroy the variable (set it back to it's default), eg iInt=0

2. Use the End statement in any Procedure.

3. Close the Workbook.

See "Understanding the Lifetime of Variables" in the VBE help and it's ascociated links for deeper insight.

WOW! It sounds like the DIM function is a lot like the Validation function within the worksheet. Only valid data is allowed, otherwise it is discarded. Would I do a search on VARIANT to see what the different variants are? What are the common occasions you would use this (i.e. would you use this if data were to be imported into the script?)

Hi Phantom

Hmmm, no it's not really like th Validation in Excel. Variables are used to store Data Types. See below from Excel help

data type
The characteristic of a variable that determines what kind of data it can hold. Data types include Byte, Boolean, Integer, Long, Currency, Decimal, Single, Double, Date, String, Object, Variant (default), and user-defined types, as well as specific types of objects.




Note Arrays of any data type require 20 bytes of memory plus 4 bytes for each array dimension plus the number of bytes occupied by the data itself. The memory occupied by the data can be calculated by multiplying the number of data elements by the size of each element. For example, the data in a single-dimension array consisting of 4 Integer data elements of 2 bytes each occupies 8 bytes. The 8 bytes required for the data plus the 24 bytes of overhead brings the total memory requirement for the array to 32 bytes.

A Variant containing an array requires 12 bytes more than the array alone.

Note Use the StrConv function to convert one type of string data to another.




Using Data Types Efficiently


Unless otherwise specified, undeclared variables are assigned the Variant data type. This data type makes it easy to write programs, but it is not always the most efficient data type to use.

You should consider using other data types if:

Your program is very large and uses many variables.


Your program must run as quickly as possible.


You write data directly to random-access files.
In addition to Variant, supported data types include Byte, Boolean, Integer, Long, Single, Double, Currency, Decimal, Date, Object, and String. Use the Dim statement to declare a variable of a specific type, for example:

Dim X As Integer

This statement declares that a variable X is an integer — a whole number between –32,768 and 32,767. If you try to set X to a number outside that range, an error occurs. If you try to set X to a fraction, the number is rounded. For example:

X = 32768 ' Causes error.
X = 5.9 ' Sets x to 6.



_________________
Kind Regards
Dave Hawley
8 Add-ins in one
Forty+ more here
OzGrid.com
This message was edited by Dave Hawley on 2002-07-13 03:14

I'm too tired to try that now. I have it saved to look at later. As always, thanks for your help.

Dave Hawley said:

2. Module Level
Must be placed at the very top of a Module and will then be available to ALL Procedures within that module.


3.Project Level
Must be placed at the very top of a Standard Module using the Public Key word, eg


Public iInt as Integer

.

Click to expand...

Hi Dave - does the Public key word need to be used in the case of point 2 (module level) too? I've tried using Dim but the value is lost outside of the procedure

Cheers

Starkey said:

Hi Dave - does the Public key word need to be used in the case of point 2 (module level) too? I've tried using Dim but the value is lost outside of the procedure

Cheers

Click to expand...

Please start a new thread - this one is ancient.