Today's content is Chapter 10 of "Excel Application of VBA" "Processing of Dates and Times in VBA". This is section 2, "Current Date and Time, Time Functions, and Time Values." This set of tutorials starts from a simple recording macro, all the way to the construction of the form, rich in content and numerous cases. You can easily grasp the relevant knowledge, this set of tutorials for beginners, a total of three volumes, seventeen chapters, are our use of EXCEL work process need to master the knowledge points, I hope you can master the use.
<h1>Lecture 2: Current Date and Time, Time Functions, and Time Values</h1>
Hello everyone, let's talk about the acquisition of dates and times, as well as the application of time functions, the concept of time values. These are often used in our daily work, and it is necessary to study these knowledge points in detail.
<h1>1 NOW function</h1>
To get the current date and time, you can use the Now function. This function returns a Variant(Date), which specifies the current date and time based on the computer's system date and time.
Syntax: Now
Example application: Today = Now
<h1>2 Hour, Minute, Second function</h1>
1) The Hour function returns a variable (integer) that specifies an integer representing an integer between 0 and 23 of the hour of the day, inclusive.
Syntax: Hour(time)
Argument: Time is required to be any variable, numeric expression, string expression, or any combination that can represent time. If "time" is Null, "Null" is returned.
2) The Minute function returns a Variant (Integer) that specifies an integer between 0 and 59, representing the number of minutes.
Syntax: Minute(time)
Argument: Time is any variable, numeric expression, string expression, or any combination that can represent time. If Time is null, "Null" is returned.
3) The Second function returns a Variant (Integer) that specifies an integer between 0 and 59 (which contains the numbers 0 and 59) to represent the number of seconds in a minute.
Syntax: Second(time)
<h1>3 TimeValue function</h1>
This function returns a Variant (Date) containing time. This function converts a string to a time series number. The ordinal number of the time is a number between 0 and 1. For example, noon (half of the day) is represented as 0.5.
Syntax: TimeValue(time)
Argument: time required, usually a string expression, represented from 0:00:00(12:00:00A.M.) to 08:59:59 (11:59:59 P.M.) Time (including both times). However, time can also be an arbitrary expression that represents the time in that range. If time contains Null, Null is returned.
Valid times can be entered using either a 12-hour clock or a 24-hour clock. For example, "2:24PM" and "14:24" are both valid time parameters.
If the time parameter contains date information, TimeValue does not return that information. However, if time includes invalid date information, an error occurs.
<h1>4 Application examples of time functions</h1>
To better understand the time function, let's look at the following example, code:
Sub mynzA()
MsgBox "The time is now:" & Now
MsgBox "The hours of the present time are:" & Hour (Now)
MsgBox "The number of minutes in the present time is:" & Minute(Now)
MsgBox "The number of seconds of the present time is:" & Second (Now)
MsgBox TimeValue("9:20:01 am")
Dim y As Double
y = TimeValue("9:20:01 am")
The value of MsgBox "TimeValue(" & "9:20:01 am" & ") is: " & y
y = TimeValue("12:00:00")
The value of MsgBox "TimeValue(" & "12:00:00" & ") is: " & y
End Sub
Code screenshot:
1) MsgBox "The time is now:" & Now This line of code will prompt the user that the current Now now is the date + time, as follows the result of running:
2) MsgBox "The number of hours of the current time is:" & Hour (Now) This code prompts the user for the current number of hours:
3) MsgBox "The number of minutes in the current time is:" & Minute(Now)
4) MsgBox "The number of seconds of the present time is:" & Second(Now)
5) MsgBox TimeValue ("9:20:01 am") This code prompts the user for the result of TimeValue ("9:20:01 am").
6） Dim y As Double
The above code suggests a TimeValue value of the Double data type:
7) y = TimeValue("12:00:00")
Today's content turn:
1) What does the NOW function do?
2) What does the TimeValue function do?
3) How do I get the hours, minutes, seconds of time?
Reference program file for this lecture: Workbook 10.xlsm
Share the results and enjoy the positive energy
<h1>Application and learning methods of VBA:</h1>
VBA is an effective means (tool) to use Office to automate personal small offices. This is my definition of the application of VBA. Before the arrival of the new office software to replace OFFICE, whoever can do the ultimate in data processing is the king. One of the pinnacle skills is VBA!
I remember when I first learned VBA 20 years ago, there was very little information at that time, and I could only look at the source code to figure it out for myself, which was really difficult. After 20 years, in order not to let my friends who study VBA repeat my previous experience, I launched seven VBA special tutorials based on my many years of actual experience in using VBA.
The first set: VBA code solution is the explanation of each knowledge point in VBA, the tutorial is 147 lectures, covering the vast majority of VBA knowledge points, the program files provided are a rare code treasure house, is a must-have tutorial for beginners and intermediate personnel; the current version of the tutorial provided by this set of tutorials is the revised second edition, and the program files have passed the 32-bit and 64-bit OFFICE system tests.
The second set: VBA database solution The database is a professional tool for data processing, and the tutorial details the method and example operation of connecting ACCDB and EXCEL using ADO, which is suitable for intermediate personnel to learn. The current set of tutorials provides a revised first version of the tutorial, and the program files are tested by both 32-bit and 64-bit OFFICE systems.
The third set: VBA array and dictionary solution Array and dictionary is the essence of VBA, dictionary is an effective means to improve the level of VBA code, worthy of in-depth learning, is the means of code improvement for beginners and intermediate personnel. The current version of this set of tutorials is the first revision, and the program files pass the 32-bit and 64-bit OFFICE system tests.
The fourth set: VBA code solution video is a video guide for beginners, you can get started quickly and master this skill faster. This set of tutorials is a video explanation of the first set of tutorials (revised version), and the video is more acceptable.
The fifth set: interpretation and utilization of classes in VBA This is an advanced tutorial that explains the nihilism of classes and the degree of the flesh, although the use of classes is less, but careful study can promote the improvement of their own VBA theory. The comprehension of this set of tutorials is mainly the reader's comprehension, the comprehension of a Buddhist philosophy. The current version of this set of tutorials is the first revision, and the program files pass the 32-bit and 64-bit OFFICE system tests.
The sixth set of tutorials: VBA information acquisition and processing, this is an advanced tutorial, covering a wider range, more practical, for intermediate and senior personnel. The tutorial consists of 20 topics, including: cross-application information acquisition, use of random information, email sending, VBA Internet data scraping, VBA delay operation, clipboard application, Split function extension, worksheet information interaction with other applications, the use of FSO objects, worksheet and folder information acquisition, graphical information acquisition, and custom worksheet information functions. Program files are tested by both 32-bit and 64-bit OFFICE systems.
The seventh set of tutorials: VBA EXCEL application This is a beginner tutorial This tutorial has a total of three volumes, from the creation of macros, dialog boxes, workbooks and worksheet objects, cell objects and other basic content, to loop structure, error handling, string operations, dates and times, events, array applications, function procedures, etc., all the way to the application of controls and form objects are our must to improve our EXCEL level.
The order of study of each tutorial above: 7→ 1 (or 4) → 3→ 2→6→5. The fourth set is a video explanation of the first set, so the first and fourth can only choose one.