How can we solve when Microsoft Excel 2007 stays in memory

When we exit an Excel that has VBA in it, an instance stays in memory and starts taking resources making the PC slow.

How can solve this problem so when the Excel is exited, it removes itself from memory.

Note: only happens with an Excel that has VBA, all others when exited, also exits memory.
rayluvsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dlmilleCommented:
Please post the VBA code.  It has likely either created an instance of Excel, or has kicked off a procedure to run at some point in the future (e.g., via OnTime or other related API).

Post the whole workbook if you can - you can just sanitize the data or even delete all the worksheets but one and delete the data in that, but I need to see your modules and how it all comes together.

Dave
rayluvsAuthor Commented:
Sub UpdateSelectedLinks()
Dim aLinks As Variant
Dim i As Integer
Application.ScreenUpdating = False
aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
On Error Resume Next
If Not IsEmpty(aLinks) Then
    For i = 1 To UBound(aLinks)
        ActiveWorkbook.UpdateLink Name:=aLinks(i), Type:=xlExcelLinks
    Next i
End If
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

Sub RelinkWorkbooks()
Dim aLinks As Variant, vLink As Variant
Dim flPath As String
Dim wb As Workbook
aLinks = ThisWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
    If IsArray(aLinks) Then
        For Each vLink In aLinks
            If Dir(vLink) = "" Then
                flPath = Application.GetSaveAsFilename(vLink)
                If flPath <> "False" Then
                    ThisWorkbook.ChangeLink vLink, flPath, xlExcelLinks
                End If
            End If
        Next
    Else
        If Dir(aLinks) = "" Then
            flPath = Application.GetSaveAsFilename(vLink)
            If flPath <> "False" Then
                ThisWorkbook.ChangeLink aLinks, flPath, xlExcelLinks
            End If
        End If
    End If
End If
End Sub

Sub ImportTransFromOutlook()
Dim olkApp As Object
Dim olkns As Object
Dim myfolder As Object
Dim mai As Object
Dim olMailItems As Object
Dim strFilter As String
Dim sh As Worksheet
Dim arr As Variant
Dim ln As Variant
Const olFolderInbox As Integer = 6
    
    Set sh = ThisWorkbook.Sheets("Ficohsa (HN LPS) a618 c5991")
    Set olkApp = CreateObject("Outlook.Application")
    Set olkns = olkApp.GetNamespace("MAPI")
    Set myfolder = olkns.GetDefaultFolder(olFolderInbox)
    strFilter = "[subject] = " & "VISA" 'here we set the condition that SUBJETC has to be VISA in order to proceed
    Set olMailItems = myfolder.items.Restrict(strFilter)
    With sh
        For Each mai In olMailItems
            With sh.Range("B" & sh.Range("B" & sh.Rows.Count).End(xlUp).Row).Offset(1, 0)
                .Value = Format(mai.receivedtime, "dd mmm yyyy") 'Transaction Date
                arr = Split(mai.body, vbCrLf) 'split body to be line by line using vbCrLf
                .Offset(0, 2).Value = arr(0)  'Transaction Main type CARD or ATH
                .Offset(0, 3).Value = arr(1)  'Type, ex: PROFF, MEAL, etc.
                .Offset(0, 4).Value = arr(2)  'Transaction description
               'Begin to read the rest of the body and find the value of the trans by looking for Currency "L."
                For Each ln In arr
                    located = InStr(ln, "L.")
                    If located <> 0 Then
                       Length = Len(ln)
                       lvalue = Mid(ln, located + 2, Length - (located + 2)) 'Set the value to export
                      'The next 2 if/then is if incase the value has more than one".", like the 1000 values usually does
                      'These If/Then will delete thos "." so it can export the correct value
                       If Right(lvalue, 1) = "." Then
                          lvalue = Left(lvalue, Len(lvalue) - 1)
                            If Right(lvalue, 1) = "." Then
                               lvalue = Left(lvalue, Len(lvalue) - 1)
                               End If
                          End If
                        .Offset(0, 6).Value = lvalue
                    End If
                Next
            End With
        Next
    End With
    
