[VBA] How to Master Class Property Definitions (Property Let/Get/Set)

2025年12月27日

Introduction

When creating objects in VBA Class Modules, Properties serve as the gateway to safely manipulate the object’s “data (attributes)” from the outside.

Properly defining properties allows for advanced control, such as:

Value Validation: Preventing invalid values (e.g., negative prices) from being set.
Read-Only Properties: Providing internally calculated results (e.g., price including tax) as immutable values to the outside.
Object Storage: Holding objects like Shape or Range as properties.

In this article, I will thoroughly explain how to use Property Let, Property Get, and Property Set, which are the core functions of VBA classes, with concrete examples.

1. Handling Values with Property Let and Property Get

For values such as String, Long, or Currency, use Let (for writing) and Get (for reading).

Property Let: Executed when a value is written from the outside in the form Object.Property = Value.
Property Get: Executed when a value is read from the outside in the form Variable = Object.Property.

Class Module: `ClsProduct` Code Example

' --- Private variable to hold internal data ---
Private p_UnitPrice As Currency

' --- UnitPrice Property ---

' [For Writing] Called when a value is assigned to the UnitPrice property
Public Property Let UnitPrice(value As Currency)
    ' Value Validation: If a value less than 0 is attempted, set it to 0
    If value < 0 Then
        p_UnitPrice = 0
    Else
        p_UnitPrice = value
    End If
End Property

' [For Reading] Called when the value of the UnitPrice property is referenced
Public Property Get UnitPrice() As Currency
    UnitPrice = p_UnitPrice
End Property

Usage in a Standard Module

Sub Test_Let_Get()
    Dim item As New ClsProduct
    
    ' Property Let is called
    item.UnitPrice = -500
    
    ' Property Get is called
    ' The invalid value -500 has been converted to 0 by the validation logic
    Debug.Print "Unit Price: " & item.UnitPrice ' -> Displays "Unit Price: 0" in the Immediate Window
End Sub

In this example, the Property Let procedure includes validation logic to check if the incoming value is negative. This prevents the unit price of the object from becoming negative, maintaining data integrity.

2. Handling Objects with Property Set

When handling objects like Range, Worksheet, or Shape as properties, use Set instead of Let.

Property Set: Executed when an object is assigned from the outside in the form Set Object.Property = Object.

Class Module: `ClsProduct` Code Example (Addition)

' --- Private variable to hold internal data (Addition) ---
Private p_LinkedShape As Shape

' --- LinkedShape Property (For Objects) ---

' [For Writing] Called when an object is assigned to the LinkedShape property
Public Property Set LinkedShape(value As Shape)
    Set p_LinkedShape = value
End Property

Usage in a Standard Module

Sub Test_Set()
    Dim item As New ClsProduct
    
    ' Property Set is called (Note the use of Set keyword)
    Set item.LinkedShape = ActiveSheet.Shapes(1)
End Sub

3. Read-Only Properties

By defining only Property Get and omitting Property Let or Property Set, you can create a Read-Only property. This is extremely useful for providing values calculated from internal data (e.g., price with tax).

Class Module: `ClsProduct` Code Example (Addition)

' --- PriceWithTax Property (Read-Only) ---

' [Read-Only] Returns the calculated result when referenced
Public Property Get PriceWithTax() As Currency
    Const TAX_RATE As Double = 0.1
    ' Returns the unit price multiplied by the tax rate
    PriceWithTax = Round(p_UnitPrice * (1 + TAX_RATE))
End Property

Usage in a Standard Module

Sub Test_ReadOnly()
    Dim item As New ClsProduct
    item.UnitPrice = 1000
    
    ' Property Get is called
    Debug.Print "Price with Tax: " & item.PriceWithTax ' -> Displays "Price with Tax: 1100"
    
    ' The code below causes a Compile Error (because writing is not defined)
    ' item.PriceWithTax = 1200
End Sub