Classes: Creating Custom Objects

Classes are objects and objects are classes. Have you ever seen an error message like this:

“Cannot get the WorksheetFunction property of the Range class”

We call it the Range Object, but we could just as accurately call it the Range Class. Objects are nothing more than the properties, methods, and events they contain. A Range object is what it is because of its unique combination of properties, methods, and events (and because the name of the class is “Range”).

You may have seen how to use classes for Application Level Events, CommandbarButton events, or QueryTable events. You can also use classes to create your own custom objects. Some of the benefits of using class modules are

  • Encapsulation – a programming best practice, encapsulation allows you to keep related code in one place making debugging easier and leading to…
  • Portability – with an encapsulated class, you can move that class to another VBA project and use it there.
  • Ease of Programming – while coding the “business” end of an application, it’s easier to use a classes properties and methods than to code all that information yourself. Of course, setting up the class takes some time and resources too.
  • Self-Documentation – Properly named classes and their respective properties and methods aid in creating self documenting code.

Assume you want to create a card game in Excel. You can create a Card class which has certain properties and methods which you can then use to program your game. If later you want to create a different card game, you copy that class to another project and save yourself some setup time. Here’s a simple example of Card class:

Private mValue As String

Property Get Value() As String

    Value = mValue
End Property

Property Let Value(aValue As String)

    If aValue Like “1#[C,D,H,S]” Then
        mValue = aValue
    ElseIf aValue Like “#[C,D,H,S]” Then
        mValue = aValue
        Err.Raise Number:=vbObjectError + 513, _
            Source:=“MyCardGame.Card”, _
            Description:=“Value must in the form 1D, 2H, 10S, 13C”
    End If
End Property

Property Get Suit() As String

    Suit = Right(mValue, 1)
End Property

Property Get Number() As Long

    Number = Val(mValue)
End Property

The name of the class is Card and it has three properties, Value, Suit, and Number. Value is a read/write property and Suit and Number are both read-only. I start by defining a private variable called mValue. This will hold the Value property for this instance of the class. Then I define a series of Property Let/Get statements that allow the programmer access to that variable.

The Value property has both a Property Get and a Property Let procedure which is what makes it read/write. The Get procedure merely returns the mValue variable. The Let procedure assigns a value to the mValue variable, but also includes some error checking. If the programmer tries to assign a value to the Value property that is not in the proper format, and error is raised. The property format being a number 1-13 followed by a one-letter suit (Clubs, Diamonds, Hearts, Spades).

The Suit and Number properties only have Get procedures. The programmer cannot assign values to these properties, only read them. Both Get procedures use the mValue variable to retrieve the portion of the Value that the property calls for. For the Suit property, it returns the right most character – the one-letter alpha that is the suit. For the Number property, the Val() function is used to strip off the suit and only return the number.

You don’t need to provide the programmer with a Suit and Number property, but since the programmer will likely be you, you want to make life as easy as possible. If you create a poker game and want to determine if a poker hand is a flush, you can just use the Suit property.

Here’s some code that uses the new Card object.

Sub TestCard()

    Dim Crd As Card
    Set Crd = New Card
    ‘Nothing assigned to value
   Debug.Print Crd.Value, Crd.Suit, Crd.Number
    Crd.Value = “9S”
    Debug.Print Crd.Value, Crd.Suit, Crd.Number
    Crd.Value = “10D”
    Debug.Print Crd.Value, Crd.Suit, Crd.Number
End Sub

And the results:


Posted in Uncategorized

9 thoughts on “Classes: Creating Custom Objects

  1. Thank you so much for this – I spent a long afternoon last Friday looking for just this information to create an easily-readable excel app at work. As it was, I didn’t find any instructive info, so just wrote in base code :(

    Still, I’m on holiday now, so if it all goes wrong….

    (It’ll be waiting for me when I get back!)

  2. “Portability – with an encapsulated class, you can move that class to another VBA project and use it there.”

    The popular term is ‘code reuse’. I prefer this because, as well as reuse in other projects, it applies equally to code reuse within a project. Whenever I find myself copying and pasting chunks of code, I know what I really need is to encapsulate it in a class.

    I’m not being down on you, Dick, but when I was learning I didn’t find it much help to read ‘abstract’ business object descriptions such Chip Pearson’s CEmployee class. I just didn’t get it. I’d think, that’s all very well if you’re dealing with many Employees simultaneously but my apps only ever seemed to have one Employee (or whatever) at a time. I just couldn’t see the incentive of using a class. I didn’t ‘get’ custom objects until I needed them. And when I did, I struck with them.

    I think the ultimate example of the use of a class module is the ‘common ActiveX event handler’ ( or perhaps for Excel it’s the ‘Application events handler’ ( With these, the benefits of using a class are clear. My personal ‘ready rolled’ favorite is Stephen Bullen’s CFormChanger ( from because it encapsulates (i.e. hides) highly complex but extremely useful code.



  3. JC: I agree with you to an extent. I like Chip’s employee class example, but it doesn’t sell me on using classes. I think guys like Chip and Stephen write such complex applications that abstraction isn’t just good practice, it’s an absolute necessity for maintainability.

    I whole heartedly agree that JW’s control event class is the ultimate example. It’s easy enough that average Joes like me can use it in their code and that really brings home the realization of the power of the class.

    I started writing an app for Euchre in Excel. As I was writing it, I thought it would be useful to me to abstract the cards from the logic of the game. That way if I want to write a different card game, I can move my card object hierarchy (Card, Cards, Player, Stack, etc) and all I have to do is work out the logic of the game.

  4. RE: Classes: Creating Custom Objects
    To Do: Read this post in it’s entirety, try it, and practice it.

    Encapsulation – a programming best practice, encapsulation allows you to keep related code in one place making debugging easier and leading to…
    Portability – with an encapsulate…

  5. For the last Excel application I solely wrote from scratch, out of approx 4K lines of code, only one method (21 lines) is in a standard module and then only because it has to be a public sub in a standard module so it can be triggered by a hyperlink. I guess I could’ve rolled my own hyperlink class triggered from an ActiveX control but it was a last minute requirement ;-) So, yeah, I’m heavily into ‘abstract’ classes now.

    Interestingly, when I was first leaning this stuff, I cut my teeth by implementing a card game called Chrononauts ( It never made it to the GUI stage (I would’ve needed Looney Labs’ permission for that – oops!) but I made lots of discoveries just writing the object model i.e. the classes and collection classes with their properties, methods and events. I also discovered that inheritance would’ve com in handy for the Card base object :-(



  6. I think this page does a decent job explaining the fundamentals of objects & classes (I am just now learning the power of them after using VBA and Delphi for a few years). HOWEVER, most of my object-oriented learning has been coming from C++ and I have found few resources on the web discussing object-oriented programming in VBA; not to say that there are few resources, but the number of people selling VBA books and the number of websites focusing on simple VBA concepts has made it difficut to find what I am looking for!

    I took the time to write all that because, although the example above shows how to access properties, it does not show how to make a class in the first place! The statement “The name of the class is Card and it has three properties,” is made, yet the Card class itself is never declared!

    I’m sure I’ll find the line or two of code I need to complete this, but it always erks me when someone writes a code example but does not include the entire piece of code. There are situations where such a practice is understandable, but I don’t think this is one of them.

    After all that complaining I must say I’m glad this site is here; just trying to help make it better! Like I said before, I’ve had a hard time finding pages even similar to this!

  7. davidd31415: I tried to go to that MSDN site and it was no longer there. Any ideas where to find it?

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.