Excel VBA 2003 Out of Memory Error

[craffel]

I am receiving a run-time error #7 "Out of memory" as I am adding to cells of a worksheet from a collection of custom objects i have created. I have gone through a tone of documentation and cannot resolve the situation.

What I am adding to the worksheet is formula's with references to another worksheet in the workbook. The worksheet being referenced has a long name "Current Forecast By LTD" which is appended to each cell referenced like so "'Current Forecast By LTD'!A1".

If I remove the worksheet name (to test) and add the same cells to my first worksheet (it of course is referencing the incorrect cells) it works. I think I am running into a size limit on the worksheet being appended to. What I would like to do is increase the amount of memory that I can use with a worksheet such that I am able to reference my original worksheek, or any other fix. The following is my code

Function GenerateTATD(cs As String, ts As String, c As Collection)
    
    '
    ' Iterate over TATD's in Collection 'c' and create
    ' "Forecast by TATD mpr" worksheet
    '
    ' Init
    Dim tSheet As Worksheet, cSheet As Worksheet, tmp As CTATD, _
    startIndex As Integer, fubar As Variant, y As Integer
    
    ' Define
    Set tSheet = Worksheets(ts)
    Set cSheet = Worksheets(cs)
    y = cSheet.Cells(1, 1)
    Set cSheet = Nothing
    startIndex = 1
    
    ' For each TATD
    For Each tmp In c
        
        
        ' Add headers
        tSheet.Cells(startIndex, 1) = "TATD " & tmp.Name & " Forecasted Expenditures"
        tSheet.Cells(startIndex + 1, 1) = "Forecast Month"
        tSheet.Cells(startIndex + 1, 2) = "Allocated FY " & CStr(y) & "/" & CStr(y + 1) & " ROM"
        tSheet.Cells(startIndex + 1, 3) = "Apr"
        tSheet.Cells(startIndex + 1, 4) = "May"
        tSheet.Cells(startIndex + 1, 5) = "Jun"
        tSheet.Cells(startIndex + 1, 6) = "Jul"
        tSheet.Cells(startIndex + 1, 7) = "Aug"
        tSheet.Cells(startIndex + 1, 8) = "Sep"
        tSheet.Cells(startIndex + 1, 9) = "Oct"
        tSheet.Cells(startIndex + 1, 10) = "Nov"
        tSheet.Cells(startIndex + 1, 11) = "Dec"
        tSheet.Cells(startIndex + 1, 12) = "Jan"
        tSheet.Cells(startIndex + 1, 13) = "Feb"
        tSheet.Cells(startIndex + 1, 14) = "Mar"
        tSheet.Cells(startIndex + 1, 15) = "Forecast FY " & CStr(y) & "/" & CStr(y + 1) & " ROM"
        
        ' Add Previous
        tSheet.Cells(startIndex + 2, 1) = "Previous"
        tSheet.Cells(startIndex + 2, 2) = tmp.AllocatedRom(2)
        tSheet.Cells(startIndex + 2, 3) = tmp.April(2)
        tSheet.Cells(startIndex + 2, 4) = tmp.May(2)
        tSheet.Cells(startIndex + 2, 5) = tmp.June(2)
        tSheet.Cells(startIndex + 2, 6) = tmp.July(2)
        tSheet.Cells(startIndex + 2, 7) = tmp.August(2)
        tSheet.Cells(startIndex + 2, 8) = tmp.September(2)
        tSheet.Cells(startIndex + 2, 9) = tmp.October(2)
        tSheet.Cells(startIndex + 2, 10) = tmp.November(2)
        tSheet.Cells(startIndex + 2, 11) = tmp.December(2)
        tSheet.Cells(startIndex + 2, 12) = tmp.January(2)
        tSheet.Cells(startIndex + 2, 13) = tmp.February(2)
        tSheet.Cells(startIndex + 2, 14) = tmp.March(2)
        tSheet.Cells(startIndex + 2, 15) = "=SUM(C" & CStr(startIndex + 2) & ":N" & CStr(startIndex + 2) & ")"
        
        ' Add current
        tSheet.Cells(startIndex + 3, 1) = "Current"
        tSheet.Cells(startIndex + 3, 2) = tmp.AllocatedRom(1)
        tSheet.Cells(startIndex + 3, 3) = tmp.April(1)
        tSheet.Cells(startIndex + 3, 4) = tmp.May(1)
        tSheet.Cells(startIndex + 3, 5) = tmp.June(1)
        tSheet.Cells(startIndex + 3, 6) = tmp.July(1)
        tSheet.Cells(startIndex + 3, 7) = tmp.August(1)
        tSheet.Cells(startIndex + 3, 8) = tmp.September(1)
        tSheet.Cells(startIndex + 3, 9) = tmp.October(1)
        tSheet.Cells(startIndex + 3, 10) = tmp.November(1)
        tSheet.Cells(startIndex + 3, 11) = tmp.December(1)
        tSheet.Cells(startIndex + 3, 12) = tmp.January(1)
        tSheet.Cells(startIndex + 3, 13) = tmp.February(1)
        tSheet.Cells(startIndex + 3, 14) = tmp.March(1)
        tSheet.Cells(startIndex + 3, 15) = "=SUM(C" & CStr(startIndex + 3) & ":N" & CStr(startIndex + 3) & ")"
        
        ' Add delta
        tSheet.Cells(startIndex + 4, 1) = "Delta"
        tSheet.Cells(startIndex + 4, 2) = "=B" & CStr(startIndex + 2) & "-B" & CStr(startIndex + 3)
        tSheet.Cells(startIndex + 4, 3) = "=C" & CStr(startIndex + 2) & "-C" & CStr(startIndex + 3)
        tSheet.Cells(startIndex + 4, 4) = "=D" & CStr(startIndex + 2) & "-D" & CStr(startIndex + 3)
        tSheet.Cells(startIndex + 4, 5) = "=E" & CStr(startIndex + 2) & "-E" & CStr(startIndex + 3)
        tSheet.Cells(startIndex + 4, 6) = "=F" & CStr(startIndex + 2) & "-F" & CStr(startIndex + 3)
        tSheet.Cells(startIndex + 4, 7) = "=G" & CStr(startIndex + 2) & "-G" & CStr(startIndex + 3)
        tSheet.Cells(startIndex + 4, 8) = "=H" & CStr(startIndex + 2) & "-H" & CStr(startIndex + 3)
        tSheet.Cells(startIndex + 4, 9) = "=I" & CStr(startIndex + 2) & "-I" & CStr(startIndex + 3)
        tSheet.Cells(startIndex + 4, 10) = "=J" & CStr(startIndex + 2) & "-J" & CStr(startIndex + 3)
        tSheet.Cells(startIndex + 4, 11) = "=K" & CStr(startIndex + 2) & "-K" & CStr(startIndex + 3)
        tSheet.Cells(startIndex + 4, 12) = "=L" & CStr(startIndex + 2) & "-L" & CStr(startIndex + 3)
        tSheet.Cells(startIndex + 4, 13) = "=M" & CStr(startIndex + 2) & "-M" & CStr(startIndex + 3)
        tSheet.Cells(startIndex + 4, 14) = "=N" & CStr(startIndex + 2) & "-N" & CStr(startIndex + 3)
        tSheet.Cells(startIndex + 4, 15) = "=SUM(C" & CStr(startIndex + 4) & ":N" & CStr(startIndex + 4) & ")"
        
        ' Add cum
        tSheet.Cells(startIndex + 5, 1) = "Cum"
        tSheet.Cells(startIndex + 5, 2) = "=B" & CStr(startIndex + 3)
        tSheet.Cells(startIndex + 5, 3) = "=C" & CStr(startIndex + 3)
        tSheet.Cells(startIndex + 5, 4) = "=C" & CStr(startIndex + 6) & "+D" & CStr(startIndex + 3)
        tSheet.Cells(startIndex + 5, 5) = "=D" & CStr(startIndex + 6) & "+E" & CStr(startIndex + 3)
        tSheet.Cells(startIndex + 5, 6) = "=E" & CStr(startIndex + 6) & "+F" & CStr(startIndex + 3)
        tSheet.Cells(startIndex + 5, 7) = "=F" & CStr(startIndex + 6) & "+G" & CStr(startIndex + 3)
        tSheet.Cells(startIndex + 5, 8) = "=G" & CStr(startIndex + 6) & "+H" & CStr(startIndex + 3)
        tSheet.Cells(startIndex + 5, 9) = "=H" & CStr(startIndex + 6) & "+I" & CStr(startIndex + 3)
        tSheet.Cells(startIndex + 5, 10) = "=I" & CStr(startIndex + 6) & "+J" & CStr(startIndex + 3)
        tSheet.Cells(startIndex + 5, 11) = "=J" & CStr(startIndex + 6) & "+K" & CStr(startIndex + 3)
        tSheet.Cells(startIndex + 5, 12) = "=K" & CStr(startIndex + 6) & "+L" & CStr(startIndex + 3)
        tSheet.Cells(startIndex + 5, 13) = "=L" & CStr(startIndex + 6) & "+M" & CStr(startIndex + 3)
        tSheet.Cells(startIndex + 5, 14) = "=M" & CStr(startIndex + 6) & "+N" & CStr(startIndex + 3)
        tSheet.Cells(startIndex + 5, 15) = "=O" & CStr(startIndex + 3)
    
        ' Style table
        tSheet.Range("A" & CStr(startIndex) & ":O" & CStr(startIndex)).Merge
        tSheet.Rows(startIndex).Font.Bold = True
        tSheet.Rows(startIndex + 1).Font.Bold = True
        tSheet.Range("A" & CStr(startIndex) & ":O" & CStr(startIndex)).Interior.ColorIndex = 36
        tSheet.Range("A" & CStr(startIndex + 1) & ":O" & CStr(startIndex + 1)).Interior.ColorIndex = 15
        tSheet.Range("A" & CStr(startIndex + 3) & ":O" & CStr(startIndex + 3)).Interior.ColorIndex = 36
        tSheet.Range("A" & CStr(startIndex + 5) & ":O" & CStr(startIndex + 5)).Interior.ColorIndex = 36
        tSheet.Range("A" & CStr(startIndex) & ":O" & CStr(startIndex + 1)).Borders.LineStyle = xlThin
        
        ' Increment start index
        startIndex = startIndex + 8
    Next tmp
    
    ' Style Sheet
    tSheet.Columns("A:O").EntireColumn.AutoFit
    tSheet.Columns("B:O").NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
    
    ' Save workbook, free mem
    Set tSheet = Nothing
    
