total days between two dates by Month (VBA)

[cooolboy]

Hi

I would like to calculate the total of days by month wise between two dates through VBA. I have seen some example at the below link but that's on the worksheet.

Distributing Days Over Intervals

Does anyone suggest VBA function to do the same ? (could return that in array format would be useful so I can use that in other calculations). This is required for cash flow calculations through VBA.

Start Date - 01/Jan/2015
End Date - 05/Mar/2015

VBA Result

Count Month Month Days
1 1/Jan/2015 31
2 1/Feb/2015 28
3 1/Mar/2015 5


Much Appreciated
Note- If the supplied dates are wrong, then VBA has to assume default dates

 

[cooolboy]

I managed to get response from other community site (courtesy from Dirk Reichel) and is pasted below others use.

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Function GetTable(startDate As Double, endDate As Double) As Variant
Dim Table() As Variant, i As Long, y As Byte: i = 1: y = Day(startDate)
If endDate <= startDate Then GetTable = "error": Exit Function
ReDim Table(2, 0)
Table(0, 0) = "Count"
Table(1, 0) = "Month"
Table(2, 0) = "Month Days"
For startDate = startDate To endDate - 1
If Month(startDate + 1) <> Month(startDate) Then
ReDim Preserve Table(2, UBound(Table, 2) + 1)
Table(0, UBound(Table, 2)) = UBound(Table, 2)
If UBound(Table, 2) = 1 Then
Table(1, UBound(Table, 2)) = y & Format(startDate, "/mmm/yyyy")
Else
Table(1, UBound(Table, 2)) = Format(startDate, "1/mmm/yyyy")
End If
Table(2, UBound(Table, 2)) = i
i = 1
Else
i = i + 1
End If
Next
ReDim Preserve Table(2, UBound(Table, 2) + 1)
Table(0, UBound(Table, 2)) = UBound(Table, 2)
If UBound(Table, 2) = 1 Then
Table(1, UBound(Table, 2)) = y & Format(startDate, "/mmm/yyyy")
Else
Table(1, UBound(Table, 2)) = Format(startDate, "1/mmm/yyyy")
End If
Table(2, UBound(Table, 2)) = i
GetTable = Table
End Function</code>