Excel VBA - 内存不足错误（运行时 7）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3102526/
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
Excel VBA - out of memory error (runtime 7)
提问by masfenix
I am getting a very weird error when using VBA in excel. I am using LotusNotes Automation libraries to loop through a view and write everything to cell.
在 excel 中使用 VBA 时出现一个非常奇怪的错误。我正在使用 LotusNotes 自动化库遍历视图并将所有内容写入单元格。
Here is my code (variables of non-interest are removed as I know they are not causing the problem)
这是我的代码（不感兴趣的变量被删除，因为我知道它们不会导致问题）
Public Function TimeCh()
公共函数 TimeCh()
Set session = New NotesSession 'create a new session (ask for user/pass)
session.Initialize 'initialize the session (allow login)
Set db = session.GetDatabase("HIDDEN") 'Grab the DB
Set view = db.GetView("HIDDEN") 'Get the view
Application.ScreenUpdating = False
'start the loop to go through data
While Not (entry Is Nothing)
Cells(row, 1) = (entry.ColumnValues(4))
Cells(row, 2) = (entry.ColumnValues(0))
Cells(row, 3) = (entry.ColumnValues(26))
Cells(row, 4) = (entry.ColumnValues(27))
Cells(row, 5) = (entry.ColumnValues(22))
Cells(row, 6) = (entry.ColumnValues(20))
Cells(row, 7) = (entry.ColumnValues(29))
Cells(row, 8) = (entry.ColumnValues(31))
Cells(row, 9) = (entry.ColumnValues(30))
Cells(row, 10) = (entry.ColumnValues(8))
Cells(row, 11) = (entry.ColumnValues(7))
Cells(row, 12) = (entry.ColumnValues(21))
Cells(row, 13) = (entry.ColumnValues(19))
Cells(row, 14) = (entry.ColumnValues(24))
Cells(row, 15) = (entry.ColumnValues(25))
Cells(row, 16) = (entry.ColumnValues(32))
Cells(row, 17) = (entry.ColumnValues(28))
Cells(row, 18) = (entry.ColumnValues(9))
Cells(row, 19) = (entry.ColumnValues(12))
Cells(row, 20) = (entry.ColumnValues(11))
Cells(row, 21) = (entry.ColumnValues(23))
Cells(row, 22) = (entry.ColumnValues(10))
Cells(row, 23) = (entry.ColumnValues(2))
Cells(row, 24) = (entry.ColumnValues(33))
Cells(row, 25) = (entry.ColumnValues(1))
Cells(row, 26) = (entry.ColumnValues(13))
Cells(row, 27) = (entry.ColumnValues(5))
Cells(row, 28) = (entry.ColumnValues(14))
Cells(row, 29) = (entry.ColumnValues(6))
Cells(row, 30) = (entry.ColumnValues(18))
Cells(row, 31) = (entry.ColumnValues(16))
Cells(row, 32) = (entry.ColumnValues(3))
Cells(row, 33) = (entry.ColumnValues(15))
Cells(row, 34) = (entry.ColumnValues(17))
Cells(row, 35) = (entry.ColumnValues(34))
row = row + 1
Set entry = vec.GetNextEntry(entry)
Wend
Application.ScreenUpdating = True
End Function
结束函数
So it ALWAYSfails on when row = 1425 and column is 35. It gives a "out of memory" (runtime 7 error). It cant be a hardware thing because the system is very stable (dual core, 2 gig ram). And it always crashes at the same row.
因此，当行 = 1425 且列为 35 时，它总是失败。它给出了“内存不足”（运行时 7 错误）。它不能是硬件，因为系统非常稳定（双核，2 gig ram）。它总是在同一行崩溃。
I did a search,and I found one relevant thread: "Out of Memory" error in Lotus Notes automation from VBA
我进行了搜索，发现了一个相关主题： VBA 中 Lotus Notes 自动化中的“内存不足”错误
Has anyone seen something like this before?
有没有人见过这样的东西？
回答by Ed Schembor
If the code is consistently failing on the same document and the same column in the view, that would lead me to believe there is some data issue with that one document. Can you trap the error and show some unique identifier for the failing document? I would try to do that, and then look in the 35th column of the view for that document and check the value. Perhaps there is a computation in the view column which is returning @Error or similar - and in turn causing the VBA code to throw the memory exception.
如果代码在视图中的同一文档和同一列上始终失败，那将使我相信该文档存在一些数据问题。您能否捕获错误并为失败的文档显示一些唯一标识符？我会尝试这样做，然后查看该文档的视图的第 35 列并检查值。也许在视图列中有一个计算返回 @Error 或类似的 - 进而导致 VBA 代码抛出内存异常。
回答by masfenix
I had the company install Excel 2007 on my computer. Ran the report with no problems at all, so I guess it must be a 2003 internal bug.
我让公司在我的电脑上安装了 Excel 2007。运行报告完全没有问题，所以我猜这一定是 2003 年的内部错误。
回答by PowerUser
Random questions:
随机问题：
What data type is row? If you define row as a double, does it still crash?
Per the link you quoted (thanks, actually. It was my post), if you define session as a static variable, does it still crash?
Even if you don't do #2, do you remember to close your session by the end of the function?
This looks like just a subset of a much larger function. How many times is it called in a row?
行是什么数据类型？如果您将 row 定义为 double ，它还会崩溃吗？
根据您引用的链接（实际上谢谢。这是我的帖子），如果您将 session 定义为静态变量，它还会崩溃吗？
即使您不执行 #2，您是否记得在函数结束时关闭会话？
这看起来只是一个更大函数的一个子集。连续调用多少次？
