VBA Referring to a container object--syntax and object-oriented methodology

Question

This is as much a question about learning object-oriented methodology, for me, as it is about VBA syntax. Suppose I create several classes, like Car, Truck, Bus, etc. And I create another class SpeedCalculator, which instances of my vehicles will instantiate and contain. (As a newbie, let me note that this strikes me as a good time to declare a class as static and not instantiate it--which vba can't do I don't think... .) Now this speed calculator will be no simple speedometer. Rather it will calculate speed from temperature, windspeed, RPMs, etc, etc--go along with this please, just for the sake of the example.

Now the question is how for the contained object to collect its inputs, which are only available in the container objects (the vehicle objects might implement an interface (if VBA can even do that...)). "Parent." is wrong, I figured out eventually, b/c parent-child is an inheritance relation (which VBA doesn't have, again), not a containment relation, and the parent of the contained object is an Excel Application (not my object). So it seems it would be nice if there were another keyword to refer to container properties. I hope I haven't just missed something simple. Or is it more the case that that sort of reference would break object-oriented encapsultaion principles?

I guess a second approach would be to pass the container to the contained, via "Me" as an argument. But then you have to multiply all of the contained's methods, either to overload them (if VBA can even do that...), or with variously named versions--due to the different types of the containers (can we be more idealist and avoid declaring as variant or "Object"?).

And then door #3 would be the last door standing, I guess? Which would be to pass an (annoying) litany of arguments. The definition of all of which would tend to defeat the purpose of having my tidy little calculator class?

Answer 1

It's not clear to me from your question whether or not you already know VBA and/or OO, and are just asking how to use the object-oriented features of VBA. If you are new to both VBA and OO, see below for some thoughts on why VBA isn't a very good vehicle for learning OOD/OOP.

To address the general part of your question, VBA classes can implement interfaces. This is how you express inheritance of interface (an "is-a" relationship) in VBA. There is no direct way to express inheritance of implementation in VBA. Instead, to make one class inherit the implementation of another, you have the first implement the interface of the second, contain an instance of the second, and then delegate calls to that instance. See this answer for more:

VBA inheritance, analog of super

There is a link there, that I will repeat here, to the Visual Studio 6.0 Programmer's Guide:

http://msdn.microsoft.com/en-us/library/aa240846(v=VS.60).aspx

It's as good a short introduction as any on the "VBA way" of OOP (although it's written for VB6, not VBA).

---

Now, for your specific question about design: "how for the contained object to collect its inputs, which are only available in the container objects".

You need to think about what you are actually modeling here. Regardless of how you implement it, a "speed calculator" should only get to know about a very specific set of inputs, not the entire internal state of whatever vehicle is using it. In VBA, as you note, there are no static classes. Instead, use a regular code module and have a function that you call from inside your vehicle class(es):

Public Function calcSpeed(temp, windspeed, rpm)

   'do calc based only on parms passed in...

End Function

If it needs to take a zillion parameters because that's how the calculation works, so be it. Don't try to hide it. Of course, you can wrap them up in a Type or in a class if there are too many.

Now, does every different kind of vehicle calculate speed in the exact same way from the exact same set of state parameters? If so, then have a speed property or method that is implemented by your "base vehicle" class and call calcSpeed from there.

But maybe it's the case that different kinds of vehicles have different state parameters, or use different calculation methods, or the calculation is the same but not every vechicle type supplies every parameter. In that case, put the speed method in the base vehicle interface, but "override" it as needed in the implementation of each subclass. (Maybe then calcSpeed is too simplistic, and you'd end up with a library of speed calculation helper functions.)

One thing I would not do, is have a generic SpeedCalculator class that takes a Vehicle argument and then interrogates it for its state in order to do the calc. The reason why not is expressed very well in these classic articles:

http://media.pragprog.com/articles/may_04_oo1.pdf

http://pragprog.com/articles/tell-dont-ask

http://www.cmcrossroads.com/bradapp/docs/demeter-intro.html

There's also this:

http://www.ccs.neu.edu/research/demeter/demeter-method/LawOfDemeter/paper-boy/demeter.pdf

which has a quote I like:

So, what's so bad about this code (besides being a horribly contrived example)? Well, lets translate what the code is actually doing into real-language:

Apparently, when the paperboy stops by and demands payment, the customer is just going to turn around, let the paperboy take the wallet out of his back pocket, and take out two bucks.

I don't know about you, but I rarely let someone handle my wallet. There are a number of 'realworld' problems with this, not to mention we are trusting the paperboy to be honest and just take out what he's owed. If our future Wallet object holds credit cards, the paperboy has access to those too... but the basic problem is that “the paperboy is being exposed to more information than he needs to be”.

Thats an important concept... The 'Paperboy' class now 'knows' that the customer has a wallet, and can manipulate it. When we compile the Paperboy class, it will need the Customer class and the Wallet class. These three classes are now 'tightly coupled'. If we change the Wallet class, we may have to make changes to both of the other classes.

