Excel Randbetween - Refresh without Shift-F9

Question

I'm looking to have an excel worksheet automatically calculate a rand between function without having to hit shift f9 or calculate the workbook/worksheet.

I'm looking to do this in order to create some animated conditional formatting, but the rand between function won't update automatically even when auto calculate is turned on.

The `Randbetween()` updates every time I enter new data with my spreadsheet. What do you mean? — Vityata, Jun 05 '18 at 13:29
The key is you have to either enter new data or hit refresh. I want it to change with no input at all from the user. Same issue with the Now formula. — tc_NYC, Jun 05 '18 at 13:32
Do you want the refresh to occur every second or every minute or what ??????????? — Gary's Student, Jun 05 '18 at 13:38
When there is an event - volatile functions get re-calculated, if there is no event (entering new data or refreshing) then there is no need to re-calculate. You could write a macro to force recalculate every 20 seconds or duration you prefer.... — Solar Mike, Jun 05 '18 at 13:38
Ok, I'm guessing there's a way to have a vb script file at regular intervals? — tc_NYC, Jun 05 '18 at 13:41

score 1 · Answer 1 · answered Jun 05 '18 at 13:59

Place the following code in a standard module:

Public RunWhen As Double
Public Const cRunIntervalSeconds = 1
Public Const cRunWhat = "The_Sub"

Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
     schedule:=True
End Sub

Sub The_Sub()
    Application.CalculateFullRebuild
    StartTimer
End Sub

Sub StopTimer()
   On Error Resume Next
   Application.OnTime earliesttime:=RunWhen, _
       procedure:=cRunWhat, schedule:=False
End Sub

This implements 1 second auto-re-calculation:

to begin, run StartTimer
to stop, run StopTimer

Adapted from Chip Pearson

score 0 · Accepted Answer · answered Jun 05 '18 at 13:56

So I ended up doing a VB script to automatically do something to the spreadsheet using this example:

VBA Macro On Timer style to run code every set number of seconds, i.e. 120 seconds

This basically made all of the formulas update without the user doing anything (entering data or calculating).

Excel Randbetween - Refresh without Shift-F9

2 Answers2