Need help with code logic/consolidation

[bg18461]

I am trying to calculate load factor for a data table that changes monthly in access. For those who are unfamaliar with calculating Load Factor, the formula is: kwh/(kw*(days in month) *24)



My data is rolling over 24 months and I calculate the data quarterly. For the first quarter this is my code.

Function FirstQtrLFMth1to12(dThisMonth As Date, AKWH1 As Long, AKWH2 As Long, AKWH3 As Long, _
AKWH4 As Long, AKWH5 As Long, AKWH6 As Long, AKWH7 As Long, AKWH8 As Long, AKWH9 As Long, _
AKWH10 As Long, AKWH11 As Long, AKWH12 As Long, AKW1 As Long, AKW2 As Long, AKW3 As Long, _
AKW4 As Long, AKW5 As Long, AKW6 As Long, AKW7 As Long, AKW8 As Long, AKW9 As Long, _
AKW10 As Long, AKW11 As Long, AKW12 As Long) As Long

Dim sDate As String
Dim sYear As String

    sYear = CStr(Year([dThisMonth]))
    sDate = Month([dThisMonth]) & "/1/" & sYear

    Select Case sDate

        Case "1/1/" & sYear
            FirstQtrLFMth1to12 = (AKWH1 / (AKW1 * DaysM("1/1/" & sYear) * 24))
        Case "2/1/" & sYear
            FirstQtrLFMth1to12 = ((AKWH2 / (AKW2 * DaysM("2/1/" & sYear) * 24)) _
            + (AKWH1 / (AKW1 * DaysM("1/1/" & sYear) * 24))) / 2
        Case "3/1/" & sYear
            FirstQtrLFMth1to12 = ((AKWH3 / (AKW3 * DaysM("3/1/" & sYear) * 24)) _
            + (AKWH2 / (AKW2 * DaysM("2/1/" & sYear) * 24)) _
            + (AKWH1 / (AKW1 * DaysM("1/1/" & sYear) * 24))) / 3
        Case "4/1/" & sYear
            FirstQtrLFMth1to12 = ((AKWH4 / (AKW4 * DaysM("4/1/" & sYear) * 24)) _
            + (AKWH3 / (AKW3 * DaysM("3/1/" & sYear) * 24)) _
            + (AKWH2 / (AKW2 * DaysM("2/1/" & sYear) * 24))) / 3
        Case "5/1/" & sYear
            FirstQtrLFMth1to12 = ((AKWH5 / (AKW5 * DaysM("5/1/" & sYear) * 24)) _
            + (AKWH4 / (AKW4 * DaysM("4/1/" & sYear) * 24)) _
            + (AKWH3 / (AKW3 * DaysM("3/1/" & sYear) * 24))) / 3
        Case "6/1/" & sYear
            FirstQtrLFMth1to12 = ((AKWH6 / (AKW6 * DaysM("6/1/" & sYear) * 24)) _
            + (AKWH5 / (AKW5 * DaysM("5/1/" & sYear) * 24)) _
            + (AKWH4 / (AKW4 * DaysM("4/1/" & sYear) * 24))) / 3
        Case "7/1/" & sYear
            FirstQtrLFMth1to12 = ((AKWH7 / (AKW7 * DaysM("7/1/" & sYear) * 24)) _
            + (AKWH6 / (AKW6 * DaysM("6/1/" & sYear) * 24)) _
            + (AKWH5 / (AKW5 * DaysM("5/1/" & sYear) * 24))) / 3
        Case "8/1/" & sYear
            FirstQtrLFMth1to12 = ((AKWH8 / (AKW8 * DaysM("8/1/" & sYear) * 24)) _
            + (AKWH7 / (AKW7 * DaysM("7/1/" & sYear) * 24)) _
            + (AKWH6 / (AKW6 * DaysM("6/1/" & sYear) * 24))) / 3
        Case "9/1/" & sYear
            FirstQtrLFMth1to12 = ((AKWH9 / (AKW9 * DaysM("9/1/" & sYear) * 24)) _
            + (AKWH8 / (AKW8 * DaysM("8/1/" & sYear) * 24)) _
            + (AKWH7 / (AKW7 * DaysM("7/1/" & sYear) * 24))) / 3
        Case "10/1/" & sYear
            FirstQtrLFMth1to12 = ((AKWH10 / (AKW10 * DaysM("10/1/" & sYear) * 24)) _
            + (AKWH9 / (AKW9 * DaysM("9/1/" & sYear) * 24)) _
            + (AKWH8 / (AKW8 * DaysM("8/1/" & sYear) * 24))) / 3
        Case "11/1/" & sYear
            FirstQtrLFMth1to12 = ((AKWH11 / (AKW11 * DaysM("11/1/" & sYear) * 24)) _
            + (AKWH10 / (AKW10 * DaysM("10/1/" & sYear) * 24)) _
            + (AKWH9 / (AKW9 * DaysM("9/1/" & sYear) * 24))) / 3
        Case "12/1/" & sYear
            FirstQtrLFMth1to12 = ((AKWH12 / (AKW12 * DaysM("12/1/" & sYear) * 24)) _
            + (AKWH11 / (AKW11 * DaysM("11/1/" & sYear) * 24)) _
            + (AKWH10 / (AKW10 * DaysM("10/1/" & sYear) * 24))) / 3
        Case Else
            FirstQtrLFMth1to12 = 0

    End Select

