How to use objects from the menu toolbar

The Object menu on the Code VBA toolbar allows you to produce targeted high quality compact code effortlessly. It contains all object variables in your current procedure (here ws) and application specific global objects. Starting from the code below (in Excel), the menu looks like the image on the right.

Object menu Excel
Sub Demo()
    Dim str1 As String
    Dim str2 As String
    Dim ws As Worksheet

End Sub
Notes
  • If you are nor familiar with using Objects in programming, first read Understanding objects, methods, properties, and events.
  • An alternative and faster way to open the menu is by using Shift-Space.
  • The image shows the situation in Excel when coding in a standard module. If you are coding from class module, e.g. ThisWorkbook or Sheet1, that one would be represented in the menu by the Me keyword.

Using the Object's Methods

From the menu, select which object to work with, for example ThisWorkbook. If you want to let the object execute an action, e.g. Method Close, select submenu Methods and select Close.

close ThisWorkbook using code vba menu
Notes
  • The methods are presented alphabetically, in some cases prominent one's are added to the top.
  • The tooltip on the menu shows the code to be inserted (image on the right).
  • Pressing F1 will open the MS online help for more info.
  • If the procedure has a single argument, an extra submenu allows you to select a variable of the required type to have the procedure completed in one go.
  • Save ThisWorkbook As using code vba method builderIf more decisions are required, this is indicated in the menu by ... When chosen, the Method Builder dialog opens to support you in completing the procedure call.

Using the Object's Properties

Next on the menu is the classes' Properties submenu. The code in tooltip in the example below str1 = ThisWorkbook.Path indicates this is a read-only property whose value will be assigned to a (New) String variable str. Alternatively, you could have selected to assign it to a String variable already declared, e.g. str1.(

Add code using ThisWorkbook Properties Path menu.

Using Object Fragments

This section is optionally present in the menu. It contains code fragments relevant to the current object either delivered through Code VBA or created by yourself.

With

When using multiple members of an object, it is considered 'good practice' to use the With statement. As an example the below With statement would easily be produced by following the menu's related classes. Along the same menu (use Alt-CQ) subsequently insert the properties Bold and Size with values.

With Sheet1.UsedRange.Font
    .Bold = True
    .Size = 12    
End With
Add With

Set

The Set menu is only available for object variables such as Worksheet ws in our example. In the image below we see the Set menu producing multiple candidates to select from. The most common would be ActiveSheet, either from Application or ThisWorkbook or ActiveWindow, or the most common: Sheets.Item(Index:=str) (shorter: Sheets(Index:=str)

Set worksheet menu Related classes for Workbook

The main objects (or classes) of Office applications (documents, sheets, charts,...) are composed of large numbers (collections) of specialized objects (sections, tables, cells, font,...) The hierachical structure of classes ('object model') is reflected in the structure of the Object menu. Because many actions you want your code to do, such as making text Bold, concern these specialized objects, in this case Font, you will often traverse the menu down the object hierarchy. For this reason the related classes take a prominent part of the menu, for the main objects (Application, Document, etc.) this can be more then half of the menu.

The related classes (or collections) are accessed through procedures, in most cases class properties. As an example the worksheets from a Workbook can be accessed via the collection Worksheets (or Sheets), or ActiveSheet.

For objects with many related classes, Code VBA selects the most used ones (here: ActiveSheet,...,Charts) and puts them on the top part of the submenu. The remaining ones are divided in non-collection (ActiveSlicer,...) and collection classes (Connections and further).

For Each

Most types (classes) have a corresponding collection class, e.g. Document - Documents. For Each allows us to process all objects in a collection.

For Each sheet in ThisWorkbook.Sheets