VBAProjects remain in memory after workbook is closed

I have a rudimentary pricing applicatiion which copies information from a
pricelist into a quote form located on a sheet within the same workbook. The
user has a toolbar that they can use to copy this quote sheet to a new file.
The program also saves a listing of all quotes generated in this fashion into
a summary sheet. After creating the quote file, the application closes the
new quote and allows the user to generate a new quote.

The problem is that after generating several quotes (like 2-3), and viewing
the summary list of quotes once or twice, Excel starts acting really strange.
the panes lockup memory errors occur, etc. When running this project in
debug mode, the VBAProjects associated with the closed workbooks are still in
memory. So after a while there can be 5-10 sets of VBAProjects (with no code
or userforms at all) in memory.

In the past I learned that using fully qualfiied workbook, sheet and range
names eliminated similar problems. But even with fully qualified references,
this problem remains. I am urgently trying to release the latest version of
this tool to my coworkers, but I am stumped as to the cause of this problem.

Any ideas?

Thanks,

I would have to guess that you are using some object(s) that are not fully
qualified and/or released before attempting to close the file
But without any idea of your code, it's hard to tell.

NickHK

"Arif Ali" <Arif Ali@discussions.microsoft.com> wrote in message
news:F74D2325-B7AF-4D3F-8100-D6F407E5A818@microsoft.com...
> I have a rudimentary pricing applicatiion which copies information from a
> pricelist into a quote form located on a sheet within the same workbook.
The
> user has a toolbar that they can use to copy this quote sheet to a new
file.
> The program also saves a listing of all quotes generated in this fashion
into
> a summary sheet. After creating the quote file, the application closes
the
> new quote and allows the user to generate a new quote.
>
> The problem is that after generating several quotes (like 2-3), and
viewing
> the summary list of quotes once or twice, Excel starts acting really
strange.
> the panes lockup memory errors occur, etc. When running this project in
> debug mode, the VBAProjects associated with the closed workbooks are still
in
> memory. So after a while there can be 5-10 sets of VBAProjects (with no
code
> or userforms at all) in memory.
>
> In the past I learned that using fully qualfiied workbook, sheet and range
> names eliminated similar problems. But even with fully qualified
references,
> this problem remains. I am urgently trying to release the latest version
of
> this tool to my coworkers, but I am stumped as to the cause of this
problem.
>
> Any ideas?
>
> Thanks,

Hi Ali,

Better you post your first running code, and let me guess that
possibility is by :
Dim Wb as Excel.Application

or something like that, that not bring the object visible is true but
visible is false .

try to post your code ... that error occured

Thanks,

hAlim

Arif Ali menuliskan:

> In the past I learned that using fully qualfiied workbook, sheet and range
> names eliminated similar problems. But even with fully qualified references,
> this problem remains. I am urgently trying to release the latest version of
> this tool to my coworkers, but I am stumped as to the cause of this problem.
>
> Any ideas?

Nick and hAlim,

Your emails both confirm my that it must be a qualification or
object-release issue. I will check the code once more (a little later today)
once more and if I cant see anything I will post the code. Otherwise I'll
notify you what I found.

Thanks,

"halimnurikhwan@yahoo.com" wrote:

> Hi Ali,
>
> Better you post your first running code, and let me guess that
> possibility is by :
> Dim Wb as Excel.Application
>
> or something like that, that not bring the object visible is true but
> visible is false .
>
> try to post your code ... that error occured
>
> Thanks,
>
> hAlim
>
> Arif Ali menuliskan:
>
> > In the past I learned that using fully qualfiied workbook, sheet and range
> > names eliminated similar problems. But even with fully qualified references,
> > this problem remains. I am urgently trying to release the latest version of
> > this tool to my coworkers, but I am stumped as to the cause of this problem.
> >
> > Any ideas?
>
>

OK Guys, I give up. I hope you still have your notifications on for this
posting! I was hesitant to post my code in the fear that it would be too
messy (embarassing) or would require too much explanation. But at present I'm
stumped. I'm obviously missing the point on this qualification issue so here
goes. The general idea is that my "Quoter" workbook consists of:

1. A sheet called QuoteForm
2. A sheet called OrderEntry Form
3. Several Sheets with catalog items: part numbers, descriptions, prices;
one on each row
4. There is a navigation toolbar (userform) to move to different sheets

