VBA – Sum Values Entered in Excel Cell without Formula

For a reason, you may wish to simply add a value in an excel cell and to have it automatically summed with the value, that was previously present in the cell. This can be valuable, if you are building some advanced Excel tool and in some cells you would like to add numbers to the cells. Or just to amaze someone how advanced your Excel knowledge skill is.

How the magic is done? We use two tricks here – the Worksheet_Change routine and the Application.Undo. The Application.Undo is a bit tricky, because it should be surrounded by Application.EnableEvents. The rest is pure calculation of the Target.Value before the undo and after it. Enjoy the code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim dCalculate As Double

    If Not IsNumeric(Target.Value) Then
        Exit Sub
    End If

    dCalculate = Target.Value
    Application.EnableEvents = False
    Application.Undo
    Target.Value = Target.Value + dCalculate
    Application.EnableEvents = True

End Sub

🙂