Declaring Your Variables

VBA is a lazy programmers dream. You can take your variable name and assign practically any data to it. But it is a good habit to declare your variables which means indicating what type of data will be contained in them. This will help by taking less memory and speeding up execution time. But the most compelling reason is that it helps tremendously with one of the most common problems in programming, typos. If you declare your variable name, then misspell it later on, you’ll get an error before your program will even run rather than having the code execute with a zero or some other random value in a variable that should hold something else . This makes debugging much easier.

Like a lot of things, sometimes the best way to encourage a good habit is to force yourself to do it. You can force yourself to declare your variables by going to Tools>Options>Editor Tab in the VBE and checking the box for “Require Variable Declaration”. This adds two words to the top of every code module, “Option Explicit”. Ok, so I suppose now you’re going to want to know how to declare variables since you’ve have no choice!

Scope

First, a little discussion about variable “Scope”. The scope of a variable determines which code modules or procedures the variable’s contents will be accessible to. There are essentially three different variable scopes and they are summarized in Table 2:

Table 2: Variable Scope

The best way to help you understand is with a simple little example. This code could stand on its own in a Module:

Listing 7: Variable scope example

Option Explicit















Const PI As Single = 3.14159265359 'Constants work for all Modules







Dim strFile As String 'This Module only







Public lngCount As Long 'All Modules















Sub Test()







'Variable Declaration – Single procedure, ie. the values are only







'accessible within the “Test” subroutine.







'This is the most common way to declare your variables







Dim blnTrueFalse As Boolean







Dim intX, intY As Integer







Dim sngCalc As Single







Dim strName As String







Dim varUnknown As Variant







Dim varDefault 'A Variant by default















'Begin rest of your code below















'Finished, now exit the Sub







End Sub

TrackBack URI for this entry

Comments (1)

Subscribe to this comment's feed

Ted said:

Very nice. I have avoided vba and macros up to this point but I think I'll give it a try.

Monday June 16, 2008 10:13AM

Show/Hide comment form

Name

Email

Title

Comment

Write the displayed characters

Add Comment