VBA difference between public variable and propert

What is the difference between

Public Variable As Integer

and

Private pVariable As Integer

Public Property Let Variable(ByVal lVariable As Integer)
    pVariable = lVariable
End Property

Public Property Get Variable()
    Variable = pVariable
End Property

in a VBA class module?

And why would I use the second version?

回答1:

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 Integer

Public Property Let Variable(ByVal lVariable As Integer)
    pVariable = lVariable
End Property

Public Property Get Variable()
    Variable = pVariable
End 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 Sub

Sub 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 operations
Private accBalance As Double

' see Getter only - no setter
Public Property Get Balance() As Double
    Balance = accBalance
End Property

Public Function Deposit(amount As Double)
    accBalance = accBalance + amount
End Function

Public Function WithDraw(amount As Double)
    accBalance = accBalance - amount
End Function

Private Sub IAccountServices_Deposit(amount As Double)
    accBalance = accBalance + amount
End Sub

Private Sub IAccountServices_WithDraw(amount As Double)
    accBalance = accBalance - amount
End 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 = 999999999999999999999999
End 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?

回答2:

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 Double
Private volume as Double
Private potentialEnergy

Public Property Get mass() As Double
   mass = density*volume
End 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 * height
End 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.