# Code Optimization

#### thebomer

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.