get old values of cell vba code example

Example: excel vba how to get the old value of a changed cell

'Place the following in the CODE MODULE of a WORKSHEET 
'to track the last value for every cell in the used range:

Option Explicit

Private r As Range
Private Const d = "||"

Public Function ValueLast(r As Range)
    On Error Resume Next
    ValueLast = Split(r.ID, d)(1)
End Function

Private Sub Worksheet_Activate()
    For Each r In Me.UsedRange: Record r: Next
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    For Each r In Target: Record r: Next
End Sub

Private Sub Record(r)
    r.ID = r.Value & d & Split(r.ID, d)(0)
End Sub

'----------------------------------------------------------------------
'And that's it.

'This solution uses the obscure and almost never used Range.ID 
'property, which allows the old values to persist when the workbook 
'is saved.

'At any time you can get at the old value of a cell and it will 
'indeed be different than a new current value:

With Sheet1
	MsgBox .[a1].Value
    MsgBox .ValueLast(.[a1])
End With