Looping through arrays in VBA

Question

I supplied the following code as an answer to this question on SO, but I was wondering if there was a better way to write out all those array loops (i.e. perhaps using a Collection/Dictionary?) It seems clunky/cumbersome as-is.

Function ContainedInMonth(OriginalStartDate As String, _
 OriginalEndDate As String) As Boolean
 Dim MonthSet As Variant
 Dim AryCounter As Integer, ISOOffset As Integer
 Dim StartYear As Integer, EndYear As Integer
 Dim StartWeek As Integer, EndWeek As Integer
 Dim StartDay As Integer, EndDay As Integer
 Dim FormattedStartDate As Date, FormattedEndDate As Date
 ' This section may (will) vary, depending on your data. 
 ' I'm assuming "YYYY-WW" is passed...
 ' Also, error/formatting checking for these values is needed
 ' and wil differ depending on that format.
 StartYear = Val(Left(OriginalStartDate, 4))
 StartWeek = Val(Right(OriginalStartDate, 2))
 EndYear = Val(Left(OriginalEndDate, 4))
 EndWeek = Val(Right(OriginalEndDate, 2))
 If StartYear <> EndYear Or StartWeek > EndWeek Then
 ContainedInMonth = False
 ElseIf StartWeek = EndWeek Then
 ContainedInMonth = True
 Else
 ' Using the calculation from wikipedia. Honestly, I'm not sure that
 ' I understand this bit, but it seemed to work for my test cases.
 ISOOffset = Weekday(CDate("1/4/" & StartYear), vbMonday) + 3 
 StartDay = (StartWeek * 7) - ISOOffset ' Adding 0 for start of week
 EndDay = (EndWeek * 7) + 6 - ISOOffset ' Adding 6 for end of week
 ' Set the starting day for each month, depending on leap year.
 If StartYear Mod 4 = 0 Then
 MonthSet = Array(0, 31, 60, 91, 121, 152, 182, 213, 244, 274, 305, 335)
 Else
 MonthSet = Array(0, 31, 59, 90, 120, 151, 181, 212, 243, 273, 304, 334)
 End If
 FormattedStartDate = 0:FormattedEndDate = 0
 For AryCounter = 11 To 0 Step -1
 If StartDay > MonthSet(AryCounter) And FormattedStartDate = 0 Then
 ' Using MM/DD/YYYY format - this may be different for you
 FormattedStartDate = CDate(AryCounter + 1 & _
 "/" & StartDay - MonthSet(AryCounter) & "/" & StartYear) 
 End If
 If EndDay > MonthSet(AryCounter) And FormattedEndDate = 0 Then
 FormattedEndDate = CDate(AryCounter + 1 & _
 "/" & EndDay - MonthSet(AryCounter) & "/" & EndYear)
 End If
 Next AryCounter
 ContainedInMonth = IIf(Month(FormattedStartDate) = Month(FormattedEndDate), True, False)
 End If
End Function

I had considered something like:

If StartYear Mod 4 = 0 Then
 TempArray = LeapSet
Else
 TempArray = NonLeapSet
End If
'Now do loops

Answer 1

This is the final result of what was done with this code, though the code pertaining to the root question remained unchanged.

Function ContainsWhatMonths(OriginalStartDate As String, _
 OriginalEndDate As String) As Variant
 Dim MonthSet As Variant
 Dim AryCounter As Integer, ISOOffset As Integer
 Dim StartYear As Integer, EndYear As Integer
 Dim StartWeek As Integer, EndWeek As Integer
 Dim StartDay As Integer, EndDay As Integer
 Dim StartWeekStartDate As Date, StartWeekEndDate As Date
 Dim EndWeekStartDate As Date, EndWeekEndDate As Date
 Dim FormattedStartDate As Date, FormattedEndDate As Date
 Dim TotalMonths As Integer, OutputMonths As String
 StartYear = Val(Right(OriginalStartDate, 4))
 StartWeek = Val(Left(OriginalStartDate, 2))
 EndYear = Val(Right(OriginalEndDate, 4))
 EndWeek = Val(Left(OriginalEndDate, 2))
 If StartYear <= EndYear Then
 ' Using the calculation from wikipedia. Honestly, I'm not sure that
 ' I understand this bit, but it seemed to work for my test cases.
 ISOOffset = Weekday(CDate("1/4/" & StartYear), vbMonday) + 3
 StartDay = (StartWeek * 7) - ISOOffset ' Adding 0 for start of week
 EndDay = (EndWeek * 7) + 6 - ISOOffset ' Adding 6 for end of week
 ' Set the starting day for each month, depending on leap year.
 If StartYear Mod 4 = 0 Then
 MonthSet = Array(0, 31, 60, 91, 121, 152, 182, 213, 244, 274, 305, 335)
 Else
 MonthSet = Array(0, 31, 59, 90, 120, 151, 181, 212, 243, 273, 304, 334)
 End If
 For AryCounter = 11 To 0 Step -1
 If StartDay > MonthSet(AryCounter) Then
 ' Using MM/DD/YYYY format - this may be different for you
 StartWeekStartDate = CDate(AryCounter + 1 & _
 "/" & StartDay - MonthSet(AryCounter) & "/" & StartYear)
 StartWeekEndDate = StartWeekStartDate + 6
 If Month(StartWeekStartDate) <> Month(StartWeekEndDate) Then
 FormattedStartDate = DateSerial(StartYear, Month(StartWeekEndDate), 1)
 Else
 FormattedStartDate = DateSerial(StartYear, Month(StartWeekEndDate) + 1, 1)
 End If
 Exit For
 End If
 Next AryCounter
 If EndYear Mod 4 = 0 Then
 MonthSet = Array(0, 31, 60, 91, 121, 152, 182, 213, 244, 274, 305, 335)
 Else
 MonthSet = Array(0, 31, 59, 90, 120, 151, 181, 212, 243, 273, 304, 334)
 End If
 For AryCounter = 11 To 0 Step -1
 If EndDay > MonthSet(AryCounter) Then
 EndWeekStartDate = CDate(AryCounter + 1 & _
 "/" & EndDay - MonthSet(AryCounter) & "/" & EndYear)
 EndWeekEndDate = EndWeekStartDate + 6
 If Month(EndWeekStartDate) <> Month(EndWeekEndDate) Then
 FormattedEndDate = CDate(Month(EndWeekEndDate) & "/1/" & EndYear) - 1
 Else
 FormattedEndDate = CDate(Month(EndWeekEndDate) & "/1/" & EndYear)
 End If
 Exit For
 End If
 Next AryCounter
 ' Switch the commenting on these two lines to return the string
 'ContainsWhatMonths = Array()
 ContainsWhatMonths = vbNullString
 TotalMonths = (Year(FormattedEndDate) - Year(FormattedStartDate)) * 12 + _
 Month(FormattedEndDate) - Month(FormattedStartDate)
 If TotalMonths >= 0 Then
 For AryCounter = 0 To TotalMonths
 OutputMonths = OutputMonths & "," & _
 Format(DateAdd("m", AryCounter, FormattedStartDate), "MM/YYYY")
 Next
 OutputMonths = Right(OutputMonths, Len(OutputMonths) - 1)
 ' Switch the commenting on these two lines to return the string
 'ContainsWhatMonths = Split(OutputMonths, ",")
 ContainsWhatMonths = OutputMonths
 End If
 End If
End Function