Hey guys,

I hope someone can help me... I need to find a way in Access VBA to average a string of numbers. I tried to use string.average but this doesn't appear to work in VBA only VB?? I need to do this as a function in the VBA code itself...

the string is like this.. StringValue = 1,2,5,10,8,12,6,4 values seperated
with commas. I just need to calculate an average of the numbers in the string.
in the example above, return the value of 6 for the average. the number of
values will not always be the same. there may be 4 entries one time, 6 the
other and so on... I just need to find the average of all the numbers in the
string.

It sounds really simple but for the life of me I cant figure it out. I saw a
string.average method on the Microsoft MSDN site but I cannot get this to
work with VBA... It must be for straight VB.


Any help is Greatly appreciated!
Thanks in advance.
Toxie :)

If your input argument is a Comma-Separated-Value String, then the following function should work for you:
Public Function AverageStr( _
ByVal sArg As String) _
As Double

Dim vArg As Variant
Dim vArgs As Variant
Dim dTot As Double, dDiv As Double

vArgs = Split(sArg, ",")

For Each vArg In vArgs
dTot = dTot + CDbl(vArg)
dDiv = dDiv + 1
Next

AverageStr = dTot / dDiv

End Function

?AverageStr("1,2,3,4,5")
3

If your input argument is a Comma-Separated-Value String, then the following function should work for you:
Public Function AverageStr( _
ByVal sArg As String) _
As Double

Dim vArg As Variant
Dim vArgs As Variant
Dim dTot As Double, dDiv As Double

vArgs = Split(sArg, ",")

For Each vArg In vArgs
dTot = dTot + CDbl(vArg)
dDiv = dDiv + 1
Next

AverageStr = dTot / dDiv

End Function

?AverageStr("1,2,3,4,5")
3
Cool! I like that.

Thanks ByteMyzer!!

I'll give it a shot and let you know how it works. I really appreciate your help! :)

Worked like a charm! Thanks to everyone and Happy Thanksgiving!!