Cumulative sum in Listbox using Static Variable in a UDF

ShredDude

Registered User.
Local time
Today, 11:08
Joined
Jan 1, 2009
Messages
71
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:

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)
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.
 

Attachments

that look like a list call back function for filling an unbound list or combo box

if what you are trying to do is have an additional column of information available in a query, where the column is based on a complex query - eg a value of outstanding orders for each customer - then i tried to do this in a variety of ways, and just couldnt get acceptable performance - it was ok one at a time but not acceptable over a whole dataset - so i gave up
 
Gemma:

Thanks for the response....

Yes, I'm filling an Unbound Listbox with that procedure. The Data filled into the listbox is the result of a VBA function using several input variables pulled from a different Form, some of which were planted there by a query, others by the User in Unbound fields. This facilitates some what-if simulation capabilities for the user in this particular application. So, the results in the Listbox are not reliant upon doing further Queries against the DB's Tables, just doing a bunch of math in the VBA code.

What's interesting, to me at least, is that the procedure seems to work by submitting row values for the rows needed to be displayed given the user's scrolling. That seems to be a very efficient way to do things, why calculate 1000 rows if I only need to see 10?

Perhaps if I could capture the value displayed in my cumulative sum column for (displayed) row 1 prior to the procedure doing it's thing, I'd have a reference point from which to start my calculations? Just thinking out loud here. That may work when scrolling in one direction, but not the other for my particular case. I'll play with it and see.
 

Users who are viewing this thread

Back
Top Bottom