Create Properties
Properties are values stored by a class. Simple properties, often called fields, may just be public variables within the class, as shown by the Title and Value properties of the Message class earlier. More complex properties are created using Property procedures.
Why would a property need to be complex? Several possible reasons:
- Most often, properties are complex if they represent a value that is calculated in some way, such as the count of a list of items.
- In other cases, a property may represent information that can be read, but not changed. These are called read-only properties.
- Less often, a property may represent information that can be set only once, but never changed. These are called write-once properties.
- Finally, a property may represent a value that can be set but never read. You almost never need to do that, but if you do, you'd call it a write-only property.
Let's continue on with the Message class example a bit to create two new properties that extend its email capabilities. The Recipients property that follows is another simple property that accepts a list of email addresses to send the message to:
' Message class
Public Recipients As String
To use this property from the Send method, we make these changes shown in bold:
Public Sub Send(Optional ToAddress As String)
Dim msgToSend As String, result As Double
If (ToAddress = "") Then ToAddress = Recipients
msgToSend = "mailto:" & ToAddress
msgToSend = msgToSend & "?SUBJECT=" & Title
msgToSend = msgToSend & "&BODY=" & Value
ThisWorkbook.FollowHyperlink msgToSend, , True
End Sub
Now, you can add Recipients as a list of email addresses separated by semicolons, just as they would be in a regular email message. Send no longer requires a ToAddress; if omitted, it uses the Recipients property. For example, this code sends a message to two recipients:
' TestMessage module
Sub TestMessageRecipients1( )
Dim msg1 As New Message
msg1.Title = "Message to Send"
msg1.Recipients = ";"
msg1.value = "This message brought to you by Excel."
msg1.Send
End Sub
That was a pretty easy, but what if the addresses come from a range of cells? It would be nice if the class were smart enough to convert those settings. To do that, you need to add Property procedures that convert values from a range of cells into a string of email addresses. You'd want Recipients to accept string values as well, so you need to create three different types of Property procedures: a Set procedure to accept the range setting, a Let procedure to accept a string setting, and a Get procedure to return the setting as a string. The following sample shows those additions to the Message class:
' Message class
Private m_Recipients As String
' Accept Range settings.
Property Set Recipients(value As Range)
Dim cel As Range
m_Recipients = ""
For Each cel In value
m_Recipients = m_Recipients & cel.value & ";"
Next
End Property
' Accept String settings as well.
Property Let Recipients(value As String)
' Set the internal variable.
m_Recipients = value
' Exit if ""
If value = "" Then Exit Property
' Make sure last character is ;
If Mid(value, Len(value) - 1, 1) = ";" Then
m_Recipients = value
Else
m_Recipients = value & ";"
End If
End Property
' Return the internal string variable.
Property Get Recipients( ) As String
Recipients = m_Recipients
End Property
Notice that I used a private variable, m_Recipients, to store the property setting within the class. That's a common practice with Property proceduresthe Set, Let, and Get procedures control access to that internal variable. In programming circles, those procedures are called accessor functions; often, you use accessors to validate a setting. For example, you might want to check whether email addresses are valid before allowing the property to be set.
To test the new Recipients property, enter some email addresses in A1:A3 and run the following code:
' TestMessage module
Sub TestMessageRecipients2( )
Dim msg1 As New Message
msg1.Title = "Message to Send"
msg1.value = "Some message text."
' Set the property as a range.
Set msg1.Recipients = [a1:a3]
' Show the addresses (gets property as string).
MsgBox "About to send to: " & msg1.Recipients
' Create message.
msg1.Send
End Sub
5.3.1. Read-Only Properties
Recipients is a read/write property. To create a read-only property, omit the Let and Set procedures. For example, the following code creates a RecipientCount property that returns the number of people set to receive a message:
' Read-only property to get the number of recipients.
Property Get RecipientCount( ) As Integer
Dim value As Integer
If m_Recipients > "" Then
value = UBound(Me.AddressArray)
Else
value = 0
End If
RecipientCount = value
End Property
' Read-only property to get an array of recipients.
Property Get AddressArray() As String( )
Dim value( ) As String
If m_Recipients > "" Then
' This is why m_Recipients must end with ;
value = VBA.Split(m_Recipients, ";")
End If
AddressArray = value
End Property
OK, I got a little tricky there and created two read-only properties. RecipientCount uses AddressArray to convert the string of recipients into an array, and then it counts the number of items in the array. There are other ways to get the count, but this way demonstrates using Me to call a property from within the class itself. Besides, AddressArray might come in handy later on...
5.3.2. Write-Once/Write-Only Properties
These types of properties are rarely needed and I thought about omitting them, but in the interest of being thorough, I decided to include some discussion here. It's easy to create a write-onlypropertyjust omit the Get procedurebut it's hard to even think of a situation in which that's useful to anyone...maybe setting a password or something:
Private m_Password As String
' Write-only property, rarely used.
Property Let Password(value As String)
m_Password = value
End Property
Because there is only a Let procedure and m_Password is Private, users can set the Password property but they can't get it. That might also be useful for database connection strings that can include username and password information that you should keep secure.
Write-once properties are somewhat more useful because they can represent information used to initialize an object. Once they are initialized, you usually don't want those settings to change, so a write-once property makes sense.
Write-once properties check to see if they have been previously set, and if they have, they raise an error:
Private m_Connection As String
' Write-once property, use to initialize object settings.
Property Let ConnectionString(value As String)
If m_Connection > "" Then
Err.Raise 2001, "ConnectionString", "Property already set"
Else
m_Connection = value
End If
End Property
In this case, ConnectionString is both write-once and write-only since I don't want others to see the setting once it is established. If the connection needs to change, the only way to do it is to create a new object with a new ConnectionString.
/ Anything you can do with write-only properties can be done equally well using methods. Defining Password or ConnectionString as Sub procedures, rather than as Property Let procedures, results in equivalent code.