Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Infinite loop

Posted on 2013-12-25
10
Medium Priority
?
391 Views
Last Modified: 2013-12-25
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

Open in new window

0
Comment
Question by:MacroShadow
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 39739372
My guess would be that strTmpDate does not contain a valid date. Have you tried stepping through the code?
0
 
LVL 27

Author Comment

by:MacroShadow
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

by:Alexander Eßer [Alex140181]
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 14

Assisted Solution

by:Alexander Eßer [Alex140181]
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 27

Author Comment

by:MacroShadow
ID: 39739384
Doesn't this line
strTmpDate = Format(intDay - 1 & "/" & DatePart("M", Date + 30 * i) & "/" & DatePart("YYYY", Date + 30 * i), "DD/MM/YYYY")

Open in new window

change the strTmpDate variable? That's what I intended it to do.
0
 
LVL 81

Accepted Solution

by:
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

Open in new window

0
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39739391
What does the "Debug.Print strTmpDate" show?! Is it always the same value?!
0
 
LVL 81

Expert Comment

by:byundt
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

by:Alexander Eßer [Alex140181]
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

by:byundt
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

Open in new window

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
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…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question