Microsoft Office Tutorials and References
In Depth Information
End Function
In the VARIANT_DEMO Function procedure, MyVar starts out as a three-character text string that looks like a
number. Then this string is divided by 2, and MyVar becomes a numeric data type. Next, MyVar is appended to
a string, converting MyVar back to a string. The function returns the final string: Answer: 61.5.
You'll notice that I don't follow my own advice in this chapter. In many of the subse-
quent function listings in this chapter, I don't declare the variables used. I omitted the
variable declarations to keep the code simple so that you can focus on the concept be-
ing discussed. In the code examples at this topic's website, I always declare the vari-
Forcing yourself to declare all variables
To force yourself to declare all the variables that you use, include the following as the first instruction in your VBA
Option Explicit
This statement causes your procedure to stop whenever VBA encounters an undeclared variable name. VBA is-
sues an error message (Compile error: Variable not defined), and you must declare the variable before you can
To ensure that the Option Explicit statement appears in every new VBA module automatically, enable the Require
Variable Declaration option on the Editor tab of the VB Editor Options dialog box. To display this dialog box,
choose Tools⇒Options.
Using constants
A variable's value may — and often does — change while a procedure is executing. That's why it's called a
variable. Sometimes, you need to refer to a named value or string that never changes: in other words, a con-
You declare a constant by using the Const statement. Here are some examples:
Const NumQuarters As Long = 4
Const Rate = .0725, Period = 12
Const CompanyName as String = “Acme Snapholytes”
The second statement declares two constants with a single statement, but it does not declare a data type. Conse-
quently, the two constants are variants. Because a constant never changes its value, you normally want to de-
clare your constants as a specific data type. The scope of a constant depends on where it is declared within your
Search JabSto ::

Custom Search