mode, mean, average etc calculator, out of range error?

Question

I get the error:

Line: 23
Char: 4
Subscript out of range: 'numbers'
which is at: if numbers(i) = a then

I have made this in javascript and it works, but I need to convert it.

function info(numbers)
    dim numbers2(99999)
    numbers3 = ""
    dim low
    dim high
    dim mean
    dim halve
    total = 0
    dim spaces(99999)
    dim a
    dim b
    dim i
    dim c
    dim whole
    dim meadian
    dim mode1
    dim mode2
    dim intResult
    dim med
    x = UBound(numbers)+1
    For a=0 To 999999 Step 1
        For i=0 To x Step 1
            if numbers(i) = a then
                c = numbers(i) 
                numbers2 = c
                numbers3 = numbers3 + c + " "
                low = numbers2(0)
                high = a
                total = total + c
            end if
    Next
    Next
    halve = UBound(numbers2)/2
    whole =  UBound(numbers2)
    intResult = whole Mod 2
    If intResult = 0 Then
        halve = halve - 0.5
        median = numbers2(halve)
        med = true
    Else
        median = (numbers2(halve1)+numbers2(halve1-1))/2
        med = false
    End if
    mean = total / UBound(numbers)
    if med = true then
        msgbox(numbers3 & chr(13) & chr(13) & "Lowest:  " & low & chr(13) & "Highest: " & high & chr(13) & "Total:   " & total & chr(13) & "Median:  " & median & chr(13))
    else
        msgbox(numbers3 & chr(13) & chr(13) & "Lowest:  " & low & chr(13) & "Highest: " & high & chr(13) & "Total:   " & total & chr(13) & "Median:  " & median & "   -" & numbers2(halve1) & "x" & numbers2(halve1-1) & chr(13))
    end if
end function
dim q(19,291,29)
info(q)

And also, how can I be able to put q in inside a inputbox? Just ask if you want the javascript code.

`x = UBound(numbers)+1` means that `x` will be larger than the array size, that's why the loop fails. — Roger Rowland, Nov 22 '13 at 07:24
Thx, that worked, but now I get a mismatch error with that line — Bradley McInerney, Nov 22 '13 at 07:39

Ansgar Wiechers · Answer 1 · 2013-11-25T15:26:29.083

If you're going to declare your variables (good idea for production code): use Option Explicit and declare all variables. Otherwise don't bother.

Variable declarations become a little more readable if you put them on one line (comma-separated):

Dim numbers2(99999), numbers3, low, high, mean, halve, total
Dim spaces(99999), ...
...

numbers3 = ""
total = 0

As @RogerRowland has mentioned, the line x = UBound(numbers)+1 will return an index 1 greater than the upper boundary of your array, so the loop

For i=0 To x Step 1
  if numbers(i) = a then
    ...
  end if
Next

will try to access an element outside the array in the last loop cycle. Better do it like this:

For i=0 To UBound(numbers)
  If numbers(i) = a Then
    ...
  End If
Next

Step 1 is the default, BTW, so it can be omitted.

The Type Mismatch error you got after fixing that is most likely because you declared numbers2 as a static array:

dim numbers2(99999)

but then assign a scalar to it inside the loop:

numbers2 = c

That line should probably be

numbers2(i) = c

mode, mean, average etc calculator, out of range error?

1 Answers1