Finding non-VBA alternatives for the volatile TODAY and NOW functions is tricky, but here’s an example of some creative workarounds for the Evil Boss’ report generator.
That last one that uses a dropdown I pulled from a at the ExcelHero LinkedIn group . It’s pretty clever: what you do is create a small Table somewhere, and populate it using the naughtily volatile =TODAY() function, as well as the words “Please choose…” .
(An alternate wording to “Please choose…” would be “Choose wisely…”).
Then you point a dropdown list at that Table. And then you assign the name TodaysDate to the cell that the dropdown lives in, and use that instead of TODAY() throughout the rest of your workbook.
The beauty of dropdowns is that while they may reference that volatile function, as soon as the Evil Boss makes a choice, the choice gets written to the cell as a string, and not as a reference to that volatile function. So it IS today’s date, but it is NOT volatile. Of course, you need a big obnoxious message pointing at it so that:
- The Evil Boss remembers to fill it out today; and
- The Evil Boss also remembers to update it tomorrow
But obnoxious is my middle name.
That’s a fun read…thanks…and an opportunity to get me thinking about some other ways to solve the problem. Double thanks. We used to call this “Idiot Proofing”. Now we call it “Director Proofing”, as if there were a difference.
That said, here are a couple of other ideas…
If it is always a new workbook and not an update of a current work book make a template where the cell with the date says “Click Me!” and is Named “ClickMe”. Then in the worksheet module put this code.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range(“ClickMe”)) Is Nothing Then
If Intersect(Target, Range(“ClickMe”)) = Range(“ClickMe”) _
And UCase(Range(“ClickMe”).Value) = “CLICK ME!” Then
Range(“Clickme”).Value = Format(Now(), “m/d/yyyy”)
End If
End If
End Sub
Or, if the date should be updated when changes are saved on a new day, try this, using “TheDate” was the Name for the cell this time.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Not ThisWorkbook.Saved Then
If Range(“TheDate”) < Format(Now(), "d/m/yyyy") Then
Select Case MsgBox("Change to today's date?", vbYesNoCancel, "Gentle Reminder")
Case vbCancel
Cancel = True
Case vbYes
Range("TheDate").Value = Format(Now(), "d/m/yyyy")
Case vbNo
End Select
End If
Else
Cancel = True
End If
End Sub
I recently discovered that Bloomberg provide their own BToday() function. It provides the current date via DDE – this means it’s not volatile but automatically ticks over at midnight (if you want it to). Might be useful for those of you in the financials world.
DDE…Daily Dose of Excel?
Dyanmic Data Exchange http://en.wikipedia.org/wiki/Dynamic_Data_Exchange