Excel dies only when userform disposed?

[JFitz]

Hi!

I don't know if anyone can help with this but when I run my application to automate excel, the excel session will close only when the userform is disposed (it stays open in memory). This isn't very helpful as it means that you can't perform other functions on the form which may require re-opening the excel file which is already open. You would have to close the form and re-open it in order to run other functions displayed there.

I have no idea why this is occurring. As far as I can see all the COM objects are dereferenced properly (all set to nothing - range objects, worksheet objects, workbook objects and then the application is quit and the application object itself is set to nothing) so I can't see what is keeping excel open. Anyone any ideas why excel might stay open until the form is disposed? Is there something I don't know about?!

Thanks,

JF

[si_the_geek]

Welcome to VBForums

You mention a userform, so is this code running from with an Office app (Excel?), or within "proper" VB?

As to the actual issue, it sounds like you haven't actually closed everything properly (or perhaps in the wrong order). If you show us the code, we can probably find the issue(s) for you.

[JFitz]

Hi thanks for the quick reply!

The code is too long to put here but I could put the clean up routine in. Basically I have my own class called AC which sets all of these variables to nothing. I'll put the code below but I'm not sure what it will look like to you guys! Ultimately the AC class will contain code which looks something like this

If Not ws Is Nothing Then
         Marshal.FinalReleaseComObject(ws) 'go through our variables in reverse-order of importance 
         ws = Nothing
      End If

so that in the end, everything is set to nothing. I'm pretty sure the code should be ok but maybe you can point something out. Just to make sure everything is nothing I've looked at each COM object separately in the debugger and they are all equal to nothing so I don't think that can be the problem. And I think the order that I dereference them is ok? Range first, Worksheet, Workbook and then application.

cleanup:
      Try
         If Not xlApp Is Nothing Then
            GC.Collect()
            GC.WaitForPendingFinalizers()   'release minor objects that we are not referencing by variables we control
            GC.Collect()
            GC.WaitForPendingFinalizers() 'Double up to ensure that any objects with finalizers are collected.

            'Cleanup Range objects using the RngCleanUp funtion of the AutomationClass
            AC.RngCleanUp(rngIPDial, rngX28, rngUlf, rng1, rng2, rng3, rngSummary)

            'Cleanup Worksheet ojects by using the WorkSheetCleanUp Subroutine of the AutomationClass
            AC.WorkSheetCleanUp(wsIPDial, wsX28, wsUlf, wsCopy, wsSummary)

            'Cleanup Workbook ojects by using the WorkBookCleanUp Subroutine of the AutomationClass
            AC.WorkBookCleanUp(wb, SaveDocuments, wbControlFile, False, wbPriceBook, False) 'This subroutine will not save any changes made to the workbook

            xlApp.Quit()
            Marshal.FinalReleaseComObject(xlApp)
            xlApp = Nothing
            GC.Collect()
            GC.WaitForPendingFinalizers()   'release minor objects that we are not referencing by variables we control
            GC.Collect()
            GC.WaitForPendingFinalizers() 'Double up to ensure that any objects with finalizers are collected.
         End If
      Catch ex As Exception
         MsgBox("An error has occurred ...")
         AC.CreateExceptionReport(ex.ToString)
         Exit Sub
      End Try

[JFitz]

That's proper VB by the way just in case there's any ambiguity!

Thanks,

JF

[si_the_geek]

I don't use VB.Net myself, so I'm not sure about some things like the GC (hopefully somebody else will comment!)

The order you close the Excel objects is fine, but we can't tell if things are being closed properly, as it depends on the code in RngCleanUp etc.

[JFitz]