End Function

I have 4 functions (1 for each quarter), my last quarter is

Function FourthQtrLFMth1to12(dThisMonth As Date, AKWH1 As Long, AKWH2 As Long, AKWH3 As Long, _
AKW1 As Long, AKW2 As Long, AKW3 As Long)
Dim sDate As String
Dim sYear As String
    sYear = CStr(Year([dThisMonth]))
    sDate = Month([dThisMonth]) & "/1/" & sYear
 
    Select Case sDate
        Case "10/1/" & sYear
            FourthQtrLFMth1to12 = (AKWH1 / (AKW1 * DaysM("10/1/" & sYear) * 24))
        Case "11/1/" & sYear
            FourthQtrLFMth1to12 = ((AKWH2 / (AKW2 * DaysM("11/1/" & sYear) * 24)) _
            + (AKWH1 / (AKW1 * DaysM("10/1/" & sYear) * 24))) / 2
        Case "12/1/" & sYear
            FourthQtrLFMth1to12 = ((AKWH3 / (AKW3 * DaysM("12/1/" & sYear) * 24)) _
            + (AKWH2 / (AKW2 * DaysM("11/1/" & sYear) * 24)) _
            + (AKWH1 / (AKW1 * DaysM("10/1/" & sYear) * 24))) / 3
        Case Else
            FourthQtrLFMth1to12 = 0
 
    End Select
 
End Function

I would like to consolidate my code to this

Function QtrLFMth13to24(dThisMonth As Date, ParamArray lngLF() As Variant) As Long
Dim i As Integer
'lngLF() is zero based...
    i = Month(dThisMonth) - 1
'function should only receive 16 array elements (6 months data) at a time ...
    If i > 5 Then i = i - 6
    QtrLFMth13to24 = lngLF(i) + lngLF(i + 1) + lngLF(i + 2)
End Function

I used the above code for simple functions like adding, but the load factor requires more math and I am not sure how to institute it, any ideas?

 

[ajetrumpet]

you're going to have to find something in common with your quarter functions. it looks like you have the situation well in hand. what don't you know how to do? put your code blocks for each month into an array? that is certainly difficult, and as far as i can tell, you might as well just keep it as is. as far as I know, you can't use actual code words inside an actual vba array.

also, you may not have received an answer to this because much of the inside project info that is needed to accomplish this, YOU only know.

 

[Kafrin]

Firstly, try to simplify your individual functions, then see if you can put them together, eg:

Public Function FirstQtrLFMth1to12(dThisMonth As Date, AKW() As Variant)
Dim sDate As String, lYear As Long, lMonth As Long
    lMonth = Month(dThisMonth)
    lYear = Year(dThisMonth)
    sDate = "/1/" & CStr(lYear)
    
    FirstQtrLFMth1to12 = AKW(0)(lMonth) / (AKW(1)(lMonth) * DaysM(CStr(lMonth) & sDate) * 24)
    If lMonth - 1 > 0 Then FirstQtrLFMth1to12 = FirstQtrLFMth1to12 + (AKW(0)(lMonth - 1) / (AKW(1)(lMonth - 1) * DaysM(CStr(lMonth - 1) & sDate) * 24))
    If lMonth - 2 > 0 Then FirstQtrLFMth1to12 = FirstQtrLFMth1to12 + (AKW(0)(lMonth - 2) / (AKW(1)(lMonth - 2) * DaysM(CStr(lMonth - 2) & sDate) * 24))
    FirstQtrLFMth1to12 = FirstQtrLFMth1to12 / 3
End Function
 
'Which you call like this...
Public Function FirstQtrLF(dThisMonth As Date)
    Dim Arr() As Variant
    
    Arr = Array(Array(0, AKWH1, AKWH2, AKWH3, AKWH4, AKWH5, AKWH6, AKWH7, AKWH8, AKWH9, AKWH10, AKWH11, AKWH12), _
                     Array(0, AKW1, AKW2, AKW3, AKW4, AKW5, AKW6, AKW7, AKW8, AKW9, AKW10, AKW11, AKW12))
    
    FirstQtrLF = FirstQtrLFMth1to12(dThisMonth, Arr)
    
End Function