VBA Date Variable

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:

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:

Assign Now to Variable

The Now Function will return today’s date and current time:

Assign Time to Variable

The Time Function will return the current time:

Assign a Date to Variable with DateSerial

The DateSerial function will return a date based on an input year, month and day:

Assign a Time to Variable with TimeSerial

The TimeSerial function will return a time, based on an input hour, minute and second:

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

Excel VBA Date Variable Example

This procedure demonstrates how to create date variables, assign them values, and output them into an Excel worksheet:

The result is:

Declaring a Date variable in VBA

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.

 

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)