Set olMailItems = Nothing
Set olkns = Nothing
Set olkApp = Nothing
Set myfolder = Nothing
 
End Sub

Open in new window

dlmilleCommented:
Is this the only code you have in the Excel workbook?

Does Excel terminate properly if you DON'T run these macros?

Does it stay in memory if you DO run the ImportTransFromOutlook() macro?

Do you have this problem if you do the same thing on another machine?

Dave
Cloud as a Security Delivery Platform for MSSPs

Every Managed Security Service Provider (MSSP) needs a platform to deliver effective and efficient security-as-a-service to their customers. Scale, elasticity and profitability are a few of the many features that a Cloud platform offers. View our on-demand webinar to learn more!

rayluvsAuthor Commented:
Answers:

Is this the only code you have in the Excel workbook?
Yes

Does Excel terminate properly if you DON'T run these macros?
No

Does it stay in memory if you DO run the ImportTransFromOutlook() macro?
No (ok here)

Do you have this problem if you do the same thing on another machine?
No
dlmilleCommented:
Ok - let's do a couple things, as it may not be this workbook or macro that causes the problem.

Do you have some add-ins on your Excel installation?  Let's disable all those, then restart Excel, load the workbook, and see if when we exit Excel, it clears memory properly.

If it does, you'll have to add your add-ins back one by one to see which is the culprit.

If that doesn't fix it, then do an Office Detect and Repair.

But first, try the disable add-in approach.

Dave

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rayluvsAuthor Commented:
This only happens with the Excel with VBA.  All other excel when exiting, also exits the memory as it should.

Nevertheless, let's try.  Where do I disable add-in.
dlmilleCommented:
Does this problem happen when you run another workbook that has VBA as well?  Any workbook?  If so, then these steps are very important as there's an Excel problem, not a VBA problem.

-------------- Steps:
1.  Uninstall add-ins:  http://office.microsoft.com/en-us/excel-help/load-or-unload-add-in-programs-HP010096834.aspx

2.  If that doesn't work, then do the Office Detect and repair: http://www.howtogeek.com/howto/microsoft-office/detect-and-repair-applications-in-microsoft-office-2007/

3.  If that doesn't work, I recommend uninstalling and re-installing office.  But, let's proceed one step at a time.

Dave
rayluvsAuthor Commented:
That is the only excel with VBA.  But to test it, we created a simple VBA in a Book and saved.  Enter and exit and ok; didnt stay in memory.

This is the VBA:
Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveCell.FormulaR1C1 = "123"
    Range("I11").Select
    ActiveCell.FormulaR1C1 = "1231"
    Range("I12").Select
    ActiveCell.FormulaR1C1 = "2312"
    Range("I13").Select
    ActiveCell.FormulaR1C1 = "3123"
    Range("I14").Select
    ActiveCell.FormulaR1C1 = "123"
    Range("I15").Select
    ActiveCell.FormulaR1C1 = "12"
    Range("I16").Select
    ActiveCell.FormulaR1C1 = "31"
    Range("I17").Select
    ActiveCell.FormulaR1C1 = "23"
    Range("I18").Select
    ActiveCell.FormulaR1C1 = "123"
    Range("I19").Select
    ActiveCell.FormulaR1C1 = "123"
    Range("I20").Select
    ActiveCell.FormulaR1C1 = "1231"
    Range("I21").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("I22").Select
End Sub

Open in new window


Also noticed that in the Excel with the problem when entering and quickly exiting, it always ask if we want  to save, like it does an update to the Excel.

Also noticed that with all Excel,current, new, VBA, no VBA, when entering Excel, it waits a bit until we can start pressing keys.

Strange
dlmilleCommented:
Keep following the steps I gave you.

Can you post your workbook?

Dave
rayluvsAuthor Commented:
Wasn't authorized to post the workbook.
dlmilleCommented:
just delete the data/sheets, add a blank sheet - need to see what you have in VBA and references, etc.

