CODEWITHSUNDEEP
excelvba
M.Johnson
M.Johnson

Reputation: 1

Set date as the value for a workbook defined name?

I'm trying to have VBA write a value to the Name manager to store a date instead of using the Today() function in formulas.
I have this code that seems to work.
ThisWorkbook.Names.Add "Today", "=Datevalue(""" & Date & """)"

Is there a better way to do this? Without using DATEVALUE()?

Upvotes: 0

Views: 209

Answers (1)

user10798192
user10798192

Reputation:

Any place Today() is used, a strictly numerical value should be able to be used in its place.

ThisWorkbook.Names.Add name:="Today", refersto:=chr(61) & CLng(Date)

That should do. It seems redundant to change Date to a text value only to turn around and convert it to a date again with DateValue.

You may not want the defined name visible to others. 

ThisWorkbook.Names.Add name:="Today", refersto:=chr(61) & CLng(Date), visible:=false

I would recommend placing this in the Workbook_Open or other appropriate sub procedure.

Option Explicit

Private Sub Workbook_Open()
    On Error Resume Next
    ThisWorkbook.Names("Today").Delete
    On Error GoTo 0
    ThisWorkbook.Names.Add Name:="Today", RefersTo:=Chr(61) & CLng(Date), Visible:=False
End Sub

Upvotes: 3

Related Questions