Excel.exe stays in memory after macro finishes

Question

I'm using VBA automation from an Access 2010 to create charts using Excel 2010. My code works fine, but leaves an orphan Excel.exe process running. I have traced the problem to a call to an Excel VBA function in a separate module. If I eliminate the call to this function, the Excel process is closed when the codes finishes. The function is Chip Pearson's FindAll with modifications to get it to run in Access, such as declaring ranges as Excel.Range and explicitly referencing the Union method as Excel.Application.Union. How do I need to modify this function so that is does not leave an orphaned Excel process running?

The following code binds the Excel application in the main procedure:

Dim oApp As New excel.Application
Dim oBook As excel.Workbook
Dim oSheet As excel.Worksheet

Set oBook = oApp.Workbooks.Add
Set oSheet = oBook.Worksheets(1)

Then, at the end:

oBook.Close False
oApp.Quit

Set oSheet = Nothing
Set oBook = Nothing
Set oApp = Nothing

And I am calling FindAll using this line:

      b = FindAll(oSheet.Range("a2", oSheet.Range("a2").End(xlDown)), strWellID).Rows.Count

The FindAll function is below:

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' modFindAll
' By Chip Pearson, chip@cpearson.com. www.cpearson.com
' 24-October-2007
' This module is described at www.cpearson.com/Excel/FindAll.aspx
' Requires Excel 2000 or later.
'
' This module contains two functions, FindAll and FindAllOnWorksheets that are use
' to find values on a worksheet or multiple worksheets.
'
' FindAll searches a range and returns a range containing the cells in which the
'   searched for text was found. If the string was not found, it returns Nothing.

' FindAllOnWorksheets searches the same range on one or more workshets. It return
'   an array of ranges, each of which is the range on that worksheet in which the
'   value was found. If the value was not found on a worksheet, that worksheet's
'   element in the returned array will be Nothing.
'
' In both functions, the parameters that control the search have the same meaning
' and effect as they do in the Range.Find method.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Function FindAll(SearchRange As Excel.Range, _
                FindWhat As Variant, _
                Optional LookIn As XlFindLookIn = xlValues, _
                Optional LookAt As XlLookAt = xlWhole, _
                Optional SearchOrder As XlSearchOrder = xlByRows, _
                Optional MatchCase As Boolean = False) As Range
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' FindAll
    ' This searches the range specified by SearchRange and returns a Range object
    ' that contains all the cells in which FindWhat was found. The search parameters to
    ' this function have the same meaning and effect as they do with the
    ' Range.Find method. If the value was not found, the function return Nothing.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    Dim FoundCell As Excel.Range
    Dim FirstFound As Excel.Range
    Dim LastCell As Excel.Range
    Dim ResultRange As Excel.Range

    With SearchRange
        Set LastCell = .Cells(.Cells.Count)
    End With

    'On Error Resume Next
    On Error GoTo 0

    Set FoundCell = SearchRange.Find(What:=FindWhat, _
            after:=LastCell, _
            LookIn:=LookIn, _
            LookAt:=LookAt, _
            SearchOrder:=SearchOrder, _
            MatchCase:=MatchCase)

    If Not FoundCell Is Nothing Then

        Set FirstFound = FoundCell
        Set ResultRange = FoundCell
        Set FoundCell = SearchRange.FindNext(after:=FoundCell)

        Do Until False ' Loop forever. We'll "Exit Do" when necessary.
            If (FoundCell Is Nothing) Then
                Exit Do
            End If

            If (FoundCell.Address = FirstFound.Address) Then
                Exit Do
            End If

            Set ResultRange = Excel.Application.Union(ResultRange, FoundCell)
            Set FoundCell = SearchRange.FindNext(after:=FoundCell)
        Loop
    End If

    Set FindAll = ResultRange

    'added below

    Set ResultRange = Nothing
    Set FoundCell = Nothing
    Set FirstFound = Nothing
    Set LastCell = Nothing
    Set SearchRange = Nothing
End Function

Before passing `SearchRange` to the above function, how are you binding with the Excel application? The reason Excel leaves an instance behind is because you are not `Flushing the Toilet` ;) You have to quit the Excel Application and set it to nothing after the above function is run. — Siddharth Rout, Jun 25 '12 at 16:05
When the main procedure finishes, I do have the following to flush the toilet. oBook.Close False oApp.Quit Set oSheet = Nothing Set oBook = Nothing Set oApp = Nothing — Jeff, Jun 25 '12 at 17:42
Try this. Replace `Excel.Application.Union(ResultRange, FoundCell)` with `oApp.Union(ResultRange, FoundCell)` and try it... — Siddharth Rout, Jun 25 '12 at 17:49
That gives a runtime error 424 "Object Required" when it hits the Union line. — Jeff, Jun 25 '12 at 17:54
Try this... change `Function FindAll(SearchRange As Excel.Range, _ FindWhat As Variant,.....` to `Function FindAll(oAp as Excel.Application, SearchRange As Excel.Range, _ FindWhat As Variant, ....` and then use the function as `b = FindAll(oApp, oSheet.Range("a2",....` if this still doesn't work, then I will write one small sample to test it :) — Siddharth Rout, Jun 25 '12 at 17:59
Passing the Excel application to the function as you suggested worked. Thanks for your help! — Jeff, Jun 25 '12 at 18:11
Great! You might want to post the solution as answer and accept it so that it may help anyone who has the same problem? — Siddharth Rout, Jun 25 '12 at 18:12
Will do, but I have to wait 8 hours since I'm a new registered user! — Jeff, Jun 25 '12 at 18:20
@SiddharthRout `You have to quit the Excel Application and set it to nothing after the above function is run` how is that possible when the code has `Dim ... As New` in it? `oApp` will keep getting reinstantiated. — JimmyPena, Jun 26 '12 at 13:18

score 0 · Answer 1 · edited May 23 '17 at 12:21

Jimmy Pena and Siddharth Rout provide excellent comments as to how help remove the phantom excel process.

I only wanted to add that while I would not necessarily recommend it in all cases, if it is a persistent issue and you are looking for a quick "fix" while you troubleshoot, you can brute-force the close, as noted in this SO question: VBA script to close every instance of Excel except itself

Sub ForceExcelExit()

Dim BruteForce As String

BruteForce = "TASKKILL /F /IM excel.exe"
Shell BruteForce, vbHide

End Sub

Testing reveals a swift if not particularly elegant end to the process.

You can also use an iterative loop to end such processes in a slightly more elegant manner - but still using system process methods, rather than application / application interface methods: HOW TO close Excel instance started by mail merge

While I do not endorse either of these methods as a blanket solution to leaving instances active due to incomplete or insufficient error-checking, code encapsulation or general callback, method use, variable instantiation or other programmatic issues, it is a fix in cases where a fix is needed.

Excel.exe stays in memory after macro finishes

1 Answers1