Array Example

ARRAY EXAMPLE

Option Explicit
Sub deviationScores()
'The active sheet is assumed to be Sheet1.
'All the scores in column b starting at row 4 are read into an array.
'The highest score is determined and
'differences between each score and the highest score is written
'back to its adjacent cell
'Note: problem can't be done without using an array since
'all the scores must processed twice,
'the first time to find the highest score,
'and the second time to find the differences.
'K. Ming Leung, Nov. 9, 2007

    Dim score(1 To 500) As Double
    Dim maxScore As Double, average As Double, sum As Double
    Dim numberScores As Integer, n As Integer

    maxScore = 0    'set maximum score to be the lowest possible score
    sum = 0#

    'find the total number of scores
    Sheets("Sheet1").Select
    Range("b4").Select
    numberScores = ActiveCell.Row
    Selection.End(xlDown).Select
    numberScores = ActiveCell.Row - numberScores + 1
    Range("b4").Select

    'sum up the scores and find the highest score
    For n = 1 To numberScores
        score(n) = ActiveCell.Value
        sum = sum + score(n)
        If score(n) > maxScore Then
            maxScore = score(n)
        End If
        ActiveCell.Offset(1, 0).Select
    Next n

    'compute the average and display its value
    average = sum / numberScores
    ActiveCell.Value = average
    Range("b4").Select
    ActiveCell.Offset(numberScores, -1).Select
    ActiveCell.Value = "average ="

    'write the heading for the difference in scores
    Range("c3").Select
    ActiveCell.Value = "Differences"

    'write the differences in scores
    For n = 1 To numberScores
        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = maxScore - score(n)
    Next n

    'display the highest score in a dialog box
     MsgBox "The highest score is: " & maxScore

End Sub

Arrays can be passed to and from procedures. For example, suppose that we developed a Sub procedure, named MinMax, to determine the minimum and maximum values of a one-dimensional array, and temp() is a one-dimension array of temperature values, with its size given by nt. The Call statement could be written as

Call MinMax(temp(), nt, min, max)

The minimum and maximum values of the array are stored in parameters min and max, respectively. Notice the empty parentheses for the array temp in the call to the function MinMax.

The Sub procedure header would be written as

Sub MinMax(x, n, mn, mx)

Notice that no parentheses are used there.

In the following example, the temperature in degree Celsius of a substance is recorded at various times, and the results are placed in Sheets in column B starting with row 4. The corresponding times are placed in column A.

The following program reads the temperatures into an array temp(), and sends it to a Sub procedure MinMax to find the maximum and minimum values, which are then sent back to the main Sub to be displayed.

Option Explicit
Sub Example()
Dim i As Integer, nt As Integer
Dim t(100) As Double, temp(100) As Double
Dim min As Double, max As Double
Sheets("Sheet1").Select
Range("a4").Select
'determine the number of data in column A
nt = ActiveCell.Row
Selection.End(xlDown).Select
nt = ActiveCell.Row - nt + 1
'input the data
Range("a4").Select
For i = 1 To nt
    t(i) = ActiveCell.Value
    ActiveCell.Offset(0, 1).Select
    temp(i) = ActiveCell.Value
    ActiveCell.Offset(1, -1).Select
Next i
Call MinMax(temp(), nt, min, max)
MsgBox "minimum = " & min & " maximum = " & max, , "Temperature"
End Sub

Sub MinMax(x, n, mn, mx)
Dim i As Integer
mn = x(1)
mx = x(1)
For i = 2 To n
    If x(i) < mn Then 
        mn = x(i)
    End If
    If x(i) > mx Then 
        mx = x(i)
    End If
Next i
End Sub