VBA 宏内存泄漏（如何清除变量=
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11375502/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Macro Memory Leak (how to clear variable=
提问by Andrej
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.
我有一个可以抓取一些数据的 VBA 宏脚本。它是使用 MSIE 刮掉它的。我相信 MSIE 是内存泄漏的核心问题。
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.
我做了一个循环，它只生成 1 个 IE 实例并 ping 同一个网站。记忆似乎没有韭菜。
Then I made a loop which always ping a different site and memory usage started increasing with each request.
然后我做了一个循环，它总是 ping 不同的站点，并且每次请求时内存使用量都开始增加。
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.
似乎 IE 的实例正在缓存请求，因此对象越来越大。这只是一个假设。
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!
任何输入都会很棒！
回答by Siddharth Rout
I tested the above code and it was destroying the IE Object correctly. Also in regards to this
我测试了上面的代码，它正确地破坏了 IE 对象。还有关于这个
It seems like the instance of IE is caching the requests so the object is getting bigger. This is just an assumption.
似乎 IE 的实例正在缓存请求，因此对象越来越大。这只是一个假设。
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.
这是 IE 任务管理器的 8 个循环截图。它显示增加，但如果你看到它也会降低它。所以我相信你所看到的不是内存泄漏。
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
快照
