craffel
04-24-2008, 11:10 AM
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.
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.