Solved

Delaying Excel calculations

Posted on 2016-09-07
24
51 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 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
 
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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 28

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 28

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
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 the scrolling table in Microsoft Excel using the INDEX function.

706 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now