How can i get the NOW function to update every second?-CodePudding

The

CodePudding user response：

Solved with VBA: -> Update clock every Second

Sub clock_timer() Sheets("Sheet1").Range("B6").Value = Now Application.OnTime Now TimeValue("00:00:01"), "clock_timer" End Sub

Solved without VBA: -> Update clock every Minute

Using Query & Connections, follow these steps;

Step 1: Select "B5 to B10" & click on Insert -> Table Step 2: Click on Data -> From Table/Range Step 3: A new pop up will open with Query -> Click on Close & Load Step 4: Right click on newly created Table under section "Queries & Connections" & go to Properties Step 5: Edit Refresh option to "Every 1 Minute"

Another Solution without VBA: By downloading the excel's extensions "XLTools"

Hope it Helps...

CodePudding user response：

I worked on this answer while Sachin Kohli published his answer. My answer is similar to his solution using VBA, but generalizes the concept a little further.

If you copy this code into a standard module:

Public Sub RecalculateCells(Optional ByVal CellAdress As String = "", _ Optional ByVal CellWorkbook As String = "", _ Optional ByVal CellWorksheet As String = "", _ Optional ByVal updateTimeInterval As Double = 1#, _ Optional ByVal updateState As Long = 0) Static continue As Boolean If updateState = 1 Then continue = True If updateState = -1 Then continue = False If continue Then Workbooks(CellWorkbook).Worksheets(CellWorksheet).Range(CellAdress).Calculate Application.OnTime Now() updateTimeInterval / 86400#, _ "'RecalculateCells """ & CellAdress & """, """ & CellWorkbook & _ """, """ & CellWorksheet & """, " & updateTimeInterval & ", 0'" End If End Sub Public Sub StartUpdatingRange(rng As Range, updateTimeInterval As Double) RecalculateCells "B4", rng.Worksheet.Parent.Name, rng.Worksheet.Name, _ updateTimeInterval, 1 End Sub Public Sub StopUpdatingRange() RecalculateCells , , -1 End Sub

You can recalculate any range at any given time interval. The following example sub will recalculate the cells B6 to B10 once every second.

Sub UpdateB6toB10() StartUpdatingRange ThisWorkbook.Worksheets(1).Range("B6:B10"), 1 End Sub

You can stop the recalculation at any given time by calling StopUpdatingRange. You can also call StartUpdatingRange multiple times with different ranges and different update rates. One call to StopUpdatingRange will then stop all of the updates.

Note that if calculation is set to automatic, everything will get updated at the specified rate, not only the selected cells. Therefore, to actually only update the selected cells, set the calculation mode to manual: