Excel Workbook Remains in Memory

[Paul Wolff]

Hi Govert,

I'm wondering why a workbook (with a macro) remains in the VBA editor after I manually unload it.

Here's what I've done

1.  Created a simple com server that has a routine called HelloWorld.  Here's the code:

'*************************************************************************************************************

Imports ExcelDna.Integration

Imports ExcelDna.ComInterop

Imports System.Runtime.InteropServices

Imports Microsoft.Office.Interop

Public Class AddIn

    Implements IExcelAddIn

    Public Sub AutoOpen() Implements IExcelAddIn.AutoOpen

        ComServer.DllRegisterServer()

    End Sub

    Public Sub AutoClose() Implements IExcelAddIn.AutoClose

        ComServer.DllUnregisterServer()

    End Sub

End Class

<ComVisible(True)>

<ClassInterface(ClassInterfaceType.AutoDispatch)>

<ProgId("ReleaseApp")>

Public Class Apps

    Public Sub HelloWorld()

        Dim xxlapp As Excel.Application = ExcelDnaUtil.Application

        Dim strChk As String

        strChk = xxlapp.ActiveSheet.cells(1, 1).text

        If strChk = "" Then

            xxlapp.ActiveSheet.cells(1, 1).value = "Hello World"

        Else

            xxlapp.ActiveSheet.cells(1, 1).value = ""

        End If

    End Sub

End Class

'*********************************************************************************************************************************

2. Loaded a workbook CallHelloWorld.xls that contains a HelloWorld macro.  Here's the VBA code:

'**********************************************

Sub HelloWorld()

   Dim lib As Object

  

   Set lib = CreateObject("ReleaseApp")

   lib.HelloWorld

   Set lib = Nothing

End Sub

'*********************************************************

3. Run the HelloWorld Macro

4. Unload the CallHelloWorld.xls from Excel

5. Although the workbook has been unloaded, the worksheets and macros are still visible in the VBA editor.

After I unload the workbook the workbook still appears in the VBA editor, so there's an object that hasn't been cleared properly.  After looking into this, the line of code that causes the workbook from unloading properly is when the xlapp object is set to the ExcelDNAUtils.application object.

Is there something wrong with my code?  Is there a method I can run so that after using DNA, the workbooks unload from Excel properly?  I'm running large analyses that crash intermittently and want to eliminate any memory leaks I may have.  And this is the first one I've found.

I've run the app in Excel 2003, 2010, and 2016.  Same results in any of the versions of Excel.

Thanks in Advance,

Paul

.

[Govert van Drimmelen]

Hi Paul,

You probably need to force a garbage collection (GC.Collect) somewhere on the .NET side to release the COM reference to the sheet.

There might also be other times when the project stays loaded in the IDE, if I judge from the Google result for "vba project stays open in ide".

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Paul Wolff]

Hi Govert,

Thanks for the reply and for the google search suggestion.  I had previously tried the garbage collector and I tried some of the ideas that came up in the google search.  None of them worked.  With ActiveX Dll's in VB6 I could always find the objects that weren't cleared properly and unload workbooks from memory and clear them from the IDE.  I crunch large data sets using Excel with objects provided by a com server and I'm pretty sure not clearing memory properly is one of the reasons excel crashes intermittently.  

I wish there were some way to clear memory properly with VB.net.

Paul

[Patrick O'Beirne]

That shadow or ghost of a workbook's VBA is often there in the VBE.
I doubt if it impinges memory much, there are too many worse ways to use memory.
As you've already done searched Google, you've set all references to Nothing, and it's still there?
AFAIK all you can do is restart Excel.

[Paul Wolff]

Hi Patrick,

I appreciate your reply.

Yes I've set references to nothing.  With other code I've written, the usual cause of the problem is to have a module or global level variable addressing an excel object and then to close the workbook before setting the object to nothing.

But in the code I've included, I set xlapp to nothing, and called the garbage collector.  And the workbook still remains in the IDE.

Paul

On Wednesday, September 14, 2016 at 5:26:52 PM UTC-4, Paul Wolff wrote:

[Govert van Drimmelen]

Hi Paul,

I still think correct use of the .NET GC sorts this out, so I'm trying to reproduce your issue top play around a bit.

But I am not able to get the workbook stuck in the IDE given your instructions.

My add-in and VBA work right, the only trick was that I had to set ComServer="true" in the .dna file.

I am using 32-bit Excel 2013.

I wonder if you have other add-ins loaded?

Otherwise, perhaps you can give some ideas on how I can reproduce the same effect that you see on my machine.

I've tried

* saving / not saving,

* running with the IDE open / IDE not open, 

* another sheet open / no other sheets open.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Patrick O'Beirne [obeir...@gmail.com]
Sent: 15 September 2016 06:05 PM
To: exce...@googlegroups.com
Subject: Re: [ExcelDna] Re: Excel Workbook Remains in Memory

[Paul Wolff]

Hi Govert,

After your reply, I loaded the workbook and DNA xll file on my laptop and ran it.  It worked fine, i.e. the workbook was cleared from IDE after unloading.

After quite a bit of fiddling, I finally got lucky; It turns out it's the *!**$% Com Add-ins.  After I deactivated those, the workbook unloads from the IDE as I expect it too in both excel 2016 and excel 2010.  Not 2003 though, but 2 out of 3 is pretty good.

And one of the tips in the google search you provided did mention the adobe pdf maker add-in.  I looked for it, but in the wrong place so I didn't see it.  I wasn't familiar with Com Add-Ins, but I am now.

Thank you!

Paul

On Wednesday, September 14, 2016 at 5:26:52 PM UTC-4, Paul Wolff wrote:

[Govert van Drimmelen]

That's a pleasure Paul!

I also accept donations via PayPal: https://www.paypal.com/cgi-bin/webscr?cmd=_donations&business=92N99RV5NQ29C&lc=US&item_name=Govert%20van%20Drimmelen&item_number=ExcelDna¤cy_code=USD&bn=PP%2dDonationsBF%3abtn_donate_LG%2egif%3aNonHosted

Regards,

Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Paul Wolff [pauljos...@gmail.com]
Sent: 15 September 2016 10:21 PM
To: Excel-DNA
Subject: [ExcelDna] Re: Excel Workbook Remains in Memory