Six tools for faster declaring of variables in VBA

Explicitly declaring variables with their type in your procedures is considered a good practice. Together with a meaningful name it indicates the intended use of a variable. In addition when you specify type, the compiler knows exactly how much memory to allocate and how to handle it, which makes your code run more efficiently. Unfortunately, the VBA interpreter does not use the type to prevent typical 'Type Mismatch' errors. The Code VBA coding assistant uses this structural information to determine sensible continuations when writing code.

Declaring variables in a procedure basically is very simple. This page shows the tools available to Code VBA users to speed up this most common programming task, thus enhancing your work flow.

• Code VBA IntelliSense triggers:
  • Typing Dim triggers a type menu
  • Typing , after a declaration opens the menu again to add another one

  

  • Typing type prefix (+ variable name) followed by =
• Using a continuation from the Code VBA Variable menu
  • Quick Variable - from the selected type
  • Using the Using the Variable Builder
• One or more variables for the parameters of an inserted procedure call or code snippet.

Code VBA IntelliSense triggers

With Code VBA installed, one of the first things you will notice as soon as you start declaring variables in a procedure using dim a continuations menu pops up allowing you to select the type.

Dim strName As String

Quick Variable - from the selected type

The fast and simple way to declare a variable is from the Variable continuations menu, which you can get after typing dim inside a procedure or from the Code VBA toolbar » Variable menu. The menu conveniently lists which types can be use. Select the type you require and the declaration code gets inserted. The cursor is conveniently placed for you to enter the name for the variable. The continuations menu also has sub menus for the Office application's object types (in the image current is Excel) and also for referenced libraries: other Office applications, Scripting, DAO and some more.

Typing , after a declaration opens the menu again to add another one

A fast way to get multiple items on one line, e.g.

Dim strFirstName As String, strLastName As String

Typing type prefix (+ variable name) followed by =

Faster still is typing an appropriate prefix (lowercase! + variable name) followed by =, e.g. iCounter=. This will insert the complete declaration for the Integer type

Dim iCounter As Integer
iCounter=

In addition, it will open the continuations menu for numerics (image right) where you might simply select a value, or create or select a function that produces the value.

Using the Variable Builder

The Variable Builder has some extra features you may appreciate.

• Check box 'Excel types' to declare type as one of the classes in the open Office application (here Excel); Uncheck to use the VBA types.
• If you want to declare multiple variables in one go it can do so using the Insert button which keeps the variable builder dialog open. This can be even faster than the above approaches.
• If in a longer procedure you want a variable to be placed at the start you can tick 'Add to top'. This is faster than having to move back and forth.
• You can have the variable explicitly initialized:
  Dim strCompany As String: strCompany = ""
• When placed in the module declaration section additional controls allow you to use Public or Private instead of Dim.

One or more variables for the parameters of an inserted procedure call

Actually, using Code VBA many variables will be named and assigned a type for you. Whenever you use a Procedure Call Builder, it presents the possibility to have new variables declared that, as a default, correspond to the names of the procedure's parameters. (The same applies to the use of a Code Snippet Inserter.) In the image right you see the code to be inserted includes two variable declarations. To the right of each declarations there is an assignment statement prepared for you to expand:

Dim strFilename As String: strFilename =
Dim strPassword As String: strPassword =