Using Class Modules in Excel VBA to Store and Compare Values of An Array When it Changes

Question

I chanced upon an answer to a previous post on the topic above. I have tried out the code recommended by Radek (as per the link provided below) and it works.

I am wondering if someone could explain to me how to code works, i.e. how the MyIniVal and MyRange does not have the same value since the Initialize_MySheet procedure sets their value to be the same?

Much thanks in advance!

https://stackoverflow.com/a/26738652/4814794

The code is:

Class module called "Class1":

Public WithEvents MySheet As Worksheet
Public MyRange As Range
Public MyIniVal As Variant

Public Sub Initialize_MySheet(Sh As Worksheet, Ran As Range)
    Set MySheet = Sh
    Set MyRange = Ran
    MyIniVal = Ran.Value
End Sub
Private Sub MySheet_Calculate()

If MyRange.Value <> MyIniVal Then
    Debug.Print MyRange.Address & " was changed from " & MyIniVal & " to " & MyRange.Value
    StartClass
End If

End Sub

and in a normal module:

Dim MyClass As Class1

Sub StartClass()
Set MyClass = Nothing
Set MyClass = New Class1
MyClass.Initialize_MySheet ActiveSheet, Range("A2")
End Sub

Please edit the question and add the code you have tried. – LittlePanda Apr 21 '15 at 12:36 — LittlePanda, Apr 21 '15 at 12:36

score 1 · Answer 1 · answered Apr 21 '15 at 12:59

MyIniVal stores the value of the cell at the time it is passed to the Initialize_MySheet procedure. MyRange stores a reference to the cell on the worksheet and will update as the cell value changes.

Edit - it is conceivable that Ran could hold a reference to multiple cells in which case the code will fail.

Using Class Modules in Excel VBA to Store and Compare Values of An Array When it Changes

1 Answers1