ADDED AS PROMISED IN COMMENTS:

It's not that you couldn't readily have an instance of a class Speedometer contained within your Vehicles. (My example of a simple function might be too simplistic. Maybe you need a class to model the other things about speedometers - they have mass, take up space, etc.) It's how the two classes depend on each other. In this example, Vehicle needs to know about Speedometer. But why should the reverse be true? If Speedometer takes a Vehicle as a parameter, and then asks it for the particular things it needs to know to calculate speed, the code will certainly work. However, you've coupled Speedometer to Vehicle more tightly than necessary.

One of the reasons to use an OO approach in the first place is because it lets you be more exact about how concepts relate to each other. It's better to have Vehicle tell Speedometer, "Here are some facts about the world. Give me back a speed.", rather than, "Here I am, Me, the Vehicle that contians you. Ask me whatever you need to about anything related to me, and then give me back a speed." (Note that whether the "facts about the world" are raw temp, windspeed, etc., or an instance of some SpeedometerInput Type/Class isn't the issue. It's that speedometers don't need to know all about vehicles.)

Using the most exact interface you can get away with doesn't make that big of a deal in a simple example. But it becomes huge when added up over many design decisions.

---

Finally, If you have a choice, I wouldn't use VBA as a vehicle for learning object-oriented design or programming. You can do "OOP" in VBA, but in a Microsoft-/COM-specific way that is literally a relic from the mid-1990s. You can browse around stackoverflow for plenty of examples of things that are normally done in OO programming languages (and with their much better libraries) that are cumbersome and tricky in VBA. Here are a few off the top of my head that I've either asked or answered:

Is there a way to overload the constructor / initialize procedure for a class in VBA?

Is there a way to write an equality test for a VBA class with private members without exposing knowledge of the existence of those private members?

Restrict type in a Collection inside a class module

Excel-VBA - Is there anything like Javas Set container in VBA?

So, unless you're either constrained to learn with VBA because you can't install anything but MS Office on your machine, or you plan to be doing a lot of VBA work becuase you're using Excel, Access, etc. and have some problems where OOP can help, I'd look elsewhere. Python, .NET, or Java are all available for free on Windows and have tons of resources available for the beginner.

Answer 2

I'm not sure if this is what you're looking for, but I'll give it a shot. If Car class contains one Speedometer class, Car contains Windspeed and Acceleration properties, Speedometer contains a Mass property, and speed is defined as Windspeed times Acceleration divided by Mass, then here's how I would set it up.

In class CCar

Private mlCarID As Long
Private mdWindSpeed As Double
Private mdAcceleration As Double
Private mclsSpeedometer As CSpeedometer

'Getters and setters
Public Property Get CarID() As Long: CarID = mlCarID: End Property
Public Property Let CarID(ByVal lCarID As Long): mlCarID = lCarID: End Property
Public Property Get Acceleration() As Double: Acceleration = mdAcceleration: End Property
Public Property Let Acceleration(ByVal dAcceleration As Double): mdAcceleration = dAcceleration: End Property
Public Property Get WindSpeed() As Double: WindSpeed = mdWindSpeed: End Property
Public Property Let WindSpeed(ByVal dWindSpeed As Double): mdWindSpeed = dWindSpeed: End Property

'read only property to the speedometer class
Public Property Get Speedometer() As CSpeedometer
    Set Speedometer = mclsSpeedometer
End Property

'create the child and set the parent property
Private Sub Class_Initialize()
    Set mclsSpeedometer = New CSpeedometer
    Set mclsSpeedometer.Parent = Me
End Sub

Private Sub Class_Terminate()
    Set mclsSpeedometer.Parent = Nothing
    Set mclsSpeedometer = Nothing
End Sub

'pass through property
Public Property Get Speed() As Double
    Speed = Me.Speedometer.Speed
End Property

In class CSpeedometer

Private mdMass As Double

Private mclsParent As CCar

Public Property Get Mass() As Double: Mass = mdMass: End Property
Public Property Let Mass(ByVal dMass As Double): mdMass = dMass: End Property

Public Property Get Parent() As CCar
    Set Parent = mclsParent
End Property

Public Property Set Parent(clsCar As CCar)
    Set mclsParent = clsCar
End Property

Public Property Get Speed() As Double
    'references to parent properties
    Speed = Me.Parent.WindSpeed * Me.Parent.Acceleration / Me.Mass
End Property

In a standard module

Sub GetSpeed()

    Dim clsCar As CCar

    Set clsCar = New CCar

    clsCar.CarID = 1
    clsCar.WindSpeed = 10
    clsCar.Acceleration = 5
    clsCar.Speedometer.Mass = 100

    Debug.Print clsCar.Speed

End Sub

You have to make sure you destroy your parent/child relationship properly or you'll get a memory leak. I use CopyMemory to set up parent properties to avoid that particular problem. It's described here http://www.dailydoseofexcel.com/archives/2007/12/28/terminating-dependent-classes/#comment-29661