Range to 2d Object. Excel does not close.
Range to 2d Object. Excel does not close.
Range to 2d Object. Excel does not close.
Range to 2d Object. Excel does not close.
Range to 2d Object. Excel does not close.
Range to 2d Object. Excel does not close. Range to 2d Object. Excel does not close. Range to 2d Object. Excel does not close. Range to 2d Object. Excel does not close. Range to 2d Object. Excel does not close. Range to 2d Object. Excel does not close. Range to 2d Object. Excel does not close. Range to 2d Object. Excel does not close.
Range to 2d Object. Excel does not close. Range to 2d Object. Excel does not close.
Range to 2d Object. Excel does not close.
Go Back  Xtreme Visual Basic Talk > > > Range to 2d Object. Excel does not close.


Reply
 
Thread Tools Display Modes
  #1  
Old 05-15-2007, 09:27 AM
JFitz15 JFitz15 is offline
Regular
 
Join Date: Jul 2006
Posts: 74
Default Range to 2d Object. Excel does not close.


Hi!

I have a problem closing excel. The problem occurs specifically because I transfer range objects into a 2D array as below:

Code:
myRange = myWorkSheet.Range(anotherRange.Cells(1, 1), anotherRange.Cells(10, 1))
my2dObjectArray = CType(myRange.Value, Object(,))
If I don't do the second step (transferring myRange into a 2D array - the reason I do transfer is because makes the program run much faster of course) then there is no problem.

If I do perform the second step then when I dispose of the userform the following message box is displayed by Excel. It says:

Quote:
EXCEL.EXE Application error
The instruction at "0x300341b6" referenced memory at "0x650eeed8". The memory could not be "read".
Click OK to terminate the progam
What this means is that excel is left in memory and does not close properly (is still open in the task manager).

The only thing I could think of doing was to set my2dObjectArray equal to nothing but that did not have any effect (i.e. excel still does not close properly).

So I guess this is a sort of 'you know it or you don't' kind of question. Anyone any ideas?

Thanks,

JF
Reply With Quote
  #2  
Old 05-16-2007, 06:01 AM
JFitz15 JFitz15 is offline
Regular
 
Join Date: Jul 2006
Posts: 74
Default

Just an extra note on that... (although this will probably only add to the confusion!)...

the problem doesn't happen when I'm just dealing with excel, it only seems to start happening when I'm manipulating access as well...

Reply With Quote
  #3  
Old 05-16-2007, 06:13 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hey JF,

I think that that the line that "appears" to be causing the problem is not actually causing the problem. At least not directly. My guess is that without the second line which assigns the array returned from 'myRange.Value', the compiler is simply skipping the step of creating the range object in the first place. (It won't bother calling the command that returns the object if the compiler knows that it won't be used later.) And it is an open refernce to the Range object that is preventing your Excel application instance from releasing.

Have a look at the Automating Office Programs with VB.NET tutorial. It goes into some detail about dealing with hanging MS Office applications when using Automation. I think it should get you going.

Also, keep in mind that it is more difficult to release when running your application hosted within the Visual Studio IDE. This is because the debugger will hold onto references that would normally be garbage collected. This is done so that you can step through your code and see the values even if the variable will never get used again. Therefore, you might need to test your code as a release build running as a stand-alone EXE to make sure that it is not the IDE host environment that is holding onto the object references.

Hope this helps, come back with any questions...
Mike

