I'm new to Error Handling.

I have a macro that is on a loop controlling two workbooks. On most days
everything works fine and there are no issues. Once in a while a worksheet
disappears for no reason. I solved that problem by adding code that closes
and re-opens the offending worksheet on each interation. Now once in even
less of a while, it hangs on re-opening that workbook. I guess it never fully
closed before it was told to re-open, but I'm not sure.

So, I'd like to insert an "on error" code that closes both workbooks and
then fires the marco that starts my loop over again, "Loop" is the name of
that macro. Can anyone help me?

Thanks,

You could probably cure the problem by putting a delay in the existing code
that allows enough time for the offending workbook to do its thing. Here is
a function that you can adjust by changing the value of s. It is measured in
seconds and will respond to tenths of seconds as it is now set at 5/10 or
half a second. To call the function, simply type HalfSecDly at the
appropriate point in your code.

Public Function HalfSecDly()
s = Timer + 0.5
Do While Timer < s
DoEvents
Loop
End Function

"sb1920alk" wrote:

I'm new to Error Handling.

I have a macro that is on a loop controlling two workbooks. On most days
everything works fine and there are no issues. Once in a while a worksheet
disappears for no reason. I solved that problem by adding code that closes
and re-opens the offending worksheet on each interation. Now once in even
less of a while, it hangs on re-opening that workbook. I guess it never fully
closed before it was told to re-open, but I'm not sure.

So, I'd like to insert an "on error" code that closes both workbooks and
then fires the marco that starts my loop over again, "Loop" is the name of
that macro. Can anyone help me?

Thanks,

Well, that's not going to solve the underlying problem. This could work if
the time delay is sufficient, but it only takes one hiccup to stop the loop
until I notice it and fix it manually. All I need to do to fix it is close
both files (well...someone one of them is already closed, so I would first
need the error handler to check if each is open and then close it) and then
reopen both files and call my "Loop" macro. This should work every time.

"JLGWhiz" wrote:

You could probably cure the problem by putting a delay in the existing code
that allows enough time for the offending workbook to do its thing. Here is
a function that you can adjust by changing the value of s. It is measured in
seconds and will respond to tenths of seconds as it is now set at 5/10 or
half a second. To call the function, simply type HalfSecDly at the
appropriate point in your code.

Public Function HalfSecDly()
s = Timer + 0.5
Do While Timer < s
DoEvents
Loop
End Function

"sb1920alk" wrote:

I'm new to Error Handling.

I have a macro that is on a loop controlling two workbooks. On most days
everything works fine and there are no issues. Once in a while a worksheet
disappears for no reason. I solved that problem by adding code that closes
and re-opens the offending worksheet on each interation. Now once in even
less of a while, it hangs on re-opening that workbook. I guess it never fully
closed before it was told to re-open, but I'm not sure.

So, I'd like to insert an "on error" code that closes both workbooks and
then fires the marco that starts my loop over again, "Loop" is the name of
that macro. Can anyone help me?

Thanks,