VBA and Excel for Engineers and Scientists – Part 2


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

Scope

How Variable is Declared

Single procedure

Dim statement within the procedure (Sub or Function)

Single Module

Dim or Private statement before the first procedure in a module

All Modules

Public statement before the first procedure in a module

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 

Info: If you omit the “as VariableType” portion of the declaration statement, VBA implicitly assigns it as a data type Variant as seein near the bottom of Listing 7 above.

1 Comment

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

Comments are closed.