End Function

P.S. I have gone through the rest of my code and ensured that every 'Set var' has a corresponding 'Set var = Nothing'. I would greatly appreciate any help.

Thanks in advance.

[Oorang]

What line does the error occur on?

[craffel]

The error occurs on this line.

tSheet.Cells(startIndex + 2, 2) = tmp.AllocatedRom(2)

I've tried commenting it out and then the eror occurs on the next line.

tSheet.Cells(startIndex + 3, 3) = tmp.April(1)

[Oorang]

That helps quite a lot then. Next question. Is CTATD as user definied type? If so can you post the it?

[craffel]

Yep CTATD is a user defined Class Module. The following code is what I used to define it:

Private pName As String
Private pJan(1 To 2) As String
Private pFeb(1 To 2) As String
Private pMar(1 To 2) As String
Private pApr(1 To 2) As String
Private pMay(1 To 2) As String
Private pJun(1 To 2) As String
Private pJul(1 To 2) As String
Private pAug(1 To 2) As String
Private pSept(1 To 2) As String
Private pOct(1 To 2) As String
Private pNov(1 To 2) As String
Private pDec(1 To 2) As String
Private pForecastMonth(1 To 2) As String
Private pAllocatedROM(1 To 2) As String
Private pForecastROM(1 To 2) As String
''''''''''''''''''''''
' Name property
''''''''''''''''''''''
Public Property Get Name() As String
    Name = pName
