# Looping through arrays in VBA

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


If StartYear Mod 4 = 0 Then
TempArray = LeapSet
Else
TempArray = NonLeapSet
End If

'Now do loops


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 & "," & _