[Edit: I don't know about the Access part of the equation here, but the situation will be parallel. Apply the concepts discussed within the Automating Office Programs with VB.NET tutorial to both your Excel application instance and Access and you should be ok. If not, show us more of your code and hopefully we can spot the problem. -- Mike]
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #4  
Old 05-16-2007, 09:22 AM
JFitz15 JFitz15 is offline
Regular
 
Join Date: Jul 2006
Posts: 74
Default

Hi Mike,

Actually i've been on this forum a few times before (not sure if you remember!) so actually I've read through most of the basics at this stage (and a lot of stuff on this site! v.good ).

But it looks like you might be right on both accounts. Firstly, I'm not so sure it is that line that's causing the problem... but then I'm not really sure what is causing the problem now. Again, strangely the problem only occurs when I'm using access and excel in the same program.

And secondly, I tried running the program as a stand alone exe and that did the trick (well, I've only tried once... but it did the trick then!). So, I guess it has something to do with the VB IDE holding onto the object...

Anything I should do about this? Or is it ok as long as the exe is working fine...

Thanks for your help, JF
Reply With Quote
  #5  
Old 05-16-2007, 05:19 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Quote:
Originally Posted by JFitz
Actually i've been on this forum a few times before (not sure if you remember!)
Yep, I do remember.

Quote:
looks like you might be right on both accounts. Firstly, I'm not so sure it is that line that's causing the problem... but then I'm not really sure what is causing the problem now. Again, strangely the problem only occurs when I'm using access and excel in the same program.
I'm not sure why this would be, but you need to take the same care with the Access objects as you do with the Excel objects.

Quote:
And secondly, I tried running the program as a stand alone exe and that did the trick (well, I've only tried once... but it did the trick then!). So, I guess it has something to do with the VB IDE holding onto the object...
Yes, that does sound like the issue then. You could call Marshal.FinalReleaseComObject() and/or explicitly set all local variables = Nothing when done with them, but this can be very tedious and error prone (is you miss even one, it will hang).

You could simply not wory about it and just test your program as an EXE occasionally to make sure that it releases, although multiple hangs even while developing can be annoying... So I think the best thing might be to encapsulate the loal variables into a separate "DoWork()" method. The main sub (1) opens Excel and/or Access, storing both in global variables, (2) calls DoWork(), and then (3) calls GC.Collect(), GC.WaitForPendingFinalizers, xlApp.Quit() and lastly Marshal.FinalReleaseComObject(xlApp).

This should allow your application to release both when hosted in the IDE and when run as a stand-alone EXE.

Let us know how it goes!
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by Mike Rosenblum; 05-16-2007 at 05:30 PM.
Reply With Quote
  #6  
Old 05-18-2007, 11:25 AM
JFitz15 JFitz15 is offline
Regular
 
Join Date: Jul 2006
Posts: 74
Default

Humm,

Well currently I use a button to open excel / access storing them in global object variables and then call my main program (something like below). I don't actually use a main() sub which is probably bad programming but I'm not sure of the necessity for it so I don't use it. Of course... maybe your ideas above show why a main sub might be necessary!

Code:
Private Sub btnRate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRate.Click

      xlApp = New Excel.Application

      AC.OpenFile("This function opens my file") 
      
      mySub()       'Contains local variables

'Perform some cleanup?

   End Sub

But ok, I think I understand what you are saying there. Dowork() contains all my code and this is called from the main function like mySub is above (or maybe I'll just call it from my button function as usual? Would that have the same result?) And then do a cleanup. BTW, if an excel object goes out of scope, it will still need to be cleaned up right, like a range object?

Only thing is, as I may have a number of buttons on my form, that would require that I have several main() funtions as each button does something a bit different (I don't show all code above) and cleanup will be different etc. but I assume it's bad programming to do all those steps you mention in a button sub.

Ok, I hope I haven't confused you too much there!

Have a good weekend! JF
Reply With Quote
  #7  
Old 05-18-2007, 01:16 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hey JF,

Ok, it looks like you are already using the setup that I was describing. As a simplified example, I was thinking something like the following:

Code:
Friend xlApp As Excel.Application Private Sub btnDoSomething_Click() xlApp = New Excel.Application DoSomething() ' Cleanup: GC.Collect() GC.WaitForPendingFinalizers() GC.Collect() GC.WaitForPendingFinalizers() xlApp.Quit() Marshal.FinalReleaseComObject(xlApp) End Sub
In the above, the call to DoSomething() would access the project-wide 'xlApp As Excel' instance and do what it needs to do: create or reference Workbook, Worksheet and/or Range objects as necessary, but using only local variables to do so.

Any other public variables that you might need (if you do need them) should be released explicitly within your cleanup routine. But, ideally, the Excel.Application could be the only one, and should always be closed and released last.

You also describe multiple buttons doing different things. I'm not quite clear on what you are describing here, but you can either have each button (a) Open Excel, (b) "DoWork()", and then (c) Close Excel each time, or perhaps you should have dedicated 'OpenExcel' and 'CloseExcel' buttons and then the other controls do various tasks that are required. I'm thinking something like this:

Code:
Friend xlApp As Excel.Application Private Sub btnOpenExcel_Click() If xlApp Is Nothing Then btnOpenExcel.Enabled = False xlApp = New Excel.Application btnCloseExcel.Enabled = True End If End Sub Private Sub btnCloseExcel_Click() If xlApp Is Nothing Then Exit Sub btnCloseExcel.Enabled = False GC.Collect() GC.WaitForPendingFinalizers() GC.Collect() GC.WaitForPendingFinalizers() xlApp.Quit() Marshal.FinalReleaseComObject(xlApp) xlApp = Nothing; btnOpenExcel.Enabled = True End Sub Private Sub btnDoSomething_Click() DoSomething() End Sub Private Sub btnDoSomethingElse_Click() DoSomethingElse() End Sub

Something like this might make sense for you, but it's just a suggestion. Keeping your (a) Open, (b) Do Work, (c) Close sequence in tact for each button might make sense if you don't want Excel remaining open during the interim.

Hope this helps JF, and I hope you have a good weekend too...

,
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by Mike Rosenblum; 05-18-2007 at 01:26 PM.
Reply With Quote
  #8  
Old 05-25-2007, 09:35 AM
JFitz15 JFitz15 is offline
Regular
 
Join Date: Jul 2006
Posts: 74
Default

Thanks Mike,

Well the application may perform different tasks and so each button may be required to do a similar but ultimately different task e.g. one may open excel, the other may open excel and access etc.

But I take on board what you say about using one global xlapp and having everything else as a local variable and then using the cleanup in the button instead.

In fact, this may simplify my code quite a bit. Normally if I have an exception in my code I need to put code like

Code:
Try
      'Try something
Catch ex As Exception
     GoTo cleanup
End Try
So I have heard people compaining about using 'GoTo' and saying that it should never be used (for some reason?) but that is the way I set up my program (I had a cleanup routine at the end and if an error occured then the application would skip straight to the cleanup routine - presumably I saw someone else doing that and that's why I did it that way...). But, using your method, no cleanup routine should be required (except that of xlapp i the button) because as the local excel object variables all go out of scope they are dereferenced anyway. That's to say, all I would need when an error occurs is;

Code:
Try
      'Try something
Catch ex As Exception
     Exit sub
End Try
All the excel object variables except xlapp go out of scope and are dereferenced. So if that's the case then that would make things a lot 'cleaner' (sorry!)

btw, any particular reason for using the 'Friend' modifier? I would normally just declare xlapp as a normal class variable.


Thanks,

JF

Last edited by JFitz15; 05-25-2007 at 09:41 AM.
Reply With Quote
  #9  
Old 05-25-2007, 01:50 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hey JF,

Quote:
Originally Posted by JFitz15
Normally if I have an exception in my code I need to put code like

Code:
Try
      'Try something
Catch ex As Exception
     GoTo cleanup
End Try
So I have heard people compaining about using 'GoTo' and saying that it should never be used (for some reason?) but that is the way I set up my program (I had a cleanup routine at the end and if an error occured then the application would skip straight to the cleanup routine - presumably I saw someone else doing that and that's why I did it that way...)
Goto's should be avoided, yes. Your usage there is pretty good though, so I wouldn't worry too much about it. But cleaner would be to use a 'Finally' block instead:
Code:
Sub MySub() Dim xlApp As Excel.Application Try xlApp = New Excel.Application ' Etc... ' Etc... ' Etc... Catch ex As Exception ' Error caught! MessageBox.Show(ex.ToString()) Finally If xlApp IsNot Nothing ' Your Cleanup Code Goes Here ' Your Cleanup Code Goes Here ' Your Cleanup Code Goes Here End If End Try
So the idea is you wrap the entire section that is subject to possible problems (pretty much your whole routine in this case) and use the 'Finally' block as your "ultimate goto" location. Note that you should declare all your variables before the 'Try' section so that you can reference and release them within the 'Finally' section.

Quote:
Originally Posted by JFitz15
But, using your method, no cleanup routine should be required (except that of xlapp i the button) because as the local excel object variables all go out of scope they are dereferenced anyway. That's to say, all I would need when an error occurs is;

Code:
Try
      'Try something
Catch ex As Exception
     Exit sub
End Try
All the excel object variables except xlapp go out of scope and are dereferenced. So if that's the case then that would make things a lot 'cleaner' (sorry!)
Yes this is basically right. But, again, I think you should be thinking in terms of using a 'Finally' statement instead. This way you do not have to suppress the error reporting and yet still make sure that your cleanup code fires. We'll see an example of this as part of the answer to your next question...

Quote:
Originally Posted by JFitz15
Well the application may perform different tasks and so each button may be required to do a similar but ultimately different task e.g. one may open excel, the other may open excel and access etc.

But I take on board what you say about using one global xlapp and having everything else as a local variable and then using the cleanup in the button instead.
Ok, I think I get the picture. You don't have to use a global variable - arguably you shouldn't. You could instead create your methods such that they accept an Excel.Application variable to use, and the caller deals with the creation and cleanup. What I have in mind is something like this:
Code:
Private Sub btnDoSomething_Click() Dim xlApp As Excel.Application = New Excel.Application Try DoSomething(xlApp) Finally CloseExcel(xlApp) End Try End Sub Private Sub btnDoSomethingElse_Click() Dim xlApp As Excel.Application = New Excel.Application Try DoSomethingElse(xlApp) Finally CloseExcel(xlApp) End Try End Sub Sub DoSomething(ByVal xlApp As Excel.Application) ' Just an example, do whatever you need to in here: MessageBox.Show("xlApp.Version = " & xlApp.Version) End Sub Sub DoSomethingElse(ByVal xlApp As Excel.Application) ' Just an example, do whatever you need to in here: MessageBox.Show("xlApp.Workbooks.Count = " & xlApp.Workbooks.Count.ToString()) End Sub Private Sub CloseExcel(ByVal xlApp As Excel.Appliation) If xlApp Is Nothing Then Exit Sub GC.Collect() GC.WaitForPendingFinalizers() GC.Collect() GC.WaitForPendingFinalizers() xlApp.Quit() Marshal.FinalReleaseComObject(xlApp) xlApp = Nothing End Sub
See if this makes sense for you?

Quote:
Originally Posted by JFitz15
Btw, any particular reason for using the 'Friend' modifier? I would normally just declare xlApp as a normal class variable.
Ah, no, sorry there isn't any good reason to use 'Friend' here. It seems that your program is a windows application (an executable) and not a class library (a DLL) and so the 'Friend' modifier is redundant. My bad. If you wish to have access to a global field restricted to the class's members only, then use the 'Private' modifier. In order to give it scope to the entire project, use the 'Friend' modifier, and in the case of a class library, if you wish to expose a member to external callers then use 'Public' scope. But in this case 'Private' probably makes the most sense, unless there is some reason why it would need to be reached from outside the class.

-- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #10  
Old 05-28-2007, 07:59 AM
JFitz15 JFitz15 is offline
Regular
 
Join Date: Jul 2006
Posts: 74
Default

Hi Mike,

My sub could be 2000 or more lines long so obviously I need to use lots of try and catch statements to catch all the different errors that can occur and have specific handling.

So if I was to do it the way you suggest then that would mean having lots of try and catch statements within the overall try catch finally statement. But that would still mean that while the overall try catch finally statement could catch random errors I haven’t accounted for, the individual try catch statements would still have to perform their own cleanup, since they won’t go into the overall ‘finally’ statement. Since my cleanup could be 20 lines long that would make my code much longer and messier.

I probably explained that badly, but lets just say that I would need a lot of try and catch statements in the overall try and catch statement and it seems to me that in a lot of cases that would render the overall try catch finally statement pointless and would require me to put my cleanup routine in all the individual try and catch statements within the overall try and catch statement (unless I continue to use a ‘goto’ for those).

Apart from that I think I get everything else that you say!

JF
Reply With Quote
  #11  
Old 05-28-2007, 10:31 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hey JF,

Quote:
Originally Posted by JFitz15
My sub could be 2000 or more lines long so obviously I need to use lots of try and catch statements to catch all the different errors that can occur and have specific handling.
Yep, and you should. Actually, wait... your 'sub' is 2000 lines long? I hope you meant class or module here. If your sub actually is 2000 lines long, I think you should consider breaking it down into smaller routines. But don't go breaking this up "just for the heck of it" or "Mike said so", because this could take a lot of time. But going forward, think about routines that have smaller chunks. My routines usually have between one and twelve lines of code. That's it.

Quote:
So if I was to do it the way you suggest then that would mean having lots of try and catch statements within the overall try catch finally statement. But that would still mean that while the overall try catch finally statement could catch random errors I haven’t accounted for...
Yes, but using an outer Try-Finally block to catch the unaccounted errors is exactly what it's good for. If all else fails, the outer Try-Finally block will catch the exception and your routine will perform the necessary cleanup before closing.

Quote:
the individual try catch statements would still have to perform their own cleanup, since they won’t go into the overall ‘finally’ statement. Since my cleanup could be 20 lines long that would make my code much longer and messier.
Ok, maybe yes, maybe no. If you have different operations that required different cleanup aspects, then yes, you need a separate Try-Finally block for each of these. However, if they are all basically the same, something like "on error, shut down Excel cleanly and report the error to the user" then you could use just one outer Try-Finally block and therefore all your internal code can be much, much cleaner. Either way, however, I think that an outer Try-Finally block remains a strong consideration.

Quote:
... lets just say that I would need a lot of try and catch statements in the overall try and catch statement and it seems to me that in a lot of cases that would render the overall try catch finally statement pointless and would require me to put my cleanup routine in all the individual try and catch statements within the overall try and catch statement (unless I continue to use a ‘goto’ for those).
No goto's please! I get your point. But I believe that if you are thinking of using a 'goto' then you should probably consider an outer Try-Finally block instead.

Just keep this all in mind. Only you know what's best for your own code...

,
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #12  
Old 05-29-2007, 09:05 AM
JFitz15 JFitz15 is offline
Regular
 
Join Date: Jul 2006
Posts: 74
Default

Hi!

Well I can't really see the point in breaking up the sub... it would actually make things much more difficult for me instead of making it easier as the code would now be separated into many different subs. I suppose if I could see the advantage of breaking it up it might make sense to do it but currently it would just separate my code and make it more difficult to follow what is going on.

Equally, not using a GoTo would require me to have about 20 to 30 lines of code in each try catch block I use (within the overall try catch finally - which does make good sense!) and since I could have, say, 30 try catch statements with their own specific error handling, that would require adding potentially 900 extra lines of code just to add cleanup to each statement. (And that is only because I have dll class which I call to do my cleanup... if I put that code back into my sub that would mean my cleanup could really be 100 lines long easily which would translate to adding 3000 lines of code doubling the size of my sub routine!)

So neither situation there makes any sense. Although as I say having an over all try catch finally definately does make sense, and having the cleanup of xlapp in the button makes sense to me also, but the only useful way to arrange my program due to it's size seems to be to add a goto when an exception occurs within the try catches which exist within the overall try and catch.

So, that leads me to assume that since I am certain you know a lot more than I do about all this that somewhere along the line I'm just not quite understanding what you mean... so I guess I'm stuck with goto!

Well maybe one day it will all become clear!

JF
Reply With Quote
  #13  
Old 05-29-2007, 09:32 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Quote:
Originally Posted by JFitz15
Well I can't really see the point in breaking up the sub... it would actually make things much more difficult for me instead of making it easier as the code would now be separated into many different subs. I suppose if I could see the advantage of breaking it up it might make sense to do it but currently it would just separate my code and make it more difficult to follow what is going on.
Yes, I suspected as such. As I wrote above: "But don't go breaking this up 'just for the heck of it' or 'Mike said so', because this could take a lot of time. But going forward, think about routines that have smaller chunks..."

In future routines I think you'll start to think about this kind of thing differently. Typically anywhere you have comments that read something like "In this section we get the User ID" or "Now we upload the result from the server". Following such comments, typically you'll have anywhere from 5 to 20 lines of code, or sometimes more. These are prime candidates for encapsulating these sections into a separate routine.

For example, let's say that you had a method that looked something like this:
Code:
Sub MySub() ' ------------------------------------------------ ' Get the user's ID: Dim userName As String = "Fred" SomeCode.Method(userName) SomeOtherCode.Property = Something Else Dim userId as Integer = SomeObject.ID ' -------------------------------------------- ' Use the ID to do something with the User's ID: Some.Other.Code.Here(userId) Some.Other.Code.Here(userId) Some.Other.Code.Here(userId) Some.Other.Code.Here(userId) End Sub
What I am suggesting is that it could be broken up as follows:
Code:
Sub MySub() Dim userName As String = "Fred" Dim userId as Integer = GetUserID(userName) DoSomethingWithUserID(userId) End Sub Function GetUserID(ByVal userName As String) As Integer SomeCode.Method(userName) SomeOtherCode.Property = Something Else return SomeObject.ID End Function Sub DoSomethingWithUserID(ByVal userId As Integer) ' Use the ID to do something with User ID: Some.Other.Code.Here(userId) Some.Other.Code.Here(userId) Some.Other.Code.Here(userId) Some.Other.Code.Here(userId) End Sub
Doing this breaks up the code into logical sections that can usually be dealt with more cleanly. The main 'MySub()' now has only three lines of code! However, refactoring a 2000 line method would be a huge task, so I think the addage "if it ain't broke, don't fix it" really does apply here. However, in the future, keep this approach in mind as it might help you out.


Quote:
Equally, not using a GoTo would require me to have about 20 to 30 lines of code in each try catch block I use (within the overall try catch finally - which does make good sense!) and since I could have, say, 30 try catch statements with their own specific error handling, that would require adding potentially 900 extra lines of code just to add cleanup to each statement...
I can't say for sure, because I cannot see your code. So only you can make these decisions. However, if you have unique cleanup code for a given section, then that section should have it's own cleanup. If you have 30 different cleanup routines, this implies 30 different Try-Finally blocks. (Although, this does sound like a huge amount.) However, if you have 30 different potential points of failure to protect, but effectively only one, universal cleanup routine that needs to be applied, then this suggests to me that there should be one enclosing Try-Finally block around the entire section thereby all exceptions will be routed to the same place. Your use of Goto's would also work, but I suspect that one day you will see all those Goto's and cringe. If you have to then you have to (the Goto is still around for a reason), but it does sound to me that a single encompasing Try-Finally block could possibly do the trick. But only you can judge this for your own code. Just keep the alternatives in mind.

Quote:
Well maybe one day it will all become clear!
I think you are closer than you may realize...

,
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #14  
Old 05-29-2007, 10:44 AM
JFitz15 JFitz15 is offline
Regular
 
Join Date: Jul 2006
Posts: 74
Default

Well I find it hard to see the advantages of splitting into several routines currently but I will try it in future and maybe that will show me the advantages more clearly.

Regarding the try and catch... try this scenario which might explain what I don't quite follow

Code:
 Dim xlapp As Excel.Application

   Sub mysub()

      Try
         Try
            xlapp.Visible = True
         Catch ex As Exception
            MsgBox("This particular error is reported to the user and what to do")
         End Try

         MsgBox("This shouldn't get used")

      Catch ex As Exception
         MsgBox("An secondary error is reported if one occurs")
      Finally
         MsgBox("And now we cleanup")
      End Try

   End Sub
Now, I haven't instantiated the xlapp here, so xlapp.visible = true will cause an error to occur in the second try and catch.

So I tested this code and what I thought might happen (I should have tested sooner though!) did in fact happen. The inner try and catch where I want to report the error works fine, but then the code does not skip to the cleanup. That would be the point of the GoTo. I have no problem with having the one overall cleanup routine, but how do I get to it without using GoTo?

The place where I have 'MsgBox("This shouldn't get used")' is the problem, this could be 100s of lines of code that I obviously don't want the program to run... I want it to go to the finally block where my cleanup is... but from what I've seen by running that code, it does not skip to the finally block...

Last edited by JFitz15; 05-29-2007 at 10:50 AM.
Reply With Quote
  #15  
Old 05-29-2007, 11:31 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hey JF,

I see your dillemma. And a Goto could be used here, where all the 'Finally' sections (including the outer 'Finally' section as well) could have the line 'Goto Cleanup' and at the 'Cleanup' linelable would be the one cleanup routine.

However, the "proper" way is for each error handler to either "handle it", that is, fix the situation and/or report it, or "not handle it", which is to say that the error is trapped, possibly extra error messages tacked on, and then the error is re-thrown. To take your code as an example, it would look something like this:

Code:
Try Try xlapp.Visible = True Catch ex As Exception Throw New ApplicationException( _ "Attempt to set 'xlApp.Visible = True' failed.", _ ex) End Try MsgBox("This shouldn't get used") Catch ex As Exception MsgBox(ex.ToString() & vbCrLf & _ ex.InnerException.ToString()) Finally MsgBox("And now we cleanup") End Try End Sub
The first 'Catch' section in the above looks like this:
Code:
Catch ex As Exception Throw New ApplicationException( _ "Attempt to set 'xlApp.Visible = True' failed.", _ ex)
Notice that we throw a new Exception, including an error message, but also including the original exception (the 'ex' part) within the newly thrown exception. The original, 'ex As Exception' that we pass in is known as the "Inner Exception". This way, later, other code can trap the thrown exception and then find the InnerException, if there is any.

The outer Catch section then contains the following:
Code:
Catch ex As Exception MsgBox(ex.ToString() & vbCrLf & _ ex.InnerException.ToString())
I'm not 100% sure that showing the 'ex.InnerException.ToString()' in addition to the original 'ex.ToString()' is necessary because the inner exception might be part of the 'ex.ToString()' result as well (I'm not sure without testing), but the point is that you can access the outer exception, the inner exception, and even an inner exception to the inner exception (if there is one), and so on, all the way down the line...

Again, I don't know that you should try to refactor 2,000 lines of code. That sounds daunting and potentially error prone. But just keep these techniques in mind for the future.

Hope this helps!
,
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #16  
Old 05-29-2007, 11:41 AM
JFitz15 JFitz15 is offline
Regular
 
Join Date: Jul 2006
Posts: 74
Default

That's the one Mike!

Actually I hadn't thought of that but that would solve the problem. Yeah... throw as exception... that would do it...

Well lots of good ideas there for me to try out... thanks a million for all of it!

Take it easy,

JF
Reply With Quote
  #17  
Old 05-29-2007, 12:24 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Good stuff JF.

Also one last thing...

Let's say that that there are a few situations that you can handle, but others you have no clue. In the case of an unknown exception case, you might want to re-throw the original exception without touching it. The effect is as if you had never handled it at all:

Code:
Dim xlApp As Excel.Application Try xlApp.Visible = True Catch ex As Exception If xlApp Is Nothing Then Throw New ApplicationException( _ "Cannot set Visible property because xlApp is null.", _ ex) Else ' No clue; re-throw, unchanged: Throw End If End Try
In the above we check for known error possibilities and handle those however we want. However, for the cases we can't handle, then just re-throw the exception.

However, note that in the above I wrote 'Throw' and not 'Throw ex'. Calling 'Throw ex' is 100% valid, and will more-or-less do what you expect, however the full path to the original exception will now be cut-off and the error would originate at your routine. By calling 'Throw' without any exception object you are passing on the original exception unchanged.

I hope that this was more helpful than confusing! But this really wraps up everything there is to know about trapping & throwing exceptions. So I didn't want to leave this out...

Happy coding ,
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #18  
Old 05-30-2007, 04:51 AM
JFitz15 JFitz15 is offline
Regular
 
Join Date: Jul 2006
Posts: 74
Default

Hi,

Yeah, if an exception occurs in my code it's usually an unfixable one... if it was fixable then I would probably test to see whether it can occur before hand and then take appropriate steps (i.e. don't wait for it to get to the stage where an exception is being thrown).

So, usually I just want a message box explaining what has happened and what the user needs to do (which might just be to call me!), which I would put into the inner exception. So, in that case I think I would probably almost always just throw the entire exception as you suggest to the outer try catch statement and then what I like to do is just output the exception into a note pad document which I place on the persons C: drive.

So the first message is for the user and the second will probably just be for me to look at (not sure if it's useful cause I haven't ever used it yet but occurred to me that it might be useful when I started making the programs... so I can just go to the persons C: drive and look at the exception report, although again... not too sure whether what is outputted is useful because what is outputted when I am debugging I think is different then what is outputted when running as an exe...)

JF
Reply With Quote
  #19  
Old 05-30-2007, 09:33 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Quote:
Originally Posted by JFitz15 View Post
Yeah, if an exception occurs in my code it's usually an unfixable one... if it was fixable then I would probably test to see whether it can occur before hand and then take appropriate steps (i.e. don't wait for it to get to the stage where an exception is being thrown).
This is exactly correct. And this is why most routines would have only one outer Try-Catch-Finally block to handle all unexpected errors. Typical would be for the Catch section to save 'ex.ToString()' to a log file and report the error via a message box and for the Finally section to do your cleanup code.

Unless you *really* can do something about smaller test-cases, there is generally no reason to clutter up one's code with Try-Catch-Finally blocks everywhere.

Quote:
...not too sure whether what is outputted is useful because what is outputted when I am debugging I think is different then what is outputted when running as an exe...)
Especially in early phases ("Beta test") you can compile your application as a Debug bulid so that it contains greater debugging information. Just be aware that .NET IL code is fairly transparent to decompile unless you use a obfuscator or protector, and that adding Debug information helps with your error reporting, but can also help outsiders understand your program.

Mostly, though, if you're worried about getting it to work bug-free, then compling as a Debug build can help.
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #20  
Old 05-31-2007, 08:06 AM
JFitz15 JFitz15 is offline
Regular
 
Join Date: Jul 2006
Posts: 74
Default

Hi,

Well the inner try and catches are generally things like opening files and copying sheets, creating worksheet objects, and so the only reason an exception is thrown is because the user forgot to add particular sheets etc... so that's what I mean by an 'unfixable' error! If it's a fixable error, such as an empty cell throwing a null reference exception then of course you can just test whether the cell is empty before hand... etc...

All my applications are for internal use so compiling as a debug build sounds good. Although I don't know exactly what that is... is that still a .exe? How do you go about that?

thanks,

JF
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Range to 2d Object. Excel does not close.
Range to 2d Object. Excel does not close.
Range to 2d Object. Excel does not close. Range to 2d Object. Excel does not close.
Range to 2d Object. Excel does not close.
Range to 2d Object. Excel does not close.
Range to 2d Object. Excel does not close. Range to 2d Object. Excel does not close. Range to 2d Object. Excel does not close. Range to 2d Object. Excel does not close. Range to 2d Object. Excel does not close. Range to 2d Object. Excel does not close. Range to 2d Object. Excel does not close.
Range to 2d Object. Excel does not close.
Range to 2d Object. Excel does not close.
 
Range to 2d Object. Excel does not close.
Range to 2d Object. Excel does not close.
 
-->