VB - Excel checking previous value give an error

Question

I am trying to learn a bit of VB and there is an exercise to change a value and check the previous value and if it is different do something. I eventually found a solution I could understand and get to work from : How do I get the old value of a changed cell in Excel VBA? - solution 4. My code is:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Variant

    For Each cell In Target
        If previousRange.Exists(cell.Address) Then
            If Not Application.Intersect(Target, Me.Range("B12:B12")) Is Nothing Then
                If previousRange.Item(cell.Address) <> cell.FormulaR1C1 Then
                    cell.Interior.ColorIndex = 36
                End If
            End If
        End If
    Next

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim cell As Variant

    Set previousRange = Nothing 'not really needed but I like to kill off old references
    Set previousRange = CreateObject("Scripting.Dictionary")

    For Each cell In Target.Cells
        previousRange.Add cell.Address, cell.FormulaR1C1
    Next

End Sub

The next exercise was to add a button and perform an action depending on the user's response. So I added:

Private Sub CommandButton2_Click()
    Dim currentValue, message As Integer

    currentValue = Range("C3").Value
    message = MsgBox("Click OK to add 1, cancel to leave", vbOKCancel, "Addition")

    If message = 1 Then
        Range("C3").Value = currentValue + 1
    End If
End Sub

The problem I have is that the button adds one to C3 but then falls over at the If previousRange.Exists(cell.Address) statement on the Worksheet_Change sub. All the code is defined on Sheet1, but I do not seem to have a previous value generated for my button value(C3). How do I generate the previous value, or what am I missing?

Regards J

As I seemed to have made things worse I have created a new spreadsheet with just the change events code and nothing else to try and simplify the problem. So the complete code I have now is:

Option Explicit
Dim previousRange As New Dictionary

Private Sub Worksheet_Change(ByVal Target As Range)

Dim cell As Variant

For Each cell In Target
    If previousRange.Exists(cell.Address) Then
        If Not Application.Intersect(Target, Me.Range("B12:B12")) Is Nothing Then
            If previousRange.Item(cell.Address) <> cell.FormulaR1C1 Then
                cell.Interior.ColorIndex = 36
            End If
        End If
    End If
Next

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim cell As Variant

Set previousRange = Nothing 'not really needed but I like to kill off old references
Set previousRange = CreateObject("Scripting.Dictionary")

For Each cell In Target.Cells
    previousRange.Add cell.Address, cell.FormulaR1C1
Next

End Sub

Now if I change the B12 cell, the previousRange As New Dictionary code is highlighted, and a message states "Compile error:User defined type not defined". This code used to work before I introduced the message box and made a subsequent change. Must be user error. Can you help?

Regards J.

Answer 1

The .Exists method is used on dictionary objects, like the example you've cited. But I don't see where you've declared a dictionary object in your code. Maybe you're missing a declaration statement for it?

Dim previousrange As New Dictionary

Please note that, like the solution you've cited, you'll need to declare this before the sub routine. Also, you'll need to enable the Microsoft Scripting Runtime. Here's how:

1. In the VBA editor, go to the Tools menu and click on References...
2. In the Available References list box, scroll down until you see Microsoft Scripting Runtime. Make sure its check box is checked.
3. Click OK.

Now you're able to use Dictionary objects.