Solved

How can we solve when Microsoft Excel 2007 stays in memory

Posted on 2012-04-07
31
644 Views
Last Modified: 2012-04-07
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.
0
Comment
Question by:rayluvs
  • 16
  • 15
31 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 37819644
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
0
 

Author Comment

by:rayluvs
ID: 37819682
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

0
 
LVL 41

Expert Comment

by:dlmille
ID: 37819689
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
0
 

Author Comment

by:rayluvs
ID: 37819715
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
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 37819723
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
0
 

Author Comment

by:rayluvs
ID: 37819921
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.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37819946
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
0
 

Author Comment

by:rayluvs
ID: 37819972
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
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37819975
Keep following the steps I gave you.

Can you post your workbook?

Dave
0
 

Author Comment

by:rayluvs
ID: 37819985
Wasn't authorized to post the workbook.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37819988
just delete the data/sheets, add a blank sheet - need to see what you have in VBA and references, etc.

Dave
0
 

Author Comment

by:rayluvs
ID: 37820002
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)
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37820004
You can just try disabling them for starters.

Dave
0
 

Author Comment

by:rayluvs
ID: 37820036
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
0
 

Author Comment

by:rayluvs
ID: 37820040
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.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 41

Assisted Solution

by:dlmille
dlmille earned 500 total points
ID: 37820043
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
0
 

Author Comment

by:rayluvs
ID: 37820055
But does it happens to you?
0
 
LVL 41

Assisted Solution

by:dlmille
dlmille earned 500 total points
ID: 37820065
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.
0
 

Author Comment

by:rayluvs
ID: 37820075
But did u test it in your PC?  

Does Excel stays in memory?

Did u open directly and from recent documents?
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37820078
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
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37820084
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
0
 

Author Comment

by:rayluvs
ID: 37820098
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?
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37820104
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
0
 

Author Comment

by:rayluvs
ID: 37820250
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?
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37820260
0
 

Author Comment

by:rayluvs
ID: 37820305
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.
0
 
LVL 41

Assisted Solution

by:dlmille
dlmille earned 500 total points
ID: 37820309
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
0
 

Author Comment

by:rayluvs
ID: 37820313
Thanx, will try Office Repair.
0
 

Author Comment

by:rayluvs
ID: 37820342
Didn't work.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37820344
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
0
 

Author Comment

by:rayluvs
ID: 37820361
Thanx
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Most PC repair technicians (if not all) always start their cleanup process by emptying the temp folders before running any removal tools. It makes sense because temp folders are common places for malware installers to lurk and removing all the junk …
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now