Solved

# Infinite loop

Posted on 2013-12-25
Medium Priority
395 Views
Can someone please point out why this code gets stuck of an infinite loop.

``````Demo(31)

Sub Demo(intDay As Integer)
Dim strTmpDate As String
Dim i As Integer

For i = 1 To 6
strTmpDate = Format(intDay & "/" & DatePart("M", Date + 30 * i) & "/" & DatePart("YYYY", Date + 30 * i), "DD/MM/YYYY")
Do While Not IsDate(strTmpDate)
strTmpDate = Format(intDay - 1 & "/" & DatePart("M", Date + 30 * i) & "/" & DatePart("YYYY", Date + 30 * i), "DD/MM/YYYY")
Debug.Print strTmpDate
Loop
Next
End Sub
``````
0
• 4
• 3
• 2
• +1

LVL 75

Expert Comment

ID: 39739372
My guess would be that strTmpDate does not contain a valid date. Have you tried stepping through the code?
0

LVL 28

Author Comment

ID: 39739376
Yes I have, the problem seems to be that although the code is supposed to subtract 1 day for every loop, it only does it once.
0

LVL 14

Expert Comment

ID: 39739378
The variable strTmpDate is never changed in the inner while loop. Either you should alter the value of this variable for the checks and/or add an exit condition for the inner loop.
0

LVL 14

Assisted Solution

Alexander Eßer [Alex140181] earned 800 total points
ID: 39739381
Yes I have, the problem seems to be that although the code is supposed to subtract 1 day for every loop, it only does it once.
Then you should add a new variable which holds the calculated day(s) to subtract and put something like "varDay = varDay - 1" in your inner loop...
0

LVL 28

Author Comment

ID: 39739384
Doesn't this line
``````strTmpDate = Format(intDay - 1 & "/" & DatePart("M", Date + 30 * i) & "/" & DatePart("YYYY", Date + 30 * i), "DD/MM/YYYY")
``````
change the strTmpDate variable? That's what I intended it to do.
0

LVL 81

Accepted Solution

byundt earned 1200 total points
ID: 39739390
Alex140181 is correct. You are subtracting 1 from a constant value. The following is working:
``````Sub Demo(intDay As Integer)
Dim strTmpDate As String
Dim i As Integer, j As Integer

For i = 1 To 6
strTmpDate = Format(intDay & "/" & DatePart("M", Date + 30 * i) & "/" & DatePart("YYYY", Date + 30 * i), "DD/MM/YYYY")
j = 1
Do While Not IsDate(strTmpDate)
strTmpDate = Format(intDay - j & "/" & DatePart("M", Date + 30 * i) & "/" & DatePart("YYYY", Date + 30 * i), "DD/MM/YYYY")
Debug.Print strTmpDate
j = j + 1
Loop
Next
End Sub
``````
0

LVL 14

Expert Comment

ID: 39739391
What does the "Debug.Print strTmpDate" show?! Is it always the same value?!
0

LVL 81

Expert Comment

ID: 39739393
I predict that you will get unexpected results, however, when the current day of the month is 12 or less. This is because VBA assumes date strings are mm/dd/yyyy unless the mm part is bigger than 12.
0

LVL 14

Expert Comment

ID: 39739394
That's what I intended to say, byundt, but he should have gotten to that solution by himself ;-)
0

LVL 81

Expert Comment

ID: 39739400
If you want to return the last day of the month for 6 consecutive months, then consider:
``````Sub LastOfMonths()
Dim dat As Date
Dim i As Integer
dat = Date
For i = 1 To 6
dat = DateSerial(Year(dat), Month(dat) + 2, 0)
Debug.Print Format(dat, "DD/MM/YYYY")
Next
End Sub
``````
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

As a person who answers a lot of questions, I often see code that could be simplified, made easier to read, and perhaps most importantly made easier to maintain if the code was modified to use the Select Case statement. This article explains how to…
Here is why.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
###### Suggested Courses
Course of the Month5 days, 17 hours left to enroll