End Property
Public Property Let Name(value As String)
    pName = value
End Property
''''''''''''''''''''''
' January property
''''''''''''''''''''''
Public Property Get January(index As Integer) As String
    January = pJan(index)
End Property
Public Property Let January(index As Integer, value As String)
    pJan(index) = value
End Property
''''''''''''''''''''''
' February property
''''''''''''''''''''''
Public Property Get February(index As Integer) As String
    February = pFeb(index)
End Property
Public Property Let February(index As Integer, value As String)
    pFeb(index) = value
End Property
''''''''''''''''''''''
' March property
''''''''''''''''''''''
Public Property Get March(index As Integer) As String
    March = pMar(index)
End Property
Public Property Let March(index As Integer, value As String)
    pMar(index) = value
End Property
''''''''''''''''''''''
' April property
''''''''''''''''''''''
Public Property Get April(index As Integer) As String
    April = pApr(index)
End Property
Public Property Let April(index As Integer, value As String)
    pApr(index) = value
End Property
''''''''''''''''''''''
' May property
''''''''''''''''''''''
Public Property Get May(index As Integer) As String
    May = pMay(index)
End Property
Public Property Let May(index As Integer, value As String)
    pMay(index) = value
End Property
''''''''''''''''''''''
' June property
''''''''''''''''''''''
Public Property Get June(index As Integer) As String
    June = pJun(index)
