How to handle exceptions in macros?

[#1]

Hi, I searched in many places ho to handle exceptions in macros, but none has served me. For example the "On Error GoTo ..." sintax

sub CopyToXL

On Error GoTo err
set tmpExcel = CreateObject("EXCEL.APPLICATION")
set tmpWorkBook = tmpExcel.Workbooks.Add
tmpExcel.Visible = True
...
err:
msgbox("An error ocurred while executing the script")

end sub

when I try to save this script QlikView said me that the comprobation of macro failed. And if I coment "On Error GoTo err" I don't have any problem.

Thank you for you time.. 😉 Can you helpme please? maybe there is another way to handle exceptions.

[biester]

Try to write your macro in JScript, there you really have the possibility to handle exceptions with try and catch. Perhaps that helps.

Rgds,
Joachim

[#3]

Thank you biester for your idea, but I have a big procedure in VB Script and it's ready.. :S

[#4]

I can't seem to use anything with On Error GoTo. On Error Resume Next works fine though. I would probably add an if then after problematic sections of the code to test for errors and then run a separate Sub in that case.

Try this:

sub CopyToXL
 On Error Resume Next
 set tmpExcel = CreateObject("EXCEL.APPLICATION")
 set tmpWorkBook = tmpExcel.Workbooks.Add
 If err > 0 Then
 ' Do something to handle the error
 End If
 tmpExcel.Visible = True
 ...
end sub

I know I've used the GoTo in VBA, but I did see a reference to VBScript having limited error handling options. It's also possible that QlikView's implementation does not allow for the GoTo to be used (even though GoTo gets the blue highlight in the macro editing box).

EDIT: You can use err.Clear to clear out an error after you handle it. You can also use err.Raise x (where x is the error number) to raise specific errors to test how you handle them.

[rwunderlich]

NMiller has it correct. There are only two On Error statements supported in VBS:

On Error Resume Next
On Error Goto 0

When your macro is entered, error handling is disabled (Goto 0). If you want to handle errors, issue Resume Next and explicitly test for errors as in NMiller's example. Issue "On Error Goto 0" to return to default error handling.

-Rob