How to alert me if problems in Excel Macro?

Hello, I am calling an Excel macro from within VB .net, and I was hoping that if there were any problems in the macro, that I could throw an error message from VB .net, and then close my program quietly.

I know that excel will throw the error if there is a problem in the macro which closes the excel instance, but then my program will crash because it needs that excel instance. I am trying to make my program not crash if there is a problem in the excel macro. Here is my code:

The macro is stored in the workbook. The macro is called "Main"


Try
obook.Application.Run("Main")
Catch ex As Exception
MsgBox("Error:" & ex.Message())
End Try

Yeah, a tricky thing... The problem is that Excel is running out of process here and this simply Raises an Unhandled Run-Time Error within Excel... but your .Net Application will not be notified of an Exception being Thrown. :(

It's no better in VB6.0 in this case, it's really just an in-process vs. out-of-process issue.

The only reasonable thing that I can propose is that you:

(1) Make your Main() routine into a Function instead of a Sub.

(2) Use error handling within Main to return True/False, returning 'True' if no error. Something like:Function Main() As Boolean
On Error Goto RTE

' Your Code Goes Here
' Your Code Goes Here
' Your Code Goes Here

Main = True
Exit Function
RTE:
Main = False
End Function(3) Then use code like the following:Dim bResult as Boolean
bResult = obook.Application.Run("Main")

If bResult = False Then
' Then you have a RTE.
' Exit or do whatever you want...
End If This should work nicely. Hopefully you won't have too many Subs() that you would have to convert like this...

Thanks for the help, but my macro is actually defined within my work book.

Is this what the code is intended for? Would I be allow to return a boolean variable from within my excel process, and then use that in my vb .net app?

Thanks mike, you seem to be helping me with all my questions. I appreciate the effort a lot.

Thanks for the help, but my macro is actually defined within my work book.

Is this what the code is intended for? Would I be allow to return a boolean variable from within my excel process, and then use that in my vb .net app?[/vb] Yes, make the changes to your Sub Main() macro into a Boolean Function, as I described above, and you will be able to recieve the True or False value in your .Net App using:bResult = obook.Application.Run("Main")

[quote]Thanks mike, you seem to be helping me with all my questions. I appreciate the effort a lot. No problem at all... I just hope that it works! :)

How would you organize this? I have:


Function main() As Boolean
On Error GoTo RTE

Run (Module1.page1())
Run (Module2.page2())
Run (Module3.page3())
Run (Module4.page4())
Run (Module6.page5())

main = True
Exit Function

RTE:
main = False

End Function


this is my main function, and I run 5 separate macros. I did this for organizational purposes. I have a worksheet with 5 seperate pages, and each page needs a different macro. Would this work? or do i have to go to each of the 5 macros and return a boolean if it works or not?

Yes, that basically looks ok... But I'm curious why you are using Run here? You don't need to use Run when calling within VBA, you only need to use xlApp.Run() when calling a Macro written in VBA, but called from VB.Net (or from VB 6.0).

I could be mistaken (for I don't 100% understand the functionality here), but I think you should use instead:
Function Main() As Boolean
On Error GoTo RTE

Call Module1.page1
Call Module2.page2
Call Module3.page3
Call Module4.page4
Call Module6.page5

Main = True
Exit Function

RTE:
Main = False
End Function This make sense?

Mike

thanks mike, yeah i had to separate my macro because excel told me an error about having it too big. I guess it could only be 64 k. so I had to separete the macro in many different pages to avoid this problem. =(

Thanks again for all the help

LOL, this is getting to be like Ping-Pong! :p ;)

Ok, good, yes, it looks like I understood you then... Give it a try! :cool:

a fast pace match of ping-pong indeed =)

:)