VBA: Why Use Properties Instead of Subroutines or

Question

Why do we need to use property Let, Get and Set in a VBA class when we can simply pass and return our arguments using subroutines or functions?

Answer 1

You don't need to ... for 90% of all applications standard VBA is just right. Using classes you have more "logical control" over your code.

Suppose you have a function Function IsUnique(MyArg as String) as Boolean that looks up a table of persons with a name passed in MyArg and returns TRUE if the name is not found in the table. Nothing prevents you for instance to call this function and pass the string of the favourite vegetable the user chose. You will still get a syntactically correct program, despite the fact that this call will always return TRUE because vegetables are stored somewhere else in this application.

Now suppose you have a class PersonClass containing functions (=property) Name and IsUnique()

Your code finally you would maybe like this

Dim MySpouse as PersonClass
    Set MySpouse = New PersonClass
    MySpouse.Name = "Fanny Hill"
    If MySpouse.IsUnique() Then
        MySpouse.Insert
    End If

What seems like an overkill really encapsulates the code blocks, functions and variables pertaining to a Person (here called methods and properties) into a class and make it more obvious to your code what you are doing with a variable that makes use of that class template (aka. an instance of that class)

Typical example: IP address arithmetic ..... (add IP's, find base or broadcast addresses, convert between various mask notations, check two ranges for overlap, find net mask sourrounding two or more IP-@es etc. etc. etc.) needed in a dozen of applications: write the class module once, and once tested you can easily reuse it.

Another advantage with classes is that when entering the code in the VBA editor, the code completion tells you exactly what you can do with a class (i.e. all properties and methods are presented in a dropdown list) - a comfort you don't have with procedures and functions (they only tell you the argument names and types)

Enough?

Answer 2

Moreover, Let/Get combination or Set/Get combination sets a discipline for type of argument (for Let/Set) and return value (Get) so that functions can be used on left hand side of an assignment statement. Makes coding crisp!

Answer 3

I think that the short answer is that properties are characteristics of the class while methods (subroutines) are actions.

My interpretation of this is that properties are the "adjectives" and methods are the "verbs". We could make an analogy to help illustrate this: We have two NHL goalies (let's create them as members of clsGoalie), Carey Price and Dustin Tokarski. Each has a property specific to their unique "goalie" object which we call save percentage (pSavePercentage). Both goalies require the exact same method for stopping a shot on net (Let's say Private Sub BlockShot) but their unique properties will influence the performance of that action.

The answer to why we would define the characteristics of a class with properties instead of return values is that properties extend the object, or in essence, define the object. Return values can not be used to reference back to the object they originated from. Back to our goalie analogy... Let's suppose we defined the save percentage of each of our goalies with a subroutine. We could make it work but it would require more code in our main method. First we would have to 'Dim SavePercentage As Double' (already you can see that we've stepped outside of our object). Next, 'SavePercentage = Price.calcSavePercentage'. Finally, we would have to write a whole new method (in our Main or some other module) that we would then pass our new variable of type double to... A this point we don't even know who's in net. Did Price save that shot or was it Tokarski? Confusing. In this instance, had we made save percentage a property of the class "clsGoalie" we would better leverage the power of OOP: clean code and encapsulation.

Hope this helps a bit.

Answer 4

Properties are for Classes. So I guess your real is question is more: "why use class modules ?". And indeed, you can build excellent apps for years without that need.
I started using those for very specific uses, like reading complex text files (mainframe printed reports with logical records spanning over several physical lines) or getting specific info from huge and complex Excel worksheets.
Once you get a class done that simulates the item you want to read, you can reuse that class from app to app, and concentrate on the logic of you specific app, not on the logic of that same old reading that same old file for example.