Dave
rayluvsAuthor Commented:
As for add-in, the links indicate to uninstall.  Can we disable them or should we uninstall them (see add-ins pic):

addin
(I don't want damage anything)
dlmilleCommented:
You can just try disabling them for starters.

Dave
rayluvsAuthor Commented:
The curious thing is when preparing your sample excel, we first deleted all data and left the sheets, exited and it worked.  So can it be DATA sensitive? Something in the data, a calculation or format of a cell that can be causing this?

Anyways, here is the file the sheets deleted.
ExcelVBA.xlsm
rayluvsAuthor Commented:
A more curious thing.  If I open excel in blank and then open the ExcelVBA via "Recent Document", all is ok and it exit from memory.  But if we open the Excel by clicking it directly, it stays in memory.

Please advice.
dlmilleCommented:
I think now you need to do the Office Detect and repair.

If that doesn't fix it, then I encourage an uninstall and reinstall.

Dave
rayluvsAuthor Commented:
But does it happens to you?
dlmilleCommented:
I've seen it in other posts I've participated in.  The steps for diagnosis usually are to determine if add-ins are the problem, then detect and repair, then finally uninstall/reinstall.

One other thing you might check first, login as another user and see if the same occurs.  The user profile you're using could be messed up.  If that succeeds - then you can create a new profile, dumping that one.  otherwise, add-ins, detect and repair, uninstall/reinstal are generally steps that lead to success.
rayluvsAuthor Commented:
But did u test it in your PC?  

Does Excel stays in memory?

Did u open directly and from recent documents?
dlmilleCommented:
Sorry - I didn't see the file post.  Yes, I loaded it by using File->Open, then exited excel.  I also ran it by double clicking the file in my folder, then exited Excel.  Task manager all clear of Excel.

See my last post - check another profile and see if that works, otherwise continue down the path.

Dave
dlmilleCommented:
Sorry Ramante - I tested your file, but on re-reading your post, you asked some questions that I didn't answer.

Do you have any controls on your worksheets?  E.g., any buttons or dropdown combo boxes, listboxes, images?  They could be causing a corruption, or your workbook itself may be corrupt.

Let's work this path for a moment...

Dave
rayluvsAuthor Commented:
No, no buttons or drop-down, only the VBA I sent you.

I did test the file in another user within the same user and it didnt get stuck.  So it can be the user profile?
dlmilleCommented:
The problem with using recent files versus other methods resonates with a profile problem potentially

The fact you run fine with another user appears to confirm that

Creating a new profile for the problem user profile now appears to be in order

Save
rayluvsAuthor Commented:
Yeah but what does that mean? What are this steps to follow to change user-profile?  There has to be another way to go about without doing all that process of changing user-profile; is there?
rayluvsAuthor Commented:
It seems like a long process.  The PC has over 16gb of data and over 10 installed apps that the user uses on a daily bases.  Beside fixing the Excel problem, will this process damage any of the current installation?

Sorry for the constant concern.  Is that the user is very picky and if we go thru the process and the user cant access non of his apps, will have trouble.
dlmilleCommented:
I understand.

Have you tried the other options on the off chance that they might repair the problem?  Office detect and repair and/or re-install of Office?  While it looks like the profile perhaps the linkage that is broken might get repaired via this approach.

Worth a try perhaps?

Have you backed up the user's PC before?  when did the problem start?  Is it possible to roll back to a prior version?

Did a new application get installed that perhaps affected the error, and perhaps a system restore back to a previous date might correct the problem?

I think everything would point back to recreating the profile, but I would try these things first, given the sensitivity of the issue.

Dave
rayluvsAuthor Commented:
Thanx, will try Office Repair.
rayluvsAuthor Commented:
Didn't work.
dlmilleCommented:
Ok - then re install Office.  I just did that at home today as a matter of fact - it doesn't take that long.

Barring that - recreating the user profile may be the last resort.  Probably will need to be, but I'll hold out hope on the office re install.

Dave
rayluvsAuthor Commented:
Thanx
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Anti-Spyware

From novice to tech pro — start learning today.