How do I get the old value of a changed cell in Excel VBA?

📝 Tech Blog: How to Get the Old Value of a Changed Cell in Excel VBA

Have you ever found yourself in a situation where you need to know the old value of a cell that was just changed in Excel VBA? It can be frustrating when you're trying to track changes or perform certain actions based on previous values. But worry not, I've got you covered with a simple solution!

The Challenge

Let's say you have a worksheet with certain cells that you are interested in monitoring for changes. You want to capture the old value of the cell before it gets updated and perform some operations based on that value. In the provided code snippet, the Worksheet_Change event is used to detect changes in the desired range.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    Dim old_value As String
    Dim new_value As String

    For Each cell In Target
        If Not (Intersect(cell, Range("cell_of_interest")) Is Nothing) Then
            new_value = cell.Value
            old_value = ' what here?
            Call DoFoo(old_value, new_value)
        End If
    Next cell
End Sub

You can see that the new_value is easily obtained from the cell.Value, but what about the old value? How can we retrieve it?

The Solution

To get the old value of a changed cell, we can leverage the Worksheet_Change event's Worksheet_Change(ByVal Target As Range) argument. This argument provides a reference to the range of cells that were changed.

We can utilize the Target argument to compare the current value of the cell against its previous value. By using the Worksheet_Change event's Application.Undo method, we can revert the cell to its previous state temporarily and retrieve its old value.

Let's modify the code snippet to include this solution:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    Dim old_value As String
    Dim new_value As String

    For Each cell In Target
        If Not (Intersect(cell, Range("cell_of_interest")) Is Nothing) Then
            new_value = cell.Value
            Application.Undo
            old_value = cell.Value
            Call DoFoo(old_value, new_value)
        End If
    Next cell
End Sub

With the addition of Application.Undo before retrieving the value of the cell, we can temporarily revert it to its old value. After capturing the old value, we can perform any necessary actions using both the old and new values.

Call to Action

Now that you know how to retrieve the old value of a changed cell in Excel VBA, go ahead and implement it in your own projects! 💪

I hope this guide helps you overcome the challenge of obtaining old cell values. If you have any further questions or suggestions, please feel free to leave a comment below. 🔍😊

Happy coding! 🚀