• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 82
  • Last Modified:

Delaying Excel calculations

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
Anton Tajanlangit
Asked:
Anton Tajanlangit
  • 11
  • 11
  • 2
2 Solutions
 
Wayne Taylor (webtubbs)Commented:
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
 
Wayne Taylor (webtubbs)Commented:
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
 
Anton TajanlangitAuthor Commented:
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Wayne Taylor (webtubbs)Commented:
OK, then the only alternative is to utilise VBA as in my second suggestion above.
0
 
Anton TajanlangitAuthor Commented:
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
 
Wayne Taylor (webtubbs)Commented:
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
 
Anton TajanlangitAuthor Commented:
I copy-pasted the first code you sent into the Editor window and got this message.
Compile-error.jpg
0
 
Wayne Taylor (webtubbs)Commented:
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
 
Anton TajanlangitAuthor Commented:
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
 
Wayne Taylor (webtubbs)Commented:
What you have posted looks fine. But what do you mean by "didn't work"? Did you get an error? Did nothing happen?
0
 
Anton TajanlangitAuthor Commented:
Yes, no delay in calculations. Instant results still.
0
 
Wayne Taylor (webtubbs)Commented:
Are the results from formulas, or results generated by your code?
0
 
Anton TajanlangitAuthor Commented:
The results are from formulas.
0
 
Wayne Taylor (webtubbs)Commented:
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
 
Anton TajanlangitAuthor Commented:
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
 
Wayne Taylor (webtubbs)Commented:
There doesnt appear to be anything there that would cause it. But did you set calculation to Manual as I suggested?
0
 
Anton TajanlangitAuthor Commented:
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
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
 
Anton TajanlangitAuthor Commented:
Yes, it worked! Thanks so much!
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Anton! Glad to help.
0
 
Wayne Taylor (webtubbs)Commented:
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
 
Anton TajanlangitAuthor Commented:
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
 
Wayne Taylor (webtubbs)Commented:
You haven't accepted any comments as answer yet, so you are still able to distribute points as necessary.
0
 
Anton TajanlangitAuthor Commented:
VB coding is a language I still don't understand, but these two experts have been making it easy for me!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 11
  • 11
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now