Introduction

This blog post describes how to automatically select a value based on time and date using vba in excel.

Example,

This workbook is automatically recalculated each minute.

Array formula in cell F4:

=INDEX($B$2:$B$34, MATCH(TEXT($E$2, "yyyy-mm-dd tt:mm"), TEXT($A$2:$A$34, "yyyy-mm-dd tt:mm"), 0)) + CTRL + SHIFT + ENTER

Formula in E2:

=NOW()+ ENTER

Setup recalculation on time intervals

  1. Press Alt-F11 to open vba editor
  2. Click Module on the Insert menu
  3. Copy and paste vba code into a module.
  4. Exit vba editor
Public NextTime As Double
Sub SetTimer()
 Application.OnTime NextTime, "Recalc"
End Sub
Sub Recalc()
 Calculate
 NextTime = Now + TimeValue("00:01:00")
 SetTimer
End Sub

If you want ten minutes between recalculations, change TimeValue("00:01:00") to TimeValue("00:10:00")

Start "recalculation on time intervals" when workbook is opened

  1. Press Alt-F11
  2. Doubleclick ThisWorkbook in project window
  3. Select workbook from dropdown list
  4. Copy code below and paste into code window.
Private Sub Workbook_Open()
 Recalc
End Sub