error in programme

[biker man]

the programme i wrote is to return the maximum, minimum and average
of
100 numbers. But it keeps returning an error. Does anyone no whats
wrong with it? what its missing?
cheers

Sub testing()
Dim a(10) As Single, max As Single
Dim min As Single, sum As Single
Dim Average As Integer, i As Integer
n = 100
max = a(1)
min = a(1)
sum = a(1)
For i = 1 To 100
If max > a(i) Then max = a(i)
If min < a(i) Then
min = a(i)
End If
sum = sum + a(i)
Next i
Average = sum / n
End Sub

 

[Pete_UK]

You have Dim a(10) As Single, which will reserve up to 10 elements in
the array, but your For loop goes up to 100, so it will fail when
i=11. Just change it to Dim a(100) ...

max, min, sum and average are reserved words, so you might also think
about changing these names (eg to my_max, my_min etc) to avoid
confusion.

Hope this helps.

Pete

 

[Guest]

not sure where you actually read in the numbers, unless a(i) is supposed to
be a reference to a cell.
You dimension an array a(10) as an array with 10 values, after which you
specify i = 1 to 100 and refer to a(i), therefore running out of the
specified arrray lenght of 10.
Furhtermore, the logic in your code is completely flawed, as max > a(i)
followed by max = a(i) will actually give you the minimum.

Suppose your numbers are in column a then

sub Find_Max_Min_Average

Range("A1").select
max = activecell
min = activecell
n = 1
total = activell
for i = 1 to 99
activecell.offset(1,0).select
if activecell > max then max = activecell
if activecell < min then min = activecell
total = total + activecell
n = n + 1
next
Average = total / n

activecell.offset(1,0).select
activecell.offset(0,1) = max
activecell.offset(0,2) = min
activecell.offset(0,3) = n
activecell.offset(0,4) = average

end sub

 

[Dave O]

I made some changes to your code, below. An apostrophe in a line
causes the compiler to disregard everything on that line after the
apostrophe, so I entered notes that way. First, I assumed your a(10)
should be an array of 100 entries, so I changed that; then I changed
the variables to include a capital letter. (That's a personal
preference, not required; it allows me to enter code in all lower case
letters. The compiler recognizes variables and capitalizes them the
way they are declared, so I see right away if there's a typo.) I
deleted the Average variable, since Sum / 100 is that value; next in
the code I populated the array- your code will need to find a way to
scoop up 100 entries to populate the array.

I deleted some rows ("Max = a(1)", for instance) because they can be
handled by the main body of the code, changed some of your logic as
noted, and provided message boxes as an output mechanism.

This code will work, assuming you will always have 100 entries to work
on; you might consider letting your input mechanism determine the
upper bound of your array. Let us know if you need help getting the
data into memory.

Dave O


Sub testing()
'max, min, avg of 100 entries
Dim A(1 To 100) As Single 'declare array
Dim Max As Single
Dim Min As Single
Dim Sum As Single
'deleted: Dim Average As Integer
Dim I As Integer

'added: values to populate the array
For I = 1 To 100
A(I) = I
Next I

'deleted: n = 100
'deleted: Max = a(1)
'deleted: Min = a(1)
'deleted: Sum = a(1)

For I = 1 To 100

'changed: If Max > a(I) Then Max = a(I)
If A(I) > Max Then Max = A(I)

'changed: If Min < a(I) Then
If A(I) < Min Then Min = A(I)
'deleted: End If

Sum = Sum + A(I)
Next I
'deleted: Average = Sum / n

'added:
MsgBox "Max value is " & Max
MsgBox "Min value is " & Min
MsgBox "The average is " & Sum / 100

End Sub

 

[Dave Peterson]

You didn't put anything in a().

You could let excel do the heavy lifting for you, too:

Option Explicit
Sub testing2()
Dim a(1 To 100) As Double
Dim max As Double
Dim min As Double
Dim sum As Double
Dim Average As Double
Dim i As Long

'put some stuff in that array
For i = LBound(a) To UBound(a)
a(i) = Rnd
Next i

With Application
max = .max(a)
min = .min(a)
sum = .sum(a)
Average = .Average(a)

End With

MsgBox "max = " & max & vbLf _
& "min = " & min & vbLf _
& "sum = " & sum & vbLf _
& "average = " & Average

End Sub