Code Optimization

T

thebomer

Joined
Jan 24, 2013
Messages
1
Reaction score
0
I have been putzing around writing VBA macros and functions for a little while now, but My assumption is that my code is far less than optimal. Here is an example of a function that looks at a date and asigns the fiscal calendar and planing calendar time buckets to the date:

Code: 
Public Function fiscalperiod(DateEntry As Date, Optional DisplayOption As Integer) As String
 
Dim MonthEntry As Integer
Dim YearEntry As Integer
Dim DayEntry As Integer
Dim WeekStart As Date
Dim FiscalYearStart As Date
Dim CalYearStart As Date
Dim FiscalWeekNum As Integer
Dim CalWeekNum As Integer
Dim FiscalYear As Integer
Dim CalYear As Integer
Dim FiscalQuarter As Integer
Dim CalQuarter As Integer
Dim FiscalMonth As Integer
Dim CalMonth As Integer
MonthEntry = Month(DateEntry)
YearEntry = Year(DateEntry)
DayEntry = Day(DateEntry)
 
WeekStart = DateSerial(YearEntry, MonthEntry, DayEntry - Weekday(DateEntry, vbSunday) + 1)
If DateSerial(YearEntry, 10, 1) - Weekday(DateSerial(YearEntry, 10, 1), 2) > DateEntry Then
    FiscalYearStart = DateSerial(YearEntry - 1, 10, 1) - Weekday(DateSerial(YearEntry - 1, 10, 1), 2)
    Else
        FiscalYearStart = DateSerial(YearEntry, 10, 1) - Weekday(DateSerial(YearEntry, 10, 1), 2)
    End If
 
FiscalYear = Year(FiscalYearStart) + 1
 
If DateSerial(YearEntry + 1, 1, 1) - Weekday(DateSerial(YearEntry + 1, 1, 1), vbSunday) + 1 > DateEntry Then
    CalYearStart = DateSerial(YearEntry, 1, 1) - Weekday(DateSerial(YearEntry, 1, 1), vbSunday) + 1
    Else
        CalYearStart = DateSerial(YearEntry + 1, 1, 1) - Weekday(DateSerial(YearEntry + 1, 1, 1), vbSunday) + 1
    End If
FiscalWeekNum = ((WeekStart - FiscalYearStart) / 7) + 1
CalWeekNum = ((WeekStart - CalYearStart) / 7) + 1
FiscalQuarter = Application.WorksheetFunction.Min(Application.WorksheetFunction.RoundUp((FiscalWeekNum / 13), 0), 4)
If FiscalWeekNum < 5 Then
    FiscalMonth = 1
    CalMonth = 10
    CalQuarter = 4
    CalYear = Year(FiscalYearStart)
    ElseIf FiscalWeekNum < 9 Then
        FiscalMonth = 2
        CalMonth = 11
        CalQuarter = 4
        CalYear = Year(FiscalYearStart)
    ElseIf FiscalWeekNum < 14 Then
        FiscalMonth = 3
        CalMonth = 12
        CalQuarter = 4
        CalYear = Year(FiscalYearStart)
    ElseIf FiscalWeekNum < 18 Then
        FiscalMonth = 4
        CalMonth = 1
        CalQuarter = 1
        CalYear = Year(FiscalYearStart) + 1
    ElseIf FiscalWeekNum < 22 Then
        FiscalMonth = 5
        CalMonth = 2
        CalQuarter = 1
        CalYear = Year(FiscalYearStart) + 1
    ElseIf FiscalWeekNum < 27 Then
        FiscalMonth = 6
        CalMonth = 3
        CalQuarter = 1
        CalYear = Year(FiscalYearStart) + 1
    ElseIf FiscalWeekNum < 31 Then
        FiscalMonth = 7
        CalMonth = 4
        CalQuarter = 2
        CalYear = Year(FiscalYearStart) + 1
    ElseIf FiscalWeekNum < 35 Then
        FiscalMonth = 8
        CalMonth = 5
        CalQuarter = 2
        CalYear = Year(FiscalYearStart) + 1
    ElseIf FiscalWeekNum < 40 Then
        FiscalMonth = 9
        CalMonth = 6
        CalQuarter = 2
        CalYear = Year(FiscalYearStart) + 1
    ElseIf FiscalWeekNum < 44 Then
        FiscalMonth = 10
        CalMonth = 7
        CalQuarter = 3
        CalYear = Year(FiscalYearStart) + 1
    ElseIf FiscalWeekNum < 48 Then
        FiscalMonth = 11
        CalMonth = 8
        CalQuarter = 3
        CalYear = Year(FiscalYearStart) + 1
    Else
        FiscalMonth = 12
        CalMonth = 9
        CalQuarter = 3
        CalYear = Year(FiscalYearStart) + 1
End If
FiscalWeekNum = Format(FiscalWeekNum, "0#")
CalWeekNum = Format(CalWeekNum, "0#")
FiscalMonth = Format(FiscalMonth, "0#")
CalMonth = Format(CalMonth, "0#")
FiscalQuarter = Format(FiscalQuarter, "0#")
CalQuarter = Format(CalQuarter, "0#")
FiscalYear = Format(FiscalYear, "000#")
CalYear = Format(CalYear, "000#")
 
If DisplayOption = 0 Then
    fiscalperiod = "FY" & FiscalYear & " FM" & Format(FiscalMonth, "0#")
    ElseIf DisplayOption = 1 Then
        fiscalperiod = "FY" & FiscalYear & " FW" & Format(FiscalWeekNum, "0#")
    ElseIf DisplayOption = 2 Then
        fiscalperiod = "CY" & CalYear & " CM" & Format(CalMonth, "0#")
    ElseIf DisplayOption = 3 Then
        fiscalperiod = "CY" & CalYear & " CW" & Format(CalWeekNum, "0#")
    ElseIf DisplayOption = 4 Then
        fiscalperiod = "FY" & FiscalYear & " FQ" & Format(FiscalQuarter, "#") & " FM" & Format(FiscalMonth, "0#") & " FW" & Format(FiscalWeekNum, "0#")
    ElseIf DisplayOption = 5 Then
        fiscalperiod = "CY" & CalYear & " CQ" & Format(CcalQuarter, "#") & " CM" & Format(CalMonth, "0#") & " CW" & Format(CalWeekNum, "0#")
   Else
        fiscalperiod = "Invalid format, use 0-5"
End If
'fiscalperiod = "FY" & FiscalYear & " P" & Format(FiscalMonth, "0#")
 
End Function

The code works fine but I wonder if anyone has a better way of doing some of the things I am doing in here.
 
Back
Top