Option Explicit
Enum input_columns
in_LBound = 0
in_Region
in_Sector
in_Opportunity
in_Onhire_Date
in_Offhire_Date
in_Hire_Duration
in_Value_USD
in_UBound
End Enum
Enum fiscal_calendar_columns
fc_LBound = 0
fc_month_key
fc_financial_year
fc_financial_period
fc_first_date
fc_last_date
fc_days_in_month
fc_UBound
End Enum
Enum output_columns
out_LBound = 0
out_Region
out_Sector
out_Opportunity
out_Onhire_Date
out_Offhire_Date
out_Hire_Duration
out_Value_USD
out_Year
out_Month
out_Hire_Days_in_Month
out_Distributed_Value
out_First_Date_of_Month
out_Last_Date_of_Month
out_UBound
End Enum
Function opportunity_analysis(vInput As Variant, _
vFiscalYear As Variant) As Variant
Dim i As Long
Dim j As Long
Dim lvdim As Long
Dim k As Long
Dim m As Long
Dim oFC_Start As Object
Dim oFC_End As Object
With Application.WorksheetFunction
'Initialize
lvdim = 1
ReDim v(1 To out_UBound - 1, 1 To lvdim) As Variant
On Error GoTo ErrHdl
Set oFC_Start = CreateObject("Scripting.Dictionary")
Set oFC_End = CreateObject("Scripting.Dictionary")
i = 2
Do While vFiscalYear(i, fc_financial_year) <> ""
oFC_Start(vFiscalYear(i, fc_financial_year) & "|" & _
vFiscalYear(i, fc_financial_period)) = _
vFiscalYear(i, fc_first_date)
oFC_End(vFiscalYear(i, fc_financial_year) & "|" & _
vFiscalYear(i, fc_financial_period)) = _
vFiscalYear(i, fc_last_date)
i = i + 1
Loop
i = 2
v(out_Region, 1) = "Region"
v(out_Sector, 1) = "Sector"
v(out_Opportunity, 1) = "Opportunity"
v(out_Onhire_Date, 1) = "Onhire Date"
v(out_Offhire_Date, 1) = "Offhire Date"
v(out_Hire_Duration, 1) = "Hire Duration)"
v(out_Value_USD, 1) = "Value USD"
v(out_Year, 1) = "Year"
v(out_Month, 1) = "Month"
v(out_Hire_Days_in_Month, 1) = "Hire Days in Month"
v(out_Distributed_Value, 1) = "Distributed Value"
v(out_First_Date_of_Month, 1) = "First Date of Month"
v(out_Last_Date_of_Month, 1) = "Last Date of Month"
j = 2
Do While vInput(i, in_Value_USD) <> ""
k = 12 * (Year(vInput(i, in_Offhire_Date)) - Year(vInput(i, in_Onhire_Date))) + _
Month(vInput(i, in_Offhire_Date)) - Month(vInput(i, in_Onhire_Date)) + 1
For m = 1 To k
v(out_Region, j) = vInput(i, in_Region)
v(out_Sector, j) = vInput(i, in_Sector)
v(out_Opportunity, j) = vInput(i, in_Opportunity)
v(out_Onhire_Date, j) = vInput(i, in_Onhire_Date)
v(out_Offhire_Date, j) = vInput(i, in_Offhire_Date)
v(out_Hire_Duration, j) = vInput(i, in_Hire_Duration)
v(out_Value_USD, j) = vInput(i, in_Value_USD)
v(out_Year, j) = Year(DateSerial(Year(vInput(i, in_Onhire_Date)), _
Month(vInput(i, in_Onhire_Date)) + m - 1, Day(vInput(i, in_Onhire_Date))))
v(out_Month, j) = Month(DateSerial(Year(vInput(i, in_Onhire_Date)), _
Month(vInput(i, in_Onhire_Date)) + m - 1, Day(vInput(i, in_Onhire_Date))))
v(out_Hire_Days_in_Month, j) = .Min(v(out_Offhire_Date, j), oFC_End(v(out_Year, j) & "|" & v(out_Month, j))) - _
.Max(v(out_Onhire_Date, j), oFC_Start(v(out_Year, j) & "|" & v(out_Month, j))) + 1
v(out_Distributed_Value, j) = Round(v(out_Hire_Days_in_Month, j) * v(out_Value_USD, j) / _
v(out_Hire_Duration, j), 2)
v(out_First_Date_of_Month, j) = oFC_Start(v(out_Year, j) & "|" & v(out_Month, j))
v(out_Last_Date_of_Month, j) = oFC_End(v(out_Year, j) & "|" & v(out_Month, j))
j = j + 1
Next m
i = i + 1
Loop
ReDim Preserve v(1 To out_UBound - 1, 1 To j - 1) As Variant
opportunity_analysis = .Transpose(v)
End With
Exit Function
ErrHdl:
If Err.Number = 9 Then
If j > lvdim Then
'We need to increase last dimension
lvdim = 10 * lvdim
ReDim Preserve v(1 To out_UBound - 1, 1 To lvdim) As Variant
Resume 'Back to statement which caused error
End If
End If
'Other error - terminate
On Error GoTo 0
Resume
End Function
Opportunity Analysis.xlsx
