VBA Creating a Custom Class Adding Functionality to a Class


Any public Sub, Function, or Property inside a class module can be called by preceding the call with an object reference:


In a 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

A 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 DateRange object.

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.