The user simply double clicks on the catalog items to be added to the quote,
and they appear on the quote form and orderentry form. (For now this code is
not included). When all items have been added, the user then saves the
quoteform and/or order entry forms to a separate workbook. There are
separate buttons that launch each of these options. We'll get to that part a
little later.

Remember the problem is that each time I open and subsequently close a
workbook, the VBAProject says in memory. The only VBAProject that should stay
in memory throughout the process is the main workbook that contains the code
below.

Public QSPath As String
Public oExcel As Excel.Application
Public QS, Quoter, NewBook As Excel.Workbook
Public ParentSheet, FactorySuite, QuoteForm, OrderEntry, NewQuoteForm, _
NewOrderEntry As Excel.Worksheet

Private Sub Workbook_Open()

'first define and assign global variables
Dim QuoteSummaryExists As Boolean

Set oExcel = GetObject(, "Excel.Application")
Set Quoter = oExcel.ActiveWorkbook
Set QuoteForm = Quoter.Sheets("QuoteForm")
Set OrderEntry = Quoter.Sheets("OrderEntry")
Set FactorySuite = Quoter.Sheets("FactorySuite")
ThisWorkbook.Bookname = Quoter.Name ' Save name of activeworkbook

Set fs = CreateObject("Scripting.FileSystemObject")

homedrive = Environ("HOMEDRIVE") 'Capture Drive Letter

Homepath = Environ("HOMEPATH") 'Capture MyDocuments Path
Homepath = Homepath & "\My Documents\"

QSPath = homedrive & Homepath ' Set QuoteSumary Path

QuoteSummaryExists = fs.fileexists(QSPath & "QuoteSummary.xls")
'Check for existence of QuoteSummary

If Not QuoteSummaryExists Then
Call CreateQuoteSummary
End If

Quoter.Activate
QuoteForm.Activate 'Switch to Quoter Spreadsheet

If (QuoteForm.Range("b24").Value <> "") Or (QuoteForm.Range("c15").Value <>
"") Or (QuoteForm.Range("M15").Value <> "") Then
vbans = vbNo
vbans = MsgBox("Erase Existing Quote Information?", vbYesNo)
If vbans = vbYes Then

QuoteForm.Range("m17..r17").Value = CDate((Now()))


'Customer Name
QuoteForm.Range("C15..I15").Value = ""
' Name
QuoteForm.Range("C16..I16").Value = ""
'Address
QuoteForm.Range("C17..I17").Value = ""
'City
QuoteForm.Range("C18..E18").Value = ""
'State
QuoteForm.Range("g18").Value = ""
'zip
QuoteForm.Range("I18").Value = ""
'Quote Number
QuoteForm.Range("m15..r15").Value = ""
'Salesperson
' QuoteForm.Range("m16..r16").Value = ""
'Quote Date
' QuoteForm.Range("m17..r17").Value = ""
'QuoteBody
QuoteForm.Range("b24..r44").Value = ""
End If
End If


oExcel.ActiveWindow.ScrollRow = 1
QuoteForm.Range("A1").Value = ""
QuoteForm.Range("B24").Select
ActiveCell.Value = ""

'ActiveWorkbook.Sheets("FactorySuite").Activate
FactorySuite.Range("A1").Select

UserForm1.MultiPage1.Style = fmTabStyleTabs

UserForm1.Show 0
UserForm1.Left = 560
UserForm1.Top = 30

Set fs = Nothing

End Sub

If the quote summary does not exist, create it!

Private Sub CreateQuoteSummary()
Dim NewBook As Excel.Workbook

oExcel.ScreenUpdating = False

Set NewBook = oExcel.Workbooks.Add

NewBook.Activate

NewBook.Sheets("Sheet1").Activate
NewBook.Sheets("Sheet1").Range("A1").Value = "Salesman"
NewBook.Sheets("Sheet1").Range("B1").Value = "Quote Date"
NewBook.Sheets("Sheet1").Range("C1").Value = "Customer"
NewBook.Sheets("Sheet1").Range("D1").Value = "Quote Num"
NewBook.Sheets("Sheet1").Range("E1").Value = "Quote Amt"
NewBook.Sheets("Sheet1").Range("F1").Value = "FileName"

NewBook.Sheets("Sheet1").Range("a1").Select
NewBook.ActiveSheet.Name = "Quotes"
NewBook.SaveAs (QSPath & "QuoteSummary.xls")
NewBook.Close savechanges:=False
oExcel.ScreenUpdating = True
Set NewBook = Nothing
End Sub


