"On error" within While loop failing
 

OK, so I know using an "On error" within a While loop isn't terribly
kosher but I'm interfacing Excel with a realtime data system and the
existence/timing/availability of the key file is unpredictable. So I
have used the following VBA code:

' Work off copy to avoid file collisions
On Error GoTo againAgain:

bTryAgain = True
While bTryAgain = True
FileCopy sFullSrcFileName, sFullFileCopyName
bTryAgain = False
GoTo notAgain
againAgain:
Call addLogMsg("openOrders file conflict: " & Now())
notAgain:
Wend

The issue is that the 'On error' works fine the first time the file
isn't available. The log entry is made and the While loop restarts.
What's fascinating, and deeply annoying, is that the second time
through the loop execution halts at the "FileCopy". Simply halts with
the line highlighted. No warning pop-up, no loop, no thing else --
though an Error 70 has been triggered. The intent of the code is to
keep looping through the While loop until the FileCopy can succeed.
I'm not at all clear why the 'On error' logic stops after just one
error . Any ideas out there?

= M =

"On error" within While loop failing
 

I'd start by reading Chip Pearson's notes on error handling:
http://www.cpearson.com/Excel/ErrorHandling.htm

Especially the stuff that starts with:
"Enabled And Active Error Handlers"
and continues for the remainder of that page.

On 07/07/2010 11:33, Marchand wrote:
OK, so I know using an "On error" within a While loop isn't terribly
kosher but I'm interfacing Excel with a realtime data system and the
existence/timing/availability of the key file is unpredictable. So I
have used the following VBA code:

' Work off copy to avoid file collisions
On Error GoTo againAgain:

bTryAgain = True
While bTryAgain = True
FileCopy sFullSrcFileName, sFullFileCopyName
bTryAgain = False
GoTo notAgain
againAgain:
Call addLogMsg("openOrders file conflict: "& Now())
notAgain:
Wend

The issue is that the 'On error' works fine the first time the file
isn't available. The log entry is made and the While loop restarts.
What's fascinating, and deeply annoying, is that the second time
through the loop execution halts at the "FileCopy". Simply halts with
the line highlighted. No warning pop-up, no loop, no thing else --
though an Error 70 has been triggered. The intent of the code is to
keep looping through the While loop until the FileCopy can succeed.
I'm not at all clear why the 'On error' logic stops after just one
error . Any ideas out there?

= M =

--
Dave Peterson

"On error" within While loop failing
 

On Jul 7, 9:55*am, Dave Peterson wrote:
I'd start by reading Chip Pearson's notes on error handling:http://www.cpearson.com/Excel/ErrorHandling.htm

Especially the stuff that starts with:
"Enabled And Active Error Handlers"
and continues for the remainder of that page.

On 07/07/2010 11:33, Marchand wrote:



OK, so I know using an "On error" *within a While loop isn't terribly
kosher but I'm interfacing Excel with a realtime data system and the
existence/timing/availability of the key file is unpredictable. So I
have used the following VBA code:

* * *' Work off copy to avoid file collisions
* * *On Error GoTo againAgain:

* * *bTryAgain = True
* * *While bTryAgain = True
* * * * *FileCopy sFullSrcFileName, sFullFileCopyName
* * * * *bTryAgain = False
* * * * *GoTo notAgain
againAgain:
* * * * *Call addLogMsg("openOrders file conflict: "& *Now())
notAgain:
* * *Wend

The issue is that the 'On error' works fine the first time the file
isn't available. The log entry is made and the While loop restarts.
What's fascinating, and deeply annoying, is that the second time
through the loop execution halts at the "FileCopy". Simply halts with
the line highlighted. No warning pop-up, no loop, no thing else --
though an Error 70 has been triggered. The intent of the code is to
keep looping through the While loop until the FileCopy can succeed.
I'm not at all clear why the 'On error' logic stops after just one
error . Any ideas out there?

= M =

--
Dave Peterson

Dave,

Thank you for the link. It's just what I needed to update both my
brain and the VBA code.

= M =