Explicit and implicit declarations - VBA For Excel

The VBA default data type is the Variant data type. As This shows, a variable of the variant data type consumes 16 bytes plus 1 for each character, and is used to store a data item when the data type is not known. Using a Variant data type can be costly in memory and program execution time. Lomax estimates that expressions using only Variant data execute about 30% slower than the same expressions using the correct intrinsic data types. In VBA, the programmer is not required to declare a variable type explicitly, but it is advisable to do so for these reasons:

  • It gives clarity to program design; the programmer will better understand the purpose of each variable when they are explicitly declared.
  • It prevents inadvertent creation of new variables by misspelling the name of an existing one; this will make debugging easier since any spelling errors will be identified before program compilation.
  • It makes code easier to understand by grouping all variable names at the beginning of a program.
  • Code is executed faster if the correct data type is assigned to each variable.This is because they will consume less memory, and therefore be retrieved more quickly.
  • Less memory is required if the correct data type is assigned to each variable.

Option explicit

A programmer can force all variable declarations in a program by inserting an Option Explicit command before the procedure definition begins. By inserting this optional command,the VBA program will expect every variable that is used in the program to be declared before it can be used. This is good VBA program practice. An example of Option Explicit follows:

Option Explicit
'Notice how the option explicit command precedes the procedure
definition all variables must be declared
Sub thisProg ()
Dim salesChart As Chart
'Specific object Chart declaration must be declared
Dim theTotal As Long
' this variable must be declared since Option explicit has been used
Dim myDaughtersName As String
' this variable must also be declared

Setting explicit declarations for variables

You can set VBA for Excel to so that you are forced to declare all variables explicitly without having to write an Option Explicit line in every program. This can be done either globally (for all modules) or on a module by module basis. To do this, choose Tools > Options from the VBE. You will see the Options dialog box. Click the Editor tab, and tick the Require Variable Declaration box. Notice, that it is unchecked by default. Now, if you do this, you will find an Option Explicit appear for each new module that you create.

Setting the explicit variable declaration requirement

Setting the explicit variable declaration requirement

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

VBA For Excel Topics