XL97 VBA Code to Calc

Topic

New Reply

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

Replies

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

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

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

Hope this helps! –Sam

Reply | Quote

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

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

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

Reply | Quote

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

Reply | Quote

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

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

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