Programming Excel with VBA and .NET

Properties

Function procedures return a result and so can be used on the righthand side of an assignment:

    x = CubeRoot(42)

But what if you want to put a procedure name on the lefthand side? That’s what properties do. Property procedures can be assigned to or assigned from—they can appear on either side of =. For example, the following code defines a Name property for a module:

    ' Code in PublicProcedures module
    Dim m_name As String

    ' Assign the name.
    Public Property Let Name(arg As String)
        m_name = arg
    End Property

    ' Return the name
    Public Property Get Name( ) As String
        Name = m_name
    End Property

Code outside the module can set or return the value from m_name by calling the Name property:

    Sub TestProperties( )
        PublicProcedures.Name = "Module name"
        Debug.Print PublicProcedures.Name
    End Sub

You could do something similar by just making m_name a public variable, but properties allow you special control that you don’t get with that technique. For example, the following code makes sure that Name is set only once:

    Public Property Let Name(arg As String)
        If arg <> "" And m_name = "" Then
            m_name = arg
        Else
            MsgBox "Name is already set to: " & m_name
        End If
    End Property

You can make a property read-only by not defining a Let procedure:

    Const m_date = #6/5/2004#

    ' Read-only property (no Let procedure)
    Public Property Get Created( )
        Created = m_date
    End Property

Properties can represent objects if they use Set instead of Let. For example, the following read/write property keeps track of a range ...