4

Object-Oriented Theory and VBA

A wise sage once said: divid et impera (divide and conquer). The original statement was probably made about an impending battle, as in, divide the enemy and conquer them by divisions. Interestingly enough, this advice is apropos of the way we can conquer software engineering problems. We can divide the problem into many smaller, discrete problems and conquer them individually, or we can permit the problem to divide our attentions and the problem will conquer us.

A well-considered rule of thumb is to organize a solution by addressing the most complex and critical problems first. Generally, these challenging pieces act as a forcing function for significant portions of the solution, and an application without these critical pieces would be considered incomplete.

This chapter is about the mechanism that supports dividing and conquering a problem, the class. In Chapter 5 you will learn about general object-oriented theory and how this theory is supported in Visual Basic for Applications.

Comparing Classes and Interfaces

General object-oriented theory supports two common means of describing something. The first is the class. Classes are typically used to describe entities in the solution domain. A low-tech way of thinking about classes is that a class is used to nouns that are important to the problem's solution. A common analogy is that a class is a blueprint and an object is an example of the thing that the blueprint describes. The second descriptive construct is an interface. Interfaces typically describe a capability or an aspect of something. Comparatively, whereas a class might describe a thing like a television, an interface might describe a capability of a television—like the ability to increase or decrease the volume—but this capability might also exist somewhere else. For example, attenuating volume is an aspect of things that make sounds. Car radios, stereos, televisions, walkmans, bullhorns, and many other things support the ability to attenuate volume, but bullhorns and walkmans have little else in common.

