Returning an array of nonduplicated random integers

The function in this section, RandomIntegers , returns an array of nonduplicated integers. The function is intended to be used in a multicell array formula.


Select a range and then enter the formula by pressing Ctrl+Shift+Enter. The formula returns an array of nonduplicated integers, arranged randomly. For example, if you enter the formula into a 50-cell range, the formulas will return nonduplicated integers from 1 to 50.

The code for RandomIntegers follows:

Function RandomIntegers() Dim FuncRange As Range

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

' Create Range object

Set FuncRange = Application.Caller

' Return an error if FuncRange is too large CellCount = FuncRange.Count If CellCount > 1000 Then

RandomIntegers = CVErr(xlErrNA) Exit Function End If

' Assign variables

RCount = FuncRange.Rows.Count

CCount = FuncRange.Columns.Count ReDim V(1 To RCount, 1 To CCount) ReDim ValArray(1 To 2, 1 To CellCount)

Fill array with random numbers and consecutive integers For i = 1 To CellCount

' 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

RandomIntegers = V

End Function

0 0

Post a comment