From then on, as this spreadsheet gets populated in subsequent runs of the
tool, there is a button click event that calls the following routine. This
routine opens the quote summary, populates a listbox and then closes the
spreadsheet.

Public Sub InitQuoteSummaryWindow()

oExcel.ScreenUpdating = False

Set QS = oExcel.Workbooks.Add(QSPath & "QuoteSummary.xls")
QS.Sheets("quotes").Activate
QS.ActiveSheet.Range("A1").Select
i = 0
Do While ActiveCell.Value <> ""
i = i + 1
ActiveCell.Offset(1, 0).Select
Loop

frmQuoteSummary.ListBox1.Font.Name = "Arial"
frmQuoteSummary.ListBox1.Font.Size = 10
frmQuoteSummary.ListBox1.ColumnCount = 6
QS.Activate
frmQuoteSummary.ListBox1.ColumnHeads = False
frmQuoteSummary.ListBox1.RowSource = "a1:f" & CStr(i)
frmQuoteSummary.ListBox1.MultiSelect = fmMultiSelectSingle
frmQuoteSummary.ListBox1.ColumnWidths = "72;108;108;108;96;96"
frmQuoteSummary.ListBox1.TextAlign = fmTextAlignLeft

oExcel.ScreenUpdating = True

QS.Close
Set QS = Nothing

End Sub

Now lets look at how quotes and order entryforms are generated and saved.
Here is the button click event one for SaveNewOrderEntry. Note that this
event exists in a separate module and therefore includes the prefix
Thisworkbook. before the global variables like QuoteForm, etc. (I have not
included the code for the two other varioations: SaveQuoteForm and SaveBoth.
If we cant solve the issue looking at the code above, we can look at those
too.)

Private Sub btnSaveNewOE_Click()

Set ThisWorkbook.NewBook = ThisWorkbook.oExcel.Workbooks.Add

ThisWorkbook.OrderEntry.Copy Before:=ThisWorkbook.NewBook.Sheets("Sheet1")

ThisWorkbook.NewBook.Activate

fname =
ThisWorkbook.oExcel.GetSaveAsFilename(InitialFileName:=(Range("c9").Value) &
" " & Format(Now(), "mmddyy"))
If fname <> False Then
If Right(fname, 1) = "." Then
fname = Left(fname, Len(fname) - 1)
End If

If Right(fname, 3) = "xls" Then

ThisWorkbook.NewBook.SaveAs Filename:=fname

Else

ThisWorkbook.NewBook.SaveAs Filename:=fname & ".xls"

End If

ThisWorkbook.NewBook.Close savechanges:=False

Else

ThisWorkbook.NewBook.Close savechanges:=False

End If
Set ThisWorkbook.NewBook = Nothing
End Sub



Thanks in advance!!!!

