Strange lag when using classes

Hi,

I'm experiencing a strange lag when I create a simple class structure. I have an instrument class (which is essentially a dictionary of fields and values) and an instrumentbag class (which is basically a dictionary of instruments). When I test it, I get a strange lag. It only displays the second to last change and not the latest value. That is, if a value is 1 and then I change it to first 2 then 3, the value shown by the testGet function is 2. Has anyone else experienced this?

Public iBag As InstrumentBag

Function testSet(Name As String, Fields As Range, Values As Range) As Variant
Application.Volatile (True)
If (iBag Is Nothing) Then
Set iBag = New InstrumentBag
End If
If (Fields.Count = Values.Count) And (Fields.Rows.Count = Values.Rows.Count) And (Fields.Columns.Count = Values.Columns.Count) Then
Dim r As Integer
Dim c As Integer
For r = 1 To Fields.Rows.Count
For c = 1 To Fields.Columns.Count
iBag.Data(Name, Fields(r, c)) = Values(r, c)
Next c
Next r
End If
testSet = Now
End Function

Function testGet(Name As String, Fields As Range, TimeStamp As Variant) As Variant
Application.Volatile (True)
If (iBag Is Nothing) Then
Set iBag = New InstrumentBag
End If
Dim svar() As Variant
ReDim svar(Fields.Rows.Count, Fields.Columns.Count)
Dim r As Integer
Dim c As Integer
For r = 1 To Fields.Rows.Count
For c = 1 To Fields.Columns.Count
svar(r, c) = iBag.Data(Name, Fields(r, c))
Next c
Next r
testGet = svar
End Function

FYI, I did the same in a .NET xll and got the same behaviour, but not allways. I think it's related to the creation/calculation order.

If you are using Application.Volatile and Ranges, then you must be using Excel...

Have you tried creating a Sub in the same module as your functions, and running both your functions from that as a test? That should show if it is Excel's order in which it executes your functions that is causing the problem.

If the problem occurs even when you execute the functions in a known sequence, you'd have to post your class modules so we can have a look at what they are doing.

Hi,

Thanks for your reply. You are correct, I am using Excel. Here are my 2 missing classes; first an instrument:

Private pData As Dictionary
Private Sub Class_Initialize()
Set pData = New Dictionary
End Sub
Private Sub Class_Terminate()
Set pData = Nothing
End Sub
Public Sub Init(Name As String)
pData.Add "NAME", UCase(Name)
End Sub
Public Property Get Data(Field As String) As Variant
Dim svar As Variant
svar = ""
Dim f As String
f = UCase(Field)
If pData.Exists(f) Then
svar = pData(f)
End If
Data = svar
End Property
Public Property Let Data(Field As String, Value As Variant)
pData(UCase(Field)) = Value
End Property


Then an InstrumentBag:

Private pInstruments As Dictionary
Private Sub Class_Initialize()
Set pInstruments = New Dictionary
End Sub
Private Sub Class_Terminate()
Set pInstruments = Nothing
End Sub
Public Property Let Data(Name As String, Field As String, Value As Variant)
Dim n As String
n = UCase(Name)
Dim temp As Instrument
If pInstruments.Exists(n) = False Then
Set temp = New Instrument
temp.Init (n)
temp.Data(Field) = Value
pInstruments.Add n, temp
Else
Set temp = pInstruments(n)
temp.Data(Field) = Value
End If
End Property
Public Property Get Data(Name As String, Field As String) As Variant
Dim svar As Variant
svar = ""
Dim n As String
n = UCase(Name)
If pInstruments.Exists(n) Then
svar = pInstruments(n).Data(Field)
End If
Data = svar
End Property

Basically, instead of using an event, I send a timestamp whenever the testSet function is done. That timestamp is used to trigger the testGet function. I cannot see anything wrong with this procedure.

The thing is when I try the same in .NET with en event and an RTD, the problem persists. Hence, I believe that it has something to do with the calculation order.

Try creating a new Sub in the same module as your functions, and running both your functions from that in the order you expect. This will tell you if Excel's calculation order is the problem, or there is something in the functions/classes I can't see.