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

user_14979638 0
Viewed 71

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)

vba excel
2 Answers

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.

enter image description here

user_11405790

Last Three Months (MMYY)

Option Explicit

Sub StockMedio() 'CodigoArtigo As String
    
    On Error GoTo clearError
    
    Dim agora As Date: agora = Now
    Dim mes As Long: mes = Month(agora)
    Dim ano As Long: ano = Year(agora)
    
    Dim matriz() As String: matriz = UltimosTresMeses(mes, ano)
    If IsEmpty(matriz) Then GoTo ProcExit
    
    Dim sufixo As String
    Dim nome_folha As String
    Dim n As Long
    
    For n = 0 To 2
        ' Do something for each month, e.g.:
        sufixo = matriz(n)
        nome_folha = "Stock final " & sufixo
        Debug.Print nome_folha & "(" & sufixo & ")"
    Next n

ProcExit:
    Exit Sub
clearError:
    Resume ProcExit

End Sub


Function UltimosTresMeses( _
    mes As Long, _
    ano As Long) _
As Variant
    On Error GoTo clearError
    If mes < 1 Or mes > 12 Then GoTo ProcExit
    Dim este As String: este = Right(ano, 2)
    Dim matriz(0 To 2) As String
    If mes > 2 Then
        matriz(0) = Format(mes - 2, "0#") & este
        matriz(1) = Format(mes - 1, "0#") & este
    Else
        Dim passado As String: passado = Right(ano - 1, 2)
        If mes = 1 Then
            matriz(0) = "11" & passado
            matriz(1) = "12" & passado
        Else
            matriz(0) = "12" & passado
            matriz(1) = "01" & este
        End If
    End If
    matriz(2) = Format(mes, "0#") & este
    UltimosTresMeses = matriz
ProcExit:
    Exit Function
clearError:
    Resume ProcExit
End Function
user_98140690
Related