disable EXCEL macro runtime-error

[tingxing]

Hello,

I call EXCEL's VBA macro from VB code.
Sometimes macro might run into runtime errors, which leads users to
VBA environment.

Can I disable the runtime errors?

I searched but can not locate a similar session about this.
Thanks in advance.

Ting

 

[JLGWhiz]

You can trap the error and have the user report it:

Sub Whatever()
Dim 'Stuff
'assign variables
On Error GoTo ErrHandler:
'your code
ErrHandler:
If Err.Number <> 0 then
ErrMsg = Error(Err.Number
MsgBox "Error " & ErrMsg & "has occured. Record " _
& "this number for debugging">
Exit Sub
End If
End Sub

 

[JLGWhiz]

Had typos.

Sub Whatever()
Dim 'Stuff
'assign variables
On Error GoTo ErrHandler:
'your code
ErrHandler:
If Err.Number <> 0 then
ErrMsg = Error(Err.Number)
MsgBox "Error " & ErrMsg & "has occured. Record " _
& "this number for debugging"
Exit Sub
End If
End Sub

 

[tingxing]

Thanks for the answer.

But sometimes the error is inevitable because of input data.
I can manage to change the macros, but considering the backwards
compatibility issues, error sure will come up again.

No way to skip them just as I can do by using "on error resume next"
inside the macro itself?


Ting

 

[JLGWhiz]

Personally, I would not want to disable them or skip them. If the code is
throwing errors, I would want to know what the error is and fix the code.
Errors caused by input can be fixed by anticipating the error and building
the code to accomodate the bad input, and guide the user to providing the
correct input. Most user initiated errors can be avoided by using If...Then
statements with criteria that produces a message box if the user makes the
wrong input. The message box then instructs them with the correct type of
input. If there are limited choices for the user, then you could use ListBox
to restrict them to only those choices that will not produce errors.

There are many ways around the problem of errors opening the VBE, but
ignoring the fact that the code allows that error is not something I would
recommend.