Using a For Each...Next statement on values passed to a function

[samoht]

I don't know a lot about visual basic, and this may be a very dumb question, but I've tried searching the internet for ages without being able to find an answer and I'm completely stumped.

Basically I want to use a For Each...Next statement on the values passed to a public function (bdiave).

In my code, x1, x2, x3, etc, are values held in fields that are then passed to the function from within a query. I want to either be able to perform the For Each...Next statement directly on them, as if they were values in an array, or I want to be able to put them in an array without having to add them one by one, which is what I'm currently doing.

I don't know if this is impossible or really easy!

Here is my code so far:

Public Function bdiave(x1, x2, x3, x4, x5, x6, x7, x8, x9, x10, x11, x12, x13, x14, x15, x16, x17, x18, x19, x20, x21) As Integer

Dim total As Integer
Dim count As Integer
Dim ave As Integer
Dim missing As Integer

count = 0
total = 0

Dim myarray(1 To 21) As Long
myarray(1) = x1
myarray(2) = x2
myarray(3) = x3
myarray(4) = x4
myarray(5) = x5
myarray(6) = x6
myarray(7) = x7
myarray(8) = x8
myarray(9) = x9
myarray(10) = x10
myarray(11) = x11
myarray(12) = x12
myarray(13) = x13
myarray(14) = x14
myarray(15) = x15
myarray(16) = x16
myarray(17) = x17
myarray(18) = x18
myarray(19) = x19
myarray(20) = x20
myarray(21) = x21


For Each bdival In myarray

If bdival < 37707 Then
total = total + bdival
count = count + 1
End If
Next bdival



ave = total / count
missing = 21 - count
total = total + (missing * ave)
bdiave = total

End Function

Thank in advance for any help with this!

[HiTechCoach]

Try:



Dim I as Integer



and the loop would look like this:



For I = 1 to 21 myarray

If myarray(I) < 37707 Then
total = total + bdival
count = count + 1
End If

Next I





TIP: Use the Code tags to make your code more readable

[samoht]

Thanks! I'll try that out :thumb