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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.