Function to get the sum of a listbox column (1 Viewer)

[MrHans]

I am trying to create a dynamic function that will calculate the sum of a column of a listbox.
When calling the function I would like to specify the form, the listbox and the column.

Somehow the form name and listbox name are not coming through.
Do you have any suggestions?

Function GetListBoxSum(frmName As Form, lstName As ListBox, intColumn As Integer) As Currency

    Dim i As Integer
    Dim j As Integer
    Dim ctl As Control

    Set ctl = Forms![frmName]![lstName]
    j = ctl.ListCount - 1

    GetListBoxSum = 0
    For i = 1 To j
        GetListBoxSum = GetListBoxSum + ctl.Column(intColumn, i)
    Next i

End Function

To call the function, I use:

GetListBoxSum(Me.Form, Me.lstPurchasesResult, 6)

 

[CJ_London]

try

Set ctl = Forms(frmName).controls(lstName)

if your form and/or controlnames have spaces you will need to include double quotes around the names

However not sure why you need to have to pass the form and controls as names, you can just pass the control

 Function GetListBoxSum(ctl as control, intColumn As Integer) As Currency

    Dim i As Integer
 
    GetListBoxSum = 0
    For i = 1 To ctl.ListCount - 1
         GetListBoxSum = GetListBoxSum + ctl.Column(intColumn, i)
    Next i

End Function

 

[MrHans]

Excellent stuff, thank you very much.

Yes, passing the object directly is what I actually wanted.

Thanks again CJ.