Date and Time in Excel-VBA

In this modern world of computer technology the creation of Excel reports are termed to be the crucial elements, and the particular type of programs that helps us in performing of the tasks is none other than the Microsoft Excel.

Besides all these, there is the availability of the other most essential tool to achieve these various tasks is VBA, which is the acronym for the Virtual Basic for Applications. This application is termed the Microsoft Applications, whose practical knowledge plays an essential role for the professionals in efficiently maintaining or handling the massive amount of available data. Due to this, the corporate world's people must have training related to macro excel.

This tutorial we will quickly cover the various steps involved in adding data and time in Excel VBA.

How to Add DATE and TIME in the Microsoft Excel- VBA?

Before discussing the topics of how to work with the data and the Time in the Microsoft Excel –VBA, let us first know about the following things in detail as follows:

1. What do you understand by the term Excel VBA?
2. How to open VBA in Excel 2010?

Now, moving on to discussing each of the above concepts one after the other in detail,

What do you understand by the term Excel VBA?

The word VBA is an acronym for Visual Basic for Applications. And the term Excel VBA is defined to be the most acceptable type of Microsoft's programming language that can be used for Excel and the other various Microsoft office programs; some of them are as follows:

• Microsoft Word.
• Microsoft PowerPoint.

And it was noted that the particular Office suite programs share a standard programming language.

How to open VBA in Excel 2010?

Opening of the VBA Environment

An individual can easily access the VBA (Virtual Basic for Applications) environment in Microsoft Excel 2010 with the help of the Microsoft Visual Basic for Applications window.

One must ensure that the Developer tab is visible in the toolbar. As seen in the attached Screenshot.

And the Developer tab is a toolbar which has specific types of buttons for the opening of the VBA editor and to make the creation of the Form/ActiveX controls, such as the checkboxes, buttons and many more.

• If the Developer tab is unavailable over the toolbar, one can follow the following steps to make it appear on the taskbar.
• Click on the File menu option from the toolbar, and we will display the various options after clicking on it.

• And in that options, click on the Options buttons. An excel options window will appear, and one should Customize Ribbon options on the left-hand side.

• After choosing the Customize Ribbon option, a window with lots of opportunities will open, and one should click on the Developer checkbox on the right-hand side.

• After that, click on the Ok button.

• And the Developer tab will get appeared in the toolbar effectively.
• Now one should select the Developer tab from the toolbar and then click or select the Visual Basic option in the Code Group.

And now, doing the above steps, one can get the Microsoft Visual Basic Editor on their screen and write the specific amount of code for performing the various operations.

How to make changes in the format of the Date in VBA?

Let us understand this with the help of the example discussed below:

# Example 1:

Below given is an example wherein the excel sheet has multiple cells occupied with the same date.

And in these, we will perform or apply the various data formats for the same data to visualize its impact.

And to do this, we will firstly be copying the data to the respective next column,

And now, we will first apply the format of “DD-MM-YYYY” FORMAT in the respective cell called A1.

Now in this code, we will select the cell with the help of the RANGE object.

Code:

Sub Date_Format_Example1 ()
Range (“A1”)
End Sub

Code written in the VBA (Virtual Basic for Applications), and screenshots is attached below.

As we are making changes in the date format of the individual cell, we are liable to access the “Number Format” property from the object RANGE. And then, we need to set the number format by putting the equality sign in the double quotations.

Code:

Sub Date_Format_Example1 ()
Range (“A1”).NumberFormat =”dd-mm-yy”
'This will change the Date to 24/10/2020'
End Sub

And the output for the above results is as follows:

# Example 2:

Similarly, we are using different formatting codes according to our needs and the choice of the other cells. We have also mentioned the regulations and codes on the VBA and their Screenshot.

Code:

Sub Date_Format_Example2 ()
Range (“A2”) .NumberFormat =”ddd-mm-yyy”
'This will change the Date to "Sat-10-2020."
End Sub

End Sub

Output:

The output for the above code is shown below in the screenshots:

Sub Date_Format_Example2 ()
Range (“A3”) .NumberFormat =”dddd-mm-yyy”
'This will change the Date to "Saturday-10-2020."
End Sub

Code written in the VBA (Virtual Basic for Applications), and screenshots is attached below.

Output:

 The output for the above code is shown below in the screenshots:

Change Date Format with the help of using the FORMAT Function

It was known that in the respective VBA (Virtual Basic for Applications), we have a separate Function known as the FORMAT function that the particular individuals can use to the specific format of the cell.

Syntax:

An individual must specify the value used for the "Expression" and apply the particular "FORMAT" accordingly.

Code:

Sub Date_Format_Example ()
Dim MyVal As a Variant
Val =58967
MsgBox Format(MyVal, “DD-MM-YYYY”)
End Sub

Output:

 The output for the above code is shown below in the screenshots:

Following points to remember

The following points that an individual must remember while working with Microsoft Excel using the VBA ((Virtual Basic for Applications) are as follows:

• The critical point is that an individual system's default date will be effectively applied to excel.
• And the particular type of the Number Format property can be used by an individual to change the format of the respective date in the VBA (Virtual Basic for Applications).
• And by the help of the Format Function, one can change the format of the date as per the needs of the individual and their choice.

How to make use of the Time Function in the Excel VBA

The particular VBA (Virtual Basic for Applications) returns the current Time. The essential point that needs to be noted by an individual is that the above-used function does not have any arguments, which means a process with no ideas. The other important point is that the above-used function returns the current Time of the respective computer system.

And with the help of the VBA (Virtual Basic for Applications), one can easily find out the actual Time that is usually taken by the particular lines of the codes to complete the process efficiently.

Besides all this, Time is that kind of function (volatile function) with no syntax.

And in these modern worlds of computer science, excel also provides a similar function that is the NOW () function, which in turn is used for inserting the current times and the current date in the working area of the excel sheet called the spreadsheet.

TIME ()

In this, an individual needs to enter the function, and there is no need for the parenthesis to enclose it, as TIME is capable enough of inserting the current Time, and the results given by the TIME function are in the form of the strings.

How to use the TIME Function in the VBA (Virtual Basic for Applications)?

Now, the understanding of the Time Function in excel is as follows:

One can make use of the Time Function by following up on the below-mentioned steps:

STEP 1: First, an individual needs to create a macro, and the code for that macro is as follows:

Code:

Sub Time-Example ()
End Sub

Code written in the VBA (Virtual Basic for Applications), and screenshots is attached below.

STEP 2:  Declaring the variable in the form of the string and the respective code for that is as follows with the help of the attached screenshots on the VBA.

Sub Time_Example()
Dim CurrentTime As String
End Sub

Code written in the VBA (Virtual Basic for Applications), and screenshots is attached below.

STEP 3: After that, one should assign the value to this variable through the TIME Function, and the associated code on the VBA with the Screenshot is as follows:

Sub Time_Example()
Dim CurrentTime As String
CurrentTime=Time
End Sub

Code written in the VBA (Virtual Basic for Applications), and screenshots is attached below.

STEP 4: Now, we will display the results in the message box

Code:

Sub Time_Example()
Dim CurrentTime As String
CurrentTime=Time
MsgBox CurrentTime=Time
End Sub

And the output for the above code is as follows:

In this way, by following the steps mentioned earlier, one can effectively insert the current Time in the excel sheet according to their need of use.