automatically converts between data types during assignment. If one
variable doesn't exactly match the type of another, Basic changes the
value to fit. You can see this easily if you perform the following
Sub Conversions( )
Dim d As Double, s As Single, i As Integer
d = WorksheetFunction.Pi
s = d
i = d
Debug.Print d, s, i
The preceding code displays the following output in the Immediate window:
3.14159265358979 3.141593 3
You need to be aware of automatic conversion
, because it can result in the unintended loss of precision.
Here the conversion
is done by rounding the number up or down to reflect the precision of
the variable receiving the assignment. Not all conversion can be done by
rounding. For example, the following lines convert pi to a string:
Dim str As String
str = WorksheetFunction.Pi
Warning: not all conversions succeed. The following line causes a type mismatch error because d is a numeric variable and the "Pi" can't be converted to a number:
d = "Pi"
Conversions may also
fail if the assignment exceeds the limit of the target variable. For
example, the following lines result in an overflow error since the
positive limit for Integers is 32,627:
Dim l As Long
l = 32768
i = l
You can explicitly perform any of these automatic conversions using the Visual Basic conversion functions listed in Table 4.
Table 4. Visual Basic type conversion functions
|CVar|| || || || |
It would be nice if you could turn off Visual Basic's automatic conversions and use the explicit conversion functions shown in Table 4
only when needed. But you can't. In addition to the conversion
functions, Visual Basic provides other keywords that are useful when
working with types; they are listed in Table 5.
Table 5. Visual Basic keywords for working with types
|IsDate||Return True if the variable is a date|
|IsEmpty||Return True if the variable hasn't been initialized|
|IsNull||Return True if a Variant variable does not contain valid data|
|IsNumeric||Return True if the variable can be converted to a numeric value|
|IsObject||Return True if the variable is a reference to an object|
|TypeName||Return the name of the variable's type as a string|
|TypeOf||Determine the type of an object variable within an If statement|
4. Scope and Lifetime
Dim is not the only way to declare a variable. The full list of declaration keywords is shown in Table 6.
Table 6. Visual Basic declaration statements
|Statement||Use to declare||Available|
|Dim||A variable with the default scope||Inside or outside a procedure|
|Public||A variable or procedure that is available from other modules or classes||Outside a procedure only|
|Private||A variable or procedure that is not available from other modules or classes||Outside a procedure only|
|Static||A variable that retains its value between procedure calls||Inside a procedure only|
Which statement you use to declare a variable and where you declare it determines the scope and lifetime
of that variable. Scope is the range of places from which a name is visible. Dim, Public, and Private are statements that specify scope. Lifetime is how long Visual Basic retains the value of a variable; Static specifies lifetime.
There are three levels of scope in a Visual Basic project:
are declared with Dim inside a procedure and are visible only from within that procedure.
Module-level variables are declared outside of a procedure with Dim or Private and are visible only from all procedures within that module or class.
Global variables are declared outside of a procedure with Public and are visible from all procedures in all modules and classes within the project.
Figure 2 illustrates the different levels of scope within a Visual Basic project.
It's a common practice to prefix global variables
with g_ and module-level variable names with m_ as shown in Figure 2.
Figure 2. If Option Explicit is specified, you get an error when a variable is not visible
A variable defined at one level of scope can be shadowed by another defined at a lower level of scope. Shadowing hides the higher-level variable from the local use. For example, Proc1 in Figure 2 could shadow m_Var by declaring it at the procedure level as shown here in bold:
Sub Proc1( )
' Shadow module-level variable.
' Set values.
m_Var = "Private module-level"
m_PublicVar = "Public module-level"
m_PrivateVar = "Private module-level"
localVar = "Procedure-level"
In this case, Proc1 creates a new, local version of m_Var and sets its value. Proc2 can't see that value but it can still see the module-level version of m_Var, which doesn't contain a value. If you run Main, the output is this (note the blank space where m_Var should be):
Public module-level Private module-level
Shadowing is often a mistake, rather than an intentional technique. Using a scope prefix like g_ or m_ for global and module-level variables
helps to avoid this problem.
Declaring a local variable as Static
tells Visual Basic to retain the variable's value between procedure
calls. Ordinarily, local variables are created when a procedure is
called, then destroyed when the procedure ends. This means local
variables have a very short lifetime. Static
tells Visual Basic to keep the variable alive as long as the program is
running—for Excel Visual Basic projects, that lifetime begins when the
user opens the workbook and ends when he closes it.
As a practical matter, you
can do much the same thing with global and module-level variables since
they are also created when Excel opens the workbook and destroyed when
the workbook closes. The difference is scope
; Static variables are local and so
can't be changed outside of the procedure where they are declared. The
following example demonstrates using a Static variable:
Sub StaticVariable( )
' Static variables
Static staticVar As Integer
staticVar = staticVar + 1
' This variable is global.
g_GlobalVar = g_GlobalVar + 1
' They both retain their value.
Debug.Print staticVar, g_GlobalVar
Both staticVar and g_GlobalVar display the same value. If you run StaticVariable repeatedly, you'll see their values increment. The difference is that other procedures
can see (and change) the value of g_GlobalVar.
Why is this
important? Because restricting the scope of variables is one of the keys
to preventing accidental errors in your code. The more global variables
you have, the more likely you are to have adverse interactions between
variables as private as possible. Use arguments to pass values between
procedures. Use module-level or global variables for values that need to
be shared by most or all procedures.