How To Use VBA To Insert Current Date Time In Excel

This article will tell you how to use the VBA sub-procedure to catch the worksheet change event and then insert the current date time in the next adjacent cell. It will also tell you how to use VBA to create an excel function and call the function to insert the current date and time in the current cell.

1. How To Create VBA Sub Procedure To Catch Worksheet Change Event And Insert The Current Date & Time In The Adjacent Cell.

1. Click the excel Developer tab, then click the Visual Basic icon to open the Microsoft Visual Basic for Applications window.
2. If you can not find the Developer tab, you can read the article How To Enable Developer Tab And Write VBA Source Code In Excel to learn how to enable it.
3. Double-click the worksheet name in the VBA Project panel on the window’s left side.
4. Then it will open the source code editor on the window’s right side.
5. And the VBA project file’s extension is .xlsm.
6. Now input the below private sub-procedure source code in the source code editor.
7. The sub-procedure Worksheet_Change will be triggered when you do any change on the selected worksheet. You can see the code comments for detailed explanations.

   ' This function will catch the worksheet change event.
   ' So when you make any change in the worksheet, it will trigger this function.
   
   ' The Target variable is the changed excel range.
   Private Sub Worksheet_Change(ByVal Target As Range)
   
   ' If the changed worksheet is located in the first column (column A).
   ' And the target cell value is empty string
   If Target.Column = 1 And Target.Value  "" Then
       
       ' Disable any excel events on this worksheet.
       Application.EnableEvents = False
   
       ' Assign the current date and time value to a variable.
       CurrTime = Now()
       
       ' Define a variable to save the date and time string format.
       TimeFormat = "dd-mm-yyyy hh:mm:ss"
       
       ' Format the current date and time value to a string.
       CurrTimeString = Format(CurrTime, TimeFormat)
       
       ' Set the current date and time string to the cell after the current cell.
       ' For example, if the current cell is A1, then it will insert the date and time string in cell B.
       Target.Offset(0, 1) = CurrTimeString
   
       ' Enable excel events on this worksheet.
       Application.EnableEvents = True
   
   End If
   
   End Sub
   

8. Click the Save button to save the above VBA source code.
9. Now when you input or update cell data in the worksheet’s first column (column A), it will insert or update the date & time value in column B.

2. How To Create Excel Function To Insert The Current Date & Time In Selected Cell.

1. Open the Microsoft Visual Basic for Applications window.
2. Click the Insert —> Module menu item in the top menu bar.
3. Then it will insert a new Module file under the VBA Project / Modules folder.
4. If there is already a module that exists, then you can double-click the module to open it.
5. Input the below source code in the module file.

   Function InsertDateTime()
     
        ' Get the current date time value.
        CurrDateTime = Now()
        
        ' Define date time string format.
        DateTimeFormat = "dd-mm-yyyy hh:mm:ss"
        
        ' Return the formatted current date time string.
        InsertDateTime = Format(CurrDateTime, DateTimeFormat)
     
   End Function

6. Now you can call the function InsertDateTime in the formula =InsertDateTime() in an excel cell, then it will insert the current date & time string in the cell.
7. If you want to insert the current date & time in the target cell when the user inputs a value in another cell, then you can create the below function.

   ' Define an excel function, this function will return the current date & time string value.
   ' The input argument is the target excel cell or cell range address.
   Function InsertDateTimeTo(Target As Range)
     ' If the target cell is not empty.
     If Target.Value  "" Then
     
        ' Get the current date time value.
        CurrDateTime = Now()
        
        ' Define date time string format.
        DateTimeFormat = "dd-mm-yyyy hh:mm:ss"
        
        ' Return the formatted current date time string.
        InsertDateTimeTo = Format(CurrDateTime, DateTimeFormat)
     Else
        
        ' If the target cell is empty Return an empty string.
        InsertDateTimeTo = ""
     End If
   End Function
   

8. You can call the above function InsertDateTimeTo(Target As Range) in a formula such as =InsertDateTimeTo(A1) in cell B1.
9. Now when you input any data in cell A1, it will display the current date & time string in cell B1.