Solved

Infinite loop

Posted on 2013-12-25
10
385 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 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 13

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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 13

Assisted Solution

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

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 13

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Error with a code discussed on this page 5 15
Clear Filter 8 40
Opening forms with an input box 5 22
ProperCase in Excel (Sheet) 3 13
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

810 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