Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
TIP: persisting cell data - ie keep track
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Persisting Treeview in VBA | Excel Programming | |||
ftp paths defined in variables are persisting. How to clear?(vba) | Excel Programming | |||
Excel RTD,SQL Server and persisting range | Excel Programming | |||
excel add-in function not persisting | Excel Discussion (Misc queries) | |||
MVPs needed!! Persisting metadata... | Excel Programming |