JenniferMurphy
Well-known Member
- Joined
- Jul 23, 2011
- Messages
- 2,525
- Office Version
- 365
- Platform
- Windows
I need to do some calculations (sum, average, count) on a collection of lists of integers that represent product ratings. The lists are of variable length. I have written a little UDF that will do one of several calculations on each list. Here's some sample data:
And here's the UDF code:
Is there anything in this code that could be better? I never know, for example, if I am using the best datatypes.
Thanks
And here's the UDF code:
VBA Code:
Function ListCalc(pList, Optional pType As String = "Average")
Dim ListArray() As String
ListArray = Split(pList, " ")
Dim ListSum As Double
ListSum = 0
Dim ListCount As Long
ListCount = UBound(ListArray)
Dim I As Integer
For I = 0 To ListCount
ListSum = ListSum + CDbl(ListArray(I))
Next I
Select Case UCase(pType)
Case "SUM"
ListCalc = ListSum
Case "COUNT"
ListCalc = ListCount + 1
Case Else
ListCalc = ListSum / (ListCount + 1)
End Select
End Function
Is there anything in this code that could be better? I never know, for example, if I am using the best datatypes.
Thanks