Excel VBA: Insert Timestamp When a Macro Is Run

Get FREE Advanced Excel Exercises with Solutions!

In this article, I’ll show you how you can keep a timestamp in Excel VBA every time a Macro is run.


Excel VBA: Insert Timestamp When a Macro Is Run (Quick View)

Sub Timestamp_When_a_Macro_is_Run()

Timestamp_Sheet = "Sheet1"
Timestamp_Column = "B"

Set Timestamp_Range = Worksheets(Timestamp_Sheet).Range(Timestamp_Column + Right(Str(1), 1))
i = 1
While Timestamp_Range.Cells(i, 1) <> ""
    i = i + 1
Wend

Timestamp_Range(i, 1) = Now
Timestamp_Range(i, 1).NumberFormat = "m/d/yyyy h:mm:ss AM/PM"

End Sub

VBA Code to Timestamp When a Macro is Run in Excel VBA


Excel VBA Timestamp When Macro Is Run: Step-by-Step

So, without further delay, let’s go to our main discussion today. We’ll break down the VBA code step-by-step to learn how to timestamp when a Macro runs using VBA.

Actually, in VBA, timestamping is easy. VBA provides a function called Now that returns the current time each time a Macro is run. The function along with some number formatting can provide you an excellent timestamping every time you run your code.

But there’s an additional task here. We want to keep a record of all the instances when the Macro is run. Therefore, we’ve to arrange the code in a way such that it can keep a record of all the instances. We’ll use a while-loop for this purpose.

In a nutshell, there are a total of 3 steps to developing the code.


⧪ Step 1: Inserting Necessary Inputs

At the very outset of the code, we have to insert the necessary inputs into the code. The inputs include:

  • The Name of the Worksheet Where You Want to Record the TimeStamps.
  • The Column Inside the Worksheet
Timestamp_Sheet = "Sheet1"
Timestamp_Column = "B"

Inserting Inputs to TimeStamp When a Macro is Run in Excel


⧪ Step 2: Iterating Through a While-Loop to Find Out the Position of New Timestamp

Next, we’ll iterate through a while-loop to find the position of the new timestamp.

Set Timestamp_Range = Worksheets(Timestamp_Sheet).Range(Timestamp_Column + Right(Str(1), 1))
i = 1
While Timestamp_Range.Cells(i, 1) <> ""
    i = i + 1
Wend

Iterating to TimeStamp When a Macro is Run in Excel

⧪ Step 3: Timestamping

This is the most important step. We’ll use the Now function of VBA along with some number formatting to timestamp, that is, to record the instant when the Macro is run.

Timestamp_Range(i, 1) = Now
Timestamp_Range(i, 1).NumberFormat = "m/d/yyyy h:mm:ss AM/PM"

So the complete VBA code will be:

VBA Code:

Sub Timestamp_When_a_Macro_is_Run()

Timestamp_Sheet = "Sheet1"
Timestamp_Column = "B"

Set Timestamp_Range = Worksheets(Timestamp_Sheet).Range(Timestamp_Column + Right(Str(1), 1))
i = 1
While Timestamp_Range.Cells(i, 1) <> ""
    i = i + 1
Wend

Timestamp_Range(i, 1) = Now
Timestamp_Range(i, 1).NumberFormat = "m/d/yyyy h:mm:ss AM/PM"

End Sub

VBA Code to Timestamp When a Macro is Run in Excel VBA


Developing the Macro to Insert Timestamp When It Is Run

We’ve seen the step-by-step analysis of the code to timestamp when a Macro runs.

Now let’s see how to develop the Macro with the code.

⧪ Step 1: Opening VBA Window

Press ALT + F11 on your keyboard to open the Visual Basic window.

Opening the VBA Window to TimeStamp When a Macro is Run in Excel

⧪ Step 2: Inserting a New Module

Go to Insert > Module in the toolbar. Click on Module. A new module called Module1 (or anything else depending on your history) will open.

⧪ Step 3: Putting VBA Code

This is the most important step. Insert the given VBA code in the module.

Putting the VBA Code to TimeStamp When a Macro is Run in Excel

⧪ Step 4: Running the Code

Click on the Run Sub / UserForm tool from the toolbar above.

The code will run recording the current instant, in a new cell of your desired column (Column B here).

Read More: Now and Format Functions in Excel VBA


Things to Remember

In this code, we’ve used the most common format to record a date and time together (“m/d/yyyy h:mm:ss AM/PM”). If you wish, you can change the format. Here you can have a look at the use of time formats available in VBA.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

Therefore, this is the process to timestamp each time a Macro runs using Excel VBA. If you have any questions or suggestions regarding this, please feel free to share these in the comment section below or on our forum.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo