On Error GoTo ???

[rudo]

Hello,

I try to use following error-routine to make a macro check if a file on (peer-to-peer) network is available. If the file on the network is not available, an inputbox should appear with options retry, use local copy, quit program.

sub startprogr()
Start:
On Error GoTo NoNetwork
Workbooks.Open Filename:="\\ComputerB\FileToUse.xls"
On Error GoTo 0

'no error encountered
'file on network is available

Exit sub

NoNetwork:
On Error GoTo 0
title ="Network not available"
s = "1. Retry" & vbcrlf & _
"2. Use local files" & vbcrlf & _
"3. Quit"
choice = Inputbox(s, title, 1)

If choice = 1 then goto Start
If choice = 2 then goto LocalSolution
If choice = 3 then application.quit

'....

My problem: when network is not available and using the option Retry the inputbox doesn't appear again. (VB message error 1004 appears instead.)

Does anyone know the correct error instructions?

Thank in advance for your kind help.

Rudo

 

[rmikesmith]

rudo,

Use

If Choice = 1 Then Resume Start

you need to use "Resume" to return from an error handler.


HTH
M. Smith

 

[rudo]

Dear M.Smith,

Thank you very much for your reply. I presume, you will not be surprised to hear it works fine now.

I have difficulties to find sufficient information about the Error subject. (I have several VBA books, but I am still looking for one with clear and explicit exemples.) Would you mind to give me a few more details?

Do I have to use Resume also in my second option, at the point where my LocalSolution will join the main program?

Could you give a small exemple to illustrate the difference between On Error GoTo 0 and Err.Clear ?

Thank you very much for your help.

Greetings,

Rudo

 

[rmikesmith]

Rudo,

Yes, you should always use Resume to return from an explicit error handler. This resets the error condition to no error. In fact, doing so calls the Clear method. Here is the text of the help topic for Err.Clear:

Clears all property settings of the Err object.

Syntax

object.Clear

The object is always the Err object.

Remarks

Use Clear to explicitly clear the Err object after an error has been handled, for example, when you use deferred error handling with On Error Resume Next. The Clear method is called automatically whenever any of the following statements is executed:

Any type of Resume statement

Exit Sub, Exit Function, Exit Property

Any On Error statement

Note The On Error Resume Next construct may be preferable to On Error GoTo when handling errors generated during access to other objects. Checking Err after each interaction with an object removes ambiguity about which object was accessed by the code. You can be sure which object placed the error code in Err.Number, as well as which object originally generated the error (the object specified in Err.Source).

On Error GoTo 0 turns off error handling, meaning that all errors are intercepted by VBA with the result that if an error occurs, your code halts execution and the end user sees the error dialog with choices to retry, cancel or debug. Generally, you don't want this to happen so you should include explicit error handling.

I recommend using the Object Browser in the VBE. For each object, as well as its methods & properties, you can right click and select help for that particular item. In the current case, I looked up the Clear method of the ErrObject object. Post back if you need additional info.

Regards,
Mike