This chapter shows you how to start building modular code—code broken up into individual components rather than all built together into a monolithic lump. You'll also see how to approach creating reusable code that you can use in other procedures.
The second part of this chapter discusses how you can build and use your own classes in VBA to implement custom objects, store information in them, and return information from them.
In this chapter you will learn to do the following:
Arrange your code in modules
Call a procedure
Pass information from one procedure to another
Understand what classes are and what they're for
Create an object class
The code that you've created so far in this book has been effective—it worked—but much of it has been less concise and less elegant than it might be. This section shows you how to make some improvements to your code.
Modular code is code composed of different procedures that you can use in combination. The name doesn't specifically come from the fact that you store your VBA code in modules (although you do continue to do so).
For example, suppose you're working in Word. You can take a monolithic approach and create a single procedure that does a lot of things: Create a document based on the user's choice of template, insert text and format it, save it in a particular folder under a name of the user's choice, print it to a specific printer, and then close it. Whew!
Or...you can take the modular approach and subdivide this lengthy series of tasks into several separate procedures—one for each task. You can then create a kind of master procedure that runs each of these individual task procedures. You will achieve the same effect as executing the large, monolithic procedure. But subdivided code is easier to read, test, and reuse. Think of it as employing multiple small Subs rather than a single large Sub.
You can also create new master procedures that reuse these individual task procedures.
Modular code has several advantages over code that lumps everything together in one long Sub or function. For one thing, it's often easier to write modular code because you create a number of short procedures, each of which performs a specific task.
You can usually debug these procedures relatively easily too, because their shorter length makes it simpler to identify, locate, and eliminate bugs.
The procedures will also be more readable because they're less complex, and you can more easily follow what they do.
Modular code is also more efficient, for four reasons:
By breaking your code into procedures, you can repeat their tasks at different points in a sequence of procedures without needing to repeat the lines of code. Having less code should make your procedures run faster.
By reusing whole procedures, you can reduce the amount of code you have to write. And by writing less code, you give yourself less chance to write new errors into your program.
If you need to change an item in the code, you can make a single change in the appropriate procedure instead of having to make changes at a number of locations in a long procedure (and perhaps missing some of them). This change then also applies to any procedures that call the procedure.
You can call individual procedures from other procedures without having to assimilate them into the other procedures. Just think how tedious it would be if you had to create each of VBA's many built-in functions from scratch instead of being able to invoke them at will. You can do much the same with functions you create—reuse them rather than reinventing the wheel.
How much you worry about breaking down your code so it's modular will vary from project to project and from procedure to procedure. For example, if you record a macro to perform a simple, one-time task on a number of presentations, there's no need to worry about breaking it down into its components and formalizing them as procedures. Just go ahead and use a single procedure.
However, if you sit down to plan a large procedure that's going to automate the creation of your company's budget-estimate spreadsheets, you can benefit greatly from dividing the code into a set of several procedures. This task is not only complex and requires a lot of code, it's also a program that must be reused every time there's a new budget proposal.
You can go about creating modular code in two main ways:
Record (if the application you're using supports the VBA Macro Recorder) or write a procedure as usual and then examine it and break it into modules as necessary. This is a great way to start creating modular code, but it's usually less efficient: You'll end up spending a lot of time retrofitting your original, large procedure as you break it into smaller, separate procedures.
List the different actions that your project requires, then code each action (or set of actions) as a separate procedure. This method requires more forethought but usually proves more efficient in the long run.
Once you've created a set of procedures, you can move them to a different module within the same project, or even to a different project. By grouping your procedures in modules, you can easily distribute the procedures to your colleagues without including any they don't need. And by grouping your modules in projects, you can even more easily distribute the modules and procedures. In addition, you can remove from your immediate working environment any modules of code that you don't need, thus avoiding slowing your computer.
When a procedure needs to use another procedure, it calls it (by name) in the same way that you learned in Chapter 9 to call a built-in function like MsgBox
. To call a procedure in the same project, either enter the name of the procedure to be called as a statement or use a Call
statement with the name of the procedure.
The syntax for the Call
statement is the same for procedures as for functions:
[Call]name
[,argumentlist
]
Here, name is a required String argument giving the name of the procedure to call. Meanwhile, argumentlist is an optional argument providing a comma-delimited list of the variables, arrays, or expressions to pass to the procedure. You use argumentlist only for procedures that require arguments.
Calling involves two procedures, the caller and the called. For example, the following CreateReceiptLetter
procedure (the caller) calls the procedure FormatDocument
(the called):
Sub CreateReceiptLetter()
'other actions here
Call FormatDocument
'other actions here
End Sub
Most programmers omit the Call
keyword, using just the name of the procedure. This next code does the same thing as the previous code example:
Sub CreateReceiptLetter()
'other actions here
FormatDocument
'other actions here
End Sub
However, as with calling built-in functions, some programmers believe that using the Call
keyword can make it clearer that your code is calling a procedure, and it enables you to search more easily for your calls. (When debugging, you can see what procedures are calling others by choosing the Call Stack option on the Editor's View menu. This feature is only available in break mode, however, not during design time.) In the following example, a procedure named Caller
calls a procedure named Called
, which takes the String argument strFeedMe
. Note that when you use Call
, you need to enclose the argument list in parentheses:
Sub Caller()
Call
Called("Hello")
End Sub
Sub Called(ByVal strFeedMe As String)
Msgbox strFeedMe
End Sub
Again, you can omit the Call
keyword and, if you wish, the parentheses, but achieve the same result:
Sub Caller() Called "Hello" End Sub
As well as calling a procedure in the same project, you can call a procedure in another open project in the same host application (but usually not in another application). Typically, the syntax used to call a procedure in another project is as follows, although it can vary by application and version:
Project.Module.Procedure
To call a procedure in another project, you need to add a reference to that project in the VBA Editor's References dialog box. Choose Tools
Let's turn our attention to another benefit of modular code: You can refine your code and make it run faster by making logical improvements and visual improvements.
Breaking a large procedure into several smaller procedures can improve the logic of your code by forcing you to consider each set of actions the procedure takes as modular, which means they're separate from other sets of actions. And you can also improve the logic of your code in other ways: by using explicit variable declarations, by stripping out unnecessary statements to simplify any code you record, and by using With
statements to eliminate repetitive object references. The following sections describe ways you can improve the quality of your code.
Instead of declaring variables implicitly, declare all your variables explicitly:
Dim strName As String strName = "Lola Montez"
Use that approach rather than the implicit declaration approach, which skips declaring the variable and merely assigns a value to it (which implicitly creates it):
strName = "Lola Montez"
Explicit declaration allows VBA to allocate only as much memory as that variable type needs. What's more, by specifying the data type of a variable, you relieve VBA of the necessity to waste time figuring out the data type each time the variable appears in your code. Better still, you avoid the risk of unintentionally storing the wrong type of data in the variable. Because the variable is explicitly typed, VBA displays an error message rather than storing the data and changing the variable type.
Table 16.1 shows the details on the amounts of memory that the different types of variables require.
Table 16.1. Memory consumed by the different types of variables
Variable | Memory Needed (Bytes) |
---|---|
Boolean | 2 |
Byte | 1 |
Currency | 8 |
Date | 8 |
Variant/Decimal | 12 |
Double | 8 |
Integer | 2 |
Long | 4 |
Object | 4 |
Single | 4 |
String | Variable-length strings: 10 bytes plus the storage required for the string, which can be up to about two billion characters; fixed-length strings: the number of bytes required to store the string, which can be from 1 to about 64,000 characters |
Variant | Variants that contain numbers: 16 bytes; variants that contain characters: 22 bytes plus the storage required for the characters |
How much memory you save by specifying data types, and how much difference choosing variable types makes to your procedures, depends on the type of work you're doing. For example, if you store a million characters in a Single variable, the 12 bytes you save by specifying that it's a String variable rather than a Variant variable make little difference. But if you use many variables on a computer with limited memory, specifying the appropriate data types for your variables may save enough memory to enable your procedure to run where it otherwise wouldn't have been able to, or at least enable it to run faster. Of course, hardware is continually improving—and memory is hardware. Now that RAM is becoming cheap and plentiful, conserving memory is becoming increasingly less an issue for programmers.
A second reason for declaring your variables explicitly rather than implicitly is to make your code easier to read and to debug. And a third reason is that you can implement some runtime range checking. If you know something will be less than 32,768, and you therefore declare it as being the Integer data type (rather than the Long type), you'll automatically get a helpful error if a Long-size value creeps into it somehow at runtime.
When you're performing multiple actions with an object, you can often use With
statements to avoid repeating the object reference for each action. This simplifies your code. It becomes easier to read. And it may make it run marginally faster.
For example, the following statements contain multiple references to the first Paragraph
object—Paragraphs(1)
—in the ActiveDocument
object in Word:
ActiveDocument.Paragraphs(1).Range.Font.Bold = True ActiveDocument.Paragraphs(1).Range.Font.Name = "Times New Roman" ActiveDocument.Paragraphs(1).LineSpacingRule = wdLineSpaceSingle ActiveDocument.Paragraphs(1).Borders(1).LineStyle = wdLineStyleDouble ActiveDocument.Paragraphs(1).Borders(1).ColorIndex = wdBlue
You can replace this redundancy by employing a With
structure that references the Paragraphs(1)
object in the ActiveDocument
object to simplify the number of references involved:
With ActiveDocument.Paragraphs(1) .Range.Font.Bold = True .Range.Font.Name = "Times New Roman" .LineSpacingRule = wdLineSpaceSingle .Borders(1).LineStyle = wdLineStyleDouble .Borders(1).ColorIndex = wdBlue End With
When you need to work with multiple child objects contained within a single parent object, you can either use separate With
statements or pick the lowest common denominator of the objects you want to work with and use an outer With
statement along with nested With
statements for the child objects.
If you wish, you can further reduce the number of object references in the previous code example by using nested With
statements for the Font
object in the Range
object and for the Borders(1)
object, like this:
With
ActiveDocument.Paragraphs(1)With
.Range.Font .Bold = True .Name = "Times New Roman" End With .LineSpacingRule = wdLineSpaceSingleWith
.Borders(1) .LineStyle = wdLineStyleDouble .ColorIndex = wdBlue End With End With
With
statements are great for reducing repetitive object references and making your code easier to read, but don't use them just because you can. If you have only one statement within a With
statement, as in the following example (which again uses Word), you're probably wasting your time typing the extra code to set up the With
structure:
With ActiveDocument.Sections(1).Headers(wdHeaderFooterPrimary) _ .Range.Words(1) .Bold = True End With
Likewise, don't nest With
statements unless you need to—it gets confusing, like this bizarre example:
With ActiveDocument With .Sections(1) With .Headers(wdHeaderFooterPrimary) With .Range With .Words(1) With .Font .Italic = True .Bold = False .Color = wdColorBlack End With End With End With End With End With End With
This code is better when written like this:
With ActiveDocument.Sections(1).Headers(wdHeaderFooterPrimary).Range. _ Words(1).Font .Italic = True .Bold = False .Color = wdColorBlack End With
When you use a Select Case
statement, arrange the Case
statements so that the most likely ones appear first. This saves VBA some work and time—VBA goes down through the list of Case
statements until it finds a match, so the earlier in the list it scores a match, the quicker the execution of the statement.
If you need to implement a setting (especially a Boolean one) every time a particular procedure runs, there's no point in checking the current value. For example, suppose you wanted to make sure the EnableAutoRecover
property (a Boolean property that sets or returns whether the AutoRecover feature is on for the current workbook) of the ActiveWorkbook
object in Excel is set to True
. You could check the current value of EnableAutoRecover
and, if it is False
, set it to True
like this:
If ActiveWorkbook.EnableAutoRecover = False Then _ ActiveWorkbook.EnableAutoRecover = True
But that wastes code. Instead, simply go ahead and just set the property to True
:
ActiveWorkbook.EnableAutoRecover = True
To improve the efficiency of your code, try to remove all unused elements from it. When creating a complex project with many interrelated procedures, it's easy to end up with some procedures that are almost or entirely useless. You were trying out various approaches and perhaps sketched in a couple of procedures that ended up never being used, for example.
You'll find it easier to remove superfluous procedures if you've commented your code comprehensively while creating it so you can be sure that what you're removing is unused rather than used. If you're in doubt as to which procedure is calling which, display the Call Stack dialog box (see Figure 16.1); choose View
Alternatively, try one of these techniques:
Set a breakpoint at the beginning of a suspect procedure so that you'll be alerted when it's called.
Display message boxes at decisive junctures in your code so you can see what's happening: Is the procedure ever called?
Use a Debug.Print
statement at an appropriate point (again, perhaps the beginning of a procedure) to temporarily log information in the Immediate window.
Before you remove an apparently dead procedure from your code, make sure not only that it's unused in the way the code is currently being run, but also that it's not used in ways in which the procedure might be run were circumstances different. If you think that the procedure might still be used, try moving it to a project from which you can easily restore it rather than deleting it altogether.
Once you've removed any unused procedures, examine the variables in the procedures. Even if you're using the Option Explicit
declaration and declaring every variable explicitly, check that you haven't declared variables that end up not being used. For simple projects, you'll be able to catch the unused variables by using the Locals window to see which of them never get assigned a value. For more complex projects, you may want to try some of the available third-party tools that help you remove unneeded elements from your code.
If in doubt, comment out the declaration of the supposedly superfluous variable, make sure you're using Option Explicit
, and run the code a few more times, exercising the different paths that it can take—the various tasks that the code performs. If you don't get a "Variable Not Defined" compile error, you can probably eliminate the variable.
Removing unused procedures and variables isn't crucial. They do no real harm; they're just a bit of debris. But they do clutter up your code, potentially making it harder to understand and modify if you come back to it later for maintenance or reuse.
Another way to improve your code is to modify it so it's as easy as possible to read, maintain, and modify.
As you've seen in the examples so far in this book, you can make code much easier to follow by indenting some lines of code with tabs or spaces to show their logical relation to each other or to visually illustrate subordination and structures such as loops.
You can click the Indent and Outdent buttons on the editor's Edit toolbar or press Tab and Shift+Tab to quickly indent or unindent a selected block of code, with the relative indentation of the lines within the block remaining the same.
Use the line-continuation character (a space followed by an underscore) to break long lines of code into two or more shorter lines. Breaking lines makes long statements fit within the Code window on an average-size monitor at a readable point size and enables you to break the code into more logical segments.
You can't use the line-continuation character to break strings, however. If you want to break a long string, you must divide the string into smaller strings and then use the concatenation character (&
) to attach the parts together again. You can separate the parts of the divided string (which are merely separated by the line-continuation character). For example, consider a long string such as this:
strMessageText = "The macro has finished running. Please check your presentation to ensure that all blank slides have been removed."
Instead, you could divide the string into two, and then rejoin it like this:
strMessageText = "The macro has finished running. "& _
"Please check your presentation to ensure that "& _
"all blank slides have been removed."
To make your code more readable, use blank lines to separate statements into logical groups. For example, you might segregate all the variable declarations in a procedure as shown in the following example so that they stand out more clearly:
Sub Create_Rejection_Letter Dim strApplicantFirst As String, strApplicantInitial As String, _ strApplicantLast As String, strApplicantTitle As String Dim strJobTitle As String Dim dteDateApplied As Date, dteDateInterviewed As Date Dim blnExperience As Boolean strApplicantFirst = "Shirley" strApplicantInitial = "P" strApplicantLast = "McKorley" ]
You can use variables to simplify and shorten complex syntax. For example, you could display a message box by using an awkwardly long statement such as this one:
If MsgBox("The document contains no text." & vbCr & vbCr _ & "Click the Yes button to continue formatting the document." & _ " Click the No button to cancel the procedure.", _ vbYesNo & vbQuestion, _ "Error Selecting Document: Cancel Procedure?") Then
Alternatively, you could use one String variable for building the message and another String variable for the title:
Dim strMsg As String Dim strTBar As String strMsg = "The document contains no text." & vbCr & vbCr strMsg = _ strMsg & "Click the Yes button to continue formatting the document. " strMsg = strMsg & "Click the No button to cancel the procedure." strTBar = "Error Selecting Document: Cancel Procedure?" If MsgBox(strMsg, vbYesNo & vbQuestion, strTBar) Then
At first sight, this code looks more complex than the straightforward message box statement, mostly because of the explicit variable declarations that increase the length of the code segment. But in the long run, this approach is much easier to read and modify.
In the previous example, you could also replace the vbYesNo & vbQuestion
part of the MsgBox
statement with a variable (preferably a Long rather than a Variant). But doing so makes the code harder to read and is seldom worthwhile.
Often when you call another procedure, you'll need to pass information to it from the calling procedure and, when the procedure has run, pass back other information or a modified version of the same information.
The best way to pass information from one procedure to another is by using arguments. You declare the arguments to pass in the declaration line of the procedure that passes them. The arguments appear in the parentheses after the procedure's name. You can pass either a single argument (as the first of the following statements does) or multiple arguments separated by commas (as the second does):
Sub PassOneArgument(MyArg)
Sub PassTwoArguments(FirstArg, SecondArg)
As with functions (discussed in Chapter 9), you can pass an argument either by reference or by value. When a procedure passes an argument to another procedure by reference, the recipient procedure gets access to the memory location where the original variable is stored and can change the original variable. By contrast, when a procedure passes an argument to another procedure by value, the recipient procedure gets only a copy of the information in the variable and can't change the information in the original variable.
Passing an argument by reference is useful when you want to manipulate the variable in the recipient procedure and then return the variable to the procedure from which it originated. Passing an argument by value is useful when you want to use the information stored in the variable in the recipient procedure and at the same time make sure the original information in the variable doesn't change.
By reference is the default way to pass an argument, but you can also use the ByRef
keyword to state explicitly that you want to pass an argument by reference. Both of the following statements pass the argument MyArg
by reference:
Sub PassByReference(MyArg) Sub PassByReference(ByRef MyArg)
To pass an argument by value, you must use the ByVal
keyword. The following statement passes the ValArg
argument by value:
Sub PassByValue(ByVal ValArg)
In practice, however, you'll rarely, if ever, need to employ ByVal
. Arguments are nearly universally passed by reference, the default.
If necessary, you can pass some arguments for a procedure by reference and others by value. The following statement passes the MyArg
argument by reference and the ValArg
argument by value:
Sub PassBoth(ByRef MyArg, ByVal ValArg)
You can explicitly declare the data type of arguments you pass in order to take up less memory and ensure that your procedures are passing the type of information you intend them to. But when passing an argument by reference, you need to make sure that the data type of the argument you're passing matches the data type expected by the called procedure. For example, if you declare a String in the caller procedure and try to pass it as an argument when the called procedure is expecting a Variant, VBA gives an error.
To declare the data type of an argument, include a data-type declaration in the argument list. The following statement declares MyArg
as a String and ValArg
as a Variant:
Sub PassBoth(MyArgAs String
, ValArgAs Variant
)
You can specify an optional argument by using the Optional
keyword. Place the Optional
keyword before the ByRef
or ByVal
keyword if you need to use ByRef
or ByVal
:
Sub PassBoth(ByRef MyArg As String, ByVal ValArg As Variant, _ Optional ByVal MyOptArg As Variant)
Listing 16.1 shows a segment of a procedure that uses arguments to pass information from one procedure to another.
Example 16.1. Passing arguments from one procedure to another
1. Sub GetCustomerInfo() 2. Dim strCustName As String, strCustCity As String, _ strCustPhone As String 3. 'Get strCustName, strCustCity, strCustPhone from a database 4. CreateCustomer strCustName, strCustCity, strCustPhone 5. End Sub 6.
7. Sub CreateCustomer(ByRef strCName As String, _ ByRef strCCity As String, ByVal strCPhone As String) 8. Dim strCustomer As String 9. strCustomer = strCName & vbTab & strCCity _ & vbTab & strCPhone 10. 'take action with strCustomer string here 11. End Sub
Listing 16.1 contains two minimalist procedures—GetCustomerInfo
and CreateCustomer
—that show how to use arguments to pass information between procedures:
The first procedure, GetCustomerInfo
, explicitly declares three String variables in line 2: strCustName, strCustCity
, and strCustPhone
.
Line 3 contains a comment indicating that you would write additional code here to obtain the data and assign information to the variables.
Line 4 calls the CreateCustomer
procedure and passes to it the variables strCustName, strCustCity
, and strCustPhone
as arguments. Because this statement doesn't use the Call
keyword, the arguments aren't enclosed in parentheses.
Execution then switches to line 7, which starts the CreateCustomer
procedure by declaring the three String arguments it uses: strCName
and strCCity
are to be passed by reference, and strCPhone
is to be passed by value.
Line 8 declares the String variable strCustomer
. Line 9 then assigns to strCustomer
the information in strCName
, a tab, the information in strCCity
, another tab, and the information in strCPhone
.
Line 10 contains a comment indicating where the procedure would take action with the strCustomer
string (for example, dumping it into some kind of primitive database), and line 11 ends the procedure.
Another way to pass information from one procedure to another is to use either private variables or public variables. You can use private variables if the procedures that need to share information are located in the same module. If the procedures are located in different modules, you'll need to use public variables to pass the information.
Listing 16.2 contains an example of passing information by using private variables.
Example 16.2. Passing data using a private variable
1. Private strPassMe As String 2. 3. Sub PassingInfo() 4. strPassMe = "Hello." 5. PassingInfoBack 6. MsgBox strPassMe 7. End Sub 8. 9. Sub PassingInfoBack() 10. strPassMe = strPassMe & " How are you?" 11. End Sub
Listing 16.2 begins by declaring the private String variable strPassMe
at the beginning of the code sheet for the module. strPassMe
is then available to all the procedures in the module.
The PassingInfo
procedure (lines 3 to 7) assigns the text Hello
. (with the period) to strPassMe
in line 4 and then calls the PassingInfoBack
procedure in line 5. Execution then shifts to line 9, which starts the PassingInfoBack
procedure. Line 10 adds How are you?
with a leading space to the strPassMe
String variable. Line 11 ends the PassingInfoBack
procedure, at which point execution returns to the PassingInfo
procedure at line 6, which displays a message box containing the strPassMe
string (now Hello. How are you?). Line 7 ends the procedure.
A class is the formal definition of an object—typically, a custom object. By defining classes, you can build your own custom objects. A class acts as a sort of template for an object: Once you've defined the class, you can create objects based on it. The relationship between class and object is sometimes described as similar to a cookie cutter and a cookie or a blueprint and the houses based on that blueprint.
Another way to think of this relationship between class and object is to recall the distinction between design time and runtime. You create a class during design time by writing code that describes the object (or multiple objects) that will come into being during runtime when the class code executes. The phrase come into being is more commonly expressed as follows: an object is instantiated. (An instance—the object—of the class comes into existence during runtime.)
Programming means processing information. You can store information within ordinary code, such as storing the information Donald in a string variable:
MyString = "Donald"
And you can manipulate or process that information in ordinary code, as you've been doing throughout this book. Here we process some data information by figuring out the length of a string:
MsgBox Len(myString)
However, you can also process data within classes. You can use classes to store information, to process information, and to make information accessible to the various objects in an application. For example, if you retrieve information from outside the host application you're using and need to make it available to your VBA procedures in that host application, you might encapsulate it in a class to simplify access to the information. Using a class is cleaner and can be more efficient than, say, using public variables to store information and make it available to various procedures.
To create a class, you insert a class module in a project (Insert
A major distinction between a class module and a regular code module is that you don't directly execute code in a class module. Instead, you declare an object variable of the class's type (in a regular code module). You can then use the class's members (its properties and methods) in your regular code.
The concept of classes can be difficult to grasp, so the following sections present a simple example of a class that relates to something physical—the book you're holding. The example describes a class named Book
that contains the salient information about a book. After creating the class, the example adds this book's information to it so that you can see how the class works. Entire books endeavor to explain classes and objects (object-oriented programming). But I'll give you a taste of it here.
The following example class works in any VBA host application.
Before you start creating a class, decide the following:
What does the object that the class describes do?
What information does the class need to contain for the object to do what it's supposed to do? You use variables and properties to store this information. You use variables to store information used privately, internally inside the object, and properties to make available pieces of that information that need to be accessed from outside the object. You can create both read-only and read/write properties.
What actions will the user need to take with the class? Things a class can do, its behaviors, are called its methods. You create subroutines and functions to implement the class's methods—subroutines for the methods that return no value after doing their job and functions for the methods that do return a value after executing.
Each object based on our Book
class will contain information about a book project. The class we'll create will need properties for storing information such as the title, author, and price and will need a method that displays all the book information at the same time.
The first step in creating your class is to insert a class module in the appropriate project. You create a class module in much the same way you create a regular module.
In the Project Explorer, right-click the target project or one of the items it contains and choose Insert
If you have the Require Variable Declarations check box selected (on the Editor page of the Tools
If you don't have the Require Variable Declarations option selected, it's a good idea to type in the Option Explicit
statement anyway to force yourself to declare variables explicitly in the class module.
Now change the name of the class to something more descriptive than Class
n
. Display the Properties window (if it's not already displayed) and enter the new name in the (Name)
text box. Make the name descriptive, because you'll be using it in your code and you'll want its purpose to be easily grasped. We can name our example class Book
. Press Enter or click elsewhere in the Visual Basic Editor window to make the change take effect.
The Instancing
property controls whether a class module is visible from a project that contains a reference to the project that the class module is in. The default setting, 1 – Private
, prevents other projects from seeing the class module and from working with instances (object) of that class. The other setting is 2 – PublicNonCreatable
, which allows a project with a reference set to the class's project to see the class and work with instances of it created by the class's project. The project with the reference, however, still can't create instances of the class by itself.
To permit an outside project access to instances of a class, set the Instancing
property to 2 – PublicNonCreatable
. Otherwise, leave the default setting of 1 – Private
intact. With the default Private
setting, only the project that created the class can access it.
Next, declare the variables and constants that the class will need for its internal operations. These declarations work just like the declarations you've met so far in the book, except that you'll probably want to use a naming convention to indicate that the variables and constants belong to the class. We'll use the prefix book
on the constants and variables to make it easy for the programmer to see that they're part of the Book
class.
The Book
class uses the declarations shown in the following snippet to declare one constant (bookName
) and five variables (bookTitle, bookAuthor, bookPages, bookPrice
, and bookPublicationDate
) of assorted data types:
Const bookName = "Book Project" Dim bookTitle As String Dim bookAuthor As String Dim bookPages As Long Dim bookPrice As Currency Dim bookPublicationDate As Date
Now add the properties to the class. Table 16.2 lists the properties that the Book
class uses.
Table 16.2. Properties of the Book class
Description | |
---|---|
| A read-only String property that contains the International Standard Book Number (ISBN) for the book. Creating a new object of the |
| A read/write String property that sets or returns the formal title of the book. |
| A read/write String property that sets or returns the author's name. |
| A read/write Long property that sets or returns the page count of the book. |
| A read/write Currency property that sets or returns the price of the book. |
| A read/write Date property that sets or returns the publication date of the book. |
| A read/write Boolean property that specifies whether the book is hardcover ( |
| A read/write Byte property that sets or returns the number of CDs included with the book. |
You can create properties for a class in either of two ways. The first way is less formal than the second but provides you with less control over the properties.
The first way to create a property is to declare a Public variable in the class module. Doing so creates a read/write property with the name of the variable. For example, the following statement (entered in a class module) creates a read/write Boolean property named HardCover
:
Public HardCover As Boolean
Using a Public variable is an easy way to create a property, but it's a bit limited: You can't choose to make the property read-only (or write-only), and you can't execute any other code when you set or return the value of the property.
You can then set and return the property in the normal manner. For example, say we've created the Boolean property HardCover
in an instance named MastVBA
of the Book
class. The following statements set (storing or writing data in) the property and then display a message box returning (reading the value from) it:
MastVBA.HardCover = False MsgBox MastVBA.HardCover
The second and more formal way to create a property is to use property procedures. There are three types of property procedures—Property Let, Property Get
, and Property Set
:
A Property Let
procedure assigns a value to a property.
A Property Get
procedure returns the value from a property.
A Property Set
procedure sets a reference to an object.
You typically use these procedures in pairs, pairing a Property Get
procedure with a Property Let
procedure or a Property Set
procedure with a Property Let
procedure. You can also use a Property Let
procedure on its own to create a read-only property.
To assign a value to a property, you use a Property Let
procedure. The syntax for a Property Let
procedure is as follows:
Property Letname
([arglist
,]value
) [statements
] End Property
These are the components of the syntax:
The Property
keyword starts the procedure, and the End Property
keywords end the procedure.
name is a required argument specifying the name of the property procedure being created. If you're creating a Property Get
procedure as well for this property, it will use the same name as the Property Let
procedure.
arglist is a required argument listing the arguments that are passed to the procedure. If arglist contains multiple arguments, you separate them with commas.
For example, the following Property Let
procedure creates the String property Title
for the class, assigning the argument NewTitle
and passing its value to the variable bookTitle
:
Property Let Title(NewTitle As String) bookTitle = NewTitle End Property
If you don't add a Property Get
procedure for this Title
data, the property named Title
will be write-only. Write-only properties aren't widely useful, so the next step is to assign a method of reading the property. Then it becomes a read/write property.
To return a value from a property, you use a Property Get
procedure. The syntax for a Property Get
procedure is as follows:
Property Getname
[(arglist
)] [Astype
] [statements
] End Property
The components of the syntax are the same as for the Property Let
procedure, except for two things:
First, Property Get
adds the optional type argument, which specifies the data type for the property.
Second, for Property Get
, the arglist argument is optional. You can have arguments for Property Get
procedures, but you won't usually need to. If you do use arguments, their names and data types must match those in the corresponding Property Let
procedure.
For example, the following Property Get
procedure creates the String property Title
, assigning to it the contents of the bookTitle
variable:
Property Get Title() As String Title = bookTitle End Property
If this Property Get
procedure existed alone (without being paired with a Property Let Title
procedure), it would be a read-only property. However, because we've paired it with the Property Let Title
procedure shown in the previous section, you now have a read/write property, and the Title
property is ready for use.
Instead of assigning a value to a property, you can assign an object to it. To do so, you use a Property Set
procedure rather than a Property Let
procedure. The syntax for a Property Set
procedure is as follows:
Property Setname
([arglist
,]reference
) [statements
] End Property
The components of the syntax are the same as for the Property Let
procedure, except that Property Set
uses the reference argument rather than the value
argument. reference is a required argument specifying the object to reference.
For example, the following Property Set
procedure creates the object property Where
that references a range:
Property Set Where(rngR As Range) bookRange = rngR End Property
Listing 16.3 shows the full listing of properties for the Book
class.
Example 16.3. All the properties of the Book
class
1. Public Property Let Title(strT As String) 2. bookTitle = strT 3. End Property 4. 5. Public Property Get Title() As String 6. Title = bookTitle 7. End Property 8. 9. Public Property Let Author(strA As String) 10. bookAuthor = strA 11. End Property 12. 13. Public Property Get Author() As String 14. Author = bookAuthor 15. End Property 16. 17. Public Property Let Pages(intPages As Integer) 18. bookPages = intPages 19. End Property 20. 21. Public Property Get Pages() As Integer 22. Pages = bookPages 23. End Property 24. 25. Public Property Let Price(curP As Currency) 26. bookPrice = curP 27. End Property 28. 29. Public Property Get Price() As Currency 30. Price = bookPrice 31. End Property 32. 33. Public Property Let PublicationDate(dtePD As Date) 34. bookPublicationDate = dtePD 35. End Property
36. 37. Public Property Get PublicationDate() As Date 38. PublicationDate = bookPublicationDate 39. End Property 40. 41. Public Property Get Available() As Boolean 42. Available = Date >= bookPublicationDate 43. End Property
In Listing 16.3, each property for the Book
class is declared as Public so that it is publicly accessible.
The code illustrates how you organize your paired procedures by putting each Property Let
procedure next to the corresponding Property Get
procedure: The Property Let Title
procedure in lines 1 through 3 is matched by the Property Get Title
procedure in lines 5 through 7, and so on for the Author, Pages, Price
, and PublicationDate
property procedures.
Pairing the procedures makes it easy to read the code to make sure each procedure that should have a counterpart does have one and to make sure the arguments match. But you can separate your Property Let
procedures and Property Get
procedures if you prefer.
You'll notice that the Property Get Available
property procedure in lines 41 through 43 doesn't have a corresponding Property Let
procedure. The Available
property is read-only, with its value being generated inside the object, never assigned from code outside the object (as a Property Let
writing procedure would permit).
Now that we've created properties as places to store data, it's time to add functionality that will process that data. It's time to add the class's methods by adding subroutines and functions as necessary. Aside from their code being located within the class module—which causes them to show up as methods in the list of properties and methods for the class—subroutines and functions within a class are like the subroutines and functions you use in ordinary code modules.
The Book
class uses only one method, ShowInfo
, which displays a message box showing the properties of the book. Listing 16.4 displays the ShowInfo
procedure.
Example 16.4. The ShowInfo
method of the Book
class
1. Sub ShowInfo() 2. Dim strM As String 3. strM = "Title:" & vbTab & bookTitle & vbCr 4. strM = strM & "Author:" & vbTab & bookAuthor & vbCr 5. strM = strM & "Pages:" & vbTab & bookPages & vbCr 6. strM = strM & "Price:" & vbTab & "$" & bookPrice & vbCr 7. strM = strM & "Date:" & vbTab & Me.PublicationDate & vbCr 8. If Me.Available Then strM = strM & vbCr & "AVAILABLE NOW" 9. MsgBox strM, vbOKOnly + vbInformation, bookName _ & " Information" 10. End Sub
The ShowInfo
procedure builds a string containing the information from the class and then displays the string in a message box. Here's what happens:
Line 2 declares the String variable strM
, which the procedure uses to store the information for the prompt
argument in the message box.
Line 3 adds to strM
the text Title:
, a tab, the contents of the bookTitle
variable (which contains the title of the book in the object), and a carriage return.
Line 4 builds on strM
, adding the author information. Likewise, line 5 adds the information on the page count, and line 6 adds the price information (including a dollar sign for completeness).
Line 7 also builds on strM
, adding the date information. However, instead of using the class's internal variable (bookPublicationDate
) to return the date stored, it calls the PublicationDate
property of the object (which is identified by the Me
keyword). This is by way of an example—returning bookPublicationDate
works fine too. But you'll see the difference when you retrieve information from the object: Instead of supplying the variable, VBA runs the Property Get PublicationDate
procedure to return the information.
Line 8 returns the Available
property of the object (again referred to as Me
). If Available
is True
, this statement adds a blank line (another vbCr
) and the string AVAILABLE NOW
to strM
.
Line 9 displays an OK message box containing strM
. The message box title is set to bookName
(the constant that contains the text Book Project
) and Information
, and the message box uses an Information icon.
Recall that you can't execute class code directly or from within the Code window. You can't put your insertion point inside the ShowInfo
procedure and press F5 to run the code or F8 to step through the code. A class is a description, and before you can execute or test a class, you must create an instance of the class. You can't test the plumbing in a house by just looking at the blueprints before the house has been built. In other words, you must create an object based on the class template, then test the object.
To instantiate, then interact with, an object, you must write code in an ordinary code module (like the modules we've been using throughout this book so far, such as the Module1
or NewMacros
module).
To use the class you created, you create a new instance of the object by using the New
keyword in either a Dim
statement or a Set
statement. For example, the following statement creates a new Book
-class object variable:
Dim myBook As New Book
The following statements declare an Object variable named bookAnotherBook
and then assign to it a new instance of the Book
object:
Dim bookAnotherBook As Object Set bookAnotherBook = New Book
You can then access the properties and methods of the Book
object as you would any other VBA object's properties and methods (note the syntax: objectVariableName.Property
). For example, the following statement sets the Price
property of bookAnotherBook
:
bookAnotherBook.Price = 54.99
Listing 16.5 contains a short procedure called Class_Test
that shows the Book
class in action. Type this procedure into an ordinary code module (not a class module). And be sure the module you type this into is in the same project as the Book
class module.
Example 16.5. Testing the Book
class
1. Sub Class_Test() 2. 3. Dim myBook As New Book 4. 5. myBook.Title = "Mastering VBA for Microsoft Office 2010" 6. myBook.Price = 49.99 7. myBook.Author = "Richard Mansfield" 8. myBook.Pages = 840 9. myBook.PublicationDate = #7/17/2010# 10. 11. myBook.ShowInfo 12. 13. End Sub
The listing shows an example of using the new class. Here's what happens:
Line 1 begins the Class_Test
procedure, and line 13 ends it.
Line 2 is a spacer. Line 3 declares a new object variable named myBook
of the Book
class. Line 4 is another spacer.
Lines 5 through 9 set the five properties of the myBook
object—Title, Price, Author, Pages
, and PublicationDate
—as you'd set the properties for any other object. Note that the object name (the object variable name) is separated by a period from the properties and methods of that object.
Line 10 is a spacer. Line 11 invokes the ShowInfo
method of the myBook
object—again, as you'd invoke a method for any other object.
Here's one more thing to try. As you remember, the Available
property is implemented as a read-only Boolean property. Try to set this property by entering the following statement in the Code window in the Class_Test
procedure right after the declaration of myBook
:
myBook.Available = True
When you press the . key after typing myBook
, VBA displays Available
on the list of properties and methods. But once you've entered it and an equal sign (myBook.Available =
), VBA doesn't display the Auto List Members list with False
and True
as it would for a read/write Boolean property because Available
isn't available in this context. Second, when you try to run the code, you'll get a compile error, "Can't assign to read-only property."
Rather than use a single lengthy, complex procedure that accomplishes many tasks at once, programmers usually subdivide their code into smaller, self-contained procedures—dedicated to a single, discrete task.
Shorter, self-contained, single-task procedures offer the programmer several advantages. Name three.
You execute a procedure by calling it from within your programming code.
How do you call a procedure?
Sometimes a procedure requires that you pass it some information. For example, a procedure that searches text and makes some style changes to it will require that you pass the text you want modified.
Sometimes a procedure passes back information to the procedure that called it. For example, it might pass back a message describing whether the actions taken in the procedure were (or were not) accomplished successfully.
What kind of procedure can pass back information to the caller?
Contemporary computer programs employ classes for various reasons—to help organize large programs, to make code more easily reusable, to provide certain kinds of security, or as a superior substitute for public variables. But beginners sometimes have a hard time wrapping their mind around the concept, particularly the relationship between classes and objects.
What is the difference between a class and an object?
Choose the correct answer (only one answer is correct):
A class is like a cookie and an object is like a cookie cutter.
A class is like a programmer and an object is like a module.
A class is like a blueprint and an object is like a house built from that blueprint.
The VBA Editor employs a special kind of module for creating classes.
How do you create a class module in the VBA Editor?
18.117.232.239