Retrieve Current Date And Time

VBA gives you the ability to retrieve the current date and time information from your system using some of its built-in functions. VBA includes several date-related built-in functions that you can add to subroutines and functions that you create. You use these functions to return a system date or time, perform date calculations, set a date, and even time a specific process.

If you want to include the current date and time information, you can select from three different functions. The Date function returns the current system date, the Time function returns the current system time, and the Now function returns both the date and time.

VBA formats the date and time information to match your system's short date format. You can modify the date and time settings via the Start menu.

When working with dates, you can avoid displaying a date outside of a range by remembering the date range that Excel accepts. VBA accommodates a much larger date range than Excel by accepting dates between January 1, 100, and December 31, 9999. Excel for Windows, however, only accepts dates between January 1, 1990, and December 31, 9999. If you happen to use Excel on a Macintosh, the date range is even smaller with the acceptable dates being January 1, 1904, to December 31, 9999. Hopefully none of these date limits pose any issues for you as you work with dates in the macros your create. If you need to display dates outside the range, you can do so by placing the date in a string variable.

You can assign the results of the Date or Time function to another variable, a worksheet cell, or another function, such as the MsgBox function, as in this example:

MsgBox("Current Date and Time: " & Now()).

RETRIEVE CURRENT DATE

RETRIEVE CURRENT DATE

-D Create a new subroutine.

0 Type MsgBox ("Current date: " & Date).

■ You can modify the date setting by clicking Start O Settings O Control Panel O Regional Options.

-D Create a new subroutine.

0 Type MsgBox ("Current date: " & Date).

■ The current system date displays in the message box.

■ You can modify the date setting by clicking Start O Settings O Control Panel O Regional Options.

Ttl VBA uses the same serial number system for dates and times as Excel, which stores each date as a numeric value. You can express each date and time as a sequential number starting at 0. You can express the date portion of the number as the integer portion of the number and the time portion of the date as a decimal value between 0 and 0.99999999 representing times from 0:00:00 (12:00 Midnight) to 23:59:99 (11:59:99 PM). Because Excel stores dates and times as numeric values, you can easily manipulate them by adding and subtracting them.

Excel uses the Western calendar to determine the number of days in each month and which month is the first month of the year. Using this calendar, the first month of the year, January, has 31 days and the second month has 28 days with the exception of years divisible by 4, which have 29 days. All other months have 31 or 30 days. One exception to the leap year rule, century years must be divisible by 400 to be a leap year; therefore 2000 is a leap year, but 3000 is not.

RETRIEVE CURRENT TIME

RETRIEVE CURRENT TIME

□ Create a new subroutine.

< Switch to Excel and run the macro.

□ Create a new subroutine.

LH Type Cells(1, 1) = "Current Time: " & Time() replacing Cells(1, 1) with the location for the time and "Current Time" with the text string to display.

< Switch to Excel and run the macro.

m

File Edit

View Insert Format

Tools Data Windol"

Help

Type a question For help

- . ff X

i □

00É

ÍJÍB7 » «a I «

n mm "i«

ii10 - B

/Us

_ m

A1

»

fi Current Time: 12:52:26 PM

A I B

C

D

E

F

G

H

1

J

K

L

il

i

Current TiHtie: 12:52:26 PM

i

3

4

5

G

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

-

m -1

h h \Sheet6/ Sheetl / Sheet2

sheets /

ieet4 / 5heet3 /

Ready

-■ The current system time displays in the first cell.

■ You can modify the time setting by clicking Start O Settings O Control Panel O Regional Options.

0 0

Post a comment