Solved

Delaying Excel calculations

Posted on 2016-09-07
24
63 Views
Last Modified: 2016-09-13
How do I intentionally delay calculations made by Excel so that the results of an operation takes a few seconds to display? The reason for this is I am using a worksheet for a live setting—a game, in particular.

I use a Macbook Pro running El Capitan.

Thanks,
Anton.
0
Comment
Question by:Anton Tajanlangit
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 11
  • 2
24 Comments
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41788750
You can delay the calculations by setting Calculation mode to Manual, but then you need to hit F9 manually to force the calculation. Would something like that work?
0
 
LVL 47

Assisted Solution

by:Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs) earned 200 total points
ID: 41788785
An alternative is to use VBA to watch the Change event of a worksheet and wait the specified time before forcing a calculation. With this method you still need to have calculation set to Manual for the workbook.

This routine is placed in the required worksheets code module (right-click tab and select "View Code")
Private Sub Worksheet_Change(ByVal Target As Range)
    'Run the calculation in 3 seconds.
     Application.OnTime DateAdd("s", 3, Now), "RunCalculate"
End Sub

Open in new window

The below code then goes in a regular module (in the Visual Basic Editor select Insert > Module)
Public Sub RunCalculate()
    Application.EnableEvents = False
    Worksheets("Sheet1").Calculate
    Application.EnableEvents = True
End Sub

Open in new window

0
 

Author Comment

by:Anton Tajanlangit
ID: 41788915
No, Wayne, what I would want happen is that I type an entry into a cell and then after a predetermined amount of seconds, the results will display in another cell. But I don't want to press another key again just to display those results! It's just like there was no delay at all anyway.
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41788925
OK, then the only alternative is to utilise VBA as in my second suggestion above.
0
 

Author Comment

by:Anton Tajanlangit
ID: 41795373
I'm new to VBA; is it allowed to have different codes for different purposes in the window? I'm already running a VBA code in the Editor, that's why I ask.
0
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41795376
Yes, you can have as many routines (macros) as you like with any module.

The only exception is they can't have the same name. And if you already have a Worksheet_Change routine in your worksheets code module the code I posted above will need to be incorporated in to it.
0
 

Author Comment

by:Anton Tajanlangit
ID: 41795384
I copy-pasted the first code you sent into the Editor window and got this message.
Compile-error.jpg
0
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41795387
As I mentioned, you can not have 2 routines with the same name, so as you already have a Worksheet_Change() routine, you will need to incorporate what I posted into the existing routine. It is easy to do however. All you need to do is place this line of code before anything else in the routine. It will looks something like this....

Private Sub Worksheet_Change(ByVal Target As Range)
    'Run the calculation in 3 seconds.
     Application.OnTime DateAdd("s", 3, Now), "RunCalculate"

    'The rest of the original code will be here

End Sub

Open in new window


If you have any problems doing that, please post the current Worksheet_Change routine here and I will modify it for you.
0
 

Author Comment

by:Anton Tajanlangit
ID: 41795821
Didn't work... here's the first 4 lines of the code:

Private Sub Worksheet_Change(ByVal Target As Range)
'Run the calculation in 3 seconds.
     Application.OnTime DateAdd("s", 3, Now), "RunCalculate"
If Target.Count > 1 Then Exit Sub

The 4th line is the start of an already working code...
0
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41795827
What you have posted looks fine. But what do you mean by "didn't work"? Did you get an error? Did nothing happen?
0
 

Author Comment

by:Anton Tajanlangit
ID: 41795834
Yes, no delay in calculations. Instant results still.
0
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41795842
Are the results from formulas, or results generated by your code?
0
 

Author Comment

by:Anton Tajanlangit
ID: 41795848
The results are from formulas.
0
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41795854
Can you upload the workbook? Or at the very least, the remaining code? I suspect some of your other code (or something else) is interfering as by itself the code I posted works.
0
 

Author Comment

by:Anton Tajanlangit
ID: 41795861
Here's the whole code:

Dim oVal
Private Sub Worksheet_Change(ByVal Target As Range)
    'Run the calculation in 3 seconds.
     Application.OnTime DateAdd("s", 3, Now), "RunCalculate"

