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

Question

I m detecting changes in the values of certain cells in an Excel spreadsheet like this     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   Assuming this isn t too bad a way of coding this  how do I get the value of the cell before the change

User · Answer

I have read this old post  and I would like to provide another solution   The problem with running Application Undo is that Woksheet Change runs again  We have the same problem when we restore   To avoid that  I use a piece of code to avoid the second steps through Worksheet Change   Before we begin  we must create a Boolean static variable BlnAlreadyBeenHere  to tell Excel not to run Worksheet Change again  Here you can see it       Private Sub Worksheet Change ByVal Target As Range       Static blnAlreadyBeenHere As Boolean       This piece avoid to execute Worksheet Change again     If blnAlreadyBeenHere Then         blnAlreadyBeenHere   False         Exit Sub     End If       Now  we will store the old and new value     Dim vOldValue As Variant     Dim vNewValue As Variant       To store new value     vNewValue   Target Value       Undo to retrieve old value       To avoid new Worksheet Change execution     blnAlreadyBeenHere   True      Application Undo       To store old value     vOldValue   Target Value       To rewrite new value       To avoid new Worksheet Change execution agein     blnAlreadyBeenHere   True     Target Value   vNewValue       Done  I ve two vaules stored     Debug Print vOldValue  vNewValue  End Sub   The advantage of this method is that it is not necessary to run Worksheet SelectionChange   If we want the routine to work from another module  we just have to take the declaration of the variable blnAlreadyBeenHere out of the routine  and declare it with Dim   Same operation with vOldValue and vNewValue  in the header of a module  Dim blnAlreadyBeenHere As Boolean Dim vOldValue As Variant Dim vNewValue As Variant

User · Answer

Here s a way I ve used in the past  Please note that you have to add a reference to the Microsoft Scripting Runtime so you can use the Dictionary object - if you don t want to add that reference you can do this with Collections but they re slower and there s no elegant way to check  Exists  you have to trap the error    Dim OldVals As New Dictionary Private Sub Worksheet Change ByVal Target As Range  Dim cell As Range     For Each cell In Target         If OldVals Exists cell Address  Then             Debug Print  New value of    amp  cell Address  amp    is    amp  cell Value  amp     old value was    amp  OldVals cell Address          Else             Debug Print  No old value for     cell Address         End If         OldVals cell Address    cell Value     Next End Sub   Like any similar method  this has its problems - first off  it won t know the  old  value until the value has actually been changed  To fix this you d need to trap the Open event on the workbook and go through Sheet UsedRange populating OldVals  Also  it will lose all its data if you reset the VBA project by stopping the debugger or some such

User · Answer

I had to do it too  I found the solution from  Chris R  really good  but thought it could be more compatible in not adding any references  Chris  you talked about using Collection  So here is another solution using Collection  And it s not that slow  in my case  Also  with this solution  in adding the event   SelectionChange   it s always working  no need of workbook open    Dim OldValues As New Collection  Private Sub Worksheet SelectionChange ByVal Target As Range       Copy old values     Set OldValues   Nothing     Dim c As Range     For Each c In Target         OldValues Add c Value  c Address     Next c End Sub  Private Sub Worksheet Change ByVal Target As Range      On Local Error Resume Next    To avoid error if the old value of the cell address you re looking for has not been copied     Dim c As Range     For Each c In Target         Debug Print  New value of    amp  c Address  amp    is    amp  c Value  amp     old value was    amp  OldValues c Address      Next c      Copy old values  in case you made any changes in previous lines of code      Set OldValues   Nothing     For Each c In Target         OldValues Add c Value  c Address     Next c End Sub

User · Answer

Using Static will solve your problem  with some other stuff to initialize old value properly   Private Sub Worksheet Change ByVal Target As Range      Static old value As String     Dim inited as Boolean  Used to detect first call and fill old value     Dim new value As String     If Not Intersect cell  Range  cell of interest    Is Nothing Then          new value   Range  cell of interest   Value          If Not inited Then              inited   True          Else             Call DoFoo  old value  new value          End If         old value   new value     Next cell End Sub   In workbook code  force call of Worksheet change to fill old value   Private Sub Private Sub Workbook Open        SheetX Worksheet Change SheetX Range  cell of interest   End Sub   Note  however  that ANY solution based in VBA variables  including dictionary and another more sophisticate methods  will fail if you stop  Reset  running code  eg  while creating new macros  debugging some code        To avoid such  consider using alternative storage methods  hidden worksheet  for example

