Solved

Delaying Excel calculations

Posted on 2016-09-07
24
68 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
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!

 
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

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

617 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