End Property
Public Property Let June(index As Integer, value As String)
    pJun(index) = value
End Property
''''''''''''''''''''''
' July property
''''''''''''''''''''''
Public Property Get July(index As Integer) As String
    July = pJul(index)
End Property
Public Property Let July(index As Integer, value As String)
    pJul(index) = value
End Property
''''''''''''''''''''''
' August property
''''''''''''''''''''''
Public Property Get August(index As Integer) As String
    August = pAug(index)
End Property
Public Property Let August(index As Integer, value As String)
    pAug(index) = value
End Property
''''''''''''''''''''''
' September property
''''''''''''''''''''''
Public Property Get September(index As Integer) As String
    September = pSept(index)
End Property
Public Property Let September(index As Integer, value As String)
    pSept(index) = value
End Property
''''''''''''''''''''''
' October property
''''''''''''''''''''''
Public Property Get October(index As Integer) As String
    October = pOct(index)
End Property
Public Property Let October(index As Integer, value As String)
    pOct(index) = value
End Property
''''''''''''''''''''''
' November property
''''''''''''''''''''''
Public Property Get November(index As Integer) As String
    November = pNov(index)
End Property
Public Property Let November(index As Integer, value As String)
    pNov(index) = value
End Property

''''''''''''''''''''''
' December property
''''''''''''''''''''''
Public Property Get December(index As Integer) As String
    December = pDec(index)
End Property
Public Property Let December(index As Integer, value As String)
    pDec(index) = value
End Property

''''''''''''''''''''''
' ForecastMonth property
''''''''''''''''''''''
Public Property Get ForecastMonth(index As Integer) As String
    ForecastMonth = pForecastMonth(index)
End Property
Public Property Let ForecastMonth(index As Integer, value As String)
    pForecastMonth(index) = value
End Property
''''''''''''''''''''''
' Allocated Rom property
''''''''''''''''''''''
Public Property Get AllocatedRom(index As Integer) As String
    AllocatedRom = pAllocatedROM(index)
End Property
Public Property Let AllocatedRom(index As Integer, value As String)
    pAllocatedROM(index) = value
End Property

''''''''''''''''''''''
' Forecast ROM property
''''''''''''''''''''''
Public Property Get ForecastRom(index As Integer) As String
    ForecastRom = pForecastROM(index)
