In this Article
We have already gone over what variables, data types and constants are, in our VBA Data Types – Variables and Constants tutorial. In this tutorial, we are going to cover how to declare a variable as a date.
In VBA, the date data type is stored as a decimal. Both dates and times can be stored in this data type. The VBA date data type can store values ranging from 1 January 100 – 31 December 9999.
VBA Date Variable
You declare a variable as a date using the Dim keyword:
Dim dateOne as Date
Then you can assign a date to the date variable. There are multiple ways to generate dates to assign to variables:
Assign Today to Variable
The Date Function will return today’s date:
Dim dtToday dtToday = Date
Assign Now to Variable
The Now Function will return today’s date and current time:
Dim dNow dNow = Now
Assign Time to Variable
The tim will return the current time:
Dim tTime tTime = Time
Assign a Date to Variable with DateSerial
The DateSerial function will return a date based on an input year, month and day:
Dim dsDate dsDate = DateSerial(2010, 11, 11)
Assign a Time to Variable with TimeSerial
The TimeSerial function will return a time, based on an input hour, minute and second:
Dim tsTime tsTime = TimeSerial (10, 10, 45)
You must enclose dates with the # or ” when using them in your code, as shown below:
VBA DateValue Function
The VBA DateValue Function can be used to initialize a date. The following code can be used to initialize a date in VBA:
VBA TimeValue Function
The TimeValue Function generates a time:
Excel VBA Date Variable Example
This procedure demonstrates how to create date variables, assign them values, and output them into an Excel worksheet:
Sub DeclaringAVariableAsADate() Dim dateOne As Date Dim dateTwo As Date dateOne = #1/1/2019# dateTwo = "1/2/2019" Range("A1").Value = dateOne Range("A2").Value = dateTwo End Sub
The result is:
You can learn more about Date Functions in this tutorial.
VBA Date Variable in Access
The Date Variable works exactly the same in Access VBA as in Excel VBA.
This procedure demonstrates how to create a date variable and update a field in an access table with the value.
Sub DeclaringAVariableAsADate() Dim dtWork As Date dtWork = #05/10/2020# DoCmd.RunSql "UPDATE tblJobs SET WorkDate = #" & dtWork & "# WHERE JobNo = 6" End Sub