Run-time error '7': Out of memory

Question

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

Answer 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.

Answer 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.