• XL97 VBA Code to Calc

    Home » Forums » AskWoody support » Productivity software by function » Visual Basic for Applications » XL97 VBA Code to Calc

    Author
    Topic
    WSjstevens
    AskWoody Lounger
    June 18, 2001 at 2:23 pm #357112

    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

    Reply | Quote
    Viewing 1 reply thread
    Author
    Replies
    • H. Legare Coleman
      AskWoody Plus
      June 18, 2001 at 5:28 pm #529895

      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?

      Reply | Quote
      • WSjstevens
        AskWoody Lounger
        June 18, 2001 at 7:59 pm #529923

        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

        Reply | Quote
        • WSSammyB
          AskWoody Lounger
          June 18, 2001 at 8:29 pm #529939

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

          Hope this helps! –Sam

          Reply | Quote
        • H. Legare Coleman
          AskWoody Plus
          June 18, 2001 at 8:36 pm #529938

          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:

          Sub StartMappedCalc()
    Range("table_01").Calculate
End Sub

          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:

          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

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

          Reply | Quote
    • WSfatherjack
      AskWoody Lounger
      June 19, 2001 at 9:11 am #529993

      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

      Reply | Quote
      • WSjstevens
        AskWoody Lounger
        June 19, 2001 at 2:21 pm #530026

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

        Reply | Quote
        • WSSammyB
          AskWoody Lounger
          June 19, 2001 at 2:50 pm #530030

          You probably need to give us more details on what you are trying to do.

          Reply | Quote
        • H. Legare Coleman
          AskWoody Plus
          June 19, 2001 at 3:25 pm #530037

          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.

          Reply | Quote
          • WSjstevens
            AskWoody Lounger
            June 19, 2001 at 4:48 pm #530045

            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.

            Reply | Quote
            • WSrory
              AskWoody Lounger
              June 20, 2001 at 12:28 pm #530126

              Hi,
              You may have already set calculation to manual but just in case you haven’t, try changing your code to:

              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

              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.

              Reply | Quote
    Viewing 1 reply thread
    Reply To: XL97 VBA Code to Calc

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    March 2025
    S M T W T F S
     1
    2345678
    9101112131415
    16171819202122
    23242526272829
    3031  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.