Advice re class modules

[werafa]

Hi all,

I'm considering my first foray into class modules, and would like to verify that what I want is achievable. (it doesn't have to be the most efficient method - as I do want to learn to use class modules)

I do some coding around financial years, and want a FinQuarter object that will contain MonthNum FinMonthNum, and FinMonthNames.
I then want to be able to take a month number or name and return the FinQ and FinMonthNum

Is this all logical, and will it work?

thanks
Werafa

[Paul_Hossler]

Yes, and Yes

[Paul_Hossler]

http://www.cpearson.com/Excel/Classes.aspx

Very^2 simple example -- Chip has a lot more information, and a good explanation of classes

You can have subs and function in a class module also


In a Standard Module

Option Explicit

Sub test()
    Dim Qtrs(1 To 4) As clsFinQuarter
    Dim i As Long
    
    For i = LBound(Qtrs) To UBound(Qtrs)
        Set Qtrs(i) = New clsFinQuarter
        Qtrs(i).FinMonthNum = 3 * (i - 1)
    Next i
        
    
    MsgBox Qtrs(2).FinMonthName     '   Jan
    MsgBox Qtrs(4).FinMonthName     '   Jul
    
End Sub

In a Class module

Option Explicit


Private m_MonthNum As Long
Private m_FinMonthNum As Long
Private m_FinMonthNames As Variant


Private Sub Class_Initialize()
    m_FinMonthNames = Array("Oct", "Nov", "Dec", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep")
End Sub

Property Let MonthNum(N As Long)
    m_MonthNum = N
End Property
Property Get MonthNum() As Long
    MonthNum = m_MonthNum
End Property


Property Let FinMonthNum(N As Long)
    m_FinMonthNum = N
End Property
Property Get FinMonthNum() As Long
    FinMonthNum = m_FinMonthNum
End Property
 
Property Get FinMonthName() As String
    FinMonthName = m_FinMonthNames(FinMonthNum)
End Property

[werafa]

Thanks Paul,

tis simple when you know how
Regards
Werafa

[Paul_Hossler]

Oh, they can get a whole lot more complicated

I've looked at some of the stuff the pros do here, and it's amazing

[werafa]

This appears to be working the way I want it.
Thanks for your help

here is the code for anyone else who wishes to play with it

Option Explicit

Private p_MonthNum As Long
Private p_FinMonthNum As Long
Private p_FinMonthName As String
Private p_FinMonthNames As Variant
Private p_FinQuarter As Long




Private Enum pFinMonth
    July = 1
    August = 2
    September = 3
    October = 4
    November = 5
    December = 6
    January = 7
    February = 8
    March = 9
    April = 10
    May = 11
    June = 12
End Enum


Private Sub Class_Initialize()
    '-----
    p_FinMonthNames = Array("July", "August", "September", "October", "November", "December", _
        "January", "February", "March", "April", "May", "June")
    '-----
End Sub




Property Let MonthNum(N As Long)
    p_MonthNum = N
    If N < 7 Then
        p_FinMonthNum = N + 6
    Else
        p_FinMonthNum = N - 6
    End If
    p_FinMonthName = p_FinMonthNames(p_FinMonthNum)
End Property
Property Get MonthNum() As Long
    MonthNum = p_MonthNum
End Property


Property Let FinMonthNum(N As Long)
    p_FinMonthNum = N
    If N < 7 Then
        p_MonthNum = N + 6
    Else
        p_MonthNum = N - 6
    End If
    p_FinMonthName = p_FinMonthNames(p_FinMonthNum)
End Property
Property Get FinMonthNum() As Long
    FinMonthNum = p_FinMonthNum
End Property


Property Let FinMonthName(S As String)
    p_FinMonthName = S
    p_FinMonthNum = FinMonth(S) 'not sure about this line
    If p_FinMonthNum < 7 Then
        p_MonthNum = p_FinMonthNum + 6
    Else
        p_MonthNum = p_FinMonthNum - 6
    End If


End Property
Property Get FinMonthName() As String
    FinMonthName = p_FinMonthNames(FinMonthNum)
End Property

[werafa]

and

Property Get FinQuarter() As Long
Dim lFQ As Long
Dim lNum As Long
    
    lNum = p_FinMonthNum
    
    If lNum = 0 Then
        FinQuarter = 0
    Else
        lNum = lNum + 2
        FinQuarter = Int(lNum / 3)
    End If
        
End Property

[werafa]

and in a separate class module

' ------------------------------------------------------
' Name: FinQ
' Kind: Class Module
' Purpose: Let a fin month number
' and get an array with FinQ num, and the three fin months in the quarter
' Where 0 = FQ, 1 = FQm1, 2 = FQm2 , 3 = FQm3
' ------------------------------------------------------
Option Explicit




Dim p_finMonth As Long
Dim p_finQ As Long




Property Let FMNum(lFM As Long)
Dim lNum
    If lFM > 0 And lFM < 13 Then
        p_finMonth = lFM
        lNum = lFM + 2
        p_finQ = Int(lNum / 3)
    Else
        p_finMonth = 0
    End If
End Property


Property Get FQNums() As Variant
Dim lFQ As Long
Dim lFM As Long
Dim lNum As Long
    


    If p_finMonth > 0 Then
        'get the fin quarter
        lFQ = p_finQ
        lFM = p_finMonth
        
        'write the array of month numbers
        lNum = lFQ * 3 - 2
        FQNums = Array(lFQ, lNum, lNum + 1, lNum + 2)
        
    Else
        'error mgt
        FQNums = 0
    
    End If
    
End Property