VBA Custom Property Conundrum

Question

There is a question on VBA that i have asked persistently on Stack. I am willing to go into great pains and lengths to find the answer. Be aware it may look silly and childish but it's a tricky snide question and almost everyone misses the answer consistenly.

---

According to my books on VBA (Bill Jelin - VBA Macros Microsoft Excel 2010) when we are creating a custom object we open a class module. The properties of the custom objects are variables declared Private or Public. This seems extremely illogical for me!!! I will illustrate in the picture below why.

1. Simply it's Range("A1:A4")

2. A very simple Sub for explanation purposes (you can see it clearly bellow)

   Sub dd()
     Dim i As Integer
     Dim Top As Range
     Set Top = Range("A1:A4")
     i = Top.Count
     MsgBox i
   End Sub
   

3. Count property "counts" and returns the number of objects in the collection

Alright hold that childish example in your mind for a minute and proceed bellow...

---

Now lets say we are creating a custom object in VBA so we open a Class Module and name it clsEmployee. Now the object has 4 properties, according to the book, so we are declaring them like variables inside the class module:

Public EmpName as String
Public EmpID as String
Public EmpRate as Double
Public EmpWeeklyHrs as Double

There is also a method so we type her in the Class module

Public Function EmpWeeklyPay as Double
  EmpWeeklyPay = EmpRate * EmpWeeklyHrs
End Function

Now if we go to a normal module reference and instantiate the object by typing Employee the Intellisense window...

...shows us these procedures and functions

But how would you create a custom property like Count? As you can see in the graphic example in the first picture the Count property engages in some sort of logic steps and counts the cells parsing them one by one in the collection and returns the number 4 in the MsbBox. It can make only sense that these logic steps are in fact somehow hard-coded in the machine. You cannot declare that kind of property in the Class module by simply typing:

Dim Count as Integer

according to the absurd axiom in the book which says:

Properties are variables that can be declared Private or Public.

It would also be very revealing the fact that Count Property is NOT being equaled with anything in the code in picture. That means Count property is not a "straw" property it needs some logic to parse the cells in the Range("A1:A4") and say there are 4 cells. I just cannot see how i would create such a custom object with by typing in the Class module: Dim Count as Integer

How you would create in custom objects properties like Name, Value or Count without some procedures to tell the compiler what to do by just typing 'straw' declaration in the Class module?

Answer 1

I am not going to start a fight against the author of the book you're reading (in fact, I have not read that particular book). It has received rather positive reviews so I think the real source of the problem is you - misunderstanding some concepts.

Table of contents of your book

Now, are you missing a page? :P How did you get from Using a Custom Object (p 498) to Collections (p 501) without seeing/reading page 499 about Properties?

Well, anyways

creating a custom object we open a class module.

This is very poorly worded. I am not a book writer but I have read many programming books and I have never seen a professional actually teach using such poor wording. To me, someone who has some programming experience this sentence clearly makes no sense. The concept of that sentence is crucial to learning about classes, class modules etc and you should make sure you understand every single word in it along with the meaning.

What you should have done before actually posting on StackOverflow is called research.

Everyone occasionally reads something that is either unclear or can't get a full understanding of it. What you do in such cases is fire up your browser and do you own research on the topic. Read about the same thing from multiple sources and I am sure you'll get to understand it at some point. Also, never rely on one particular book, as most of authors always say something along these lines: "do more research on your own because if I were to talk about every single details about this or that this book would have to be another 1000 page long".

• See CPearson's Introduction to Classes.

• How to use the Implements in Excel VBA

• Inheritance for VBA code in MS Access

Now,

The properties of the custom objects are variables declared Private or Public.

While this is technically correct, it's worth pointing out that not only variables are properties of classes. Variables are one type of class property - which I am going to try to convince you it's better called members. When you get to learn about classes - you need to be able to distinguish the difference in meaning of terminology. You need to know what words like class, class Property, member, function, variable, collection, access modifiers, inheritance, polymorphism, object, class instance, and many more refer to.

Count property "counts" and returns the number of objects in the collection

Great, how did you know that? Range.Count Property - another good tip is to visit the MSDN Developer's reference and look at the definitions for objects your working with. Not every single class is well described on MSDN but it gives you some basic info to get you going. If you there is something else you need, again go to google and research.

Now lets say we are creating a custom object in VBA so we open a Class Module and name it clsEmployee.

Creating a custom object in VBA surely refers to a custom object module not to confuse with a custom object instance. VBA Project's custom objects are UserForm, Module (ref standard module), and Class Module. Those are object modules. Adding a class module it's like adding your own blueprint for an model you want to instantiate( your own data type to store some information the way you design in that blueprint). There is a difference between adding an object module and creating an objects instance...

Now the object has 4 properties, according to the book, so we are declaring them like variables inside the class module:

This has always been a bit tricky for beginners to understand.. I guess it's that some people confuse members with properties. I am not going to go into detail on this one as Jon Skeet already has explained (research!!). It's essential to be able to tell the difference between the terminology you're using.

When working in VBA, it is really easy when you treat everything in your class as class members. So class members are variables, properties (let/set/get), functions, procedures, types, enums, external dlls declarations, events (which are functions). As you can see instead of calling everything class properties I call them class members. Then the confusion of the context in which you use the word Property is gone. a Property is now a Let/Set/Get type of thing and is a member of a class.

But how would you create a custom property like Count?

Again, what is .Count? If you're a bit familiar with VBA you already know if not what do you do? You research

Returns a Long value that represents the number of objects in the collection.

Great. Which member generally returns things? a Function? Yep, so what does it return? a Long value. What is that value representing? the number of objects in the collection.

Why is one called a Collection and the other Class. You should know on top of you head what the difference between them is. Do you? What is it?

How would you create a .Count function in your own class? This is quite advanced. You need to understand everything else up to that point to be able to do that yourself an not just copy-paste code without actually understanding what is going on. Things like Enumerator, Interfaces, IUnknown are involved and you do not understand those terms. What do you do again? Research.

.Count is a common property always implemented on Collections. You have got a class but you do not have a Collection member in your class nor your class is a Collection itself.

Research leads me to » Implementation of a custom collection.

PS: I have purposely not linked to one external site which explains a very important keyword used multiple times in this answer.... You should figure out by yourself which one that is - Ill be awaiting your feedback :)

Hope this helps.