Average of string function??

  • Thread starter Toxie256 via AccessMonster.com
  • Start date
T

Toxie256 via AccessMonster.com

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

Allen Browne

Toxie256 via AccessMonster.com said:
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..
Click to expand...

Use the Split() function to create an array from the numbers.

Then loop through the array from LBound to UBound, using Val() to get the
value of each array element.
 
T

Toxie256 via AccessMonster.com

A few more details...

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

Allen Browne

Not sure if you saw this reply:
Use the Split() function to create an array from the numbers.

Then loop through the array from LBound to UBound, using Val()
to get the value of each array element.

If you need an exmple of looping through an array, here's one:
http://allenbrowne.com/func-09.html
 
T

Toxie256 via AccessMonster.com

I'm sorry, no I didnt see your reply when I was typing up my second entry.
When I use the Val() function it will give me a total correct? How can I get
the avg using this function?

Thanks Allen, I really appreciate your help!! :)
 
A

Allen Browne

To get the average, sum the array elements, and divide by the count.

You will need some understanding of VBA code to do this.
 
T

Toxie256 via AccessMonster.com

Worked perfect!

Thanks for the help Allen, Happy Thanksgiving!
 
Top