|
What are Variables?
Rodney POWELL
Microsoft MVP - Excel |
|
Variables are used to store information temporarily. As a
program runs, it holds values temporarily in memory. Variables are
names that the program associates with specific locations in memory.
The value to which the program refers in these areas can change
throughout a session of program operation.
Each variable has a specific type, that indicates how much
memory the data requires and the operations that can be performed on
that kind of data.
The Dim (stands for dimension) statement is used to
declare variables and allocate storage space. It may appear in a
General Declarations section at the top of a code module -or-
immediately following a procedure declaration. For example ...
Sub SampleCode()
Dim MyNumber as Integer
' Other program statements go here
End Sub
Variables declared within a procedure are not available to other
procedures and they only retain values for the life of that procedure.
This refers to a variable's scope. Variables declared
at a module-level (or within a form's General Declarations section)
are available to all procedures within that module or form
-and- they continue to retain assigned values for the life of the
program. However, these variables are not available to procedures
outside of the module in which they are declared. One alternative
to the Dim statement is the Public keyword, which
expands the scope of the variable to make it available to other
procedures outside it's own module or form. It's a better
practice to use the narrowest possible scope for your variables.
Note: Other approaches to scope involve Global, Private,
and Static declaration. Also, very similar to variables are
Constants. You may want to learn about how to use these at
some point.
Explicit declaration (in contrast to implicit declaration)
means requiring the declaration of a variable BEFORE you use it.
By default, you are NOT forced to declare a variable, but undeclared
variables are problematic. Using the Option Explicit
statement forces you to declare every variable. This also helps you
catch errors. You should place the Option Explicit statement at the
head of the General Declarations section (at the top) in your modules.
Note: You can ensure that the Option Explicit statement will be
added automatically by checking Require Variable Declaration
in the Editor tab of the Tools | Options dialog of
Office's integrated development environment (module) interface.
A type clause (i.e., ... as Integer -or- ... as String)
or appending a type-declaration character is optional, but it is
good practice for this to always be explicitly assigned by you. Valid data
types are Byte, Boolean, Integer, Long, Currency, Single, Double, Date,
String (variable-length text), String * length
(fixed-length text), Object, Variant, a user-defined type, or
a specific object type. The compiler will default to the type
Variant for a variable that does not have a type specified. A
Variant behaves like a chameleon, as it can become whatever type is
required for the data assigned to it. This is usually undesirable because
it is not memory efficient, it slows performance as VBA has to determine
what type of data the Variant represents, and it can result in problematic
type conflict errors. Variants DO serve a useful purpose, however, when
it is known that the variable type cannot be determined - such as when
capturing freeform entry by users.
The following is a summary of basic variable types supported in VBA.
| Type |
Memory |
Type-Declaration Character |
Description |
| Byte |
1 byte |
none |
Positive whole number ranging from 0 through 255 that can
be represented as a binary value. |
| Boolean |
2 bytes |
none |
True or False value |
| Integer |
2 bytes |
% |
Whole numbers ranging from -32,768 through 32,767. |
| Long (long integer |
4 bytes |
& |
Whole numbers ranging from -2,147,483,648 through 2,147,483,647. |
| Single |
4 bytes |
! |
Single-precision floating-point number (with decimal points)
ranging from -3.402823E38 to 3.402823E38. |
| Double |
8 bytes |
# |
Double-precision floating-point number (which is more precise
for very large or very small numbers) ranging from -1.79769313486232E308
to 1.79769313486232E308. |
| Currency |
8 bytes |
@ |
Large numbers between -922,337,203,685,477.5808 and 922,337,203,685,477.5807
(15 digits to left of decimal and 4 digits to the right of the decimal). |
| Date |
8 bytes |
none |
Represents dates from January 1, 100 through December 31, 9999. |
| Object |
4 bytes |
none |
An instance of a class or object reference. |
| String |
10 bytes + 1 byte per char |
$ |
Series of any ASCII characters. |
| String (fixed-length) |
length of string |
none |
Series of any ASCII characters, of a pre-defined length. |
| Variant |
min 16 bytes |
none |
Any kind of data except fixed-length String data and user-defined types. |
When you declare variables, you should choose meaningful variable names
that describe the variable's purpose. Variable names must meet the same
criteria as procedure names. They can contain a combination of as many
as 254 letters, numbers, and the underscore character ( _ ). However,
variable names cannot begin with a number, nor can you use reserved
keywords that have special meaning to the VBA compiler.
Now, let's see how to apply variables. Refer to the following example:
Sub VariableDemo()
Dim Prompt$, varUserInput As Variant
' Initialize a String variable with an instruction to
' appear in an InputBox
Prompt$ = "Please enter something."
' Apply your String variable as an argument for the
' InputBox function. Use a Variant variable to capture
' the user's entry in an InputBox
varUserInput = InputBox(Prompt$)
' Display the user's entry by applying your variable
' within a message dialog
MsgBox varUserInput
End Sub
A couple of things to notice about this example are how a
type-declaration character (that was the $ sign in the Prompt$ variable)
can be applied to define the String variable. Also, you can see how a
Variant was purposely used since the user could enter either numbers or
text in the InputBox.
It would require a lot more to provide exhaustive treatment on the
topic of Variables. This serves as a high-level overview of what
Variables are about and how to begin applying them properly. For more
detailed information, refer to the VBA online Help file or one of the
many reference books available on either VBA or Visual Basic
programming basics.
Something to add?
Let me know.
|
|
|
|
![Microsoft Office : Excel - Visual Basic for Applications [ VBA ] Software](img/1ptrans.gif){kind=small}
