I'm attempting to utilize a Static Variable to retain a value while filling a listbox with a UDF, in hopes of gaining some performance increases in the creation of a cumulative sum.
I'm using code adapted from the example here:
http://msdn.microsoft.com/en-us/library/bb242937.aspx
as pointed out to me in a previous thread by ByteMyzer.
http://www.access-programmers.co.uk/forums/showthread.php?t=164489
Many thanks once again for that.
I got the idea of the Static Variable from the brief reference to the concept in the MSFT Article.
I've attached a sample DB with a simple form with one 3 column Listbox. The recordsettype property of that ListBox is set to this UDF:
This works fine for the initial load, but once you begin scrolling around, the method using the Static Variable fails to produce a consistent result.
I'm trying to make this work to optimize another form that uses this technique to fill a list box where I generate a cumulative sum with the looping method outlined above for column 3. In my real example, it's not a simple linear function as above. As a result, when scrolling towards the bottom of a many hundreds of row long list, you start to see a hit on the performacne as the loop starts at row one everytime to recalc the cumulative sum needed of the row you're displaying.
I thought if I had a way to capture the value in the previous row, and then just submit the new row's variable to the equation, i'd see big performance gains. I attempted to grab the previous row's value with a statement like:
but while processing, the lbox still contains Null values, so that didn't work. thus my attempt to use the Static variable concept.
Any insights, or pointers much appreciated.
I'm using code adapted from the example here:
http://msdn.microsoft.com/en-us/library/bb242937.aspx
as pointed out to me in a previous thread by ByteMyzer.
http://www.access-programmers.co.uk/forums/showthread.php?t=164489
Many thanks once again for that.
I got the idea of the Static Variable from the brief reference to the concept in the MSFT Article.
I've attached a sample DB with a simple form with one 3 column Listbox. The recordsettype property of that ListBox is set to this UDF:
Code:
Public Function List_CumulativeTest(fld As Control, id As Variant, _
row As Variant, col As Variant, code As Variant) _
As Variant
Static v As Integer
Dim r As Integer
Select Case code
Case acLBInitialize 'Initialize.
List_CumulativeTest = True
'v = 0
Case acLBOpen 'Open.
List_CumulativeTest = Timer 'Unique ID.
'v = 0
Case acLBGetRowCount 'Get rows.
List_CumulativeTest = 100
Case acLBGetColumnCount 'Get columns.
List_CumulativeTest = 3
Case acLBGetColumnWidth 'Get column width.
List_CumulativeTest = -1 'Use default width.
Case acLBGetValue 'Get the data.
If row > 0 Then
If row = 1 Then v = row
Select Case col
Case Is = 0
List_CumulativeTest = row
v = v + row
Case Is = 1 ' Create Cumulative Sum using a Static Variable??
' Works correctly initially, but not after scrolling in List Box
List_CumulativeTest = v
Case Is = 2 ' Create Cumulative Sum with a Loop starting at Row 1
'Works every time
For r = 1 To row
List_CumulativeTest = List_CumulativeTest + r
Next r
End Select
Else ' Set Header Row
Select Case col
Case Is = 0
List_CumulativeTest = "Next Variable"
Case Is = 1
List_CumulativeTest = "Cum. Sum: Static Var"
Case Is = 2
List_CumulativeTest = "Cum. Sum: Loop"
End Select
End If
End Select
End Function
This works fine for the initial load, but once you begin scrolling around, the method using the Static Variable fails to produce a consistent result.
I'm trying to make this work to optimize another form that uses this technique to fill a list box where I generate a cumulative sum with the looping method outlined above for column 3. In my real example, it's not a simple linear function as above. As a result, when scrolling towards the bottom of a many hundreds of row long list, you start to see a hit on the performacne as the loop starts at row one everytime to recalc the cumulative sum needed of the row you're displaying.
I thought if I had a way to capture the value in the previous row, and then just submit the new row's variable to the equation, i'd see big performance gains. I attempted to grab the previous row's value with a statement like:
Code:
...lboxXYZ.column(2,row-1)
Any insights, or pointers much appreciated.