User · Answer

I needed this feature and I did not like all the solutions above after trying most as they are either   Slow  Have complex implications like using application undo   Do not capture if they were not selected Do not captures values if they were not changed before Too complex   Well I thought very hard about it and I completed a solution for a full UNDO REDO history   To capture the old value it is actually very easy and very fast   My solution is to capture all values once the user open the sheet is open into a variable and it gets updated after each change  this variable will be used to check the old value of the cell  In the solutions above all of them used for loop  Actually there is way easier method   To capture all the values I used this simple command  SheetStore   sh UsedRange Formula   Yeah  just that  Actually excel will return an array if the range is a multiple cells so we do not need to use FOR EACH command and it is very fast  The following sub is the full code which should be called in Workbook SheetActivate  Another sub should be created to capture the changes  Like  I have a sub called  catchChanges  that runs on Workbook SheetChange  It will capture the changes then save them on another a change history sheet  then runs UpdateCache to update the cache with the new values    should be added at the top of the module Private SheetStore   As Variant  Private SheetStoreName As String    I use this variable to make sure that the changes I captures are in the same active sheet to prevent overwrite  Sub UpdateCache sh As Object        If sh Name   ActiveSheet Name Then   update values only if the changed values are in the activesheet           SheetStoreName   sh Name           ReDim SheetStore 1 To sh UsedRange Rows count  1 To sh UsedRange Columns count    update the dimension of the array to match used range           SheetStore   sh UsedRange Formula       End If End Sub   now to get the old value it is very easy as the array have the same address of cells  examples if we want cell D12 we can use the following   SheetStore row number column number   example return   SheetStore 12 4    or the following showing how I used it   set cell   activecell   the cell that we want to find the old value for newValue   cell value   you can ignore this line  it is just a demonstration oldValue   SheetStore cell Row  cell Column    these are snippet explaining the method  I hope everyone like it

User · Answer

In response to Matt Roy answer  I found this option a great response  although I couldn t post as such with my current rating      However  while taking the opportunity to post my thoughts on his response  I thought I would take the opportunity to include a small modification  Just compare code to see   So thanks to Matt Roy for bringing this code to our attention  and Chris R for posting original code   Dim OldValues As New Collection  Private Sub Worksheet SelectionChange ByVal Target As Range     gt  gt  Prevent user from multiple selection before any changes    If Selection Cells Count  gt  1 Then         MsgBox  Sorry  multiple selections are not allowed    vbCritical         ActiveCell Select         Exit Sub     End If   Copy old values     Set OldValues   Nothing     Dim c As Range     For Each c In Target         OldValues Add c Value  c Address     Next c End Sub   Private Sub Worksheet Change ByVal Target As Range  On Error Resume Next   On Local Error Resume Next    To avoid error if the old value of the cell address you re looking for has not been copied  Dim c As Range      For Each c In Target         If OldValues c Address   lt  gt     And c Value  lt  gt     Then  both Oldvalue and NewValue are Not Empty                     Debug Print  New value of    amp  c Address  amp    is    amp  c Value  amp     old value was    amp  OldValues c Address          ElseIf OldValues c Address       And c Value      Then  both Oldvalue and NewValue are  Empty                     Debug Print  New value of    amp  c Address  amp    is Empty    amp  c Value  amp     old value is Empty   amp  OldValues c Address           ElseIf OldValues c Address   lt  gt     And c Value      Then  Oldvalue is Empty and NewValue is Not Empty                     Debug Print  New value of    amp  c Address  amp    is Empty   amp  c Value  amp     old value was    amp  OldValues c Address          ElseIf OldValues c Address       And c Value  lt  gt     Then  Oldvalue is Not Empty and NewValue is Empty                     Debug Print  New value of    amp  c Address  amp    is    amp  c Value  amp     old value is Empty   amp  OldValues c Address          End If     Next c       Copy old values  in case you made any changes in previous lines of code      Set OldValues   Nothing     For Each c In Target         OldValues Add c Value  c Address     Next c

User · Answer

