Set worksheet

Code VBA IntelliSense

The VB Editor built-in IntelliSense (started using Shift-Space) has the Following uses:

  1. it can be used to autocomplete variable or procedure names,
  2. starting from a class variable, after inserting a dot (.) it lists all properties and methods for the type,
  3. if in a value assignment (x=) expression a Boolean or enumerated type is involved, it lists the possible values to select from.
Note
By default this last option is replaced by the more extensive Code VBA IntelliSense behaviour described below. You can change this in the Code VBA preferences, but you probably won't.

Code VBA extends IntelliSense and can be activated using Shift-Space:

  1. After keywords If, Do or Dim opens the appropriate menu on the Code VBA toolbar (If/Select, Loop or Variable.)
  2. After = when coding a value expression a menu opens in which you can either introduce a new variable, start an appropriate expression builder, select suitable (same type) variables from variables or object properties and from built-in or your own functions. The menu also opens when using Shift-Space after operators in the expression (&,>,< , +,-,And,Or,Not)
  3. At a position where a condition expression is expected (after If, or While or Until) IntelliSense opens the Boolean expression menu.
  4. Set object variable: select from procedures that return this type.
  5. Code inside With: list the objects procedures alphabetically to select from. Shows all menu elements, methods and properties in an organized way. Also allows 'object chaining': direct access to features of related objects.

Shift-Space after Dim, If or Do


Dim[cursor]
If[cursor]
Do[cursor]
Shift-Space opens the appropriate menu: in the first case the 'Variable' menu for fast insertion of variable of a selected type, in the second case the If (and Select) menu, in the last case the Loop menu. The If and Do loop have a consition position. Using Code VBA IntelliSense there opens the menu for defining the condition. Below screencast shows the If menu and subsequent definition of the condition using Code VBA IntelliSense in action, here selecting ThisWorkbook.Saved property.

demo using Code VBA IntelliSense to create If construction and condition

Shift-Space after = or & (String)


Dim str1 As String: str1 = [cursor]
Shift-Space however opens a list of String variables and object properties to select from. Image below shows the menu. Looking at it you may think 'wow, is this helpful? does it not just complicate things?'

intellicode thisworkbook path

The menu is built up from several parts:

  • New String Variable ... will open a dialog where you can enter the variable name. Once selected, this will declare the variable in addition to inserting it at the cursor position. This is an common choice when programming as means of splitting the task (here coding the filename) in smaller parts.
  • strFilename is the (here single) element in the list of available string variables (and constants) in this context.
  • Long and Double are submenus which allow you to include such in the string.
  • The Name submenu gives you access to any name currently available: the name of a Worksheet, of a table or field, whatever happens to be known, and inserts it as a string.
  • The VBA submenu gives access to all VBA functions that may be used considering the current type (here String).
  • The same applies to the Application, ThisWorkbook and other object submenus. In the example we selected the documents folder (DefaultFilePath) and also the PathSeparator property. Many of use would of course have typed the "\" themselves, but hey, this is just an example and nobody is forcing you to use everything that is available.
Note
The tooltip shows both the code that will be inserted and explains what it does.

Shift-Space after >,< or +,-,... or Not, And, Or

As above, Shift-Space opens a list of the variables and object properties etcetera of the type to select from.

set worksheet

Shift-Space to Set object variable


Dim ws As Worksheet: Set ws = [cursor]

Again, Control-Space is not context aware and therefore is of little use. Shift-Space however opens a menu containing all procedures that are suitable for Setting the object variable of the given type.

Code inside With


Dim ws As Worksheet: Set ws = [cursor]
Dim ws As Worksheet

In case of Shift-Space with cursor on an empty line inside a With, the Object variable menu opens showing the menu appropriate the menu elements that apply to the 'Withed' object:


With ActiveSheet
[cursor]
End With