Dynamic Arrays?

[shittytrader]

I got this example from http://www.anthony-vba.kefra.com/vb...102.htm#Random_Number_and_Randomize_Statement and was having some trouble understanding some stuff. appreciate any help i can get.

my question relates to the sub procedure. The array has been declared with (10). I was wondering how would you make this dynamic. So that instead of stopping at 10 rows, the sub procedure would calculate all the consecutive rows with numbers in it.

thanks.





<small style="color: rgb(51, 51, 255); font-weight: bold;"></small><small style="color: rgb(51, 51, 255); font-weight: bold;">Function Mean(Arr() As Single)</small>
<small style="color: rgb(51, 51, 255);"> Dim Sum As Single</small>
<small style="color: rgb(51, 51, 255);"> Dim i As Integer

</small><small style="color: rgb(51, 51, 255);"> Sum = 0</small>
<small style="color: rgb(51, 51, 255);"> For i = 1 To UBound(Arr)</small>
<small style="color: rgb(51, 51, 255);"> Sum = Sum + Arr(i)</small>
<small style="color: rgb(51, 51, 255);"> Next i</small>
<small style="color: rgb(51, 51, 255);"> </small>
<small style="color: rgb(51, 51, 255);"> Mean = Sum / UBound(Arr)</small><small style="color: rgb(51, 51, 255);"></small>
<small style="color: rgb(51, 51, 255); font-weight: bold;">End Function</small>
<small style="color: rgb(51, 51, 255);"></small>
<small style="color: rgb(51, 51, 255);"></small>
<small style="color: rgb(51, 51, 255); font-weight: bold;">Function StdDev(Arr() As Single)</small>
<small style="color: rgb(51, 51, 255);"> Dim i As Integer</small>
<small style="color: rgb(51, 51, 255);"> Dim avg As Single, SumSq As Single</small>
<small style="color: rgb(51, 51, 255);"> </small>
<small style="color: rgb(51, 51, 255);"> avg = Mean(Arr)</small>
<small style="color: rgb(51, 51, 255);"> For i = 1 To UBound(Arr)</small>
<small style="color: rgb(51, 51, 255);"> SumSq = SumSq + (Arr(i) - avg) ^ 2</small>
<small style="color: rgb(51, 51, 255);"> Next i</small>
<small style="color: rgb(51, 51, 255);"> </small>
<small style="color: rgb(51, 51, 255);"> StdDev = Sqr(SumSq / (UBound(Arr) - 1))</small><small style="color: rgb(51, 51, 255);"></small>
<small style="color: rgb(51, 51, 255); font-weight: bold;">End Function</small>
<small style="color: rgb(51, 51, 255);"></small>

<small>The following sub routine reads the data in column one from row 1 to 10 (of Sheet1) into the array, calls both functions by passing the arguements to them, computes the mean (average) and the standard deviation, then returns the values in a message box.</small>

<small style="color: rgb(51, 51, 255);">Sub compute()</small>
<small style="color: rgb(51, 51, 255);"> Dim Arr(10) As Single</small>
<small style="color: rgb(51, 51, 255);"> Dim Average As Single</small>
<small style="color: rgb(51, 51, 255);"> Dim Std_Dev As Single</small>
<small style="color: rgb(51, 51, 255);"></small>
<small style="color: rgb(51, 51, 255);"> For i = 1 To UBound(Arr)</small>
<small style="color: rgb(51, 51, 255);"> Arr(i) = Sheets("Sheet1").Cells(i, 1)</small>
<small style="color: rgb(51, 51, 255);"> Next i</small>
<small style="color: rgb(51, 51, 255);"></small>
<small style="color: rgb(51, 51, 255);"> Average = Mean(Arr)</small>
<small style="color: rgb(51, 51, 255);"> Std_Dev = StdDev(Arr)</small><small style="color: rgb(51, 51, 255);"></small>
<small style="color: rgb(51, 51, 255);"> MsgBox "Average:" & vbTab & Average & vbCrLf & "StdDev :" & vbTab & Std_Dev</small><small style="color: rgb(51, 51, 255);"> </small><small style="color: rgb(51, 51, 255);"></small>
<small style="color: rgb(51, 51, 255);">End Sub</small>