VBA Macro Memory Leak (how to clear variable=

Question

I have a VBA Macro script that scrapes some data. It's scrapes it using MSIE.. I believe the MSIE is the core problem to the memory leakage.

I'm initializing the variable like

Set IE = CreateObject("InternetExplorer.Application")

I made a little test to see how the memory is being used.

I made a loop which makes only 1 instance of IE and pings the same website. Memory doesn't seem to leek.

Then I made a loop which always ping a different site and memory usage started increasing with each request.

I also made a test (I'm posting below) that creates NEW object in every iteration and deletes it on the end. The deleting part doesn't seem to work.

It seems like the instance of IE is caching the requests so the object is getting bigger. This is just an assumption.

Here's the sample code I used to test the leakage.

Do While True
    Dim IE As Object
    Set IE = CreateObject("InternetExplorer.Application")

    IE.Navigate "https://www.google.hr/#hl=hr&gs_nf=1&cp=3&gs_id=8&xhr=t&q=" & Counter
    IE.Visible = True

    Do While IE.readyState <> 4 Or IE.Busy = True
        Application.Wait Now() + TimeValue("00:00:01")
        DoEvents
    Loop

    Application.Wait Now() + TimeValue("00:00:01")
    Counter = Counter + 1
    Range("A" & Counter).Value = "https://www.google.hr/#hl=hr&gs_nf=1&cp=3&gs_id=8&xhr=t&q=" & Counter

    IE.Quit
    Set IE = Nothing
Loop

Any input would be great!

Answer 1

I tested the above code and it was destroying the IE Object correctly. Also in regards to this

It seems like the instance of IE is caching the requests so the object is getting bigger. This is just an assumption.

Yes it sometimes increases it but not always. See screenshot.

This is a screenshot of task manager for IE for 8 loops. It shows an increase but if you see it also brings it down. So I believe what you are seeing is not a memory leak.

EDIT

Here is some code that I had in my databank (I didn't write it) but you can run it to check the memory usage.

Sub Sample()
    Do While True
        Dim IE As Object
        Set IE = CreateObject("InternetExplorer.Application")

        IE.Navigate "https://www.google.hr/#hl=hr&gs_nf=1&cp=3&gs_id=8&xhr=t&q=" & Counter
        IE.Visible = False

        Debug.Print GetProcessMemory("iexplore.exe")

        Do While IE.readyState <> 4 Or IE.Busy = True
            Application.Wait Now() + TimeValue("00:00:01")
            DoEvents
        Loop

        Application.Wait Now() + TimeValue("00:00:01")
        Counter = Counter + 1
        Range("A" & Counter).value = "https://www.google.hr/#hl=hr&gs_nf=1&cp=3&gs_id=8&xhr=t&q=" & Counter

        IE.Quit
        Set IE = Nothing
    Loop
End Sub

Private Function GetProcessMemory(ByVal app_name As String) As String
    Dim Process As Object, dMemory As Double

    For Each Process In GetObject("winmgmts:"). _
    ExecQuery("Select WorkingSetSize from Win32_Process Where Name = '" & app_name & "'")
        dMemory = Process.WorkingSetSize
    Next
    If dMemory > 0 Then
        GetProcessMemory = ResizeKb(dMemory)
    Else
        GetProcessMemory = "0 Bytes"
    End If
End Function

Private Function ResizeKb(ByVal b As Double) As String
    Dim bSize(8) As String, i As Integer
    bSize(0) = "Bytes"
    bSize(1) = "KB" 'Kilobytes
    bSize(2) = "MB" 'Megabytes
    bSize(3) = "GB" 'Gigabytes
    bSize(4) = "TB" 'Terabytes
    bSize(5) = "PB" 'Petabytes
    bSize(6) = "EB" 'Exabytes
    bSize(7) = "ZB" 'Zettabytes
    bSize(8) = "YB" 'Yottabytes
    For i = UBound(bSize) To 0 Step -1
        If b >= (1024 ^ i) Then
            ResizeKb = ThreeNonZeroDigits(b / (1024 ^ _
                i)) & " " & bSize(i)
            Exit For
        End If
    Next
End Function

Private Function ThreeNonZeroDigits(ByVal value As Double) As Double
    If value >= 100 Then
        ThreeNonZeroDigits = FormatNumber(value)
    ElseIf value >= 10 Then
        ThreeNonZeroDigits = FormatNumber(value, 1)
    Else
        ThreeNonZeroDigits = FormatNumber(value, 2)
    End If
End Function

SNAPSHOT