VBA difference between public variable and property

As much as VBA is object-oriented it's still limited in many ways, but as far as this example goes it should be sufficient to just understand the basics of the OOP in VBA.

Your code

Private pVariable As IntegerPublic Property Let Variable(ByVal lVariable As Integer)    pVariable = lVariableEnd PropertyPublic Property Get Variable()    Variable = pVariableEnd Property

is wrong a bit unnecessary.

NOTE: You may do that in cases where you want to handle errors / validate data coming in but generally if it's as simple as setting and getting the value you wouldn't do that.

Why would you ever need a private backing field if you are exposing both the Let/Set and Get properties? All you need for this is the public variable itself and no need for properties.

The story changes 360 degrees when you have to only expose one of the properties and not the other (ie. either setter or getter only ). Maybe it's easier to understand by working through an example...

Example

Let's start by working through an easy "banking" example (obviously you wouldn't do that in VBA in real-life but it's a good concept to evaluate as a base)

Imagine you have to build a class to simulate a bank account. You need a way to deposit and withdraw money from the account as well display balance.

Normally you wouldn't have a setter for the balance field because no-one should be allowed to explicitly set the balance. (if you know a bank that allows this please let me know ;)) . The actual balance should be a private variable. There should be a property which exposes it and that's all you should consider here.

Consider a VBA class (an interface)

IAccountServices.cls

Sub Deposit(amount As Double)End SubSub WithDraw(amount As Double)End Sub

and another class to represent the account

Account.cls

Implements IAccountServices' balance should be private' cause you should only have a getter for it' you should only be able to set the balance inside this class' based on the operationsPrivate accBalance As Double' see Getter only - no setterPublic Property Get Balance() As Double    Balance = accBalanceEnd PropertyPublic Function Deposit(amount As Double)    accBalance = accBalance + amountEnd FunctionPublic Function WithDraw(amount As Double)    accBalance = accBalance - amountEnd FunctionPrivate Sub IAccountServices_Deposit(amount As Double)    accBalance = accBalance + amountEnd SubPrivate Sub IAccountServices_WithDraw(amount As Double)    accBalance = accBalance - amountEnd Sub

NOTE: This obviously is the simplest of simple examples and it does not have any error handling or checking whether the balance is sufficient to withdraw etc. This is just for demonstration purposes and not to be used in a real-life application.

With this encapsulation I see/know right away

• accBalance is a private field not accessible anywhere outside the class.

• I can only retrieve the balance() and not explicitly set it on an instance of the Account class.

• I can deposit() and withdraw() money from the account (publicly accessible methods)

In you standard module (module1) even with intelli-sense you get the .Balance listed and that's all your library/class user ever have to worry about.

Now having a standard coding module to test both classes (Module1)

Sub Main()    Dim myAccount As Account    Set myAccount = New Account    Debug.Print "Starting Balance: " & myAccount.Balance    myAccount.Deposit (2000)    Debug.Print "Deposited: 2000"    myAccount.WithDraw (250)    Debug.Print "Withdrew: 250"    Debug.Print "Ending Balance: " & myAccount.Balance    ' can't set balance    ' myAccount.Balance = 999999999999999999999999End Sub

To get an intro to VBA OOP I could recommend:

• How to use the Implements in Excel VBA

• How to use comparison methods between class object modules in VBA in a similar manner as VB.NET?

A property allows external access as though the property was a public field, while allowing the class to keep control of the data.

The Get property may compute a "variable" that doesn't actually exist in the class. E.g. a mass Get property might return the product of density times volume.

Private density as DoublePrivate volume as DoublePrivate potentialEnergyPublic Property Get mass() As Double   mass = density*volumeEnd Property 'Property Get mass

A Let property might check validity, e.g. not accept a negative volume. Or it can keep an object's field properties in sync:

Public Property Let density(rho as Double)   if rho > 0 then      density = rho      potentialEnergy = density * volume * gravity * heightEnd Property 'Property Get mass

You can also make a property read-only (or write-only -- not used much) by omitting the Let or Get property.

Other than a very slight degradation in performance, it's good practice to use properties from the start for any fields that you allow public access, even if the properties are initially trivial, to facilitate future modifications to the class.