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
My Co. uses onedrive for everything and needs multiple users sharing excel files meaning VBA won’t work for us until MS finds a way for sharepoint/onedrive to support multiuser .xlsm files.
With the newer dynamic array functions I’ve eliminated volatile functions from all our workbooks apart from…. TODAY()
I might try the drop down option above but I just don’t trust users to do it daily. I use a Named range for TODAY() so that if I find a workaround later it’ll be easy enough to deploy / test alternates.
Our project budget XL runs a bit laggy, I think it’s between unpivoting massive timesheet datasets joined using delimited strings and FILTER/CHOOSE then extracted using FILTERXML and that last pesky volatile TODAY() function. It’s not a dealbreaker but I sure would love to eliminate that lag. Fingers crossed for a non-volatile TODAY() in the future
@Marko
I don’t live in a multiuser environment so I don’t know if this makes sense, but can’t you put your VBA in an .xlam Add-In that is accessible from each user’s .xlsx file? I assume the Add-In can be located in OneDrive. My Excel Toolbox has references to some Add-In articles.