Microsoft Office Tutorials and References
In Depth Information
Some Useful Worksheet Functions
Next i
‘ Sort ValArray by the random number dimension
For i = 1 To CellCount
For j = i + 1 To CellCount
If ValArray(1, i) > ValArray(1, j) Then
Temp1 = ValArray(1, j)
Temp2 = ValArray(2, j)
ValArray(1, j) = ValArray(1, i)
ValArray(2, j) = ValArray(2, i)
ValArray(1, i) = Temp1
ValArray(2, i) = Temp2
End If
Next j
Next i
‘ Put the randomized values into the V array
i = 0
For r = 1 To RCount
For c = 1 To CCount
i = i + 1
V(r, c) = ValArray(2, i)
Next c
Next r
RandomIntegers = V
End Function
Randomizing a range
The RangeRandomize function, which follows, accepts a range argument and returns an array
that consists of the input range — in random order:
Function RangeRandomize(rng)
Dim V() As Variant, ValArray() As Variant
Dim CellCount As Double
Dim i As Integer, j As Integer
Dim r As Integer, c As Integer
Dim Temp1 As Variant, Temp2 As Variant
Dim RCount As Integer, CCount As Integer
‘ Return an error if rng is too large
CellCount = rng.Count
If CellCount > 1000 Then
RangeRandomize = CVErr(xlErrNA)
Exit Function
End If
‘ Assign variables
 
Search JabSto ::




Custom Search