VBA Productivity Tip: Intellisense

This post introduces our first VBA Productivity Tip, a summary of the VBA Editor’s intellisense feature. Intellisense can help reduce the occurrence of common syntactical and semantic errors by providing useful tooltips and lists to help you code more efficiently.

This post on using intellisense will provide you with information on how to:

  • Use intellisense to easily look up object members,┬álocal variables, global variables, functions, and subroutines directly in the coding window.
  • Ensure that the intellisense features are calibrated to meet your needs.
  • Update settings to prevent annoying and obvious editor error messages.
  • Determine if a correct variable name has been typed by utilize variable naming convention techniques.

Overview

Modern software development tools provide a robust suite of features to help programmers code efficiently and reduce the occurrence of errors. While errors should be expected to occur during the development of any program, a feature within most coding editors can help quickly detect simple syntactical and semantic errors. This features–known as intellisense–are present in most modern coding editors, including the VBA editor.

Intellisense presents unobtrusive listboxes and tooltips near the cursor when accessing known variables, objects, functions, subroutines, or members.

A basic intellisense window appears listing members of the range object.
A basic intellisense window appears listing members of the range object.

Using Intellisense

Activation

Intellisense is always active, but options permit auto-displays to be turned off. If you need to make any changes to default settings, selecting Tools > Options will bring up VBA editor options. Within the Editor tab (will be the default tab), Code Settings can be used to toggle automatic intellisense features.

VBA Editor options menu.
VBA Editor options menu.

Features will be discussed for the remainder of this post, but the following is a summary of each options:

  • Auto Syntax Check (Default Checked): Will present an error dialog box for simple syntactical errors, such as missing assignments or methods. Our suggestion: change to unchecked.
  • Require Variable Declaration (Default Unchecked): Will add the Option Explicit statement to the declaration section of any new module. Our suggestion: not completely necessary, but using Option Explicit is always a good coding practice.
  • Auto List Members (Default Checked): Will (1) present an unobtrusive list of object members after entering the dot operator, or (2) present a list of data types during data declaration, after typing “As”. If this is unchecked, the lists can still be accessed by pressing Ctrl Space simultaneously. Our suggestion: leave checked.
  • Auto Quick Info (Default Checked): Will provide a small tips box outlining function and member parameters after entering an open parenthesis on a function call. Our suggestion: leave checked.
  • Auto Data Tips (Default Checked): Will provide variable values when editor is in debug mode. Placing the cursor over each variable will present the value assigned to that variable at the moment a debug error occurred. Our suggestion: leave checked.

Object Members

Intellisense is useful while working with built-in or custom objects. Immediately after typing the dot operator, a simple dropdown menu will appear showing a list of possible members (methods and properties).

Intellisense activated for the Worksheets object.
Intellisense activated for the Worksheets object.

This tool summarizes everything available within the object library. Instead of risking a simple misspelling, or going out of the way to press F2 and use the mouse to scroll through the object library, a simple list is presented in the editor window.

Within the intellisense listbox, two icons exist for objects. The small finger pointing to a sheet of paper (e.g. the icon for the Application property) represent object properties. The (what looks to be) flying green box (e.g. the icon for the Activate method) represent object methods.

One of the great features in intellisense is its ability to narrow down results as you type. In the example below, all members beginning with the letters “pro” are displayed as it is typed. There are a few things that can be done depending in your preference:

  • Use the arrow keys to move up/down the selection, then press the Tab key to autocomplete.
  • Use the cursor to scroll up/down and autocomplete the method/property by double clicking.
  • Simply use as a reference to ensure you don’t mistakenly mistype or use an abbreviation.

Intellisense narrows down possible results as you type.

Variables, Functions, and Subroutines

This feature does not have an automatic option — pressing Ctrl and Space simultaneously on the keyboard will present a list of all identifiers in the project. This includes local/global variables, functions, subroutines, and objects which are accessible within the current procedure.

