Run-time error '7': Out of memory
Solution 1
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.
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.
Also, are you using 32 or 64 bit Excel - 64 will allow you to use more RAM.
Solution 2
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.
My bad code (PowerPoint to 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.
Solution 3
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.
Solution 4
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?
Goshawk
Updated on March 13, 2020Comments
-
Goshawk about 4 years
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:
Run-time error '7': Out of memory
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