Create an array of last 12 months including month and year based on an input Month/Year in Excel VBA-CodePudding

Is there a way to create an array of the last 12 months (Month/Year in "mmm-yy" format) based on Month/Year in "mmm-yy" from a variable?

CodePudding user response：

Use Dateadd

Sub Demo()

    Dim s As String, ar, n As Integer
    s = Format(Date, "mmm-yy") ' default
    s = InputBox("mmm-yy", "Input mmm-yy", s)
    ar = PriorYear(s)
    For n = 1 To 12: Debug.Print n, ar(n): Next

End Sub

Function PriorYear(s) As Variant

   Dim ar(1 To 12) As String, dt As Date, n As Integer

   dt = DateValue("01-" & s)
   For n = 12 To 1 Step -1
       dt = DateAdd("m", -1, dt)
       ar(n) = Format(dt, "mmm-yy")
   Next
   PriorYear = ar

End Function

CodePudding user response：

Please, try the more compact version, too:

    Dim arr, d As Date: d = Date 'you can choose any date you need
    arr = Application.Transpose(Evaluate("TEXT(DATE(" & Year(d) - 1 & ",row(" & month(d) & ":" & month(d)   11 & "),1),""mmm-yy"")"))
    Debug.Print Join(arr, "|")

I usually post an answer if OP proves that he tried something by his own and it is good to learn that this aspect is mandatory in our community. Even explain in words what you tried. I made an exception only for the challenging sake, since the question has already been answered...

CodePudding user response：

This late post demonstrates how to get the last 12 month dates via Evaluate, based on a symbolic formula syntax like

{Text(Date(StartYear,Column(StartColumn:EndColumn),1),"mmm-yyyy")}

Extra feature: The function accepts an optional argument MonthsCount changing the default value of 12 last months to any other positive value.

Public Function LastNMonths(dt As Date, Optional MonthsCount As Long = 12)
'Purpose: get 1-dim array of last 12 month dates formatted "mmm-yy")
'a) get start date
    Dim StartDate As Date: StartDate = DateAdd("m", -MonthsCount   1, dt)
    Dim yrs As Long:       yrs = Year(dt) - Year(StartDate)
'b) get column numbers representing months .. e.g. "J:U" or "A:L"
    Dim cols As String
    cols = Split(Cells(, Month(StartDate)).Address, "$")(1)
    cols = cols & ":" & Split(Cells(, Month(dt)   Abs(yrs * 12)).Address, "$")(1)
'c) evaluate dates .. e.g. Text(Date(2020,Column(J:U),1),"mmm-yyyy")
    LastNMonths = Evaluate("Text(Date(" & Year(StartDate) & _
        ",Column(" & cols & "),1),""mmm-yyyy"")")  
End Function

Example call

You might want to display the resulting "flat" array based on today's date input (ending currently in Sep-2021) within the VB Editor's immediate window by a joined list

Debug.Print Join(LastNMonths(Date), "|")

returning e.g.

Oct-2020|Nov-2020|Dec-2020|Jan-2021|Feb-2021|Mar-2021|Apr-2021|May-2021|Jun-2021|Jul-2021|Aug-2021|Sep-2021