Let s first see how to detect and save the value of a single cell of interest  Suppose Worksheets 1  Range  B1   is your cell of interest  In a normal module  use this   Option Explicit  Public StorageArray 0 to 1  As Variant        Declare a module-level variable  which will not lose its scope as          long as the codes are running  thus performing as a storage place        This is a one-dimensional array           The first element stores the  old value   and          the second element stores the  new value   Sub SaveToStorageArray     ACTION     StorageArray 0    StorageArray 1            Transfer the previous new value to the  old value       StorageArray 1    Worksheets 1  Range  B1   value            Store the latest new value in Range  B1   to the  new value     OUTPUT DEMONSTRATION  Optional        Results are presented in the Immediate Window      Debug Print  Old value    amp  vbTab  amp  StorageArray 0      Debug Print  New value    amp  vbTab  amp  StorageArray 1   amp  vbCrLf  End Sub   Then in the module of Worksheets 1    Option Explicit  Private HasBeenActivatedBefore as Boolean       Boolean variables have the default value of False        This is a module-level variable  which will not lose its scope as          long as the codes are running   Private Sub Worksheet Activate               If HasBeenActivatedBefore   False then           If the Worksheet has not been activated before  initialize the             StorageArray as follows           StorageArray 1    Me Range  B1                 When the Worksheets 1  is activated  store the current value                 of Range  B1   to the  new value   before the                  Worksheet Change event occurs           HasBeenActivatedBefore   True               Set this parameter to True  so that the contents                 of this if block won t be evaluated again  Therefore                   the initialization process above will only be executed                  once      End If End Sub  Private Sub Worksheet Change ByVal Target As Range      If Not Intersect Target  Me Range  B1    Is Nothing then         Call SaveToStorageArray               Only perform the transfer of old and new values when                  the cell of interest is being changed      End If End Sub   This will capture the change of the Worksheets 1  Range  B1    whether the change is due to the user actively selecting that cell on the Worksheet and changing the value  or due to other VBA codes that change the value of Worksheets 1  Range  B1     Since we have declared the variable StorageArray as public  you can reference its latest value in other modules in the same VBA project   To expand our scope to the detection and saving the values of multiple cells of interest  you need to    Declare the StorageArray as a two-dimensional array  with the number of rows equal to the number of cells you are monitoring  Modify the Sub SaveToStorageArray procedure to a more general Sub SaveToStorageArray TargetSingleCell as Range  and change the relevant codes  Modify the Private Sub Worksheet Change procedure to accommodate the monitoring of those multiple cells    Appendix  For more information on the lifetime of variables  please refer to  https   msdn microsoft com en-us library office gg278427 aspx

User · Answer

Just a thought  but Have you tried using application undo  This will set the values back again  You can then simply read the original value   It should not be too difficult to store the new values first  so you change them back again if you like

User · Answer

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  amp  d  amp  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 and closed   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

User · Answer

I have the same problem like you and luckily I have read the solution from this link  http   access-excel tips value-before-worksheet-change   Dim oldValue As Variant Private Sub Worksheet SelectionChange ByVal Target As Range      oldValue   Target Value End Sub Private Sub Worksheet Change ByVal Target As Range       do something with oldValue    End Sub   Note  you must place oldValue variable as a global variable so all subclasses can use it

User · Answer

Private Sub Worksheet Change ByVal Target As Range  vNEW   Target Value aNEW   Target Address Application EnableEvents   False Application Undo vOLD   Target Value Target Value   vNEW Application EnableEvents   True End Sub

User · Answer

