Difference between Property Let and Set

When creating a class in VBA and dealing with properties (member variables), there are three Property methods: Get, Set, and Let.

In languages other than VBA, it is common to use only Getter and Setter to set properties (member variables).

Therefore, some people may feel uncomfortable when they first see the definition of properties in VBA.

This time, let’s summarize the difference between Property Set and Property Let in VBA.

Difference between Property Set and Property Let

In conclusion,

• Set is required when assigning a value to a reference type variable.
• Let is used for other cases.

I believe this understanding is sufficient.

Explanation

When assigning a value to a reference type variable, Set must be used, otherwise it will result in an error.

For example, in the case of a worksheet:

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)

This applies to properties in classes as well. When you want to set a value to a property in a class,

' Class1 Module

Private m_ws As Worksheet

' Since the Worksheet class is a reference type, it needs to be defined with Set
Public Property Set ws(ByRef received As Worksheet)

    ' Set is also required when assigning a value to the property
    Set m_ws = received
End Property

' Standard Module

Dim c1 As Class1
Set c1 = New Class1

' Set is also required when calling it
Set c1.ws = ThisWorkbook.Worksheets(1)

It will result in an error if there is no Set for at least one.

It’s quite cumbersome, but it is safe to understand that the concept of Set in variable declaration is also applied to Property in VBA.

Execution Test

' Class1 Module
Private m_ws As Worksheet

Public Property Set ws(ByVal received As Worksheet)
    Set m_ws = received
End Property

Public Property Get ws() As Worksheet
    Set ws = m_ws
End Property

' Standard Module

Public Sub test()

    Dim c1 As Class1
    Set c1 = New Class1

    Set c1.ws = ThisWorkbook.Worksheets(1)

    Debug.Print c1.ws.Cells(1, 1).Value
End Sub

The value of the first cell of the first sheet of the workbook will be displayed in the Immediate window.

Can’t it be replaced with Let?

Yes, it can. It can be replaced.

If you use the previous code,

' Class1 Module

Private m_ws As Worksheet

' Except for changing Set to Let, everything else remains the same
Public Property Let ws(ByVal received As Worksheet)

    ' The use of Set is required even when assigning a value to the property in the case of Let
    Set m_ws = received
End Property

' Standard Module

Dim c1 As Class1
Set c1 = New Class1

' The use of Set is no longer required when calling it
c1.ws = ThisWorkbook.Worksheets(1)

This will result in the same outcome.

Therefore, in essence, setting properties (member variables) can be done with just Get and Let.

There may be exceptional cases where this is not possible, but I’m not sure why this aspect is so flexible…

In Conclusion

Some may feel that VBA is outdated, but there are still ways to utilize Excel in the SaaS era.

One of those ways is by using APIs of cloud services from VBA.

We have introduced some of these methods on our website, so please take a look if you’re interested.