Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Delaying Excel calculations

Posted on 2016-09-07
24
Medium Priority
?
72 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
  • 11
  • 11
  • 2
24 Comments
 
LVL 48

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 48

Assisted Solution

by:Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs) earned 800 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 48

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 48

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 48

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 48

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 48

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 48

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 48

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 33

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 1200 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 33

Expert Comment

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

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 48

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

916 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