How does all of this tie into VBA? The answer is that the class module is a funky sort of hybrid of a class and an interface. If one only declares but does not implement—add lines of code—in a class module then the class module is exactly an interface. If one declares and provides an implementation of members in a class module, then the class module is like a class that implements an interface that exactly matches the members of the interface. (The distinction that the class implements exactly the declared members of an interface is important because in other object-oriented languages a class can implement multiple interfaces, no interfaces, or have members that aren't declared by any interface.) For the most part you can think of a class module as a class, but it is valuable to know that subtle distinction exists.

Both classes and interfaces support polymorphism. This means that the hybrid class module supports polymorphism. Class polymorphism is supported through inheritance. Inheritance exists when we define a class and a second subclass that generalizes and extends the first class. For example, the concept of a Control class exists and a Button is a kind of control. In pure object-oriented terms a Button is a kind of Control, and we call inheritance of Control by Button generalization. Generalization is synonymous with inheritance. For example, a Control might support a Click behavior and a Button would extend this behavior by including the changing appearance of the button; this revised Click behavior is one of the forms—one of many possible forms, which is the meaning of the word polymorphism—of the Click behavior. VBA does not support class inheritance.

VBA does support interface polymorphism. Interface polymorphism is orthogonal to class polymorphism. With classes we could declare the type as a Control and initialize the Control with a Button; when we called Control.Click the actual Click behavior would be that of the button because a Button is a kind of Control. With interface polymorphism, we declare an interface type and any class that implements that interface satisfies the interface type, but there is no requirement that any of the types implementing the interface are related. Consider a class for a Dog and a Chess piece. Dog's and Chess pieces have nothing in common, but both could be implemented with the capability of Moving: Dog.Move and Queen.Move. We might implement an interface Imoveable—using an I-prefix is a convention for defining interfaces but not VBA class modules—that declares a method Move. Subsequently, any code designed to interface with IMoveable could invoke Move, and the form of the response would depend on the underlying object.

Technically, class polymorphism supports part of the behavior being adopted from subclasses. For example, the Control.Click method might raise an OnClick event and the Button.Click would cause the button to repaint and then use the parent's code to raise the event. Interface polymorphism will more than likely work completely independent of other classes implementing an interface. For instance, it is unlikely that moving the Dog will have no impact on the completely unrelated Queen Chess piece.

The subtle distinctions and uses for classes and interfaces are worth mastering if you plan on programming in other languages, such as Visual Basic .NET. For now, we will set this general discussion aside and focus on what we can specifically do with VBA.

Defining an Interface

VBA supports defining an interface in one class module and implementing that interface in a second class module. Using this approach means that we can implement one, two, or more classes that support the same behavior. This is useful because we can't overload methods in the same class module.

Overload means to have more than one method with the same name but different argument signature. We can't overload methods in the same class, but we can use interface polymorphism and define multiple classes that provide a similarly named behavior.

Suppose we want to define a class that calculates the Factorial of a number. Written mathematically as N!, factorials is the product of all of the integers between 1 and N where N is the number we want to calculate the factorial of. Factorials are useful in solving multinomial equations. (You don't need to write a factorial function; you can use the Fact(n) function built into Excel. To demonstrate interfaces, we'll pretend we have to implement this method ourselves.) We could add a class module in the VBE and declare an interface IFactorial that declares one method, Calculate. Here is the code:

Function Calculate(ByVal N As Long) As Long

End Function

Class modules with empty declarations are pure interfaces in VBA. If we use the Properties window and change the Name property to IFactorial then we have effectively defined a pure interface named IFactorial that declares one method, Calculate. Calculate accepts a Long and returns a Long. Next, we can implement multiple versions of this interface.

Implementing an Interface

To implement an interface we need a second class module that uses the Implements statement. For example, to implement IFactorial from the previous section we can add a second and third class module, include the Implements IFactorial statement and provide an implementation for the Calculate method. The next two listings show two implementations of the IFactorial interface. The first method implements Factorial as a recursive method and the second implements Calculate without recursion:

Implements IFactorial

Private Function IFactorial_Calculate(ByVal N As Long) As Long
  If (N > 1) Then
    IFactorial_Calculate = N * IFactorial_Calculate(N − 1)
  Else
    IFactorial_Calculate = 1
  End If
End Function

Implements IFactorial

Private Function IFactorial_Calculate(ByVal N As Long) As Long
  Dim I As Long, result As Long
  result = 1

  For I = 1 To N
    result = result * I
  Next I

  IFactorial_Calculate = result
End Function

Anywhere IFactorial can be used we can elect to use any of the classes that implement IFactorial. The next listing demonstrates how we could invoke the second form of Calculate. To use the first version of IFactorial_Calculate substitute Factorial1 for Factorial2 in the Set statement in the following example:

Sub TestFactorial()

   Dim Factorial As IFactorial
   Set Factorial = New Factorial2
   MsgBox Factorial.Calculate(4)

End Sub

Clearly implementing a Factorial method only makes sense if a satisfactory one doesn't exist. Like any technique, you will have to determine how a specific technique can be employed in your solution.

In our example one could define a single class module with methods named RecursiveFactorial and LoopFactorial, or some such method, but defining an interface IFactorial and a couple of variations of the calculation method makes it easier to substitute behaviors in a polymorphic way. By using interfaces one would only need to substitute the statement that creates the object implementing the interface but not change all of the method calls; the method calls would still be object.method, as demonstrated in the preceding example. If we used one class and two methods then changing methods would imply that every place we called one or the other calculation method we would have to change or code. Realistically, we change the code in one place—the point of declaration—with interfaces or in many places by using the older style of using a variety of names.

A reasonable person could argue that the potential for flexibility seems marginal in this one instance, but successful programming is the concerted application of better or best practices over weaker practices. Collectively, consistently using best practices, interfaces, in this instance, adds up to yield a better total result.

Defining Methods

Methods define the behavior of your class. Whatever your class needs to be able to do is implemented in the form of a subroutine of function in your class. Subroutines and functions in classes are referred to as methods. Good method names are generally whole word verbs and nouns. If you need to return a single datum then implement a function method. If you need to complete a task but not return data then implement a subroutine method.

A function comprises an access modifier, Friend, Public, Private (see the section Information Hiding and Access Modifiers later in this chapter), optionally the keyword Static, the keyword Function, a Function Name, optional parameters, and a return type. A subroutine comprises an access modifier, optionally the keyword Static, the keyword Sub, and optional parameters.

The practical needs of the method pretty much dictate whether you use a function or subroutine and the types and number of parameters and the return type (if the method is a function). Some good rules of thumb are:

• Use verbs and noun combinations and whole words for method names
• Only pass the parameters you absolutely need, if the parameter is a member field in the same class as the one containing the method, then you probably don't need to pass it
• Use whole words, preferably nouns, for parameter names
• Choose your access modifiers carefully, limiting the number of Public methods (see Information Hiding and Access Modifiers in this chapter)

You can check the Help files for the grammatical rules for methods. There are plenty of examples in this and other chapters throughout the book.

Parameters

Parameters can be passed by value, by reference, and as optional parameters. By value parameters are modified with the ByVal keyword. ByVal means that any changes to the argument are not reflected after the method returns because what you are passing is a copy of the original argument. By reference parameters are modified with the ByRef keyword. ByRef arguments are references to the outer parameter; modifications to the ByRef parameters are reflected when the method returns. Finally, the Optional modifier can be used in conjunction with ByVal and ByRef. Optional parameters must follow all non-Optional parameters.

ByVal Parameters

ByVal parameters can be changed inside of a method, but that change is not reflected when the method returns. For example, if we define:

Sub DoSomething(ByVal I As Integer)
  I = 10
End Sub

and call DoSomething using the following code:

Dim J As Integer
J = -7
DoSomething(J)
' J still equals −7

then the value of the parameter J is still −7 when DoSomething returns. It is important to understand how the state of your application is managed in order to control application state. In short, ByVal parameter changes are local to the method.

ByRef Parameters

By default if you forget to use a parameter modifier then parameters are by-reference. ByRef parameters are clandestinely pointers to variables. Thus, if you modified DoSomething so that the parameter I is now ByRef then the parameter J would be 10 after the call to DoSomething returned.

To understand how ByVal and ByRef parameters work, keep in mind that everything is a number. Variables are two sets of numbers. The first set of numbers is the location of the variable in memory. The second set of numbers is the value. When you pass a parameter by-value we are passing two numbers: the first is a new variable that has a distinct location and the second is a copy of the value of the caller's parameter. When you pass a parameter by-reference you are passing the same location as the parameter value and consequently, the same value.

Optional Parameters

The guiding principle for optional parameters is: if you define a parameter that can use a particular value most of the time, then you can save consumers some work. For example, if we defined a class that calculated sales tax and the application was used primarily in Michigan then we might define an optional parameter Tax and specify a default value of 6 precent:

Public Function AddSalesTax(ByVal SaleAmount As Double, _
  Optional ByVal Tax AS Double = .06) As Double
  AddSalesTax = SaleAmount * (1 + Tax)
End Sub

As defined, we could invoke AddSalesTax with the SaleAmount or the SaleAmount and Tax. For example, if we ran this method in Oregon then we could pass 0 for the Tax because Oregon has no sales tax.

Implementing Recursive Methods

Recursive methods are methods that call themselves as part of their implementation. Generally, within the method there is some termination condition that breaks the cycle. Recursive functions like the Factorial function, discussed earlier in the chapter are enticing because they are quick and dirty to implement. The only problem is stack winding.

When a method is called, the current location is pushed into stack memory, a limited, finite resource. Then, the parameters are pushed and the method is called. The current location is used to pick up where the code branched prior to the method call. Next, local variables are pushed on the stack. Thus, each time a method is called, more is added to the stack and the information is not removed until the method returns.

Recursive methods call themselves in their middles before the method returns, piling information on the stack without taking things off the stack. If the number of iterations is very large, then you are likely to run out of stack memory, called blowing the stack. If such a condition occurs in VBA you will get a runtime error 28, “Out of stack space.” Because of this potential to blow the stack, recursive methods aren't especially robust. We can usually remove recursion with loops.

Eliminating Recursion with Loops

A recursive method calls itself as part of the solution. The call to self is implicitly the loop control, and the termination condition can most likely be used as the boundary condition for the loop. For example, the recursive Factorial method calls itself until N is 1, and then terminates. This means that the boundary conditions are 1 and N. Since 1 * M is M we can also eliminate 1 from the condition, which means our boundary conditions are 2 and N. Since all we need are a low and high boundary condition to use a loop we can—and did—reimplement the Factorial recursive algorithm using a loop.

There are a couple of reasons to replace recursion with loops: the first reason is that a for loop runs much faster than stack winding and method calls, and the second is that you can loop an infinite number of times, but eventually the stack space will be exhausted. Consequently, eliminate recursive methods, considering them as a potential for headaches for your end users.

Defining Fields

Fields represent the state of an object. Fields can be any type you need to be, and as rule it is a good idea to provide an initial value for every field in the Class_Initialize method and a final value in the Class_Terminate method. By initializing fields you know that an object is in a known state when it begins life, and adding a final state is a good habit that will aid you in ensuring that your code is never trying to use an object that has been discarded.

For example, if we have a string field name MyField and set MyField to “done” when the Class_Terminate runs then we could always perform a check to see if MyField = “done” before using the object. Granted this technique is significantly more useful in languages like C++, C, and Object Pascal, but managing state is one of the most important habits one can build.

The signature of a field is the keyword Private—fields are private by convention—the field name, the keyword As and the data type. Fields are private by convention because unfettered access to state makes it difficult to control your object's state. The problem with fields is that most fields should only have a finite range of possible values. For example, a postal code should be valid. In the United States eieieo is probably not a valid postal code. In response, this implies that we should enforce the finite set of values for our fields, ensuring we both start and remain in a valid state. The challenge is that a field by itself does not run code. Enter the property.

Defining Properties

While fields are essential for managing state, any field that we want to expose to consumers should be married to a property. A property is a special method that is consumed like a field but actually invokes a method. As a result, properties are easy to use like fields, but provide us with a convenient place to ensure that the finite range of possible values is managed.

Properties have a special signature but look like methods. The signature of a property is an access modifier—usually Public by convention—followed by the keyword Property, the keyword Get, Let, or Set, the name of the property and either a function or subroutine-like tail. To define a property that returns a value we use Get and we add parentheses, As, and the data type of the value we are returning. To define the property that sets a value, we use the keyword Set or Let and define a parameter for the type of data we want to set. If the data type we are writing to is a class then we use Set, and if it is a non-class type then we use Let. For example, if we have a field for storing a first name then we might define the marriage of fields and properties as follows:

Private FFirstName As String

Public Property Get FirstName() As String
  FirstName = FFirstName
End Property

Public Property Let FirstName(ByVal Value As String)
  FFirstName = Value
End Property

By convention, we use an F-prefix to denote a field. All that is needed is to drop the F-prefix and we have a suitable property name that is easy to match to its underlying field. (Some programmers use an abbreviated data type prefix, but the inventors of this style, Microsoft, are discouraging its continued use. You will have to decide for yourself. FirstName using a prefix might be written as sFirstName, m_FirstName, or mvar_FirstName.) The Get property returns the underlying field value by assigning it to the function-like Get property. The Let property assigns the argument Value—again a convention we use is naming all property arguments value—to the underlying field. If you need to write validation code, then you would add that code between the first and last lines of the property statement.

Generally, we only need validation code in the Let or Set property methods, but if we elected to create a derived property then we could add code to the Get property method. The first listing shows how we might validate a sales tax property to ensure the sales tax is non-negative and less than 10 percent, and the second listing shows how we could create a derived FullName property from a first name and last name field:

Private FSalesTax As Double

Public Property Let SalesTax(ByVal Value As Double)
  If (Value < 0 Or Value > 0.1) Then
    ' do something here to indicate an error
  End If

  FSalesTax = Value
End Property

The preceding example shows how we might validate a property like SalesTax. It is unlikely that we will see sales taxes exceed 10 percent for at least a few months, and if we will never see a negative sales tax:

Public Property Get FullName() As String
  FullName = FFirstName & “ ” & FLastName
End Property

The FullName property shows how we can create a derived property. FullName is derived from two underlying fields FFirstName and FLastName (keeping in mind our F-prefix convention). If we were really ambitious then we could also write a Let FullName property that splits one string into two strings setting the underlying FFirstName and FLastName fields.

Keep in mind that properties are really just special methods. While you can write any code in a property their main job is that of data sheriff. As data sheriffs, properties are primarily responsible for reliably setting and returning the value of fields.

Read-Only Properties

A read-only property is a property that only has a Property Get definition (no Let or Set method defined). If you have a derived property or an immutable field that you want consumers to be able to read but not write, then define a Property Get method only.

Read-only properties are properties that only define a Get method, but the syntax is the same for the Get property alone as it is for a property with a symmetric write method.

Write-Only Properties

Write properties are simply properties that have a Property Let or Set method and no Property Get method. (Let is for property writers for non-object values, and Set is for property writers for object values.) Write-only properties are moderately rare, but you may find an occasion to use them. For example, a person may pump fuel into a car's gas tank but unless you want a mouth full of gas there is no way to remove the fuel through the same portal in which it was added. Only driving the car will normally reduce the fuel levels. All fun aside, write-only properties are technically possible but pragmatically rare.

Defining Events

Windows, and of necessity Excel, are built upon the trinity of the state, behavior, and signal. These are implemented by the idioms state and property, method, and events. Events are the way that objects signal to respondents that some action has occurred and if desired, now is a good time to respond.

It is a bit condescending to assume that because Excel is so well designed that one can program in Excel without understanding the mechanism behind events that support this mechanism. In fact, it is more likely that if we understand the event mechanism in intimate detail then we are likely to find more creative uses for events. To this end here is a conceptual explanation of events: producers—those who create classes—don't always know in advance how consumers—those that use the classes—will respond to changing state, so they incorporate events. These events give respondents almost unlimited opportunity to respond. Underneath the grammar, an event is nothing more than an uninitialized number that refers to a method. When a programmer associates an event handler—just a method—within an event, the event is no longer an uninitialized number. The event now has the number—called an address—of the event handler.

When you add a method to a class, you are adding a preinitialized number that refers to your method's code. When you add an event to a class, you are adding an uninitialized number that permits a consumer to insert some code at the point in which your event is raised. In this way events are a placeholder in your classes where you are permitting consumers an opportunity to insert their needed responses.

From a producer's perspective one has two responsibilities: define the events that act as the signature of the placeholders and raise the events in the places that consumers are permitted to insert their code. From a consumer's perspective, an event is an opportunity to respond to the changing state of an object. For example, when the value of a TextBox is changed a consumer might want to make sure that the value is a digit between 1 and 9 or a string in the format of a US postal code. The reality is that the producer doesn't know in advance. Consequently, the producer is programming for the general possibilities, and the consumer is filling in the necessary blanks.

Defining Events in Classes

No implementations of object-based languages are the same. Learning a couple of languages will aid in gaining perspective and exploring possibilities. Point in case, the event idiom is restricted to class modules, forms, and documents in VBA, but an event is a function pointer so this limitation is created by VBA.

In addition to event definitions and usage being limited to class modules, forms, and documents in VBA, events must be declared as subroutines, cannot use named, Optional, or ParamArray arguments. When you can do is define an event in a class and consume an event in a class (forms and documents being specific kinds of classes), but you cannot define or consume an event in a plain vanilla module. (The underlying idiom—function pointer—is not as restricted in other languages, with C++ being the most flexible.)

Define an event in a class when you want a consumer to be able to insert a response to the changing state of your class. An event definition uses the access modifier—generally Public, the keyword Event, and a subroutine header where the Sub keyword is supplanted by the Event keyword. The listing shows how we might define an event for a customer calculator. This event represents an insertion point where consumers of the Calculator class might want to respond to a change in the customer Discount, by perhaps recalculating totals:

Public Event DiscountChanged(ByVal sender As Calculator, _
  ByVal  NewDiscount As Double)

The event definition, as demonstrated, uses the Public and Event keywords and then looks like any subroutine definition with arguments and no return type. In the example, we include a reference to containing object. (This is a technique that has proven useful in many other object-oriented languages, and consequently, is a general practice we have adopted.) The second argument is the NewDiscount value.

Raising Events

Raising custom events is, by convention, a two-step process: call a Private method that actually raises the event. By using a wrapper method we have a convenient place to incorporate any additional behavior needed and the RaiseEvent statement is used of technical necessity. The listing demonstrates how we might raise the DiscountChanged event:

Private FDiscount As Double

Public Property Get Discount() As Double
  Discount = FDiscount
End Property

Public Property Let Discount(ByVal value As Double)
  If (value <> FDiscount) Then
    FDiscount = value
    Call DoDiscountChanged(FDiscount)
  End If
End Property

Private Sub DoDiscountChanged(ByVal NewDiscount As Double)
  RaiseEvent DiscountChanged(Me, NewDiscount)
End Sub

The preceding listing shows all of the elements that are likely to play a role in the event process. The first statement is the field containing the discount rate. The Property Get statement returns the current discount rate. The Property Let statement checks to make sure we aren't running unnecessary code by setting the discount rate without changing its value. The conditional check in the Property Let statement is a convention only. If, in fact, the Discount value is changed then we call the Private method DoDiscountChanged. Finally, DoDiscountChanged raises the event. The only statement we need to actually signal the event is the statement containing RaiseEvent, but by using the wrapper we don't need to pass the first argument, the reference to self.

Conventions are adopted in one of two ways: by hard-won trial and error or by copying those that have traveled the same path before us. The convention to wrap raising events provides one locale in which we can converge any dependent code. For example, suppose by default we elected to save the discount rate to a database, file, or the registry. Rather than have this code everywhere we might raise the event, we could insert this additional code in the DoDiscountChanged method.

Lastly, we would like to address speed and quality. By following conventions like using a wrapper every time even if it isn't exactly necessary, code takes on a consistent and symmetric appearance, which lends itself to the appearance of professionalism. Those consuming or extending your code will know what to anticipate. In addition, by doing the same kind of thing in the same kind of circumstances we spend less time thinking about how or why we do something. The end result is that we build reliable habits, much faster, and can focus more on the problem and solution and less on the methodology.

Handling Events

When you define an event you are playing the role of a producer. When you write an event handler you are playing the role of a consumer. A risk occurs when the consumer and the producer is the same person. The risk is that because the producer and the consumer is the same person they are intimately aware of the internal behavior of the class to be consumed and may take shortcuts, like wiring code directly to a field rather than adding an event and consuming the event. A good device is to write classes for a general consumer following good producer rules, and then, even if you are your own customer, write good consumer code as if you didn't know about the internal details of the classes being consumed.

To handle an event, we need to declare a variable using the WithEvents keyword and then use the code editor to generate the event handler for us. The next listing shows how a worksheet might define an instance of our Calculator class and pick the class and event from the Object and Procedure drop-down list boxes in the code editor:

Private WithEvents theCalculator As Calculator

Public Sub TestCalculator()

  Set theCalculator = New Calculator
  theCalculator.Discount = 0.05

End Sub

Private Sub theCalculator_DiscountChanged(ByVal sender As Calculator, _
  ByVal NewDiscount As Double)

  Call MsgBox(“Discount changed to ” & NewDiscount)

End Sub

The first statement declares a calculator variable using WithEvents. This adds the theCalculator variable to the list of objects in the code editor. Select theCalculator from the list of objects and the event DiscountChanged will appear in the procedures list, and because it is the only event, the theCalculator_DiscountChanged procedure will be added to the consuming module.

In our example, we simply show the new discount value. In a practical application we might want to recalculate totals for a customer or something similar. The general rule of thumb is to insert places in your classes where consumers can write response behavior. Remember, as a producer, it is highly unlikely that you can anticipate all present and future uses for your class. By using an event you increase the likelihood that your class can be employed in new uses, resulting in greater longevity and fewer revisions.

Information Hiding and Access Modifiers

Throughout this chapter we have talked about access modifiers. Access modifiers in VBA are the keywords Private, Public, and Friend. Before we talk about these keywords let us say that one can define everything as Public in VBA, which can be especially helpful if you are just getting started. However, we also need to say that by making everything public one is missing out on one of the most useful, albeit abstruse, facets of object-oriented programming.

Before we talk about the abstruse aspects of access modifiers, let's talk about what these words mean in practice. Both consumers and producers can invoke public members. Public members are a bit like public toilets. Anyone goes in and in some cases anything goes. Private members are for producers only. The producer of the class is the only person that can invoke private members. Friend can only modify procedures in Form and Class modules and cannot be late bound. The Friend modifier means that a procedure is public within the same project and private to external projects. In the context of Excel this means you can call Friend procedures from anywhere within the same workbook but not across workbooks.

Why do these distinctions exist and how do they help? The basic premise is that the fewer public members a class has, the easier it is to use. In response then, only a few, necessary members should be public and every other supporting member should be private. This is a highly subjective truism. From another perspective consider this: if a member is private then making it public won't break existing, dependent code but making a public member private may break existing dependent code. Here are some general guidelines that will help you decide how to use public and private access modifiers:

• Try to keep the number of public members to fewer than a half dozen
• If you are unsure, make a member private and see if the class can still perform its primary tasks
• Make all fields private
• Make all properties public
• Make all events public
• If you find that you need more than 6 to 12 public members then consider splitting the class
• Break any rule if you need to; you're the boss

Encapsulation, Aggregation, and References

Let's wrap up this chapter by talking briefly about a few abstruse concepts that are important but difficult to pin down. We are referring to encapsulation, aggregation, reference, and dependency. Encapsulation is the notion of containing things. Aggregation is the notion of things comprising parts and wholes. Reference is the notion of knowing about one thing and using something else, and dependency is the notion of relying on something else.

In the object-oriented world we talk about encapsulation and mean a class containing data. For example, a Workbook encapsulates Worksheets. Encapsulation is often exhibited by a variable name, the member of operator, and the encapsulated thing. For example, a worksheet encapsulates cells and this relationship can be expressed in the Worksheet class as a Cells property and the code might be Sheet1.Cells.

Aggregation refers to wholes and parts and the sum of those parts. A car comprises wheels and tires, engine and transmission, seats and steering wheels, and more. When put together the sum of the parts comprises a new thing. The idea behind aggregation is that by having the parts we can assemble them in different ways to create different wholes. A Hummer is not a Jeep but they may have shared parts in common. At a minimum, they have shared concepts in common. A plane is not a car, but planes and cars have shared things in common like combustion engines and tires. Aggregation reminds us that the composition of parts yields more powerful results than singular, monolithic things.

The concept Reference refers to one class knowing about another class. For example, we could add an instance of the Calculator class to a Worksheet, and the worksheet could be responsible for creating the calculator or the calculator could be created by some other class and just referred to by the Calculator. If the Worksheet creates the Calculator then the Calculator is a part and the relationship is aggregation; if the Worksheet simply refers to the Calculator then the relationship is a reference relationship. A good predefined example of a reference relationship is that represented by the Application object. Many classes in Excel have an Application property, but if an object like a Worksheet goes away, the Application isn't closed. Conversely, if the Excel application is closed then the worksheet goes away too (although it is saved, or persisted, to file). Hence, the relationship between Application and Worksheet is aggregation and Worksheet and Application is reference.

Finally, we have dependency relationships. A dependency is where one object relies on the existence of another object. A Worksheet is dependent on the existence of its cells. If a class A is dependent on another class B then the class A cannot do its job without the existence of the class B. Such a dependency relationship exists between Excel (the Application) and the Worksheet: Excel is implemented to be dependent on at least one visible Worksheet. Consequently, one can always rely on the test ThisWorkbook.Sheets.Count >= 1. Defining and relying on dependencies is an issue of reliable conditions: where one exists you'll find the other.

We introduced these terms here because where necessary we will refer to them throughout this book. Because they are general object-oriented concepts they apply to VBA. Knowing these terms will make our discussions more concise and will help you learn more advanced programming techniques by reading specific and generally applicable object-oriented programming books.

Summary

Excel is a practical tool. It is very good at solving mathematically intense problems and creating graphic representations of those problems. However, VBA is a very powerful general-programming language. VBA is almost part and parcel Visual Basic, and Visual Basic permits a programmer to solve problems in almost every category. By introducing these general object-oriented concepts, we are providing you with the opportunity to maximize your experience with Excel and pursue more advanced concepts. If you are using Excel to solve problems that integrate with other Office applications or VB applications, then having this knowledge will be especially useful.

If you are interested in the breadth and extent of a programming language like VBA then we encourage you to read books on topics like Patterns and Refactoring such as: Add Refactoring: Improving the Design of Existing Code by Martin Fowler, and Design Patterns by Erich Gamma et al. Excel is a superlative number cruncher from an object-oriented perspective, VBA is much more than a macro language.