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.
Sub Demo() Dim str1 As String Dim str2 As String Dim ws As Worksheet End Sub
Accessing the object's features using submenus
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.
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.(
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.
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
Size with values.
With Sheet1.UsedRange.Font .Bold = True .Size = 12 End With
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
or the most common:
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).
Most types (classes) have a corresponding collection class, e.g.
For Each allows us to process all objects in a collection.