VBA 宏内存泄漏(如何清除变量=

Question

我有一个 VBA 宏脚本可以抓取一些数据。它是使用 MSIE 抓取的。我相信 MSIE 是内存泄漏的核心问题。

我正在像这样初始化变量

Set IE = CreateObject("InternetExplorer.Application")

我做了一个小测试，看看内存是如何使用的。

我做了一个循环，它只生成 1 个 IE 实例并 ping 同一个网站。内存似乎不会泄漏。

然后我做了一个循环，它总是 ping 一个不同的站点，并且内存使用量开始随着每个请求而增加。

我还做了一个测试(我在下面发布)在每次迭代中创建新对象并在最后删除它。删除部分似乎不起作用。

似乎 IE 的实例正在缓存请求，因此对象变得越来越大。这只是一个假设。

这是我用来测试泄漏的示例代码。

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

任何输入都会很棒!

Answer 1

我测试了上面的代码，它正确地销毁了 IE 对象。还有关于这个

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

是的，它有时会增加但并非总是如此。见截图。

这是 8 个循环的 IE 任务管理器的屏幕截图。它显示增加，但如果你看到它也会降低它。所以我相信您看到的不是内存泄漏。

编辑

这是我数据库中的一些代码(我没有编写)，但您可以运行它来检查内存使用情况。

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

快照