I had a need to capture and compare old values to the new values entered into a complex scheduling spreadsheet   I needed a general solution which worked even when the user changed many rows at the same time   The solution implemented a CLASS and a COLLECTION of that class   The class  oldValue  Private pVal As Variant Private pAdr As String Public Property Get Adr   As String    Adr   pAdr End Property Public Property Let Adr Value As String      pAdr   Value End Property Public Property Get Val   As Variant    Val   pVal End Property Public Property Let Val Value As Variant     pVal   Value End Property   There are three sheets in which i track cells    Each sheet gets its own collection as a global variable in the module named ProjectPlan as follows   Public prepColl As Collection Public preColl As Collection Public postColl As Collection Public migrColl As Collection   The InitDictionaries SUB is called out of worksheet open to establish the collections   Sub InitDictionaries       Set prepColl   New Collection     Set preColl   New Collection     Set postColl   New Collection     Set migrColl   New Collection End Sub   There are three modules used to manage each collection of oldValue objects they are Add  Exists  and Value   Public Sub Add ByRef rColl As Collection  ByVal sAdr As String  ByVal sVal As Variant      Dim oval As oldValue     Set oval   New oldValue     oval Adr   sAdr     oval Val   sVal     rColl Add oval  sAdr End Sub  Public Function Exists ByRef rColl As Collection  ByVal sAdr As String  As Boolean    Dim oReq As oldValue    On Error Resume Next    Set oReq   rColl sAdr     On Error GoTo 0     If oReq Is Nothing Then       Exists   False    Else       Exists   True    End If End Function Public Function Value ByRef rColl As Collection  ByVal sAdr  As Variant    Dim oReq As oldValue    If Exists rColl  sAdr  Then       Set oReq   rColl sAdr        Value   oReq Val    Else       Value         End If End Function   The heavy lifting is done in the Worksheet SelectionChange callback   One of the four is shown below   The only difference is the collection used in the ADD and EXIST calls   Private Sub Worksheet SelectionChange ByVal Target As Range     Dim mode As Range    Set mode   Worksheets  schedule   Range  PlanExecFlag      If mode Value   2 Then       Dim c As Range       For Each c In Target           If Not ProjectPlan Exists prepColl  c Address  Then              Call ProjectPlan Add prepColl  c Address  c Value            End If       Next c    End If End Sub   THe VALUE call is called out of code executed from the Worksheet Change Callback for example   I need to assign the correct collection based on the sheet name      Dim rColl As Collection    If sheetName    Preparations  Then        Set rColl   prepColl    ElseIf sheetName    Pre-Tasks  Then        Set rColl   preColl    ElseIf sheetName    Migr-Tasks  Then        Set rColl   migrColl    ElseIf sheetName    post-Tasks  Then        Set rColl   postColl    Else    End If   and then i am free to compute compare the some current value to the original value   If Exists rColl  Cell Offset 0  0  Address  Then    tsk delay   Cell Offset 0  0  Value - Value rColl  Cell Offset 0  0  Address  Else    tsk delay   0 End If   Mark

User · Answer

I have an alternative solution for you  You could create a hidden worksheet to maintain the old values for your range of interest    Private Sub Workbook Open    Dim hiddenSheet As Worksheet  Set hiddenSheet   Me Worksheets Add hiddenSheet Visible   xlSheetVeryHidden hiddenSheet Name    HiddenSheet    Change Sheet1 to whatever sheet you re working with Sheet1 UsedRange Copy ThisWorkbook Worksheets  HiddenSheet   Range Sheet1 UsedRange Address   End Sub   Delete it when the workbook is closed     Private Sub Workbook BeforeClose Cancel As Boolean   Application DisplayAlerts   False Me Worksheets  HiddenSheet   Delete Application DisplayAlerts   True  End Sub   And modify your Worksheet Change event like so     For Each cell In Target      If Not  Intersect cell  Range  cell of interest    Is Nothing  Then         new value   cell Value           here s your  old  value            old value   ThisWorkbook Worksheets  HiddenSheet   Range cell Address  Value         Call DoFoo old value  new value      End If  Next cell    Update your  old  values    ThisWorkbook Worksheets  HiddenSheet   UsedRange Clear Me UsedRange Copy ThisWorkbook Worksheets  HiddenSheet   Range Me UsedRange Address

User · Answer

an idea       write these in the ThisWorkbook module close and open the workbook        Public LastCell As Range      Private Sub Workbook Open            Set LastCell   ActiveCell      End Sub      Private Sub Workbook SheetSelectionChange ByVal Sh As Object  ByVal Target As Range           Set oa   LastCell Comment          If Not oa Is Nothing Then         LastCell Comment Delete         End If          Target AddComment Target Address         Target Comment Visible   True         Set LastCell   ActiveCell      End Sub

User · Answer

You can use an event on the cell change to fire a macro that does the following   vNew   Range  cellChanged   value Application EnableEvents   False Application Undo vOld   Range  cellChanged   value Range  cellChanged   value   vNew Application EnableEvents   True

User · Answer

try this  it will not work for the first selection  then it will work nice     Private Sub Worksheet SelectionChange ByVal Target As Range      On Error GoTo 10     If Target Count  gt  1 Then GoTo 10     Target Value   lastcel Target Value      10 End Sub   Function lastcel lC vAl As String  As String     Static vlu     lastcel   vlu     vlu   lC vAl End Function

User · Answer

try this  declare a variable say  Dim oval   and in the SelectionChange Event  Public Sub Worksheet SelectionChange ByVal Target As Range  oval   Target Value End Sub   and in your  Worksheet Change event set  old value   oval

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

Examples related to vba

Examples related to excel