If Target.Count > 1 Then Exit Sub
If Target.Row > 3 And Target.Row Mod 2 = 0 Then
   Application.EnableEvents = False
   Select Case Target.Column
      Case 1
         If oVal <> "" Then
         Else
            If Target = 1 Then
               Target.Offset(0, 2).Value = WorksheetFunction.RandBetween(1, 100)
            Else
               Target.Offset(0, 2).Value = 0
            End If
         End If
         Target.Offset(1, 0).Select
      Case 7, 12, 17, 22
         If oVal <> "" Then
         Else
            If Target = 1 Then
               Target.Offset(1, -2).Value = WorksheetFunction.RandBetween(1, 100)
            Else
               Target.Offset(1, -2).Value = 0
            End If
         End If
         Target.Offset(1, 0).Select
   End Select
   Application.EnableEvents = True
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row > 3 And Target.Row Mod 2 = 0 Then
   Select Case Target.Column
      Case 1, 7, 12, 17, 22
         oVal = Target.Value
   End Select
End If
End Sub
0
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41795866
There doesnt appear to be anything there that would cause it. But did you set calculation to Manual as I suggested?
0
 

Author Comment

by:Anton Tajanlangit
ID: 41795979
Oh ok, so it was on automatic.

Now I set it to manual. Using a Mac, I went to PREFERENCES > FORMULAS AND LISTS > CALCULATION > then checked Calculate Sheets Manually.

No dice. results were still instantaneous, and this error message came up:
Screen-Shot-2016-09-13-at-9.17.14-AM.jpg
0
 
LVL 31

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 300 total points
ID: 41796045
Hi Anton,

See if the following code helps. Replace all your existing codes with the following codes.
The code will populate the random numbers with a delay of 3 seconds.
Otherwise you may change the delay in all the instances of the following line of code...
Application.Wait Now + TimeValue("00:00:03")

Dim oVal
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Row > 3 And Target.Row Mod 2 = 0 Then
   Application.EnableEvents = False
   Select Case Target.Column
      Case 1
         If oVal <> "" Then
            Application.Undo
         Else
            If Target = 1 Then
               Application.Wait Now + TimeValue("00:00:03")
               Target.Offset(0, 2).Value = WorksheetFunction.RandBetween(1, 100)
            Else
               Application.Wait Now + TimeValue("00:00:03")
               Target.Offset(0, 2).Value = 0
            End If
         End If
         Target.Offset(1, 0).Select
      Case 7, 12, 17, 22
         If oVal <> "" Then
            Application.Undo
         Else
            If Target = 1 Then
               Application.Wait Now + TimeValue("00:00:03")
               Target.Offset(1, -2).Value = WorksheetFunction.RandBetween(1, 100)
            Else
               Application.Wait Now + TimeValue("00:00:03")
               Target.Offset(1, -2).Value = 0
            End If
         End If
         Target.Offset(1, 0).Select
   End Select
   Application.EnableEvents = True
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row > 3 And Target.Row Mod 2 = 0 Then
   Select Case Target.Column
      Case 1, 7, 12, 17, 22
         oVal = Target.Value
   End Select
End If
End Sub

Open in new window

0
 

Author Comment

by:Anton Tajanlangit
ID: 41796519
Yes, it worked! Thanks so much!
0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41796547
You're welcome Anton! Glad to help.
0
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41796930
A couple things...

1. If Neeraj's code works, then your statement that the delayed results you want are from formulas is false. It's extremely helpful to us if you provide accurate responses to our questions.
2. Neeraj's code, as he mentioned, delays the input of values from the code. It does not delay the calculations as you initially requested.
3. The latest error message you received was because you again didn't follow the instructions I posted way up the top in my second suggestion. I instructed that the "RunCalculate" macro be placed in a regular module.

Next time, please put as much information as possible in your opening question and answer our questions accurately so we don't have to guess what it is you want.
0
 

Author Comment

by:Anton Tajanlangit
ID: 41797152
Hi Wayne,

I don't know, but I really believe the results are only from formulas. I'm a newbie to VB you see, so whatever's in code still doesn't make sense to me, I just copy and paste from suggestions I receive.

And I'm sorry—I should have made your post an Assisted Solution... Because I was in a rush that time I thought it was you who gave me the last code. I failed to read the heading that contained the sender's name.

I'll be more careful next time!
0
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41797162
You haven't accepted any comments as answer yet, so you are still able to distribute points as necessary.
0
 

Author Closing Comment

by:Anton Tajanlangit
ID: 41797168
VB coding is a language I still don't understand, but these two experts have been making it easy for me!
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question