Solved

Infinite loop

Posted on 2013-12-25
10
379 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
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 74

Expert Comment

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

Author Comment

by:MacroShadow
Comment Utility
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 13

Expert Comment

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

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 200 total points
Comment Utility
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 26

Author Comment

by:MacroShadow
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 80

Accepted Solution

by:
byundt earned 300 total points
Comment Utility
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 13

Expert Comment

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

Expert Comment

by:byundt
Comment Utility
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 13

Expert Comment

by:Alexander Eßer [Alex140181]
Comment Utility
That's what I intended to say, byundt, but he should have gotten to that solution by himself ;-)
0
 
LVL 80

Expert Comment

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now