O'Reilly logo

Programming Excel with VBA and .NET by Steve Saunders, Jeff Webb

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Get Dates and Times

Visual Basic stores dates and times as decimal numbers. The digits to the left of the decimal represent the number of days since December 30, 1899, and the digits to the right of the decimal represent the fraction of the day that has passed (for instance, 0.5 = noon).

This means that dates and times use the same operators as numeric types. For example, the following expression shows yesterday’s date:

    Debug.Print Date - 1

This also means that you can use date or time literals to work with dates. For example, if you type #0.0# in the Code window, Visual Basic automatically changes what you typed into the time literal for midnight shown here:

    dt = #12:00:00 AM#

You can edit that literal to add a certain number of seconds, minutes, or hours to the time. For example, the following code pauses Excel for five seconds:

    Sub TakeFive( )
        Dim dt As Date
        ' Five seconds.
        dt = #12:00:05 AM#
        Debug.Print "Paused..."
        ' Wait till five seconds from now.
        Application.Wait Now + dt
        Debug.Print "Resumed."
    End Sub

Visual Basic provides a whole set of functions for working with dates and times, as listed in Table 3-12.

Table 3-12. Visual Basic functions for working with date and time

Category

Function

Use to

Current

Date

Get or set the system date

 

Now

Get the current date and time

 

Time

Get or set the system time

 

Timer

Get the number of seconds since midnight (often used to measure performance)

Date

DateSerial

Convert year, month, and day numbers ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required