2

I have a list of dates, derived from a start and end date, for eg:

01/10/2011 - through to.. - 01/01/2012

In VBA, how can I retrieve an array of months from between these two dates, so the output would be similar to:

Oct-2011
Nov-2011
Dec-2011
Jan-2012

Is there an easily solution for this?

Community
  • 1
  • 1
cwiggo
  • 2,541
  • 9
  • 44
  • 87

1 Answers1

5

To do this entirely in VBA without actions on the worksheet:

You can create a collection with the unique months and years by looping through the dates, extracting the month and year and adding them to the collection and setting the key as the value of the month and year.

If another date has the same month and year which already exists in the collection, the collection won't duplicate it as the key with the month and year will have already been set and will produce an error. By disabling error handing (On Error Resume Next) the code will skip over the add thus not duplicating it in the collection.

Technique In Action (With comments)

Sub GetUniqueMonths()

Dim uniqueMonths As Collection
Set uniqueMonths = New Collection

Dim dateRange As Range
Set dateRange = Range("A1:A10") 'Change this to your range of dates

On Error Resume Next

Dim currentRange As Range
For Each currentRange In dateRange.Cells

    If currentRange.Value <> "" Then

        Dim tempDate As Date: tempDate = CDate(currentRange.Text) 'Convert the text to a Date
        Dim parsedDateString As String: parsedDateString = Format(tempDate, "MMM-yyyy") 'Format the date into the required format (Oct-2011 etc)
        uniqueMonths.Add Item:=parsedDateString, Key:=parsedDateString 'Add the parsed date into the collection
        'An error will be thrown if the record already exists as the key has been set to the value (e.g. Oct-2011)
        'With On Error Resume next set, it will ignore the error and continue to run without adding the record therefore no duplication of dates

    End If

Next currentRange

On Error GoTo 0 'Enable default error trapping

'Loop through the collection and view the unique months and years
Dim uniqueMonth As Variant
For Each uniqueMonth In uniqueMonths

    Debug.Print uniqueMonth

Next uniqueMonth

End Sub
Francis Dean
  • 2,386
  • 2
  • 22
  • 29
  • 1
    + 1 Nicely done. However I would recommend moving the OERN just before the `.Add` This will ensure that no other error messages are suppressed (in case there are any) :) – Siddharth Rout Sep 16 '13 at 11:49
  • Good point, I added the OERN before the loop as the date conversion could also sprout errors should the data not parse. – Francis Dean Sep 16 '13 at 11:58
  • great answer, reputation++ – cwiggo Sep 16 '13 at 14:13
  • to further my question, is it possible to grab the address of the row number or address of the outputs? – cwiggo Sep 16 '13 at 14:42
  • Yes, you could create a user defined type (structure) containing the string (Oct-2011) and a range (either in a string or Range object). A collection will add whatever value you throw at it as long as the key is valid. – Francis Dean Sep 16 '13 at 14:51
  • Is there any chance you could provide an example structure? – cwiggo Sep 20 '13 at 01:39
  • I cant seem to integrate this within my code, all I want is to generate the data: Oct-2011 A2, Sep-2011 A32 etc... can you see what I need to do? – cwiggo Sep 20 '13 at 14:43