VBA Property Get/Let in Standard Modules with optional arguments

In this post:

• The very basics
• The full potential unfold
• Read text string from file
• Write text string to file
• Summary

Not only in Class Modules but also in Standard Modules! A usefulness potentially underestimated and undervalued, and a pitty when ignored.

The very basics

The commonly used example for a Property in a Class Module:

Option Explicit
Private sCustName As String
Public Property Let CustName(ByVal s As String)
    sCustName = s
End Property
Public Property Get CustName() As String
    CustName = sCustName
End Property

provides a very basic understanding of the concept of a ‘bi-directional Function’ but may hide it’s full potential unfold when it’s also used in a Standard Module and particularly when combined with optional arguments. In the example above, in a Class Module the Property value is saved to and read from a Private declared variable. However, the ‘value’ may as well be an object and the source/target for it may be a Collection, a Dictionary, an Excel Worksheet, a File, … .

The full potential unfold

In the following example the Propertty value is written to and read from a TextFile. In a Standard Module called mFile the service Txt (the module mFile may be downloaded for the full implementation).

Public Property Let Txt( _
         Optional ByVal ft_file As Variant, _
         Optional ByVal ft_append As Boolean = True, _
         Optional ByRef ft_split As String, _
                  ByVal ft_string As String)
' -----------------------------------------------------
' Writes the string (ft_string) into the file (ft_file)
' which might be a file object of a file's full name.
' Note: ft_split is not used but specified to comply
'       with the Get Property declaration.
' -----------------------------------------------------

    ' see the download for the full implementation

End Property

Public Property Get Txt( _
         Optional ByVal ft_file  As Variant, _
         Optional ByVal ft_append As Boolean = True, _
         Optional ByRef ft_split As String) As String
' ----------------------------------------------------
' Returns the text file's (ft_file) content as string
' with VBA.Split() string in (ft_split).
' Note: ft_append is not used but specified to comply
'       with the Get Property declaration.
' ----------------------------------------------------

    ' see the download for the full implementation
    
End Property

The usage of the above outlined example service emphasizes the potential and elegance of Properties with optional arguments in Standard Modules.

Read text string from file

The optionally returned split string argument can be used to transfer the string with VBA.Split into an array.

Dim s As String
Dim v As Variant
s = mFile.Txt(ft_file:=myfile, ft_split:=sSplit)
v = VBA.Split(s, sSplit)

Write text string to file

When the string is intermitted with vbCrLf the string may represent the full content written to the file. The optional arguments allows appending the string to the file.

mFile.Txt(ft_file:=myfile, ft_append:=True) = s

Summary

1. Properties are not only for Class Modules but can play a significant role in Standard Modules
2. Properties may have any number of optional arguments provided
   • they are declared consistently in the Get and Let Property
   • they are declared in the Let Property before the assignment variable.
     
     Attention! This is in contrast to ‘normal’ declarations where they have to be declared at last
3. Considering the fact that the ‘value’ may be anything and the source/target may be a wide range of things (Worksheet, File, Collection, Dictionary, etc, the applications for Properties ‘explode’.
4. Downsides:
   • Friend Property is not supported! Public or Private is the only choice.
   • Because all optional arguments must exactly match between Get and Let some may not be used and this will be considered ‘dead code’ when quality checked e.g. with MZTools (what I use)