End Property
Public Property Let ForecastRom(index As Integer, value As String)
    pForecastROM(index) = value
End Property

[craffel]

I have tried a quick experiment to determine the length of the strings I was putting in the cells. I have replaced the following code:

' Add Previous
        tSheet.Cells(startIndex + 2, 1) = "Previous"
        tSheet.Cells(startIndex + 2, 2) = tmp.AllocatedRom(2)
        tSheet.Cells(startIndex + 2, 3) = tmp.April(2)
        tSheet.Cells(startIndex + 2, 4) = tmp.May(2)
        tSheet.Cells(startIndex + 2, 5) = tmp.June(2)
        tSheet.Cells(startIndex + 2, 6) = tmp.July(2)
        tSheet.Cells(startIndex + 2, 7) = tmp.August(2)
        tSheet.Cells(startIndex + 2, 8) = tmp.September(2)
        tSheet.Cells(startIndex + 2, 9) = tmp.October(2)
        tSheet.Cells(startIndex + 2, 10) = tmp.November(2)
        tSheet.Cells(startIndex + 2, 11) = tmp.December(2)
        tSheet.Cells(startIndex + 2, 12) = tmp.January(2)
        tSheet.Cells(startIndex + 2, 13) = tmp.February(2)
        tSheet.Cells(startIndex + 2, 14) = tmp.March(2)
        tSheet.Cells(startIndex + 2, 15) = "=SUM(C" & CStr(startIndex + 2) & ":N" & CStr(startIndex + 2) & ")"
        
        ' Add current
        tSheet.Cells(startIndex + 3, 1) = "Current"
        tSheet.Cells(startIndex + 3, 2) = tmp.AllocatedRom(1)
        tSheet.Cells(startIndex + 3, 3) = tmp.April(1)
        tSheet.Cells(startIndex + 3, 4) = tmp.May(1)
        tSheet.Cells(startIndex + 3, 5) = tmp.June(1)
        tSheet.Cells(startIndex + 3, 6) = tmp.July(1)
        tSheet.Cells(startIndex + 3, 7) = tmp.August(1)
        tSheet.Cells(startIndex + 3, 8) = tmp.September(1)
        tSheet.Cells(startIndex + 3, 9) = tmp.October(1)
        tSheet.Cells(startIndex + 3, 10) = tmp.November(1)
        tSheet.Cells(startIndex + 3, 11) = tmp.December(1)
        tSheet.Cells(startIndex + 3, 12) = tmp.January(1)
        tSheet.Cells(startIndex + 3, 13) = tmp.February(1)
        tSheet.Cells(startIndex + 3, 14) = tmp.March(1)
        tSheet.Cells(startIndex + 3, 15) = "=SUM(C" & CStr(startIndex + 3) & ":N" & CStr(startIndex + 3) & ")"

with:

