Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    XL97 VBA Code to Calc

    I am looking for a better way (time-wise) to calculate a range of cells (approx 97K cells). I have created code using "For Each Cell_In_Loop In Range("Table_01")" which eventually works but takes 15 minutes. Does anyone have a better approach?

    Thanks,
    John

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

    Re: XL97 VBA Code to Calc

    That question is kind of like saying, "My car is running slow, can you tell me what the problem is?" Kind of tough to answer without seeing the car.

    Can you show us the code?
    Legare Coleman

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XL97 VBA Code to Calc

    Sorry the keys were not in the car. Here you go. Just have the car back by 11PM..

    Sub StartMappedCalc()
    Dim TtlCount As String
    Counter = 0
    Range("R_Mapped_Cells") = "=CountA(table_01)"
    Range("R_Mapped_Cells").Value = Range("R_Mapped_Cells")
    TtlCount = Range("R_Mapped_Cells")
    For Each Cell_In_Loop In Range("Table_01")
    Counter = Counter + 1
    Application.StatusBar = "Mapped Cell Being Calculated: " & Counter & "/" & TtlCount & ""
    Cell_In_Loop.Calculate
    Next
    Application.StatusBar = False
    End Sub

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: XL97 VBA Code to Calc

    The calculate is killing you!
    Just pitch the loop and
    Range("Table_01").Calculate

    Hope this helps! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

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

    Re: XL97 VBA Code to Calc

    Edited by Charlotte to reduce horizontal scrolling
    If I understand what this routine is doing, then I think the whole thing can be replace by:

    <pre>Sub StartMappedCalc()
    Range("table_01").Calculate
    End Sub
    </pre>



    However, you will not get a status bar while the recalculate is going on.

    If you really need the status bar updating, then the following might be a little faster:

    <pre>Sub StartMappedCalc()
    Dim TtlCount As String
    DIM Counter As Long
    Dim Cell_In_Loop As Range
    Counter = 0
    TtlCount = Range("table_01").Rows.Count * Range("table_01").Columns.Count
    Application.ScreenUpdating = False
    For Each Cell_In_Loop In Range("Table_01")
    Counter = Counter + 1
    Application.StatusBar = "Mapped Cell Being Calculated: " _
    & Counter & "/" & TtlCount & ""
    Cell_In_Loop.Calculate
    Next
    Application.ScreenUpdating = True
    Application.StatusBar = False
    End Sub
    </pre>


    Since I don't have the sheets setup, I didn't test that code. I hope there are no errors in it.
    Legare Coleman

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XL97 VBA Code to Calc

    just a quick note to put this in your minds rather than a complete solution.
    If you are working with a lot of cells in a sheet and need to update them, it is faster to commit the whole range to an array variable, recalculate the array and then paste the array back into the worksheet range.
    something like this:
    Dim myArray ()

    myArray = sheets("sheet1").range("A1:IV65536")
    ' array now has dimensions of 256 x 65536
    for x = 1 to 256
    for y = 1 to 65536
    myarray(x,y)=(any calculation here)
    next y
    next x
    sheets("sheet1").range("A1:IV65536")=myarray

    dont trust this code, it is just the format, syntax is probably terrible but this works MUCH faster than interogating the individual cells
    i got this from another forum a few years ago and have not had reason to use it properly yet but the demo given was seconds faster on only a few thousand cells
    HTH

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XL97 VBA Code to Calc

    My experience with arrays is limited; how is an array calculated via vba?

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: XL97 VBA Code to Calc

    You probably need to give us more details on what you are trying to do.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

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

    Re: XL97 VBA Code to Calc

    His code will put absolute values, which must be calculated in the macro, into the cells. It will not calculate the formulas that are in the cells.
    Legare Coleman

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XL97 VBA Code to Calc

    Thanks for everyone's suggestions.
    To recap: I have a range with approx 97K cells each containing formulas. What I would like to do is calc each cell or range; I've tried both suggestions with a rewrite of my original code and the code for the range itself. Both took approx 15 minutes to run; there must be a better way to minimize the time it takes to calc.

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: XL97 VBA Code to Calc

    Hi,
    You may have already set calculation to manual but just in case you haven't, try changing your code to:
    <pre>Sub StartMappedCalc()
    Dim TtlCount As String, Counter As Long, Cell_In_Loop As Range
    With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    End With
    Counter = 0
    Range("R_Mapped_Cells") = "=CountA(table_01)"
    Range("R_Mapped_Cells").Value = Range("R_Mapped_Cells")
    TtlCount = Range("R_Mapped_Cells")
    For Each Cell_In_Loop In Range("Table_01")
    Counter = Counter + 1
    Application.StatusBar = "Mapped Cell Being Calculated: " & _
    Counter & "/" & TtlCount & ""
    Cell_In_Loop.Calculate
    Next
    With Application
    .StatusBar = False
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
    End With
    End Sub
    </pre>

    and see if that helps - the difference on my machine between Calculation set to Automatic and set to Manual was significant doing a recalc on 857000 cells!
    HTH.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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