This article will show how to create a complete custom class in VBA. It uses the example of a
DateRange object, because a starting and ending date are often passed together to functions.
Property procedure is a series of statement that retrieves or modifies a custom property on a module.
There are three types of property accessors:
Getprocedure that returns the value of a property.
Letprocedure that assigns a (non-
Object) value to an object.
Setprocedure that assigns an
Property accessors are often defined in pairs, using both a
Set for each property. A property with only a
Get procedure would be read-only, while a property with only a
Set procedure would be write-only.
In the following example, four property accessors are defined for the
StartDate(read/write). Date value representing the earlier date in a range. Each procedure uses the value of the module variable,
EndDate(read/write). Date value representing the later date in a range. Each procedure uses the value of the module variable,
DaysBetween(read-only). Calculated Integer value representing the number of days between the two dates. Because there is only a
Getprocedure, this property cannot be modified directly.
Setprocedure used to copy the values of an existing
Private mStartDate As Date ' Module variable to hold the starting date Private mEndDate As Date ' Module variable to hold the ending date ' Return the current value of the starting date Public Property Get StartDate() As Date StartDate = mStartDate End Property ' Set the starting date value. Note that two methods have the name StartDate Public Property Let StartDate(ByVal NewValue As Date) mStartDate = NewValue End Property ' Same thing, but for the ending date Public Property Get EndDate() As Date EndDate = mEndDate End Property Public Property Let EndDate(ByVal NewValue As Date) mEndDate = NewValue End Property ' Read-only property that returns the number of days between the two dates Public Property Get DaysBetween() As Integer DaysBetween = DateDiff("d", mStartDate, mEndDate) End Function ' Write-only property that passes an object reference of a range to clone Public Property Set RangeToCopy(ByRef ExistingRange As DateRange) Me.StartDate = ExistingRange.StartDate Me.EndDate = ExistingRange.EndDate End Property
Property inside a class module can be called by preceding the call with an object reference:
DateRange class, a
Sub could be used to add a number of days to the end date:
Public Sub AddDays(ByVal NoDays As Integer) mEndDate = mEndDate + NoDays End Sub
Function could return the last day of the next month-end (note that
GetFirstDayOfMonth would not be visible outside the class because it is private):
Public Function GetNextMonthEndDate() As Date GetNextMonthEndDate = DateAdd("m", 1, GetFirstDayOfMonth()) End Function Private Function GetFirstDayOfMonth() As Date GetFirstDayOfMonth = DateAdd("d", -DatePart("d", mEndDate), mEndDate) End Function
Procedures can accept arguments of any type, including references to objects of the class being defined.
The following example tests whether the current
DateRange object has a starting date and ending date that includes the starting and ending date of another
Public Function ContainsRange(ByRef TheRange As DateRange) As Boolean ContainsRange = TheRange.StartDate >= Me.StartDate And TheRange.EndDate <= Me.EndDate End Function
Note the use of the
Me notation as a way to access the value of the object running the code.
By default, a new class module is a Private class, so it is only available for instantiation and use within the VBProject in which it is defined. You can declare, instantiate and use the class anywhere in the same project:
'Class List has Instancing set to Private 'In any other module in the SAME project, you can use: Dim items As List Set items = New List
But often you'll write classes that you'd like to use in other projects without copying the module between projects. If you define a class called
ProjectA, and want to use that class in
ProjectB, then you'll need to perform 4 actions:
Change the instancing property of the
List class in
ProjectA in the Properties window, from
Create a public "factory" function in
ProjectA that creates and returns an instance of a
List class. Typically the factory function would include arguments for the initialization of the class instance. The factory function is required because the class can be used by
ProjectB cannot directly create an instance of
Public Function CreateList(ParamArray values() As Variant) As List Dim tempList As List Dim itemCounter As Long Set tempList = New List For itemCounter = LBound(values) to UBound(values) tempList.Add values(itemCounter) Next itemCounter Set CreateList = tempList End Function
ProjectB add a reference to
ProjectA using the
ProjectB, declare a variable and assign it an instance of
List using the factory function from
Dim items As ProjectA.List Set items = ProjectA.CreateList("foo","bar") 'Use the items list methods and properties items.Add "fizz" Debug.Print items.ToString() 'Destroy the items object Set items = Nothing