Intellisense works for variables, functions, and subs by pressing Ctrl Space.
Intellisense works for variables, functions, and subs by pressing Ctrl Space.
  • Variables and Excel properties appear as a hand pointing to a line in a paper.
  • Subroutines and Functions appear as a flying green box.
  • Objects appear as a gray box containing 3 colored boxes.

This specific intellisense listbox does not require a dot operator to be active, and will show all variables, subroutines, and functions which are accessible within the name space. Generally, this listbox can be accessed anywhere outside of object or declaration statements (even with partially typed variables). All project global variables and public subroutine/functions, as well as any private variables/subroutines/functions within the procedure, will appear in the intellisense listbox. Private variables/functions in other modules or procedures will not be accessible

Declaration Statements

Intellisense can provide a list of data types during variable and object declaration. Immediately after entering a space after “As” will prompt a listbox of all available data types and object classifications. Similar to the previous examples, the listbox will begin to scroll to possibilities as you type in text. Selections can be made with the Tab key or by double clicking.

Intellisense presenting a list of available data types.
Intellisense presenting a list of available data types.

Function Parameters

By activating the “Quick Tips” option, intellisense will provide guidance when calling intrinsic (built-in) or custom functions. A one-line tooltip window will appear showing all available parameters and the data type expected for each parameter.

Tips provided for completing function parameters.

In the cleanData example, the parameter Number will accept any numeric data type (variant), whereas the NumDigitsAfterDecimal parameter is optional–notated optional by being surrounded in square brackets–and must be a Long value. The current active parameter will show in bold, moving to the next one when the separating comma is entered.

In the event that a value will be returned from a function, the closing bracket will be followed by “As” and list the data type, such as follows:

Quick tip showing that a boolean value will be returned.

In the IsNumeric function, any value (variant) can be passed through the Expression parameter. If the function discovers the Expression value to be numeric, a Boolean value of True will be returned.

Managing Syntax Error Messages

There are some situations where the VBA editor may be a bit overeager with its help. The “Compile error” warning is a good example of this, where a warning dialog box pops up immediately after the editor discovers a simple syntax error. Unfortunately this can occur in situations when a developer simply needs to scroll to check on a different variable name, or chooses to return to complex statement at a later time.

The compile error warning can be just as annoying as it is helpful.
The compile error warning can be just as annoying as it is helpful.

This dialog box isn’t a major issue, but it can serve as a distraction and require a few additional mouse clicks. Sometimes this can interrupt the flow of coding. There is a way to deactivate the warning dialog. Within the Code Settings dialog box, uncheck “Auto Syntax Check”. Any simple syntax errors will still appear in red text, but the annoying dialog box will no longer appear!

Managing Variable Name/Spelling Errors

We’ve all been in situations where ideas are flowing right off our fingertips for the perfect function or subroutine. Even manually activating intellisense with Ctrl Space would throw a wrench into your pace. You’re aware of all objects and variables you intend to call, but you may be unsure about the exact spelling. For example, did you name a variable cashBal or cashBalance? This little trick will help you figure it out without skipping a beat.

Autocorrect can help uncover errors.In the overview of variables in VBA, the concept of camelCase was discussed as an effective naming convention for variables. This practice will prove effective for an auto-correct trick which will help you quickly find errors. Continuing with the cashBal/cashBalance example, the snippet to the left contains double variable cashBal. Imagine that the declaration statement is out of view — there is a way to check if the variable exists without scrolling up to the top of procedure or activating intellisense. To do this, you must enter the variable in all lowercase letters.

Autocorrect can help uncover errors.Upon pressing the Enter key, an autocorrect feature will update the variable’s case to match the declaration statement. In this example, the “b” in “cashbal” changes from lowercase to upper case, matching the variable name exactly as cashBal. If you were to type “cashbalance”, the letter “b” would not turn to a capital letter, and this would tell you that the variable name was not spelled correctly. The trick here is to look for this subtle change to validate that the spelling of the variable name matches the spelling of the variable declared in the declarations section. This auto-correct feature only works for adjusting cases, and will not correct even the most minor spelling error.

This technique will also work for objects, functions, subroutines, and all other data strictures associated with the project. This will not work, however, if a variable is not properly declared.

Leave a Reply