Run-time error '7' Out of memory Excel 2007

[MrPogle]

I have seen this error referred to many times in forums but they all seem to involve multiple, large workbooks and moving data between them. I get this problem with a single (though quite large: ~60 Mega Bytes) workbook.

My code is below. It just takes the ChartTitle, does some minor string manipulation and then makes the ChartName equal to it. It fails on the line:

.Name = str_ChartName

Here's my code:

Sub NameCharts()
Dim int_ChartCounter As Integer, str_ChartName As String, str_LastChar As String
Dim str_ChartTitle As String, int_TitleCounter As Integer, str_Char As String
Dim bln_NotChar As Boolean, int_CharNum As Integer
For int_ChartCounter = 1 To ActiveSheet.ChartObjects.Count

ActiveSheet.ChartObjects(int_ChartCounter).Select
With ActiveChart
str_ChartName = .Name
str_LastChar = Right(str_ChartName, 1)
If Not (str_LastChar = "W" Or str_LastChar = "F" Or str_LastChar = "P" Or str_LastChar = "S") And .HasTitle = True Then
str_ChartTitle = .ChartTitle.Text
str_ChartName = ""
'Debug.Print str_ChartTitle
For int_TitleCounter = 1 To Len(str_ChartTitle)
str_Char = Mid(str_ChartTitle, int_TitleCounter, 1)
int_CharNum = Asc(str_Char)
If Not ((int_CharNum >= 65 And int_CharNum <= 90) _
Or (int_CharNum >= 97 And int_CharNum <= 122) _
Or (int_CharNum >= 48 And int_CharNum <= 57)) Then
bln_NotChar = True
Else
If bln_NotChar = True And int_CharNum >= 97 Then
int_CharNum = int_CharNum - 32
str_Char = Chr(int_CharNum)
End If
bln_NotChar = False
str_ChartName = str_ChartName & str_Char
End If
Next
.Name = str_ChartName '***FAILS HERE***
End If
End With
Next
End Sub

 

[Ruddles]

What's the value of str_ChartName when that happens?

 

[shg]

What is the code supposed to do other than convert the name to the uppercase value of the chart title?

 

[xenou]

Since your name assignment comes after the loop (after the last next) it is likely you are trying to name the chart to a name that has been assigned to the previous chart already - and they cannot have the same name -- I would re-examine the structure of the loop and make sure all actions applying to charts take place inside the main loop through all the charts.

ξ

 

[shg]

Doesn't this do about the same thing?

Sub NameCharts()
    Dim oCO         As ChartObject
 
    For Each oCO In ActiveSheet.ChartObjects
        If oCO.Chart.HasTitle And InStr(1, "WFPS", Right(oCO.Name, 1), vbBinaryCompare) = 0 Then
            oCO.Name = UCase(oCO.Chart.ChartTitle.Text)
        End If
    Next oCO
End Sub