Modules
As we have seen earlier, the VBA Project window
shows folders for each sheet in the workbook and the folder ‘ThisWorkbook’.
These are actually object modules (a special type of module) that can contain
procedures (and code) specific to the respective Worksheet objects and Workbook
object. But they usually contain only Event procedures, which is a particular
type of procedure that we will cover later.
Most of the executable VBA code is written or
inserted into regular modules though (what we usually refer simply as ‘modules’).
A VBA project can include one or more regular modules, each module can contain
one or more procedures, and each procedure will have as many lines of code as
needed.
On top of object modules and regular modules,
the VBA project can also have class modules and Userforms. The first are used
to create classes that define objects, and the second adds the layout and
controls to build a user interaction form (explained later in lesson 9).
To insert a module we can either go to Insert ->
Module or click the quick access icon, both in the VBA editor (see below).
In the example below we have inserted 3 regular
modules, 2 class modules, and 2 Userforms, on top of the 3 Sheet object modules
and ThisWorkbook module. We can see all that in the VBA project explorer on the
left hand side, while the space on the right hand side is dedicated to the
procedures and code for the respective module.
Procedures
A procedure contains the executable code that
performs a specific task. Each procedure will correspond to a macro, both
having the same name. However, a macro may consist of several procedures. The
terms macro and procedure, or even VBA project, are often interchangeable, so
don’t get too concerned about the exact terminology.
To add a new procedure go to Insert ->
Procedure and then write a name for the procedure. The name must start with a
letter, cannot have spaces, period, exclamation or other such characters, and
cannot exceed 255 characters.
Alternatively, we can also add a procedure
manually by writing the statements in the module window. A procedure has an
opening statement with the name of the procedure and a closing ‘End’ statement,
and anything in between is the executable code. For a normal subroutine or Sub
procedure (the most common type of procedure), the opening statement is ‘Sub’
followed by the name of the procedure, and the closing statement is ‘End Sub’.
See the example below.
That was actually our first macro and consists
of a Sub procedure called ‘MyFirstMacro’ within a standard module (Module1). MsgBox
is the function to display a conventional Microsoft message box on the screen. When
running the macro, it just displays the message “Hello”.
There are other types of procedures. The Function
procedures are similar to the Sub procedures with the peculiarity that they
return a value. They are also called ‘Functions’ and are often passed one or
more values called arguments. Function procedures can be called out from other
procedures (e.g. Sub), but can also be used directly in the worksheet as any
other Excel built-in function formula. Below is an example of a Function that
converts Fahrenheit to Celsius:
Function ConvertToCelsius(TempFahrenheit As
Integer) As Integer
ConvertToCelsius
= (TempFahrenheit - 32) / 1.8
End Function
This Function takes the temperature in
Fahrenheit degrees as an integer and returns the temperature in Celsius (also as
an integer). We can also access that function or formula from any cell in the
workbook.
As we have seen above in the Add Procedure
dialog box, there is another type of procedure. The Property procedures are
used with class modules to create properties for defined class objects.
On top of those three, there’s yet another very
important type of procedure that specifically refers to the Workbook and
Worksheet objects, and can only be inserted in ‘ThisWorkbook’ and respective
Sheet object modules. These are the Event procedures, which contain executable
code that is triggered upon a particular event happening.
Event procedures in the Workbook object module
(ThisWorkbook) respond to events such as a workbook opening, or closing, or
changing the sheet selection in the workbook, etc. The picture below shows the
drop down of possible Event procedures for ‘ThisWorkbook’ object module.
Observe the Workbook_Open procedure is selected. The code in that procedure
will run when the workbook opens.
Similarly, the Event procedures in the Worksheet
object module respond to events at the sheet level such as sheet activation, sheet
range/cell selection change, etc. Select a Sheet object module and see the
Event procedures available for the Worksheet object.
Procedures can either be Public (Public Sub or
just Sub for the Sub procedure example) or Private (Private Sub). Public
procedures are available from any module of the VBA project or even from other
workbook. Private procedures are only available within a particular module.
Running macros and calling procedures
There are several ways to run a macro. The
easiest way is to go to ‘Macros’ in the View or Developer tabs and select the
macro we want to run. It is also possible there under ‘Options’, to set a
shortcut key to run the macro.
Other way is simply to click the ‘Play’ button
in the standard toolbar of the VBA editor while having the cursor somewhere
inside the procedure in the module window. The ‘Play’, ‘Pause’ and ‘Stop’
buttons along with other features are part of the debugging tools of Excel VBA
that we will see later in lesson 10.
Another way is to assign the macro to a button
or object added somewhere in a worksheet. Insert a button from form controls or
any object from shapes, then right-click the button or object and assign the
macro. Change the caption of the button or shape if appropriate.
A procedure can be called from other procedure
using the function ‘Call’ followed by the name of the procedure, or simply
writing the procedure name (between standard modules). If we run ‘MyFirstMacro’
in the example below, it will display a message box first, and then call the
second procedure, which displays another message box.
No comments:
Post a Comment