excel - How to go through a finite set of numbers in VBA?

Question

In my workbook the worksheets equate to months; I was aiming to make a function that applied to the current month and the previous two (the last three months). But I run into trouble thinking of a way to get a 12 and 11 returned to me when January is the first month, or 01 and 12 for February, for example.

Function StockMedio(CodigoArtigo As String)

Dim mês As String
Dim ano As String
Dim LRow As Integer
Dim nome_folha As String

    mês = Format(Now(), "MM")
    ano = Format(Now(), "YY")
    LRow = Range("A3").End(xlDown).Row
    nome_folha = "Stock final " & mês & ano
   
        
End Function

So far I have this, I didn't type the code yet because it's not really relevant to the question. Any help? (Sorry for the code not being fully in english, it just makes it easier for me to understand it)

question from:https://stackoverflow.com/questions/65867140/how-to-go-through-a-finite-set-of-numbers-in-vba

Answer 1

But I run into trouble thinking of a way to get a 12 and 11 returned to me when January is the first month, or 01 and 12 for February, for example.

You can use DateAdd to get what you want. Here is an example

Option Explicit

Sub Sample()
    Dim dt As Date
    Dim PrevMonth As String
    Dim MonthPrevToPrevMonth As String
    
    dt = Now
    
    PrevMonth = Format(DateAdd("M", -1, dt), "M")
    MonthPrevToPrevMonth = Format(DateAdd("M", -2, dt), "M")
    
    Debug.Print PrevMonth
    Debug.Print MonthPrevToPrevMonth
End Sub

Here is a test with Jan and Feb dates.