Class Property Fixed Values

In my previous post on a Custom Collection Class, I used an example of a People collection with Person items.

As well as First Name, Last Name, and City, I had originally included the Gender property (Female, Male, Unknown), but removed it just prior to posting. I purposely left it out because I didn’t want to distract from the core topic (collection classes), and because I have a special way of dealing with properties of this type.

I could have just defined Gender as a String datatype.

That would work.

We could be a little more rigid with our variable, and define an Enum.

It’s an OK approach, though it has a couple of small issues.

  • I can set Gender to numbers other than the Enums 0, 1, and 2. MyPerson.Gender = 7 will not error.
  • The required helper functions GenderEnumToString, and the StringToGenderEnum are stored in a separate place to the Gender variable, probably buried in a giant module. While it’s improbable that there will be more than 2 genders, your property might grow over time. It’s nice to have related code all in the same place.

What follows is a different approach that mixes Classes with Enums. You get the validation capability of a Class property, the Intellisense of an Enum, and related code living in the same place.

You get code that reads like

This example starts where the Custom Collection Class post ends. It is assumed you have the same example ready to use.

As in the first post, we’ll persevere with the VBA Attribute workaround. Copy the following code to Notepad, save as Gender.cls, then use VBA to Import the file. Did you know that instead of clicking File > Import, you can drag’n’drop the file from Windows Explorer to the Project window?

Notice in the code above the line that reads: Attribute Value.VB_UserMemId = 0. That line is the one thing that makes us perform the Notepad workaround, but it is pretty important that we do it. The Attribute tells VBA the Value property is the Default property for the Class. For further reading, take a look at Chip Pearson’s explainer of the feature.

There’s also a couple of Consts defining the valid range of Gender values allowed: LoLimit and HiLimit. As a Gender value is assigned, validation is performed against these Consts through Property Let Value.

Now we can add our Gender property to the Person class. The Person class should now look as follows:

Great! Now we’re ready to test it.

I’ve put together a workbook containing the code of this post, and also the code of my previous post.

You can download

7 thoughts on “Class Property Fixed Values

  1. Great series of posts Rob.

    Here is an alternative to the LoLimit and HiLimit consts.

    Can also be useful when you want to loop through all of the member values, assuming of course the values are sequential.

  2. Hey, thanks Andy.
    I do like your approach, and I’m pleased you posted it. It’s a much tidier method of range checking.

  3. Rob – Fantastic examples. I’m slowly learning how class modules work, which I’ve always struggled with.

    One quick question, is there any significance to the 513 in this line?

    Err.Raise Number:=vbObjectError + 513, Description:=“Invalid value for Gender”
  4. Lane: From the Help page on Err.Raise: The range 0–512 is reserved for system errors; the range 513–65535 is available for user-defined errors.

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

Leave a Reply

Your email address will not be published.