What Are VB Projects, Objects, Forms, Modules and Procedures?
In the Excel Visual Basic Editor (Alt+F11), display the Project Explorer (Ctrl+R). Usually (and in this picture), it appears at the top left, under the toolbars:
In Project Explorer, you will see a VBAProject for every Excel file that is open. The list will include the hidden Personal Macro workbook, Personal.xlsb, if you have personal macros.
The VBA Project is the container for Visual Basic code for each file.
If you expand the tree for any Project (click the little “+”), you will see Microsoft Excel Objects. There may also be Forms and Modules:
1. Objects, Forms and Modules
A Project consists of Microsoft Excel Objects and, optionally, Forms, Modules and Class Modules.
Excel Objects are ThisWorkbook and all the sheets. ThisWorkbook contains code relevant to Workbook operations. They include Open, Activate, Print, and Close. Each sheet object contains code that applies to that Worksheet. That would include code that runs on Activate, Change, Calculate, Deactivate, etc
Sheet names appear in parentheses, e.g. “(Sheet1)” above. The code belonging to that sheet starts off with the same name as the sheet (Sheet1, Sheet2, etc.). You can rename it in the Properties window (F4), such as “SheetMacros1” above.
Each Excel Object and Form will contain the programming code specific to that Form. Code common to more than one form resides in one or more Modules.
Forms are used as dialog boxes. This lets the program ask for data that is not in the spreadsheet and is too complex for MsgBox or InputBox. Such dialog boxes are modal. That means you cannot click on any part of the program outside the dialog box while it is open.
We will see more of Forms in a later post.
2. Procedures: Subprograms and Functions
Within each Object, Form and Module, we find the programming code. There will be a Declarations Section containing statements like:
- Option Explicit – to force variable declaration
- Option Compare Text or Option Compare Binary. To compare and sort text case-sensitive (Binary) or case-insensitive (Text).
- Option Base 0 (default) or 1 – start arrays at 0 or 1.
After the Declarations Section, we have as many macros as required. We call them Procedures. They come in two varieties: Subprograms and Functions.
A Subprogram is any macro you can run. It should start with Sub name and end with End Sub.
You can also create Function procedures, which work like Excel’s built-in functions. The form of a function is:
Function FnName(Parameters)
FnName = some calculation using Parameters
End Function
In a worksheet cell, you would then enter, e.g. =FnName(66)
That, then, is the way Excel organises macros. Subprogram and Function Procedures make up Excel Objects, Forms and Modules. Those in turn form part of one VBA Project per Excel file.