Programming4us
         
 
 
Programming

Programming Excel with VBA and .NET : Tasks in Visual Basic - Get Dates and Times

8/11/2011 5:14:40 PM
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 1.

Table 1. Visual Basic functions for working with date and time
CategoryFunctionUse to
CurrentDateGet or set the system date
 NowGet the current date and time
 TimeGet or set the system time
 TimerGet the number of seconds since midnight (often used to measure performance)
DateDateSerialConvert year, month, and day numbers into a date
 DateValueConvert a string into a date
 DayGet the day of the month from a date
 MonthGet the month of the year from a date
 WeekdayGet the weekday from a date (1 to 7)
 YearGet the year from a date
TimeHourGet the hour from a time
 MinuteGet the minute from a time
 SecondGet the second from a time
 TimeSerialConvert hour, minute, and second numbers into a time
 TimeValueConvert a string into a time

For most conversions, the Format function works better than the functions listed in Table 1. The date/time functions are mainly used for simple operations, such as getting the current year:

    Debug.Print Year(Now)

The Timer function is very handy when developing programs since it lets you see how long your code takes to run. When developing large or complex programs, it is pretty common to record the Timer value at the start of the process, then display the difference between that value and the current Timer when the task completes, as shown by the following changes to DemoSort:

    Sub DemoSort( )
Dim str As String, arr As Variant, d As Double
' Time this operation.
d = Timer

str = "Q z v w p x f g J l h r y D k i e T s u o n M a c b"
' Show case-sensitive sort.
Debug.Print SortString(str, False)
' Show case-insensitive sort.
Debug.Print SortString(str, True)
' Display how long the task took.
Debug.Print Timer - d

End Sub

You should use the Double data type when measuring performance since many tasks take only a fraction of a second. On my computer, DemoSort takes only about 0.00128 seconds.

Other -----------------
- Programming Excel with VBA and .NET : Tasks in Visual Basic - Work with Text
- A Technical Overview of the Mobile Web : THE TECHNICAL CHALLENGES OF MOBILE DEVICES (part 2)
- A Technical Overview of the Mobile Web : THE TECHNICAL CHALLENGES OF MOBILE DEVICES (part 1) - Physical Constraints
- Parallel Programming with Microsoft Visual Studio 2010 : Task Parallelism - Unhandled Exceptions in Tasks
- Parallel Programming with Microsoft Visual Studio 2010 : Introduction to Parallel Tasks
- jQuery 1.3 : DOM Manipulation - Moving elements
- .NET Debugging : Introduction to the Tools - .NET 2.0—Redistributable & .NET 2.0—SDK
- .NET Debugging : Managed Heap and Garbage Collection
- Context and Interception : Custom Component Services (part 3) - The Transaction Management Service
- Context and Interception : Custom Component Services (part 2) - The Logbook Service
- Context and Interception : Custom Component Services (part 1) - Building a Custom Context Attribute & Installing a Custom Message Sink
- Software Testing with Visual Studio Team System 2008 : Data-driven unit testing
- Software Testing with Visual Studio Team System 2008 : Unit testing an ASP.NET application
- Microsoft Enterprise Library : Error Management Made Exceptionally Easy - Replacing an Exception & Logging an Exception
- Microsoft Enterprise Library : Error Management Made Exceptionally Easy - Diving in with a Simple Example
- iPhone Programming : Connecting to the Network - Embedding a Web Browser in Your App
- iPhone Programming : Connecting to the Network - Detecting Network Status
- Parallel Programming with Microsoft Visual Studio 2010 : Introduction to Parallel Programming - Software Patterns
- Parallel Programming with Microsoft Visual Studio 2010 : Introduction to Parallel Programming - Multicore Computing & Speedup
- Microsoft ASP.NET 3.5 : Web Services for ASP.NET AJAX Applications (part 2) - Consuming AJAX Web Services
 
 
Most View
- Microsoft SQL Server 2008 R2 : Setting Up Replication (part 2) - Creating a Publication
- Developing an SEO-Friendly Website : Creating an Optimal Information Architecture (part 2)
- jQuery 1.3 : Working with numeric form data (part 6) - Finishing touches
- Windows Phone 7 : Working with the Calendar - Deleting Appointments
- Windows 7 : Managing a User Account - Limiting Computer Access
- SQL Server Integration Services : The Package Execution Utility (part 2) - Running Packages
- An OLAP Requirements Example: CompSales International (part 13) - Cube Perspectives
- Microsoft ASP.NET 3.5 : Web Services for ASP.NET AJAX Applications (part 1) - Remote Calls via Web Services
- Windows Small Business Server 2011 : Deploying SQL Server 2008 R2 for Small Business
- Processing and Storing Data in SQL Server 2005 : Data Migration from One Data Store to Another Data Store
Top 10
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 3) - Configuring Recipient Filtering
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 2)
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 1)
- Implementing Edge Services for an Exchange Server 2007 Environment : Installing and Configuring the Edge Transport Server Components
- What's New in SharePoint 2013 (part 7) - BCS
- What's New in SharePoint 2013 (part 6) - SEARCH
- What's New in SharePoint 2013 (part 6) - WEB CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 5) - ENTERPRISE CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- What's New in SharePoint 2013 (part 3) - REMOTE EVENTS