vba 运行时错误“7”：内存不足
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18277514/
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
Run-time error '7': Out of memory
提问by Goshawk
I'm trying to edit embedded charts in Word documents. My source code is below. It has worked a long time but not for the last two days. I get this error:
我正在尝试编辑 Word 文档中的嵌入式图表。我的源代码如下。它已经工作了很长时间，但不是最近两天。我收到此错误：
Run-time error '7': Out of memory
运行时错误“7”：内存不足
I have searched a lot , but I don't understand the problem. When I shutdown computer and after open it, then it works correctly, but after I get error again.
我搜索了很多，但我不明白问题所在。当我关闭计算机并打开它后，它可以正常工作，但是在我再次出错之后。
It gives error in this part:
它在这部分给出了错误：
'create range with Cell
Set oChart = oInShapes.Chart
oChart.ChartData.Activate ' ***Note: It gives error here***
'Set oWorkbook = oChart.ChartData.Workbook
Set oWorksheet = oChart.ChartData.Workbook.Worksheets("Tabelle1")
Set oRange = oWorksheet.Range(Cell)
Public Sub updatechart(Doc As word.Application, ChartName As String, ChartTitle As String, Cell As String, data As String)`
Dim oInShapes As word.InlineShape
Dim oChart As word.Chart
Dim oWorksheet As Excel.Worksheet
'Dim oWorkbook As Excel.Workbook
Dim columnArray() As String
Dim rowArray() As String
Dim oRange As Range
Dim i As Integer
Dim j As Integer
For Each oInShapes In Doc.ActiveDocument.InlineShapes
' Check Shape type and Chart Title
If oInShapes.HasChart Then
'create range with Cell
Set oChart = oInShapes.Chart
oChart.ChartData.Activate ' ***Note: It gives error here***
'Set oWorkbook = oChart.ChartData.Workbook
Set oWorksheet = oChart.ChartData.Workbook.Worksheets("Tabelle1")
Set oRange = oWorksheet.Range(Cell)
' Commet for debug
'oWorksheet.Range("B33") = (ChartTitle & 33)
' Split text
columnArray = Split(data, SeperateChar)
For i = LBound(columnArray) To UBound(columnArray)
rowArray = Split(Trim(columnArray(i)), " ")
' Set Title. For example; ChartTitle = "XY" ----- Table Titles ----> | XY1 | XY2 | XY2 | ....
' After Set Value | 0,33| 0,1 | 0,46| ....
oRange.Cells(1, i + 1) = ChartTitle & (i + 1)
For j = LBound(rowArray) To UBound(rowArray)
' Set Values
oRange.Cells(j + 2, i + 1) = CDbl(rowArray(j))
Next j
Next i
'oWorkbook.Close
oChart.Refresh
End If
Next
Set oInShapes = Nothing
Set oChart = Nothing
Set oWorksheet = Nothing
'Set oWorkbook = Nothing
Erase rowArray, columnArray
End Sub
回答by RestitutorOrbis
This has happened to me before. I had the same solution, exit excel, free up some memory and try again - and it worked. You may have to shut down other programs while using this. Its literally what it says it is, lack of available memory.
我以前也遇到过这种情况。我有同样的解决方案，退出 excel，释放一些内存，然后再试一次 - 它奏效了。在使用它时，您可能需要关闭其他程序。它的字面意思就是它所说的，缺乏可用内存。
Keep in mind that if you've run other macros that copy information to the clipboard, you will have less RAM freed up to run the macro.
请记住，如果您运行了其他将信息复制到剪贴板的宏，则可以释放更少的 RAM 来运行宏。
Also, are you using 32 or 64 bit Excel - 64 will allow you to use more RAM.
另外，您使用的是 32 位还是 64 位 Excel - 64 位将允许您使用更多 RAM。
回答by Graham Anderson
I notice that you not set oRange to nothing when cleaning up your sub, could it be that this object is using a lot of memory which isn't being released when the sub ends?
我注意到你在清理你的 sub 时没有将 oRange 设置为空，这可能是因为这个对象正在使用大量的内存而在 sub 结束时没有被释放？
回答by user3516026
I had a similar error and finally traced it down to the "For Each" statement. I think it has to do with the memory allocation for the Collection, Doc.ActiveDocument.InlineShapes in your example.
我有一个类似的错误，最后将其追溯到“For Each”语句。我认为这与集合的内存分配有关，在您的示例中为 Doc.ActiveDocument.InlineShapes。
My bad code (PowerPoint to Excel):
我的错误代码（PowerPoint 到 Excel）：
For Each sh In InputBook.Sheets("Exec Sum").Shapes
sh.Visible = False
Next
Set sh = Nothing
My fixed code:
我的固定代码：
For i = 1 To InputBook.Sheets("Exec Sum").Shapes.Count
InputBook.Sheets("Exec Sum").Shapes(i).Visible = False
Next
Avoiding a reference to a collection solved my issue.
避免引用集合解决了我的问题。
回答by hnk
The frequent access to the worksheet can create problems with resource usage. The way to go about this is to fetch data in a single access point, like
频繁访问工作表可能会导致资源使用问题。解决这个问题的方法是在单个访问点中获取数据，例如
Dim V as Variant
V = InputRange
' Now V becomes a m x n array of the cell values in InputRange
' you may manipulate and work with this data and fill all your results in
' OutputV(m,n) variant array
Dim OutputV() as Variant
ReDim OutputV(m,n)
oRange = OutputV
Usually speeds up the code by several hundred times depending on the size of the range and also uses far less resources.
通常根据范围的大小将代码加速数百倍，并且使用的资源也少得多。
