Today's content is Chapter 10 of "VBA's EXCEL Application" "Date and Time Processing in VBA". This is the second section of "current date and time, time function and time value". This set of tutorials starts with a simple recording macro and goes all the way to the construction of the form. It has rich content and many cases. It is very easy for you to master relevant knowledge. This set of tutorials is for beginners, with a total of three volumes and seventeen chapters. They are all knowledge points that we need to master in the process of using EXCEL. I hope everyone can master it.
Lecture 2 Current date and time, time function and time value
Hello everyone, let’s talk about the acquisition of date and time, the application of time functions, and the concept of time value. These are what we often use in our daily work, so it is necessary to study these knowledge points in detail.
1 NOW function
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
2 Hour, Minute, Second function
1) Hour function Returns a variable (integer) that specifies an integer representing between 0 and 23 of the hour of a day (including these two values).
Syntax: Hour(time)
Parameters: time is necessary to be any variable, numeric expression, string expression or any combination that can represent time. If "time" is Null, "Null" is returned.
2) Minute function Returns a Variant (Integer) that specifies an integer between 0 and 59 (including 0 and 59), representing the number of minutes.
Syntax: Minute(time)
Parameters: time is any variable, numeric expression, string expression or any combination that can represent time. If Time is Null, return "Null".
3) Second function Returns a Variant (Integer), specifying an integer between 0 and 59 (including 0 and 59) to represent the number of seconds within one minute.
Syntax: Second(time)
Parameters: time is any variable, numeric expression, string expression or any combination that can represent time. If Time is Null, return "Null".
3 TimeValue function
This function returns a Variant (Date) containing time. This function converts a string to a time series number. The sequence number of time is a number between 0 and 1. For example, noon (half of the day) is expressed as 0.5.
Syntax: TimeValue(time)
Parameters: time Required, usually a string expression, representing the time (including these two times) from 0:00:00(12:00:00A.M.) to 23:59:59 (11:59:59 P.M.). However, time may also be any expression indicating the time in this range. If time contains Null, Null is returned.
- can enter the valid time using a 12-hour system or a 24-hour system. For example, "2:24PM" and "14:24" are both valid time parameters.
- If the time parameter contains date information, TimeValue does not return this information. However, if time includes invalid date information, an error will occur.
4 Application example of time function
In order to better understand the time function, let's look at the following example, code:
Sub mynzA()
MsgBox "The current time is: " & Now
MsgBox "The current time is: " & Hour(Now)
MsgBox "The current time is: " & Hour(Now)
MsgBox "The current time is: " & Minute(Now)
MsgBox "The current time is: " & Second(Now)
MsgBox TimeValue("9:20:01 am")
Dim y As Double
y = TimeValue("9:20:01 am")
MsgBox The value of "TimeValue(" & "9:20:01 am" & ") is: " & y
y = TimeValue("12:00:00")
MsgBox The value of "TimeValue(" & "12:00:00" & ") is: " & y
End Sub
Code screenshot:
Code screenshot:
1) MsgBox "The current time is: " & Now This line of code will prompt the user system's current Now is date + time, as follows:
2) MsgBox "The current time hours are: " & Hour(Now) This sentence prompts the user's current hours:
3) MsgBox "The current time minutes are: " & Minute(Now)
4) MsgBox "The current time seconds are: " & Second(Now)
5) MsgBox TimeValue("9:20:01 am") This code prompts the user the result of TimeValue("9:20:01 am").
6) Dim y As Double
y = TimeValue("9:20:01 am")
MsgBox "TimeValue(" & "9:20:01 am" & ") The value of "TimeValue(" & "9:20:01 am" & ") is: " & y
The above code prompts a TimeValue value of Double data type:
7) y = TimeValue("12:00:00")
MsgBox "TimeValue(" & "12:00:00" & ") is: " & y
above code prompts a TimeValue value of Double data type:
Today's content dedication:
) What is the function of NOW function?
2) What is the function of the TimeValue function?
3) How to get the number of hours, minutes, seconds of time?
This lecture content reference program file: workbook 10.xlsm
Share the results, rejoice in positive energy
VBA application and learning methods:
VBA is an effective means (tool) to use Office to realize personal small office automation. This is my definition of VBA. Before the new office software that replaces OFFICE arrives, whoever can achieve the ultimate in data processing will be the king. Among them, the most extreme skill is VBA!
I remember when I first started VBA 20 years ago, there was very little information at that time, so I could only read the source code and think about it myself. It was really difficult. 20 years have passed. In order to prevent my friends who are studying VBA from repeating my previous experience, I have launched seven VBA special tutorials based on my years of practical VBA use experience.
The first set: VBA code solution is an explanation of various knowledge points in VBA. The tutorial has 147 lectures in total, covering most VBA knowledge points. The program files provided are a rare treasure house of code and are a must-have tutorial for beginners and intermediate personnel. The version provided by this set of tutorials is currently revised in the second version, and the program files have passed the 32-bit and 64-bit OFFICE system test.
Second set: VBA database solution Database is a professional tool for data processing. The tutorial introduces in detail the methods and example operations of using ADO to connect ACCDB and EXCEL, which is suitable for the learning of intermediate personnel. Currently, this tutorial provides a revision of the first version of the tutorial, and the program files pass the 32-bit and 64-bit OFFICE system test.
The third set: VBA array and dictionary solution Arrays and dictionaries are the essence of VBA. Dictionaries are an effective means to improve VBA code level. They are worthy of in-depth learning and are a means for the improvement of code in junior and intermediate personnel. Currently, the version provided by this tutorial is the first version of the revision, and the program files have been tested through two OFFICE systems: 32-bit and 64-bit.
Set 4: VBA Code Solution Video is a video explanation specifically 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 easier to accept.
Set 5: Interpretation and utilization of classes in VBA This is an advanced tutorial that explains the vanity of classes and the physical condition. Although the use of classes is less, careful study can promote the improvement of one's own VBA theory. The understanding of this set of tutorials is mainly the readers' understanding and the understanding of a Buddhist philosophy. Currently, the version provided by this tutorial is the first version of the revision, and the program files have been tested through two OFFICE systems: 32-bit and 64-bit.
The sixth set of tutorials: VBA information acquisition and processing, is an advanced tutorial, which involves a wider range and is more practical, and is aimed at middle and senior personnel. The tutorial has twenty topics, including: cross-application information acquisition, random information utilization, email sending, VBA Internet data grabbing, VBA delay operation, clipboard application, Split function expansion, worksheet information interaction with other applications, FSO object utilization, worksheet and folder information acquisition, graphical information acquisition, and customized worksheet information functions. The program files are tested through two OFFICE systems: 32-bit and 64-bit.
The Seventh Tutorial: VBA's EXCEL Application This is a primary tutorial. This tutorial has three volumes in total. From the basic contents such as creating macros, dialog boxes, workbooks and worksheet objects, cell objects, etc., to loop structure, error handling, string operations, date and time, events, array applications, function processes, etc., it is necessary for us to improve our EXCEL level.
The learning order of each tutorial above is: 7→1 (or 4)→3→2→6→5. The fourth set is an explanation of the first set of videos, so choose one of the first and fourth sets.
