VBA & Macro Problems

How to Use Property Get, Let, and Set in VBA Class Modules

Maira Mehtab Oct 1, 2026 871 views

Question details

The user needs to understand the differences between Property Get, Let, and Set in VBA class modules, specifically when storing objects with properties in collections.

How to Use Property Get, Let, and Set in VBA Class Modules

Product: VBA (Excel/WPS Spreadsheets)
Device & OS: not provided
Scenario: Developing or troubleshooting VBA macros that use class modules to define custom objects and store them in collections.
Observed behavior: Clarification is needed on how to properly retrieve values, assign scalar values, and handle object references without causing variable assignment errors.

Before you start

Ensure that the Developer tab is enabled in your spreadsheet application and that you have basic familiarity with the VBA Editor and object-oriented programming concepts.

Solution 1Recommended

Define and Apply Property Get, Let, and Set in a Class Module

Use specific Property procedures to control how variables and objects are read, assigned, and validated within your class module.

In VBA, Property procedures allow a class module to expose variables safely. 'Property Get' is used to return a value to the caller. 'Property Let' is used to assign a scalar value (like strings, integers, or longs). 'Property Set' is specifically required when you assign an object reference (like a Worksheet, Collection, or another Class instance).

It is common practice to store the actual data in a Private variable and expose it via these Public Property procedures, allowing you to add validation rules during assignment.

Insert a Class Module

Press Alt + F11 to open the VBA Editor. Right-click your project in the Project Explorer, choose 'Insert', and select 'Class Module'. Name the module (e.g., 'MyModule') in the Properties window.

Declare a Private Variable

At the top of the Class Module, declare a private variable to hold your data. For example: 'Private WhatEverA As Long'.

Create a Property Get Procedure

Add a 'Property Get' block to return the value. Code: 'Property Get RowPosition() As Long', followed by 'RowPosition = WhatEverA', and end with 'End Property'.

Create a Property Let Procedure for Scalars

Add a 'Property Let' block to assign scalar values. You can include validation here. Code: 'Property Let RowPosition(ByVal Value As Long)'. Inside, add validation: 'If Value < 1 Or Value > 1048576 Then Err.Raise 1004, "MyModule", "Invalid row position"'. Finally, assign it: 'WhatEverA = Value', followed by 'End Property'.

Create a Property Set Procedure for Objects

If your property stores an object rather than a scalar, use 'Property Set'. Code: 'Property Set CustomObject(ByVal obj As Object)', followed by 'Set InternalObject = obj', and 'End Property'.

Define and Apply Property Get, Let, and Set in a Class Module

Object References in Collections: When collection items are objects, they are stored as references. If you change a property of an object retrieved from a collection, you change the actual object inside the collection. To replace the object entirely, you must assign a new reference.

Advanced Macro Support

Write and Manage VBA Macros with WPS Spreadsheets

WPS Spreadsheets offers a built-in, fully functional VBA Editor, allowing you to effortlessly create class modules, utilize collections, and manage Property Get, Let, and Set procedures.

1. Open WPS Spreadsheets: Launch WPS Office and open your macro-enabled workbook (.xlsm).
2. Access the Developer Tab: Navigate to the Developer tab on the ribbon and click 'VBA Editor' to open the coding environment.
3. Insert Class Modules: Right-click anywhere in the Project Explorer panel, select 'Insert', and choose 'Class Module'.
4. Write Property Procedures: Define your Property Get, Let, and Set methods inside the newly created class module to manage your custom objects and variables.

Full support for advanced VBA macros and class module development.Seamlessly compatible with Microsoft Excel .xlsm and .xlsb formats.Built-in Developer tools for executing and debugging Property procedures.Lightweight architecture for faster macro execution.

microsoft office alternative - wps office

Frequently Asked Questions

What is the difference between Property Let and Property Set in VBA?

Property Let is used to assign basic scalar values (like integers, strings, longs, or booleans) to a property. Property Set is mandatory when you need to assign an object reference (such as a Worksheet, a Range, a Collection, or a custom Class object) to a property.

Why do I get an error when assigning an object to a custom property?

If you try to assign an object using Property Let, or if you forget to use the 'Set' keyword during assignment, VBA will attempt to assign the default scalar property of that object instead of the reference, which causes a Type Mismatch error. Always use 'Property Set' and the 'Set' keyword for objects.

How do I validate a property value in a VBA class module?

You can embed validation logic directly inside your Property Let or Set procedures. For example, before assigning a row index to a private variable, use an 'If' statement to check if the value is between 1 and 1048576. If it falls outside this range, use 'Err.Raise' to return a custom error message to the user.

Excel & Data Problems