## 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

RCount = rng.Rows.Count

CCount = rng.Columns.Count

ReDim V(1 To RCount, 1 To CCount)

ReDim ValArray(1 To 2, 1 To CellCount)

Fill ValArray with random numbers and values from rng For i = 1 To CellCount

ValArray(1, i) = Rnd ValArray(2, i) = rng(i) Next i

' Sort ValArray by the random number dimension For 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

Next r RangeRandomize = V End Function

The code is very similar to that for the RandomIntegers function. Figure 11-17 shows the function in use. The array formula in B2:B11 is:

1 Original Random

2 Aardvark Giraffe

3 Baboon Cat

4 Cat Iguana

5 Dog Elephant S Elephant Aardvark

7 Fox Dog

8 Giraffe Hippo

9 Hippo Baboon

10 iguana Javelina

11 Javelins Fox

Figure 11-17: The RangeRandomize function returns the contents of a range, in random order.

{= RangeRandomize(A2:A11)}

This formula returns the contents of A2:A11, but in random order.

4 PREV

NEXT

0 0