CODEWITHSUNDEEP
excelvba
JiggidyJoe
JiggidyJoe

Reputation: 519

Excel vba looping through months and years

I am an excel vba newbie and I am stuck on a problem. Can you please point me in the right direction.

The date structure in the worksheet is YYYYMMDD. For the purpose of this exercise, the start date is 20060321 and the end date is 20170512. I have 5 subroutines (6 if you count the Exit Subroutine). SubA, SubB, SubC, SubD and SubE. The program starts at SubA then goes down to SubE. When it reaches SubE, I want it to increment the month counter. When it reaches month 12, I want it to reset to 01 (back to Jan) then increment the Year Counter. Then I want to call SubB (that's correct - SubB). Then it needs to loop until the end date is reached.

Due to the nature of the data varYYYY, varMM and varDD are Strings. So I've tried converting them to Long, then incrementing the Long, then converting it back to a string.

~~~~~~~

I finally got this to work. Big thank you to Variatus for sharing his insights into how calling a subroutine from another subroutine worked. This was the key to getting it working. It wasn't the loop structure that was not working. It was my improper use of calling a subroutine that caused the unwanted loops.

I have pasted the simplified and updated version of the code here.

Option Explicit

Public varDD As String, varMM As String, varYYYY As String, varYYYYMM As String
Public varDayEnd As String, varMonthEnd As String, varYearEnd As String, varPrint As String
Public varA As String, varB As String, varC As String, varD As String, varE As String

Public varMMLong As Long, varYYYYLong As Long, i As Integer

Public varYearMonthEnd As String
Public Sub SubMain()

varDD = "21"
varMM = "03"
varYYYY = "2006"
varYearEnd = "2008"
varMonthEnd = "05"
varDayEnd = "12"
varA = "A"
varB = "B"
varC = "C"
varPrint = ""

varYearMonthEnd = varYearEnd & varMonthEnd
Debug.Print varYearMonthEnd

Do
    varPrint = varYYYY & varMM & varDD
    varMMLong = CLng(varMM)
    varMMLong = varMMLong + 1
    varMM = Format(varMMLong, "00")
    varYYYYMM = varYYYY & varMM
    SubA
    SubB
    SubC
    If varMM = 13 Then
        varDD = "01" ' reset varDD to 01
        varMM = "01" ' reset varMM to Jan
        varYYYYLong = CLng(varYYYY)
        varYYYYLong = varYYYYLong + 1 ' increment by one year
        varYYYY = Format(varYYYYLong, "0000")
    End If
Loop Until varYYYYMM = varYearMonthEnd


End Sub

Public Sub SubA()

'Insert code block here
Debug.Print varA

End Sub


Public Sub SubB()

'Insert code block here
Debug.Print varB

End Sub

Public Sub SubC()

'Insert code block here
Debug.Print varC

End Sub

Upvotes: 1

Views: 4485

Answers (2)

Dy.Lee
Dy.Lee

Reputation: 7567

Maybe

Sub test()
    Dim myY As Integer, myM As Integer, myD As Integer
    Dim eY As Integer, eM As Integer, eD As Integer
    Dim newDate As Date, sDate As Date, eDate As Date
    Dim prn As String, i As Integer

    myY = 2016: myM = 3: myD = 21
    eY = 2017: eM = 5: eD = 12
    vstr = Array("A", "B", "C", "D", "E")
    sDate = DateSerial(myY, myM, myD)
    eDate = DateSerial(eY, eM, 1)
    Do
        For i = 0 To UBound(vstr)
            prn = prn & vstr(i)
            Debug.Print prn
        Next i
        sDate = DateAdd("m", 1, sDate)
    Loop While sDate < eDate

End Sub

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149297

Here is a much simpler way to convert your string values to date and then increment the date by 1 day and finally convert it back to string.

Dim CurDt As Date, nextDt As Date

varDD = "21": varMM = "03": varYYYY = "2006"

CurDt = DateSerial(Val(varYYYY), Val(varMM), Val(varDD))

nextDt = DateAdd("d", 1, CurDt)

Debug.Print Format(nextDt, "YYYYMMDD")

Upvotes: 2

Related Questions