Are Excel VBA custom made Class Properties useful?

Question

I've been using VBA for Excel for simple Subs and Functions to automate tasks.

I am familiar with what Class Modules and the term Property in VBA are but all my code so far consists solely of simple Modules and UserForms and I want to delve deeper in the language.

I'm having trouble understanding the significance of Properties, specifically I'm trying to understand the difference between these two pieces of code:

Code#1

'Class Module: "clsCustomer1"
Option Explicit

Public fName As String
Public sName As String
Public PhoneNumber As String
Public PIN As String

'Module: "Test1"
Sub test1()
    Dim customer1 As New clsCustomer1

    With customer1
        .fName = "John"
        .sName = "Smith"
        .PhoneNumber = "6512346590"
        .PIN = "55648"

        Debug.Print .fName, .sName, .PhoneNumber, .PIN
    End With
End Sub

Code#2

'Class Module: "clsCustomer2"
Option Explicit
Public Property Get cl_fName(ByVal name As String) As String
    cl_fName = name
End Property

Public Property Get cl_sName(ByVal name As String) As String
    cl_sName = name
End Property

Public Property Get cl_PhoneNumber(ByVal number As String) As String
    cl_PhoneNumber = number
End Property

Public Property Get cl_PIN(ByVal number As String) As String
    cl_PIN = number
End Property

'Module: "Test2"
Sub test2()
    Dim customer2 As New clsCustomer2

    With customer2
        Debug.Print _
                    .cl_fName("John"), _
                    .cl_sName("Smith"), _
                    .cl_PhoneNumber("6512346590"), _
                    .cl_PIN("55648")
    End With
End Sub

In both cases debug.print gives the same output. I could even assign the same variables in the second case to match the first one. The actual code in the normal Module is pretty much the same in both cases, if anything the second one looks messier.

Why bother to use Properties when I can declare my variables inside a Class module?

Obviously my example is as simple as it gets, but I can't find a proper use case for Properties here.

IMO the class2 has a wrong way how `Get` should be used. Have a look e.g. [here](http://www.cpearson.com/excel/classes.aspx). — Daniel Dušek, Nov 20 '19 at 19:50
Why to have properties and _not_ just public variables is because of encapsulation, without it everybody can change the class which sooner or later leads to unexpected behaviour and problems. Have a look e.g. [here](https://stackoverflow.com/questions/18300953/why-encapsulation-is-an-important-feature-of-oop-languages). — Daniel Dušek, Nov 20 '19 at 19:53

score 1 · Answer 1 · answered Nov 20 '19 at 15:26

1

Two things come to mind. First, properties can be made ReadOnly (and even WriteOnly). That can be handy at times. Second, some properties are more complicated than just returning the value. A classic example is fullName, which would return firstName & " " & lastName. So getting (or setting) a property may have some custom logic that you want to execute each time the value is set (or read). If none of the two apply to a specific case, however, then using method #1 is totally fine.

answered Nov 20 '19 at 15:26

Pavel Lint

3,252
1
18
18

But even then, when talking about ```fullName``` wouldn't I be able to achieve the same result w/o using a Property OR a Class module? – sirocco13 Nov 20 '19 at 15:42
Of course. You can avoid using classes all-together. But it's commonly accepted that they make the code more structured and closer to real life ("Customer" class better reflects a customer than a bunch of subs do) – Pavel Lint Nov 20 '19 at 15:44
Agreed, thanks for your input :) I'll be looking around for more information. – sirocco13 Nov 20 '19 at 15:51
Another advantage is that you can use intellisense when you declare them as properties. – braX Nov 20 '19 at 16:46

Are Excel VBA custom made Class Properties useful?

1 Answers1