I am running a macro every time increment I want, can change that increment in B1.
My problem is that I run this on a workbook with LOTS of calculations that take place every second and every time my macro runs after it is called by the timer, excel struggles and takes a couple of seconds to calculate. If I turn off automatic calculation for the full workbook, everything works fine! But I CANNOT turn it off permanently because I still need all of these other calculations to run in the meantime.
I tried to use Application.Calculation = xlManual & Application.Calculation = xlAutomatic at the beginning and the end of macro1 but it would not change anything. I know it's coming from the start1 macro but I am not sure how to tweak it to make it work. Basically I think I need to change to Manual calc every time I reach the time increment and change back to Automatic right after...
Option Explicit
Public Increment As Double
Public updatetime As Date
Sub macro1()
Increment = Sheets("Sheet1").Range("B1").Value
Sheets("Sheet1").Range("C1").Value = Sheets("Sheet1").Range("C1").Value + Increment
Call Start1
End Sub
Sub Start1()
Increment = Sheets("Sheet1").Range("B1").Value
updatetime = Now + TimeValue("00:00:" & Increment)
' Application.Calculation = xlManual
Application.OnTime updatetime, "macro1", Schedule:=True
' Application.Calculation = xlAutomatic
End Sub
Sub Stopit()
On Error Resume Next
Application.OnTime updatetime, "macro1", Schedule:=False
Range("C1").Clear
End Sub
Bookmarks