Using Excel VBA to create code

organisation of the Excel VBA menu

Excel is a program with a sheer endless number of features. All these features can be used and manipulated using VBA. Because it is so large no person can know all that is available. Fortunately Excel VBA is well organized in a class library. The Excel VBA menu structure has been programmatically derived from the Excel library, and reflects its structure. In addition we interpreted it as a hierarchical menu tree based on class dependencies. We also manually made improvements so that the most used elements are most easily accessible. Below I explain the main organising principles in more detail hoping that after reading this it makes sense and will help you find the code you need to use.

Classes at the top of Excel VBA menu

Some classes have been honored by a position at the top of the Excel VBA menu. Some, such as Application, Workbook, Worksheet and Range have been elected in pole position because of their natural primary importance. Others, such as Chart, Font and Slicer have been included because they represent a large submenu which would have been repeated many times.

Using a class

To simplify the menu organisation collection classes have been merged with their corresponding class. For example Workbooks methods and properties are included under the Workbook menu.

class menu organisation

Within most classes a standard menu organisation is used - see image - which is discussed in the next sections. If you added fragments to the class, these appear directly above

Declare class or collection

class menu worksheet declare

Under declare you will find un the submenu under Declare the declaration of both the class and the collection. In some cases, such as Worksheet you will also find the Sheets collection: 


Dim ws As Worksheet
Dim wss As Worksheets
Dim shts As Sheets

Set class variable

class menu worksheet declare

The image on the right shows there are many ways a class may be set. It's up to you to choose the appropriate. A few are:

When you Set - or in another way use - a class variable, Code VBA checks if in the procedure's scope a variable of that type is already declared. If so, it assumes you want to instantiate that variable. If not, it declares the variable for you, giving it an initial name based on the class prefix and the method applied. Workbook.ActiveSheet inserts code below and in addition to creating the Dim wsActiveSheet As Worksheet also inserts the line Dim wb As Workbook: Set wb = suggesting you should specify which workbook you should pick the ActiveSheet from. 


Dim wb As Workbook: Set wb =
Dim wsActiveSheet As Worksheet
Set wsActiveSheet = wb.ActiveSheet

Set class variable from Item in a collection

You can specify which item in a collection either by using its index number or it's name.


Dim wb As Workbook: Set wb = ActiveWorkbook
Dim ws As Worksheet
Set ws = wb.Worksheets(2)
Set ws = wb.Worksheets("Sheet2")

The image below shows the many choices you have for specifying which worksheet you want to work on. At the right you see the names of the workheets in the active window so you can easily insert one of these.

class menu worksheet declare

For Each

For most classes there are corresponding collections. A common pattern is to iterate over the items in a collection. As an example below code allows you to process all sheets in the ActiveWorkbook. You could use this for filling a list box with the names of the sheets. 


Dim wb As Workbook: Set wb = ActiveWorkbook
Dim ws As Worksheet
For Each ws In wb.Worksheets

Next ws

Methods and Properties

class menu Methods, Properties and Dependent classes

Methods and properties procedures are made available in three ways:

  1. If the procedure returns the current class or collection it is placed in menu Set, e.g. Workbooks.Add.
  2. If the procedure does not return a class it is made available through menus named Methods and Properties.
  3. If the procedure returns another class or collection a separate submenu is made based on the name of the returned class - see below.

Dependent classes

An example of the third, class Worksheet has a method AutoFilter which returns an object of type AutoFilter. This is put in a submenu AutoFilter which again contains all that is needed to work with AutoFilters (Declare, Set, Methods, etcetera).

Add Fragment

Manage Fragment

Reference