Results 1 to 2 of 2

Make Timer function more efficient

Threaded View

  1. #1
    Registered User
    Join Date
    06-17-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    25

    Make Timer function more efficient

    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
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] how to make a sub or function that acts as a timer
    By dmcgov in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-25-2016, 02:11 PM
  2. [SOLVED] how to make a sub or function that acts as a timer
    By dmcgov in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-25-2016, 01:54 PM
  3. Make VBA Macro more efficient
    By boubou114 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-18-2015, 10:32 AM
  4. [SOLVED] How can I make VBA process more efficient?
    By pholt33 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-05-2015, 01:55 PM
  5. How to make Excel VBA more efficient?
    By mhni in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-17-2009, 04:31 PM
  6. How to make my formula more efficient
    By yuzi in forum Excel General
    Replies: 7
    Last Post: 05-29-2009, 05:54 PM
  7. Make code more efficient
    By allanr in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-29-2008, 08:43 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1