Class properties are typically simple statements which serve the basic purpose of reading or writing data in custom objects. However, it is possible to add additional lines of code, giving properties features similar to standard subroutines or functions.
This is the seventh post in the Tips and Tricks with VBA Classes series.
Today’s post provides an overview of scripting within class properties. In their traditional form, properties exist within classes solely to read data from, or write data to a custom object. This is done through the Get and Let properties. Generally, the need to include additional lines of code within properties is unnecessary, but doing so can create additional features in your class.
This post focuses on building more complex class properties in classes that support custom objects (as opposed to a collection class). In next week’s post, we will discuss scripting within class properties of collection classes.
In many cases, scripting within class properties could just as easily be accomplished with class methods, or even within a standard coding module. Regardless, this post exists to show you this feature as an option.
This post’s example file builds on the country list used throughout this series. Macros must be enabled upon opening.
Scripting within class properties is an optional technique to accomplishing various tasks, such as validating data, running calculations, or managing special formats.
If you recall from our post on standard classes, class modules can encapsulate “methods”, which include member subroutines and member functions. These two procedure types are typically the go-to tools to run calculations in custom objects, or forcing custom objects to do something. However, subroutines and functions must be called, either in a standard coding module, or within the class itself. By building scripts inside properties, the script will automatically fire whenever the property is called.
There are not many complex requirements to this approach. In fact, properties can encapsulate scripts just like any subroutine or function. The only exception is that no additional arguments can be passed through the Let or Get statement.
This post provides two examples of scripting within class properties. One Let property which validates data, and one Get property that completes a simple calculation.
Data Validation in the Let Property
It’s possible to include a data validation procedure within a class Let property. The main advantage is that a validation process is transferred with the class if it moves to another project. However, there are some disadvantages. First, it requires that the property parameter be declared as a Variant. Second, when used for an object within a collection class, a failed validation will not prevent the object from being added to the collection.
In the example file, the following property detects if the argument passed is a number. This is done with the IsNumeric function (line 3) — if it returns true, the CLng function is used to convert the Variant into a long value, then assigns the value to the mCountryPop member variable (line 4). If the value is not a number, an input box appears asking for a number to be input, which is subsequently assigned to the mCountryPop member variable (line 6).
Public Property Let countryPop(Value As Variant)
If IsNumeric(Value) Then
mCountryPop = CLng(Value)
mCountryPop = InputBox("Value must be numeric. Enter numeric value here.")
As stated previously, a failed validation will not prevent an object from being added to a collection — this is why the procedure itself requests an input if the original value is not a number. One potential bug in this specific example is that the input value is not re-validated (not done in this example for the sake of clarity).
If an object exists within a class collection, the object will still be added even if the validation of a property fails. You may think that the exit statement “Exit Property” will resolve this, but that simply exits that specific property. The remainder of the Insert/Add procedure will run. A default value will be assigned to the property regardless: either an empty string or zero depending on the data type. One way around this is to trigger global variable to operate a conditional in the collection class’ Insert/Add procedure.
Calculations in the Get Property
In cases where a property’s value is dependent on other properties, it may make sense to incorporate calculations within a Get property.
In the example file, we run a calculation in the countryPopDensity property to calculate a country’s population density. This data is dependent on two other properties which have been previously stored: countryPop and countryLandArea. The Round function is used to round to the nearest hundredth.
Public Property Get countryPopDensity() As Double
countryPopDensity = Round(mCountryPop / mCountryLandArea, 2)
This approach has both benefits and drawbacks. As a benefit, a member variable is not required for countryPopDensity, as it is never actually stored within the class. This can help save system memory, especially useful if many instances of a custom object are stored within a collection. However, this also comes with processor overhead as calculations get more complex, or the collection hosting the object gets significantly large. It may be worthwhile to simply store this value, calculating either through a Get property or in a separate member function.
- Our post on storing arrays as class properties shows another application of scripting within class properties.
- While it’s possible to run validation scripts in the Get (output) property, it doesn’t make logical sense. You’re better off validating data at the entry point (in a Let property) and storing as its most efficient data type. This can also provide a user with the opportunity to correct the error, as done in the example above.
- VBA does not permit more than one argument to be passed through a Let property. This may be desired when running complex calculations. As an alternative, you could use a global variable to access a value within a property. Regardless, in situations like this, you may be better off building the calculation as a member function.
- When using a property for data validation, a Variant data type is used in Let statement. This permits either a numeric or alpha value to be passed through the property without returning a “Type Mismatch” error.