Should be ok. The objects are passed in by reference. So the range object would be passed in by reference and the code in the AC class would look something like below (I've just modified it to take only one range object):

Sub RngCleanUp(Optional ByRef rng1 As Excel.Range = Nothing)

      If Not rng1 Is Nothing Then
         Marshal.FinalReleaseComObject(rng1) 'go through our variables in reverse-order of importance 
         rng1 = Nothing
      End If

And so rng1 will be set to nothing and I can see this in the debugger. Apart from that I can't think of any other reason excel should stay open until the userform is destroyed? What is it about closing the user form in this case that finally dereferences excel do you know?

[si_the_geek]

I don't know how the Marshal bit works, so I can't comment on whether that is right, or even in the right place.

One thing tho is that for WorkBooks, you need to close them before setting them to nothing (and presumably before the Marshal bit too).


edit: this VB.Net example(post #3) from the Office Development FAQ's may be useful.

[RobDog888]

Using any of the Marshal method classes is not really a good thing to do. Just because its there doesnt mean it should be used , at least for Office automation code.

Try using Marshal.ReleaseComObject instead but seriously you shouldnt need any code like this at all. I have never needed it in any of my .NET Office apps. If you write correct clean up code for your office object variables you will be good to go and have no abandoned office processes or references.

[JFitz]

Hi, thanks for your help!

Well, like I say, everything looks ok to me so that's why I ask. If there's something wrong with the way I'm writing the code then I don't know what it is!

But, I got information on how to close excel applications from another forum actually (which I suppose should remain nameless!). But here's a sample if you care to read through all of it!

This is what they write for their cleanup faq sheet. They seem to know what they're talking about.

Cleanup:    
GC.Collect()                       
GC.WaitForPendingFinalizers()     

Marshal.FinalReleaseComObject(oWS) ' <-- Requires .NET 2.0    
oWS = Nothing     

oWB.Close(SaveChanges:=False)    
Marshal.FinalReleaseComObject(oWB) ' <-- Requires .NET 2.0    
oWB = Nothing    
 
oApp.Quit()    
Marshal.FinalReleaseComObject(oApp) ' <-- Requires .NET 2.0    
oApp = Nothing
End Sub

This is what they say about this:

"If using .NET Framework versions below 2.0, then the Marshal.FinalReleaseComObject() will *not* be available to you. Instead, you would have to use Marshal.ReleaseComObject(), but there are some special caveats in using this. Cleanup, as it pertains to .NET Automation, is a tricky business. The difficulty derives from the fact that .NET is operating through the COM Interop, which is an interconnection between two technically incompatible worlds: (1) Legacy COM and (2) the .NET Framework.

There are many complex techniques for releasing a COM Object in .NET, but the above example is showing what we believe is the cleanest arrangement.

It is important to note that the Marshal.FinalReleaseComObject() method is only available when using Framework 2.0, and therefore is accessible by VB 2005, C# 2005 or the like. If using the .NET 1.0 or 1.1 framework (that is, VB or C# 2002/2003) then you have to make use of Marshal.ReleaseComObject(), but this is used in a completely different manner!

The key is that Marshal.FinalReleaseComObject() decrements the reference count for the COM object repeatedly until the the count = 0 and the COM object is fully released. However, Marshal.ReleaseComObject() only decrements the COM reference count only once, and so it is up to you to use a 'While Loop' in order to make sure that the COM object is fully released. In short, if using the .NET Framework 1.0 or 1.1

In short, .NET needs to tell COM explicitly that it is done with the object and there are basically only two ways to do this:


(1) Set the variable = Nothing and then call GC.Collect(). This works 100% fine, but calling GC.Collect() is a time consuming process. One would think that you can set all your variables = Nothing and then call GC.Collect() at the end, and this does work sometimes; however, MS Office applications are sensitive to the order in which the objects are released and, unfortunately, setting your variables = Nothing and then calling GC.Collect() does not guarantee the order in which the COM objects are released. (That is, unless you called GC.Collect() after every single = Nothing setting -- which would be very expensive.)

(2) Call Marshal.FinalReleaseComObject() or Marshal.ReleaseComObject(). This allows us to explicitly control the order in which our COM objects are released. However, this approach does not allow us to release objects to which we do not have a named variable. For example, when programming in Excel, there are usually loops where one iterates through the cells of a Range. It might not be obvious, but the result of this is that many Range objects are created and discarded rapidly. In VBA or VB 6.0, these objects are garbage collected immediately; however, in .NET, they sit, awaiting Garbage Collection.

So the result is that for proper cleanup we must call GC.Collect() and GC.WaitForPendingFinalizers() first in order to release minor objects that we are not referencing by variables we control, and then go through our variables in reverse-order of importance calling Marshal.FinalReleaseComObject() or Marshal.ReleaseComObject(). It is important that the application variable gets released last, or the application's process is likely to hang."

[RobDog888]

Yes, I am aware of those and what it does but without seeing any of your code (not the clean up code) I can not tell you where the errors are. Plus, you didnt state the .NET version. I did see that you wrapped some code up in a class but if the object variables are not being disposed of properly then the class will not dispose either causing Excel to hang and remain in memory/running.

You shouldnt ever need to use ReleaseComObject or FinalReleaseComObject. In my FAQs they all dispose of the object variables in the correct order and method. This is why they dont use FinalReleaseComObject or ReleaseComObject.

Post your code and I will go over it for you.

[JFitz]

The version I'm using is 2.0.

Well, lets just say that after my cleanup routine runs, every range, worksheet, workbook and application object is set to nothing and in that order.

Problem with my code is that it's thousands of lines long and uses classes I've made myself along with other forms etc. I suppose I could post part of it as it could be broken up into a number of smaller applications but you wouldn't be able to get it working without the source data etc which I can't send!

So I suppose the question would be: If every COM object is set to nothing and all in the supposed correct order, then what other error might you look for?

Also, where are your FAQs... I don't off hand see them... might be useful for me to look!

[JFitz]

oops I see them now after I've posted!

[RobDog888]

The index link is in my signature in red.

Even if you set the object variable = Nothing that will not destroy an Office app reference. Then depending on how the code was written you may be creating additional references unknowingly.

Yes, that does make it hard to debug when its a good sized app. I would say to first start on all the Dim's and dimensioning of Excel object variables. They should be fully qualified and not just "Dim oRange As Range". That will create the implied extra reference. Whereas if you went "Dim oRange As Excel.Range" its explicit and no extra reference.

What does your "Imports" look like? Are you using the .Interop or the .Core?

[JFitz]

Well I don't know if this is over kill but I've put my dim statements (which aren't just integer or string etc) below. In order to make sure that there are no excel objects being created in my code I've made sure that I only create class objects (i.e. the excel objects are class objects - which i would need to do anyway because different subs might need them). Then after the cleaup I look at what each one is, in the debugger and they all equal nothing.

Dim xlApp As New Excel.Application
   Dim wsData, wsSheet, wsDHLSmartbillCodes, wsVD As Excel.Worksheet
   Dim wb, wbControlFile As Excel.Workbook
   Dim rngData, rng1, rng2, rng3, rng4, rngVD, rngDHLSmartbill As Excel.Range
   Dim ExceptionFile As System.IO.File
   Dim oWrite As System.IO.StreamWriter
   Dim UnknownColumns(50), SheetRefs(10) As String
   Dim arrayxD, arrayyD, arrayzD As Object(,)
   Dim splitStringarray(4) As String
   Dim obj, obj1 As Object
   Dim openFileDialog1 As New OpenFileDialog()
   Dim AC As AutomationClass.AutomationClass

I don't know if this would have any effect but one thing I do is, in order to avoid looping through cells in excel I place all my range objects into an array of type Object(,) and loop through this (as this is far quicker). But this isn't a COM object anymore so I presume that would have no effect in keeping excel open.

And my imports are as follows (some probably aren't much use actually):

Imports System
Imports System.IO
Imports System.Text
Imports System.Runtime.InteropServices 'Imports System.Runtime.InteropServices 'For error wrapper class. Errors in Excel cannot be directly recognised by .NET
Imports System.Security.Permissions
Imports Microsoft.VisualBasic
Imports Excel.xldirection
Imports Excel.XlPasteType

[RobDog888]

Ok, a couple of things...

The application object variable does get created each time the class its contained in gets created because of the way its declared with the New keyword. If you have another line of code like "xlApp = CreateObject("Excel.Aplication")" or "xlApp = New Excel.Application" then you will have two excel application object variables per class instance.


Next, dimming your vars this way only defines the last var as the specified type. The rest are dimmed as Variant or Object. Should be setting the type for each var.

Dim wsData, wsSheet, wsDHLSmartbillCodes, wsVD As Excel.Worksheet
'Should be
Dim wsData As Excel.Worksheet, wsSheet As Excel.Worksheet, wsDHLSmartbillCodes As Excel.Worksheet, wsVD As Excel.Worksheet


So its the same with workbooks and ranges vars too.

[JFitz]

Ah yes good point! It shouldn't change anything but thanks for pointing it out. The problem with doing that is if you simply open and close the form then it will leave an open instance of excel in the task manager which hasn't gotten cleanup up. Well I fixed that now.

As for the other dim statements creating variants... I find it hard to believe that visual basic would be doing that?? That would be a big bug in vb then wouldn't it? When I go through my code and check the objects with intellisense it says that they are defined properly. i.e. if I place my cursor over rng1 for instance or any other range, it will show in the yellow info box 'Private Dim rng1 as Excel.Range'. Are you sure that this isn't a range but is actually a variant? If that was the case would that not cause problems with my code? I suppose that would be late binding then? And would require me to use ctype in my code to tell the compiler what kind of object it was receiving... so I can use intellisense... I'm getting confused now

[RobDog888]

Sorry if I may have confused you. Its my understanding as it was always that way in VB 6. I dont recall seeing anything on it in .net so I could be wrong.

[JFitz]

Well Yoda! I think you said it best when you said 'Already know you that which you need'

... so maybe the answer will come to me in a dream!

Anyway, thanks for your help... maybe it's just some small mistake I've missed!

JF