PDA

View Full Version : [SOLVED] Advice re class modules



werafa
09-17-2018, 03:33 AM
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
09-17-2018, 06:32 AM
Yes, and Yes

Paul_Hossler
09-17-2018, 06:48 AM
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
09-17-2018, 12:11 PM
Thanks Paul,

tis simple when you know how :)
Regards
Werafa

Paul_Hossler
09-18-2018, 08:59 AM
Oh, they can get a whole lot more complicated :yes

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

werafa
10-04-2018, 01:18 PM
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
10-04-2018, 01:31 PM
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
10-04-2018, 02:36 PM
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