error handling for network connections
 

Hi,

I have a large spreadsheet that the user 'synchronises' with a db on a
remote network. After 'synchronising' (ie. connecting over the network
to the db, and retrieving the required data) the sheet then runs a
large amount of code to update from the data it has just retrieved.

Occasionally the network connection drops during the sync and the code
stops with an error (obviously). This would not be a problem if the
user could simply re-connect to the network and try again, however as
this halts my code in the middle, the code has not reached the end
line (enable events = true) and so they cannot use any of the cells
on the sheet that are designed for navigation / running code.
Therefore they cannot 'sync' the sheet again as the cell on the sheet
that activates the 'sync' code is disabled.

So I need to learn how to do error handling. However on trying this
(at the start of the sync code 'on error goto 0', and at the end of
the sub 0: application.enableevents = true, exit sub) this does not
work and I have the same problem.

Please can somebody help me out.

Thanks,

error handling for network connections
 

On error Goto 0 is intended to return th esystem back to it's default error
handling. Try something more like this...

Sub Whatever
On error goto Errorhandler:
Application.enableevents = false
'your code here

Errorhandler:
Application.enableevents = true
End sub

If there is an error the code will drop down and execute the Errorhandler
code. If no error is encountered then execution will proceed through all of
the normal code and then through the errorhandler code. This way Events will
always get turned back on...
--
HTH...

Jim Thomlinson


"keri" wrote:

Hi,

I have a large spreadsheet that the user 'synchronises' with a db on a
remote network. After 'synchronising' (ie. connecting over the network
to the db, and retrieving the required data) the sheet then runs a
large amount of code to update from the data it has just retrieved.

Occasionally the network connection drops during the sync and the code
stops with an error (obviously). This would not be a problem if the
user could simply re-connect to the network and try again, however as
this halts my code in the middle, the code has not reached the end
line (enable events = true) and so they cannot use any of the cells
on the sheet that are designed for navigation / running code.
Therefore they cannot 'sync' the sheet again as the cell on the sheet
that activates the 'sync' code is disabled.

So I need to learn how to do error handling. However on trying this
(at the start of the sync code 'on error goto 0', and at the end of
the sub 0: application.enableevents = true, exit sub) this does not
work and I have the same problem.

Please can somebody help me out.

Thanks,

error handling for network connections
 

Oops... the statement
On error goto Errorhandler:
should exclude the colon like this...
On error goto Errorhandler

sorry for any confusion...
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

On error Goto 0 is intended to return th esystem back to it's default error
handling. Try something more like this...

Sub Whatever
On error goto Errorhandler:
Application.enableevents = false
'your code here

Errorhandler:
Application.enableevents = true
End sub

If there is an error the code will drop down and execute the Errorhandler
code. If no error is encountered then execution will proceed through all of
the normal code and then through the errorhandler code. This way Events will
always get turned back on...
--
HTH...

Jim Thomlinson


"keri" wrote:

Hi,

I have a large spreadsheet that the user 'synchronises' with a db on a
remote network. After 'synchronising' (ie. connecting over the network
to the db, and retrieving the required data) the sheet then runs a
large amount of code to update from the data it has just retrieved.

Occasionally the network connection drops during the sync and the code
stops with an error (obviously). This would not be a problem if the
user could simply re-connect to the network and try again, however as
this halts my code in the middle, the code has not reached the end
line (enable events = true) and so they cannot use any of the cells
on the sheet that are designed for navigation / running code.
Therefore they cannot 'sync' the sheet again as the cell on the sheet
that activates the 'sync' code is disabled.

So I need to learn how to do error handling. However on trying this
(at the start of the sync code 'on error goto 0', and at the end of
the sub 0: application.enableevents = true, exit sub) this does not
work and I have the same problem.

Please can somebody help me out.

Thanks,