loop not looping

[Charles Kenyon]

I have had better success distributing global templates locally. Updates are downloaded as part of the user login to the network.
(My experience with this is not recent, though.)

[ChrisGreaves]

...a possibility, for you to single-step through the code remotely?...

That approach is on the 'to do' list. MS Teams allows me to both see and control a remote PC. Seeing Is OK but controlling is a bit like stirring concrete.

Hi Ken.
If I understand this, you would be allowed linked-computers, but YOU would be reduced to using a voice-telephone to say "Now please click on the little yellow thingy just below the line that reads "lngCopied = lngCopyImages(strLatest, strFolder, strMonth)", and hope that your correspondent wasn't too shaky at the finger tips.

A further thought comes to mind: It seems that, at this time, the "loop not looping" has not brought the corporation to its knees.
Why not release the next updated version with a piece of code that, unless disabled by the loop, displays a graphic on the screen?

Untitled.png

You could have fun writing a bit of graphics code that showed a disk drive changing from Blue to Crimson, from the bottom up, and stop it with a

Code: Select all

MsgBox.Sub test()
    lng = MsgBox("", vbSystemModal + vbAbortRetryIgnore)
End Sub

Use your imagination, man! "Waiting for Windows Update to start" and a little circle running rings around itself, ... Knock yerrself out!

(My Rant appears to have cleared up)
Cheers
Chris

[stuck]

Yesterday I was allowed to meet on-site with the problem laptop (and its user). With it in front of me I was able to identify that the bit of the macro that was failing involved a copy & paste. It was weird.

If I put a break point one the line above the 'bad' bit and then use F8 to step through then next three lines, it worked as expected. I could then allow the code to run to the end, whereon it would loop and stop again at the break point. Once again using F8 allowed it to get over the next three lines and then away it run, loop and stop again at the break point. Rinse and repeat. BUT as soon as I removed the breakpoint the code would run through the 'bad' bit once and then stop executing. No error message, just stop.

Having got this far, the user then sort of hinted that they had fiddled with the cut, copy, paste settings In File | Options | Advanced. However, even given this new clue I could not find any differences between what those settings showed on the problem laptop compared to the same settings on my 'good' laptop.

At this point the IT people swapped the problem machine for another one. Once I'd tweaked the necessary settings on that machine, so it would find the global template that holds the code, everything worked as expected.

So. in summary, the user did something that broke the set up on his laptop but for the life of me I could not figure out exactly what they had done. The problem was resolved by the nuclear option of swapping the problem machine for a different one, one that had not been poked at by a user. This means the problem is fixed, at least until the user does something to break it again.

Ken

[HansV]

If this or a similar problem ever pops up again, you might try inserting a line

DoEvents

in one or more places inside the loop. That might solve timing problems.

[ChrisGreaves]

Oops! Hans beat me by 6 minutes!

If I put a break point one the line above the 'bad' bit and then use F8 to step through then next three lines, it worked as expected. I could then allow the code to run to the end, whereon it would loop and stop again at the break point. Once again using F8 allowed it to get over the next three lines and then away it ran, loop and stop again at the break point. Rinse and repeat. BUT as soon as I removed the breakpoint the code would run through the 'bad' bit once and then stop executing. No error message, just stop.

DoEvents any good for you?

I use it like North Americans use ketchup.
Cheers
Chris

[StuartR]

Yes, sprinkling DoEvents throughout the code often makes it taste much better

[stuck]

Thanks, I'll look into DoEvents but I still think this was a user-forced problem as it was just this one machine that was choking on this code.

Ken

[HansV]

I don't dispute that, but there is a chance that DoEvents would have made the code run on the user's original laptop.

[ChrisGreaves]

Thanks, I'll look into DoEvents but I still think this was a user-forced problem as it was just this one machine that was choking on this code.

I think that the Good news out of this is that your code works, and the Evil User is now on record as having tampered with the system to the point where a corporate-sponsored/funded program failed to operate correctly.
You come up looking good and the user has been put on a mild form of notice.

ENJOY YOUR WELL EARNED CHRISTMAS BREAK.
But only from paid work, not from Eileen's Lounge!

Cheers
Chris

[Charles Kenyon]

@HansV
Thank you for the idea to use DoEvents.

I've been working for more than a month of-and-on on a problem (mostly off) that this solved.
It is in an AutoExec code that kept throwing an error during loading but would not throw the same error when run after Word is open. I was trying to resize task panes but no document was open when it was trying to run the code.

[Leif]

Just as a BTW, I had a similar-ish problem running code in an Excel application (getting data via RS485 and saving to disk) which I crudely resolved by adding a one second delay into a loop. The only reason I could think of was that the user had upgraded to a faster laptop with SSD, and the 'natural' delay caused by writing to a HDD had disappeared...

[Doc.AElstein]

If people are into trying a few DoEvents, then it might be worth noting that a double pair of DoEvents, like
DoEvents:DoEvents
has been known to mysteriously solve problems.
DoEvents are very cheap, so if you are into sprinkling a few around, it does not do to economise.
If you find a liberal sprinkling of DoEvents, double at that, solves your problem, then you can always remove them one by one until you find the one or two or four that did the job

Alan

[HansV]

DoEvents are very cheap, so if you are into sprinkling a few around, it does not do to economise.

They do slow down a loop considerably, though. This is what you want if you suspect timing problems, of course. But don't use more than you need.

Code: Select all

Sub Test1()
    Dim t As Single
    Dim i As Long
    t = Timer
    For i = 1 To 1000000
    Next i
    t = Timer - t
    Debug.Print t
End Sub

Time: 0.008 seconds

Code: Select all

Sub Test2()
    Dim t As Single
    Dim i As Long
    t = Timer
    For i = 1 To 1000000
        DoEvents
    Next i
    t = Timer - t
    Debug.Print t
End Sub

Time: 36.8 seconds

[StuartR]

Is it possible that in the code without DoEvents VBA is optimising the loop away, but with the DoEvents it has to actually loop?
Could you add debug.print i in both loops and see what the timings are

[StuartR]

I tested this myself with either one or two DoEvents in the loop. It looks like each DoEvent takes about .000011 seconds

Code: Select all

Sub Test2()
    Dim t As Single
    Dim i As Long
    t = Timer
    For i = 1 To 100000
        DoEvents
        DoEvents
    Next i
    t = Timer - t
    Debug.Print t
End Sub

11.41406

Code: Select all

Sub Test1()
    Dim t As Single
    Dim i As Long
    t = Timer
    For i = 1 To 100000
        DoEvents
    Next i
    t = Timer - t
    Debug.Print t
End Sub

5.703125

[HansV]

So it doesn't make a lot of difference if your loop is executed hundreds or thousands of times, but you'll notice it if the loop is executed millions of times.

[ChrisGreaves]

Is it possible that in the code without DoEvents VBA is optimising the loop away, but with the DoEvents it has to actually loop?

Ooooh!
I love this line of thought!

Cheers
Chris

[StuartR]

So it doesn't make a lot of difference if your loop is executed hundreds or thousands of times, but you'll notice it if the loop is executed millions of times.

That's what it looks like to me