' Add Previous
        tSheet.Cells(startIndex + 2, 1) = "Previous"
        tSheet.Cells(startIndex + 2, 2) = Len(tmp.AllocatedRom(2))
        tSheet.Cells(startIndex + 2, 3) = Len(tmp.April(2))
        tSheet.Cells(startIndex + 2, 4) = Len(tmp.May(2))
        tSheet.Cells(startIndex + 2, 5) = Len(tmp.June(2))
        tSheet.Cells(startIndex + 2, 6) = Len(tmp.July(2))
        tSheet.Cells(startIndex + 2, 7) = Len(tmp.August(2))
        tSheet.Cells(startIndex + 2, 8) = Len(tmp.September(2))
        tSheet.Cells(startIndex + 2, 9) = Len(tmp.October(2))
        tSheet.Cells(startIndex + 2, 10) = Len(tmp.November(2))
        tSheet.Cells(startIndex + 2, 11) = Len(tmp.December(2))
        tSheet.Cells(startIndex + 2, 12) = Len(tmp.January(2))
        tSheet.Cells(startIndex + 2, 13) = Len(tmp.February(2))
        tSheet.Cells(startIndex + 2, 14) = Len(tmp.March(2))
        tSheet.Cells(startIndex + 2, 15) = "=SUM(C" & CStr(startIndex + 2) & ":N" & CStr(startIndex + 2) & ")"
        
        ' Add current
        tSheet.Cells(startIndex + 3, 1) = "Current"
        tSheet.Cells(startIndex + 3, 2) = Len(tmp.AllocatedRom(1))
        tSheet.Cells(startIndex + 3, 3) = Len(tmp.April(1))
        tSheet.Cells(startIndex + 3, 4) = Len(tmp.May(1))
        tSheet.Cells(startIndex + 3, 5) = Len(tmp.June(1))
        tSheet.Cells(startIndex + 3, 6) = Len(tmp.July(1))
        tSheet.Cells(startIndex + 3, 7) = Len(tmp.August(1))
        tSheet.Cells(startIndex + 3, 8) = Len(tmp.September(1))
        tSheet.Cells(startIndex + 3, 9) = Len(tmp.October(1))
        tSheet.Cells(startIndex + 3, 10) = Len(tmp.November(1))
        tSheet.Cells(startIndex + 3, 11) = Len(tmp.December(1))
        tSheet.Cells(startIndex + 3, 12) = Len(tmp.January(1))
        tSheet.Cells(startIndex + 3, 13) = Len(tmp.February(1))
        tSheet.Cells(startIndex + 3, 14) = Len(tmp.March(1))
        tSheet.Cells(startIndex + 3, 15) = "=SUM(C" & CStr(startIndex + 3) & ":N" & CStr(startIndex + 3) & ")"

The results show that the maximum length is 1567 which is were the memory error is occuring

tSheet.Cells(startIndex + 2, 2) = Len(tmp.AllocatedRom(2))

I have looked up the maximum length that a cell can hold and have found it to be 32767 chars, however, the maximum display length is 1024 chars. Is it possible the issue is corralated to the 1024 limit and if so is there another was to set a cell equal to a string greater then 1024 chars?

[Oorang]

That's an interesting idea, 2003 have some nonintuitive behavior on large values.You could set up a test class that truncates the string on "property get" to see if that is what is going on. But to be honest I don't think that is what is going on.

Excel doesn't use all available memory. It uses 32MB. If you exceed that you will get out of memory errors (http://support.microsoft.com/default...;en-us;313275). I suspect you are just putting more in memory than the 32 MB. Try testing the code with a just some
very small strings in a collection of say 2 classes and see if you are still getting the memory errors. If not theb we know what the issue is.

To avoid loading all that code into memory you can set yourself up to handle the info in a stream. Instead of loading all the classes into a collection and then working through the collection, load the data into one class right before you use it, then the next etc. So you only ever have one CTATD in memory.

[craffel]

I suspect that you are correct in that I am exceeding the memory limit. I have created the following test which creates a string with 2000 chars and places it in the worksheet:

