Program Stays In Memory

[Esham]

Could someone be so helpfull as to look at my small program and see if they can figure out why Excel, and the program itself, resides in the task manager after everything is closed? I'm setting all the objects to nothing (I think) but it still doesn't work. Thank you in advance!

[umilmi81]

You didn't include the form in the zip file.

Are you calling "Unload"

You need to call unload on each form

UnLoad Form1

Of course, you can also just call end

End

But I have learned that this can be bad.

[Pasvorto]

And, in the form.unload code include the

set form = nothing

substitute the name of your form for "form"

[Esham]

Sorry, the new zip file has the form.

[RobDog888]

You only incluided the .vbp file and not the frmSplash, FormMain.frm, & Module1.bas.

Do not ever use End at all.

[Pasvorto]

I guess I didn't see where you set either form = nothing.

[RobDog888]

If there is no error then your xlApp only gets Set to Nothing. It wont get .Quit. You need to either .Close or .Quit all your Excel object variables and then after that destroy them by setting them equal to nothing.

[si_the_geek]

You haven't included the module, but I can see part of the problem - you are not closing Excel properly.

In form load you should change this:

.Workbooks.Open "SOME DIRECTORY\OP Concerto Forecast.xls"
    .Worksheets("Selection").Activate

to this:

Dim xlWorkbook as Excel.Workbook
    Set xlWorkbook = .Workbooks.Open "SOME DIRECTORY\OP Concerto Forecast.xls"
    xlWorkbook.Worksheets("Selection").Activate
End With
With xlWorkbook.Worksheets("Selection")

Then, remove the ".Quit" from the end of the With, and change the section from Done: onwards to this:

Done:
    FormSplash.Hide
    Set FormSplash = Nothing
    xlWorkbook.close SaveChanges := False
    Set xlWorkbook = Nothing
    xlApp.Quit 
    Set xlApp = Nothing
    Screen.MousePointer = 0
    Exit Sub
AdoError:
    If Err.Number = 32755 Then
        Resume Done
    Else
        MsgBox Err.Description, vbCritical, "Error #: " & Err.Number & Err.Source
        Resume Done
    End If

[Esham]

I've created over 50 other vbprojects and never set my startup form to nothing and none of those reside in memory. Any other ideas. I'll try and set both forms to nothing in the startup form unload event.

[RobDog888]

The form object is not the problem. Excel remains running because just setting it to nothing will not quit the app and destroy it. Do you have any other excel object variables in the Module1.bas?

[RobDog888]

Moved from Classic VB forum.

[Esham]

The form object is not the problem. Excel remains running because just setting it to nothing will not quit the app and destroy it. Do you have any other excel object variables in the Module1.bas?

I do quit xlApp and then set it to nothing. I also have a chart object which I also set to nothing.

[randem]

Esham,

Your problem:

Done:
    Unload FormSplash
'    FormSplash.Hide
    Set FormSplash = Nothing

You hide the form but never unload it. Call a routine at the end of you program like such: (Place this in a module and call it from your forms Unload event.

Public Sub UnloadAll()
Dim frm As Form
Dim ctl As Control
 
    On Error Resume Next
    
    For Each frm In Forms
    
        For Each ctl In Controls
            Set ctl = Nothing
        Next
        
        Unload frm
        Set frm = Nothing
    Next
 
End Sub

[si_the_geek]

I have a feeling that the code in the module (which isn't in the zip file) could be the cause of your program not closing.

The changes I suggested above should correct the issue with Excel.

[Esham]

The module1.bas should be in the zip file. I 've updated it. Check again. Sorry about that.

[si_the_geek]

You should make similar changes to the CreateChart sub as I mentioned above.

Also, you should not have object variables declared as "New" anything when they are public (and arguably not at all). You should add a line like this instead when you want the application to open (in form_load and CreateChart):

Set xlApp = New Excel.Application

And Randem is correct about your splash form, you should be using the amended code as in his first example (or the other if you prefer).

[Esham]

Public Sub UnloadAll()
Dim frm As Form
Dim ctl As Control
 
    On Error Resume Next
    
    For Each frm In Forms
    
        For Each ctl In [COLOR=DarkRed]Controls[/COLOR]
            Set ctl = Nothing
        Next
        
        Unload frm
        Set frm = Nothing
    Next
 
End Sub

[/QUOTE]

It gives me an error when I compile saying the variable is not defined.

[RobDog888]

I also noticed that in your formmain's unload event your calling itself to unload again in that procedure creating a circular reference.

Private Sub Form_Unload(Cancel As Integer)
Unload FormSplash
'Unload FormMain 'Circular ref.
Set FormSplash = Nothing
Set FormMain = Nothing
 
End Sub

Also, switch the event to the Form_QueryUnload event instead. In case your clicking the 'x' at the top right corner to close the app. it will be sure to close and destroy all objects.

[randem]

Esham,

You did not state where you attempted to place the code or anything. The code does work I use it exactly as posted all the time. Did you place this code in a module?

[Esham]

Esham,

You did not state where you attempted to place the code or anything. The code does work I use it exactly as posted all the time. Did you place this code in a module?

Randem,

I placed your exact code into the Module1.bas and called it from FormMain.QueryUnload. What am I doing wrong?

Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
 
Call UnloadAll
 
End Sub

[Esham]

Everything seems to be working now except for the Controls unload loop. Thanks for all the help!

randem,

Any suggestions on why that's not working. The Forms unload works fine, just not the Controls.

[si_the_geek]

It looks like a minor change is needed:

For Each ctl In [b]Frm.[/b]Controls