Microsoft Office Tutorials and References
In Depth Information
An Important Benefit of Class Modules
Here’s the theoretical synopsis: A class is defined in a class module, and you
can think of a class as a blueprint or template for an object. In the context
of class modules, the term object can be almost any object in Excel whose
functionality you want to expand. This concept becomes clearer with VBA
examples you’ll see in this lesson dealing with controls that are embedded in
a worksheet, or are placed onto UserForms. You can have those controls all
respond to one single event, instead of needing to write numerous redundant
procedures for each control.
As you’ll see, a class module only serves the purpose of holding the code that defines (but does not
create) a class object. In some other module that is not a class module, such as a UserForm module
or workbook module (depending on the task you are solving), you can declare a variable of the class
type and create an instance of that class (known as instantiating the class) with the New keyword.
Upon instantiation, your declared variable becomes an object whose events, properties, and
methods are defined by your code in the class module.
An iMporTAnT BEnEfiT of clAss ModulEs
Suppose you have a UserForm with 12 TextBoxes, into which
a dollar figure for budgeted expenses is to be entered for each
month of the year, as in the example shown in Figure 21-3.
It’s important that only numbers are entered, so you want to
validate every TextBox entry to be numeric, while disallowing
entry of an alphabetic letter, symbol, or any character other
than a number. The following example can handle that for
TextBox1 in the UserForm module:
Private Sub TextBox1_KeyPress _
(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57 ‘numbers 0-9
KeyAscii = 0
MsgBox “You entered a non-numeric character.”, _
“Numbers only please!”
You can maybe get away with the redundancy of writing 12
separate events to monitor the entries in each TextBox. But what happens if your project requires
100 TextBoxes, or if the numeric validation process expands to allow decimals or negative numbers?
You’d have to do a lot of updates for each TextBox, and the volume of redundant code will create a
bad design that’s destined for human error and runtime failure.