At a quick glance:
All this code is in a workbook ?
If so, there's no need for the variable created in "Set oExcel = GetObject(,
"Excel.Application")", as you already have the global Application object you
can use.
As, you are not setting this to Nothing (or it's not shown here anyway), it
would maintain a reference to Excel from your WB and prevent it from
unloading.
Change this and see if it helps.

NickHK

"Arif Ali" <ArifAli@discussions.microsoft.com> wrote in message
news:FF77E4EE-F9AA-494E-B8D5-0FDCDC7B2876@microsoft.com...
> OK Guys, I give up. I hope you still have your notifications on for this
> posting! I was hesitant to post my code in the fear that it would be too
> messy (embarassing) or would require too much explanation. But at present
I'm
> stumped. I'm obviously missing the point on this qualification issue so
here
> goes. The general idea is that my "Quoter" workbook consists of:
>
> 1. A sheet called QuoteForm
> 2. A sheet called OrderEntry Form
> 3. Several Sheets with catalog items: part numbers, descriptions, prices;
> one on each row
> 4. There is a navigation toolbar (userform) to move to different sheets
>
> The user simply double clicks on the catalog items to be added to the
quote,
> and they appear on the quote form and orderentry form. (For now this code
is
> not included). When all items have been added, the user then saves the
> quoteform and/or order entry forms to a separate workbook. There are
> separate buttons that launch each of these options. We'll get to that
part a
> little later.
>
> Remember the problem is that each time I open and subsequently close a
> workbook, the VBAProject says in memory. The only VBAProject that should
stay
> in memory throughout the process is the main workbook that contains the
code
> below.
>
> Public QSPath As String
> Public oExcel As Excel.Application
> Public QS, Quoter, NewBook As Excel.Workbook
> Public ParentSheet, FactorySuite, QuoteForm, OrderEntry, NewQuoteForm, _
> NewOrderEntry As Excel.Worksheet
>
> Private Sub Workbook_Open()
>
> 'first define and assign global variables
> Dim QuoteSummaryExists As Boolean
>
> Set oExcel = GetObject(, "Excel.Application")
> Set Quoter = oExcel.ActiveWorkbook
> Set QuoteForm = Quoter.Sheets("QuoteForm")
> Set OrderEntry = Quoter.Sheets("OrderEntry")
> Set FactorySuite = Quoter.Sheets("FactorySuite")
> ThisWorkbook.Bookname = Quoter.Name ' Save name of activeworkbook
>
> Set fs = CreateObject("Scripting.FileSystemObject")
>
> homedrive = Environ("HOMEDRIVE") 'Capture Drive Letter
>
> Homepath = Environ("HOMEPATH") 'Capture MyDocuments Path
> Homepath = Homepath & "\My Documents\"
>
> QSPath = homedrive & Homepath ' Set QuoteSumary Path
>
> QuoteSummaryExists = fs.fileexists(QSPath & "QuoteSummary.xls")
> 'Check for existence of QuoteSummary
>
> If Not QuoteSummaryExists Then
> Call CreateQuoteSummary
> End If
>
> Quoter.Activate
> QuoteForm.Activate 'Switch to Quoter Spreadsheet
>
> If (QuoteForm.Range("b24").Value <> "") Or (QuoteForm.Range("c15").Value
<>
> "") Or (QuoteForm.Range("M15").Value <> "") Then
> vbans = vbNo
> vbans = MsgBox("Erase Existing Quote Information?", vbYesNo)
> If vbans = vbYes Then
>
> QuoteForm.Range("m17..r17").Value = CDate((Now()))
>
>
> 'Customer Name
> QuoteForm.Range("C15..I15").Value = ""
> ' Name
> QuoteForm.Range("C16..I16").Value = ""
> 'Address
> QuoteForm.Range("C17..I17").Value = ""
> 'City
> QuoteForm.Range("C18..E18").Value = ""
> 'State
> QuoteForm.Range("g18").Value = ""
> 'zip
> QuoteForm.Range("I18").Value = ""
> 'Quote Number
> QuoteForm.Range("m15..r15").Value = ""
> 'Salesperson
> ' QuoteForm.Range("m16..r16").Value = ""
> 'Quote Date
> ' QuoteForm.Range("m17..r17").Value = ""
> 'QuoteBody
> QuoteForm.Range("b24..r44").Value = ""
> End If
> End If
>
>
> oExcel.ActiveWindow.ScrollRow = 1
> QuoteForm.Range("A1").Value = ""
> QuoteForm.Range("B24").Select
> ActiveCell.Value = ""
>
> 'ActiveWorkbook.Sheets("FactorySuite").Activate
> FactorySuite.Range("A1").Select
>
> UserForm1.MultiPage1.Style = fmTabStyleTabs
>
> UserForm1.Show 0
> UserForm1.Left = 560
> UserForm1.Top = 30
>
> Set fs = Nothing
>
> End Sub
>
> If the quote summary does not exist, create it!
>
> Private Sub CreateQuoteSummary()
> Dim NewBook As Excel.Workbook
>
> oExcel.ScreenUpdating = False
>
> Set NewBook = oExcel.Workbooks.Add
>
> NewBook.Activate
>
> NewBook.Sheets("Sheet1").Activate
> NewBook.Sheets("Sheet1").Range("A1").Value = "Salesman"
> NewBook.Sheets("Sheet1").Range("B1").Value = "Quote Date"
> NewBook.Sheets("Sheet1").Range("C1").Value = "Customer"
> NewBook.Sheets("Sheet1").Range("D1").Value = "Quote Num"
> NewBook.Sheets("Sheet1").Range("E1").Value = "Quote Amt"
> NewBook.Sheets("Sheet1").Range("F1").Value = "FileName"
>
> NewBook.Sheets("Sheet1").Range("a1").Select
> NewBook.ActiveSheet.Name = "Quotes"
> NewBook.SaveAs (QSPath & "QuoteSummary.xls")
> NewBook.Close savechanges:=False
> oExcel.ScreenUpdating = True
> Set NewBook = Nothing
> End Sub
>
>
> From then on, as this spreadsheet gets populated in subsequent runs of the
> tool, there is a button click event that calls the following routine.
This
> routine opens the quote summary, populates a listbox and then closes the
> spreadsheet.
>
> Public Sub InitQuoteSummaryWindow()
>
> oExcel.ScreenUpdating = False
>
> Set QS = oExcel.Workbooks.Add(QSPath & "QuoteSummary.xls")
> QS.Sheets("quotes").Activate
> QS.ActiveSheet.Range("A1").Select
> i = 0
> Do While ActiveCell.Value <> ""
> i = i + 1
> ActiveCell.Offset(1, 0).Select
> Loop
>
> frmQuoteSummary.ListBox1.Font.Name = "Arial"
> frmQuoteSummary.ListBox1.Font.Size = 10
> frmQuoteSummary.ListBox1.ColumnCount = 6
> QS.Activate
> frmQuoteSummary.ListBox1.ColumnHeads = False
> frmQuoteSummary.ListBox1.RowSource = "a1:f" & CStr(i)
> frmQuoteSummary.ListBox1.MultiSelect = fmMultiSelectSingle
> frmQuoteSummary.ListBox1.ColumnWidths = "72;108;108;108;96;96"
> frmQuoteSummary.ListBox1.TextAlign = fmTextAlignLeft
>
> oExcel.ScreenUpdating = True
>
> QS.Close
> Set QS = Nothing
>
> End Sub
>
> Now lets look at how quotes and order entryforms are generated and saved.
> Here is the button click event one for SaveNewOrderEntry. Note that this
> event exists in a separate module and therefore includes the prefix
> Thisworkbook. before the global variables like QuoteForm, etc. (I have
not
> included the code for the two other varioations: SaveQuoteForm and
SaveBoth.
> If we cant solve the issue looking at the code above, we can look at those
> too.)
>
> Private Sub btnSaveNewOE_Click()
>
> Set ThisWorkbook.NewBook = ThisWorkbook.oExcel.Workbooks.Add
>
> ThisWorkbook.OrderEntry.Copy
Before:=ThisWorkbook.NewBook.Sheets("Sheet1")
>
> ThisWorkbook.NewBook.Activate
>
> fname =
> ThisWorkbook.oExcel.GetSaveAsFilename(InitialFileName:=(Range("c9").Value)
&
> " " & Format(Now(), "mmddyy"))
> If fname <> False Then
> If Right(fname, 1) = "." Then
> fname = Left(fname, Len(fname) - 1)
> End If
>
> If Right(fname, 3) = "xls" Then
>
> ThisWorkbook.NewBook.SaveAs Filename:=fname
>
> Else
>
> ThisWorkbook.NewBook.SaveAs Filename:=fname & ".xls"
>
> End If
>
> ThisWorkbook.NewBook.Close savechanges:=False
>
> Else
>
> ThisWorkbook.NewBook.Close savechanges:=False
>
> End If
> Set ThisWorkbook.NewBook = Nothing
> End Sub
>
>
>
> Thanks in advance!!!!
>

Yes all the code is in the workbook. I only added the oExcel as a result of
the problems I was having.

Although the "Set oExcel = nothing" is not shown (I just recently added it
to the workbook "BeforeClose" event) remember that many workbooks get opened
and closed during the use of this application. Those are the ones I am
seeing stuck in memory.

In other words, does it really matter that I am not setting oExcel to
Nothing, if the problems that I am seeing occur before I expect to release
oExcel? Would it help for me to clarify further the operation of my book?

I just posted a response to this but it didnt show up yet so, first:

1. I only added the oExcel because of the problems I was having.
2. I destroy oExcel in the workbook beforeclose event.
3. Long before oExcel is nullified, there are several other books that are
created and/or opened and closed.

I cant see why this will have any effect on events that take place before I
am done with oExcel.

HOWEVER, I did take out the ref's to oExcel just now, and tested only the
"InitQuoteSummary" function -- the QuoteSummary workbooks VBAProject stays in
memory as QuoteSummary1. Even though Workbooks.Count = 1, i.e., the master
project.

Arif


PS: Can you llok at Michael Beckinsales issue labelled: Repost: Excel not
Active. I am afraid my intuition (i.e. as reflected in my response!) may
have been wrong there. Especially since I am grappling with this current
issue. Thanks