' Init
    Dim tSheet As Worksheet, cSheet As Worksheet, tmp As CTATD, _
    startIndex As Integer, fubar As Variant, y As Integer, largeString As String, v As Integer
    
    ' Define
    Set tSheet = Worksheets(ts)
    Set cSheet = Worksheets(cs)
    y = cSheet.Cells(1, 1)
    Set cSheet = Nothing
    startIndex = 1
    v = 0
    
    Do While v < 2000
        largeString = largeString & "v"
        v = v + 1
    Loop
    
    
    ' For each TATD
    For Each tmp In c
        
        
        ' Add headers
        'tSheet.Cells(startIndex, 1) = "TATD " & tmp.Name & " Forecasted Expenditures"
        
        tSheet.Cells(startIndex, 1) = largeString
        
        tSheet.Cells(startIndex + 1, 1) = "Forecast Month"
        tSheet.Cells(startIndex + 1, 2) = "Allocated FY " & CStr(y) & "/" & CStr(y + 1) & " ROM"
        tSheet.Cells(startIndex + 1, 3) = "Apr"
        tSheet.Cells(startIndex + 1, 4) = "May"
        tSheet.Cells(startIndex + 1, 5) = "Jun"
        tSheet.Cells(startIndex + 1, 6) = "Jul"
        tSheet.Cells(startIndex + 1, 7) = "Aug"
        tSheet.Cells(startIndex + 1, 8) = "Sep"
        tSheet.Cells(startIndex + 1, 9) = "Oct"
        tSheet.Cells(startIndex + 1, 10) = "Nov"
        tSheet.Cells(startIndex + 1, 11) = "Dec"
        tSheet.Cells(startIndex + 1, 12) = "Jan"
        tSheet.Cells(startIndex + 1, 13) = "Feb"
        tSheet.Cells(startIndex + 1, 14) = "Mar"
        tSheet.Cells(startIndex + 1, 15) = "Forecast FY " & CStr(y) & "/" & CStr(y + 1) & " ROM"
        
        ' Add Previous
        tSheet.Cells(startIndex + 2, 1) = "Previous"
        tSheet.Cells(startIndex + 2, 2) = tmp.AllocatedRom(2)

The same error occurs, meaning the 2000 char string is placed in the worksheet without issue. I have double checked this by taking the incomplete worksheet that is generated and pasting the 2000 char string into word to count the # of chars, it is equal to 2000.

I have run the code with Windows Task Manager open to monitor the memory usage. Excel loads using ~28.5 MB's and the memory error occurs when Excel is using ~35.5 MB's. This is interesting because it does not occur at exactly 32 MB's. Also, I am using excel 2003 and the memory limit listed on the link you provided states 64 MB's (which I am not even close to). Is there a method to increase the memory that Excel is able to use?

The reason why I ask if I can augment the memory used by Excel is that I need to Collection to build all of the classes. When the Collection is being added to I continueously need to check for existing elements with the same 'pName' property and add to them if they exist otherwise create a new instance of the CTATD class and add it to the collection.

Would there be any way that I can only load single elements of the Collection into memory or reference them in the function (I have a background in C/C++ and am thinking about a pass by reference type param I can use in the function instead of passing the entire collection, or possible copy?)? Also, reading the link you provided I see that "Workaround option #2" suggestions "Select only the last row or last two rows of the filled range, and then fill farther down the sheet." and I don't quite understand how that would help and exactly what to do.

Thanks for the help so far.

[Oorang]

Is there a method to increase the memory that Excel is able to use?

I don't know of one.

In VBA all paramaters are passed by reference by default, so you have already realized those savings. But just as fyi you can explicity prefix a paramater with ByRef as well (or ByVal).

You are crossing the memory line because vba handles strings poorly. See here for a little more on that: http://www.aivosto.com/vbtips/stringopt.html). But if bad string handling is enough to put you over the edge, then you are going to need to par down your memory use anyway. If you are OK with limiting yourself to ASCII characters you could store the strings in byte arrays to half you memory consumption. A trivial example would be:

Sub Example()
    Dim bytString() As Byte
    bytString = StrConv("Foo", vbFromUnicode)
    MsgBox StrConv(bytString, vbUnicode)
End Sub

Another way to go would be to have the properties store and retrieve the data from disk (Temp files) instead of putting it in string. It will be a tad slower, but slow go is better than no go Here is a possible example. I'm sure it can be improved but it should give you the idea :

Option Explicit

'Requires reference to Microsoft Scripting Runtime (scrrun.dll) <<<<<<<<<<<<<<<<<<<<<
Private fso As Scripting.FileSystemObject

Private m_strExamplePropertyPath As String

Private Sub Class_Initialize()
    Set fso = New Scripting.FileSystemObject
    m_strExamplePropertyPath = fso.BuildPath(fso.GetSpecialFolder(TemporaryFolder), fso.GetTempName)
End Sub

