Page 1 of 3 123 LastLast
Results 1 to 15 of 43
  1. #1
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Conserving/Freeing Up Memory (Excel 2000)

    I've got a macro that performs an action on a very long column of cells. Ideally, I want it to loop through a number of columns. However, Excel gives me out of memory errors if I try to repeat it multiple times. Yet if I save, close and reopen the file in between loops everything is fine. I am assuming when I close the file, I am freeing up memory. Is there a way to build this into a macro?

    Suggestions are welcome.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conserving/Freeing Up Memory (Excel 2000)

    What are you trying to do, there might be better ways to loop. Using the "specialcells" property to reduce the number of cells to check or do something to might eliminate this problem ans it should spped up the code.

    Steve

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conserving/Freeing Up Memory (Excel 2000)

    It sounds like you might have something wrong with the code. Could you show it to us?
    Legare Coleman

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Conserving/Freeing Up Memory (Excel 2000)

    I'm not worried about speed, I'd just like it not to hang/crash. <img src=/S/crybaby.gif border=0 alt=crybaby width=15 height=15>
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Conserving/Freeing Up Memory (Excel 2000)

    Yah, I'll post it tonight.
    Are there any general principle for things I should look for?

    TIA
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conserving/Freeing Up Memory (Excel 2000)

    Not really anything general. Some of the things that can cause this are subroutines that call themselves (possibly by calling another subroutine that calls the original) without something to stop the resursion. Arrays that grow endlessly. Loops that don't end. etc.
    Legare Coleman

  7. #7
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Conserving/Freeing Up Memory (Excel 2000)

    I've extracted the looping portion only.

    <pre>Sub FillTest()

    Dim RANWks1, RANWks2 As Worksheet
    Dim DataArray1, DataArray2, RefColumn As Variant
    Dim RANArray1()
    Dim RANArray2()
    Dim DARowCounter, DAColCounter1, DAColCounter2, R, C, Count1, Count2, Chunk As Integer

    DataArray1 = Range("Sheet1!b5:IQ21013").Value

    DARowCounter = UBound(DataArray1)
    DAColCounter1 = UBound(DataArray1, 2)

    ReDim RANArray1(DARowCounter, 1)

    Application.Goto Reference:="ReferenceRange"

    With Selection.CurrentRegion
    R = .Rows.Count
    C = .Columns.Count
    RefColumn = .Resize(R, C)
    End With

    'fill sheets with data

    'create the first ChgCellRange
    Sheets("RAN_1").Select
    Range("f4").Select
    ActiveCell.Value = 1
    ActiveCell.Interior.ColorIndex = 34

    ActiveWorkbook.Names.Add Name:="ChgCell", RefersTo:=Selection

    'reset Solver in case it has been used recently
    SolverReset


    'ran formulas
    'crashes on the third rep.
    For Count1 = 1 To 3
    ReDim RANArray1(DARowCounter, 1)
    For Count2 = 1 To DARowCounter
    ' RANArray1(Count2, Count1) = "=Abs((Sheet1!RC*r4c)-sheet3!RC2)"
    RANArray1(Count2, 1) = "=Abs((" & DataArray1(Count2, Count1) & "* r4c) - " & RefColumn(Count2, 1) & ")"
    Next Count2

    'select and fill the range
    ActiveCell.Offset(1, 0).Select
    Range(ActiveCell, ActiveCell.Offset((DARowCounter), (0))).Select
    'crashes here
    Range(ActiveCell, ActiveCell.Offset((DARowCounter), (0))).Value = RANArray1

    'create the CurrCell range
    ActiveCell.Offset((DARowCounter), 0).Select
    ActiveCell.Interior.ColorIndex = 34
    ActiveWorkbook.Names.Add Name:="CurrCell", RefersTo:=Selection
    'place sum formula in cell
    ActiveCell.FormulaR1C1 = "=SUM(R[-21009]C:R[-1]C)"

    'Use Solver
    SolverOk SetCell:="CurrCell", MaxMinVal:=2, ValueOf:="0", ByChange:="ChgCell"
    SolverAdd CellRef:="CurrCell", Relation:=3, FormulaText:="0"
    SolverSolve userfinish:=True
    SolverReset

    'convert formulas to values
    ActiveCell.Offset(-1, 0).Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False

    'create the next ChgCell Range
    ActiveCell.Offset(0, 1).Select
    ActiveWorkbook.Names.Add Name:="ChgCell", RefersTo:=Selection
    ActiveCell.Value = 1
    ActiveCell.Interior.ColorIndex = 34

    'empty array?
    ReDim RANArray1(DARowCounter, 1)


    'save
    'ActiveWorkbook.Save

    Next Count1

    'For Count1 = 1 To DAColCounter2
    ' For Count2 = 1 To DARowCounter
    '' RANArray2(Count2, Count1) = Abs(DataArray2(Count2, Count1) - RefColumn(Count2, 1))
    ' Next Count2
    ' Next Count1

    'add new sheets


    ' Range("RAN_2!b5:cw21013").Value = RANArray2
    End Sub

    </pre>


    I really appreciate you taking a look at this Legare - it is driving me crazy!

    TIA
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conserving/Freeing Up Memory (Excel 2000)

    I have looked at your code and unfortunately I don't see anything immediately obvious. It is going to be VERY difficult to figure out what is happening without seeing the rest of the workbook. Statements like:

    <pre> DataArray1 = Range("Sheet1!b5:IQ21013").Value
    </pre>


    move a very large amount of data and it is very difficult to tell what the code is doing without knowing what is in that data.

    I will make a couple of comments on the code. First, it looks like the author of this code does not understand how the Dim statement works in VBA. A statement like:

    <pre>Dim RANWks1, RANWks2 As Worksheet
    </pre>


    does not define two worksheet objects. RANWks1 is defined to be a variant and RANWks2 is defined as a worksheet object. You must repeat the "As data type" for each variable in the Dim statement like this:

    <pre>Dim RANWks1 As Worksheet, RANWks2 As Worksheet
    </pre>


    Not having the variables properly defined will definitely slow the code down and there is a small possibility it could cause a problem like you describe.

    Second, it is always better to avoid selecting cells and/or worksheets. Selecting in a macro can cause the screen to flash while the macro is running and will slow the macro down a lot. Therefore, if you replace code like this:

    <pre> 'create the first ChgCellRange
    Sheets("RAN_1").Select
    Range("f4").Select
    ActiveCell.Value = 1
    ActiveCell.Interior.ColorIndex = 34
    ActiveWorkbook.Names.Add Name:="ChgCell", RefersTo:=Selection
    </pre>


    with code like this:

    <pre>Dim oCell As Range
    'create the first ChgCellRange
    Set oCell = Sheets("RAN_1").Range("f4")
    oCell.Value = 1
    oCell.Interior.ColorIndex = 34
    ActiveWorkbook.Names.Add Name:="ChgCell", RefersTo:=oCell
    </pre>


    Maybe someone else will see something that I am missing. I will also keep looking.
    Legare Coleman

  9. #9
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Conserving/Freeing Up Memory (Excel 2000)

    Thank you for your comments, I'll answer as best I can.

    ----------
    it is very difficult to tell what the code is doing without knowing what is in that data.

    It is just numbers, not formulas - in fact while I'm building this I'm just using "fake" data.
    -----------
    First, it looks like the author of this code does not understand how the Dim statement works in VBA

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> Oops, that would be me. Thank you for setting me straight with that.
    ----------
    Selecting in a macro can cause the screen to flash while the macro is running and will slow the macro down a lot

    Will turning screenupdating off, obviate the problem. Or, even with it off does the selection cause a problem?

    Thanks
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conserving/Freeing Up Memory (Excel 2000)

    Hi Catharine,

    I notice that you keep ReDim'ing RANArray1. I would do this just the first time (at the beginning of the code); within the loop I would use Erase RANArray1. I think that ReDim allocates a new memory location for the array, whereas Erase just clears the entries.

    I agree with Legare that using Range objects instead of Selection is usually more effective, but the amount of selecting going on in this macro is not large, so that could hardly explain the crashes you experience.

    You might also try to avoid the Copy/Paste Special by using
    Selection = Selection
    or if you have defined a range
    MyRange = MyRange

  11. #11
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conserving/Freeing Up Memory (Excel 2000)

    I've not run into the Redim issue before, but this article might contain some useful information. In particular:

    <big>Use Dynamic Arrays Instead of Fixed-Size Arrays</big>
    Consider using dynamic arrays instead of fixed-size arrays because you can reclaim memory when you no longer need the data in a dynamic array. Use either the Erase statement or the ReDim statement with the Preserve keyword to discard unneeded data and reclaim the memory used by the array. For example, you can reclaim the memory used by a dynamic array by using the Erase statement, as follows:

    Erase intArray

    While the Erase statement completely eliminates the array, the ReDim statement used with the Preserve keyword makes the array smaller without losing its contents.

    ReDim Preserve intArray(10, conNewUpperBound)

    Erasing a fixed-size array doesnt reclaim the memory for the array; it simply clears out the values of each element of the array. If each element was a string, or a Variant data type containing a string or an array, then erasing the array would reclaim the memory from those strings or Variant data types, not the memory for the array itself.

    Alan

  12. #12
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Conserving/Freeing Up Memory (Excel 2000)

    Thanks - Erase is new to me (okay - Arrays are new to me <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  13. #13
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Conserving/Freeing Up Memory (Excel 2000)

    Thanks Alan
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  14. #14
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Conserving/Freeing Up Memory (Excel 2000)

    <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21> I got it to loop 5 times without crashing! I think the better variable declaration and Erasing the array are working. I'm going to test it on more loops now - but I'm shutting everying else off. Just in case...
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  15. #15
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Conserving/Freeing Up Memory (Excel 2000)

    I've just tested this on a much more powerful system than my home computer. It now crashes after 19 loops. Any other suggestions?

    <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •