TIP: persisting cell data - ie keep track

[Patrick Molloy]

Many times people want to know how a cell has changed values.
This is one idea. Treat it as food for thought. I hope it opens up lotas of
ideas

we create an object thats simply a collection of values. we name this object
using the cell address that we want to track. Since we'll track severall
cells, we'll save the objects to a collection.
To the obkect, we'll allow code to save data, fetch data and we';; add a
couple of functions to get the data out.

1) create an input range on a sheet and range name it 'persist' witjout the
quotes of course,

2) add a CLASS MODULE , rename it clPersistedCell and paste this code
Option Explicit

Private m_col() As String
Private m_Index As Long
Sub SaveValue(newval As String)
Dim i As Long
m_Index = m_Index + 1
If m_Index > 10 Then m_Index = 10 'CAPPED
ReDim Preserve m_col(1 To m_Index)
For i = m_Index - 1 To 1 Step -1
m_col(i + 1) = m_col(i)
Next

m_col(1) = newval
End Sub

Private Sub Class_Initialize()
m_Index = 0
End Sub
Property Get GetVal(Optional instance As Long)
If instance = 0 Then instance = m_Index
GetVal = m_col(instance)
End Property

Property Get perstedValues()
perstedValues = m_col
End Property
Property Get Count() As Long
Count = m_Index
End Property

3) add a standard MODULE and add this code
Option Explicit
Public persistedcells As Collection
Sub PersistCell(target As Range)
Dim pc As clPersisedCell
Dim key As String
key = target.Address
If persistedcells Is Nothing Then
Set persistedcells = New Collection
End If
On Error Resume Next
Set pc = persistedcells(key)
If Err.Number <> 0 Then
Err.Clear
On Error GoTo 0
Set pc = New clPersisedCell
pc.SaveValue target.Value
persistedcells.Add pc, key
Else
pc.SaveValue target.Value
End If
End Sub
Function GetCount(target As Range)
Dim pc As clPersisedCell
On Error Resume Next
Application.Volatile
Set pc = persistedcells(target.Address)
If Err.Number = 0 Then
GetCount = pc.Count
Else
GetCount = 0
End If
End Function
Function getValues(target As Range)
Dim pc As clPersisedCell
On Error Resume Next
Application.Volatile
Set pc = persistedcells(target.Address)
If Err.Number = 0 Then
getValues = pc.perstedValues
Else
getValues = "no Data"
End If
End Function

4) got to the sheet's code page and add this code:
Option Explicit
Private Sub Worksheet_Change(ByVal target As Range)
If Not Intersect(target, Range("persist")) Is Nothing Then
PersistCell target
Calculate
End If
End Sub


5) finally select a cell to the right of the 'input' range and put this
formula
=getcount(D6)
and select a row of 12 cells and out this array formula
{=getvalues(D6)}
in my exampel D6 a cell in the range named persist


NOW
enter a value into say D6. The code creates the collection, creates an
object, put the cell's value into the object
enter a new value and see the function values change.

mail me directly if you need help or want my sheet.
Comments to this ng of course, but be kind, and remember this is just an idea.

You can use this to save other data...comments etc or structures. so if a
cell represents lets say a bond CUSIP, you could have the object define the
bond - coupon, maturity etc, or maybe its an investment, and the cell's
persisted object could hold the risk assesment...



patrick

patrickunderscoremolloyathotmailcotcom