Public Property Get Example() As String
    Dim ts As Scripting.TextStream
    Dim strRtnVal As String
    On Error GoTo Err_Hnd
    If fso.FileExists(m_strExamplePropertyPath) Then
        Set ts = fso.OpenTextFile(m_strExamplePropertyPath, ForReading, False, TristateUseDefault)
        strRtnVal = ts.ReadAll
    Else
        strRtnVal = vbNullString
    End If
Exit_Proc:
    On Error Resume Next
    ts.Close
    Set ts = Nothing
    Example = strRtnVal
    Exit Property
Err_Hnd:
    MsgBox Err.Description, vbSystemModal, "Error: " & Err.Number
    Resume Exit_Proc
End Property

Public Property Let Example(ByRef value As String)
    Dim ts As Scripting.TextStream
    On Error GoTo Err_Hnd
    If fso.FileExists(m_strExamplePropertyPath) Then
        fso.DeleteFile m_strExamplePropertyPath, True
    End If
    Set ts = fso.OpenTextFile(m_strExamplePropertyPath, ForWriting, True, TristateUseDefault)
    ts.Write value
Exit_Proc:
    On Error Resume Next
    ts.Close
    Set ts = Nothing
    Exit Property
Err_Hnd:
    MsgBox Err.Description, vbSystemModal, "Error: " & Err.Number
    Resume Exit_Proc
End Property

Private Sub Class_Terminate()
On Error Resume Next
    fso.DeleteFile m_strExamplePropertyPath, True
    Set fso = Nothing
End Sub

[craffel]

I am going to play around with the string type and length on Monday (next chance I'll have to work on it). Thanks so far fo your help, when I resolve thi issue I will post how I did it to this thread in case someone runs into the same issue in the future.

Thanks again.

[craffel]

I have resolved my issue with Excel and the "Out of Memory" error. My solution is a work around for my particular case and will most likely not be applicable to other errors of the same type. Regardless, I am going to describe what I have done in case someone runs into a similar scenario in the future.

The problem is described in the thread above, the resolution is below.

Resolution:

In my case I am adding custum objects to a Collection, one of the parameters being a string. Now I am assuming that my "Out of Memory" error was caused by me trying to access the largest of the strings (being 1500 chars) in vba. Although both Task Manager and 'Application.MemoryUsed' both show me as being below the 64MB memory limit.

The string parameter I had was a formula using references to other worksheets (why it was 1500 chars). What I did was eliminate the references and instead only place the cell coordinates ("M5, J7, etc...") in the string parameter and simply parse the string parameter and append the reference to eaceh cell coordinate thus saving my self ~ 900 chars.

The solution is somewhat sloppy but I spent way to much time debugging this and with an uncertainty of what the error was I am satisfied with the solution.

If anyone runs into a similar issue feel free and message me. I personally feel that VBA is a terrible language and I am happy to be done working with it for now.

I'd like to thank Oorang for his help, it was greatly appreciated. There is a nice community of developers here.

[Mrgrip]

I encountered a similar problem, and finally stumbled onto the answer.

Your problem was not with the overall amount of memory being used by Excel, and I believe you had already (correctly) decided that was not your problem.

The real problem was with the maximum length of a FORMULA allowed by Excel!

• Max length of cell contents (text) = 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar.

• Max length of formula contents = 1,024 characters.

So the bottom line is that you were trying to programatically save formulas that were too long.

Excel will interpret a string as a "Formula" if it starts with something that looks like a formula (an Equals sign, a Minus sign, a Plus sign) If you had changed the first character of the string to something that did NOT resemble a formula, Excel would've accepted it up to 32767 characters.

Unfortunately, in VBA this throws the "Out of Memory" error (Error 7), which is not exactly helpful. As a test, if you try to paste the same formula into Excel manually, it will tell you "Formula Too Long."

Thanks, Microsoft... gotta love those error messages.

[Oorang]

Hello Mr Grip,
Welcome to the board. Nice Tip... Tried it out with ActiveCell.Formula = "=""" & String$(1024,"X") & """" And it does indeed throw exception 7. Thanks for posting it