S
scottwilsonx
Hi all,
I was very helpfully given these 2 UDFs to create a non-repeating cycl
of numbers between the numbers entered (eg: 1 to 14 etc).
The "RandInt" function below successfully creates a list of rando
integers, however the numbers are repeated randomly.
What I would really need is a UDF that creates a non-replicating lis
which cycled thru 14 numbers. ie: if the number 1 comes out first, the
you will get (for example) 5,3,7,8,2,4,9,12,6,10,13,11,14 before 1 come
out again.
Another user gave me the 2nd function "cycrnd" which should sort ou
this problem, but I cant see how it works.
Any and all help gratefully received.
Thanks
Scott.
Public Function RandInt( _
Optional ByVal nStart As Long = 1&, _
Optional ByVal nEnd As Long = -2147483647) As Variant
Dim vArr As Variant
Dim vResult As Variant
Dim nCount As Long
Dim nTemp As Long
Dim nRand As Long
Dim i As Long
Dim j As Long
Application.Volatile
If TypeName(Application.Caller) <> "Range" Then Exit Function
With Application.Caller
ReDim vResult(1 To .Rows.Count, 1 To .Columns.Count)
nCount = .Count
If nEnd < nStart Then nEnd = nStart + nCount - 1
If nCount > nEnd - nStart + 1 Then
RandInt = CVErr(xlErrNum)
Exit Function
ElseIf nCount = 1 Then
RandInt = CLng((nEnd - nStart) * Rnd() + nStart)
Exit Function
End If
End With
ReDim vArr(0 To nEnd - nStart)
For i = 0 To UBound(vArr)
vArr(i) = i + nStart
Next i
For i = UBound(vArr) To 1 Step -1
nRand = Int(Rnd() * (i + 1))
nTemp = vArr(nRand)
vArr(nRand) = vArr(i)
vArr(i) = nTemp
Next i
nCount = 0
For i = 1 To UBound(vResult, 1)
For j = 1 To UBound(vResult, 2)
vResult(i, j) = vArr(nCount)
nCount = nCount + 1
Next j
Next i
RandInt = vResult
End Function
Function cycrnd() As Long
Static a As Variant, n As Long
Application.Volatile 'necessary for this
If IsEmpty(a) Then
a = RandInt(1, 16)
n = LBound(a)
Else
If n < UBound(a) Then n = n + 1 Else n = LBound(a)
End If
cycrnd = a(n)
End Functio
I was very helpfully given these 2 UDFs to create a non-repeating cycl
of numbers between the numbers entered (eg: 1 to 14 etc).
The "RandInt" function below successfully creates a list of rando
integers, however the numbers are repeated randomly.
What I would really need is a UDF that creates a non-replicating lis
which cycled thru 14 numbers. ie: if the number 1 comes out first, the
you will get (for example) 5,3,7,8,2,4,9,12,6,10,13,11,14 before 1 come
out again.
Another user gave me the 2nd function "cycrnd" which should sort ou
this problem, but I cant see how it works.
Any and all help gratefully received.
Thanks
Scott.
Public Function RandInt( _
Optional ByVal nStart As Long = 1&, _
Optional ByVal nEnd As Long = -2147483647) As Variant
Dim vArr As Variant
Dim vResult As Variant
Dim nCount As Long
Dim nTemp As Long
Dim nRand As Long
Dim i As Long
Dim j As Long
Application.Volatile
If TypeName(Application.Caller) <> "Range" Then Exit Function
With Application.Caller
ReDim vResult(1 To .Rows.Count, 1 To .Columns.Count)
nCount = .Count
If nEnd < nStart Then nEnd = nStart + nCount - 1
If nCount > nEnd - nStart + 1 Then
RandInt = CVErr(xlErrNum)
Exit Function
ElseIf nCount = 1 Then
RandInt = CLng((nEnd - nStart) * Rnd() + nStart)
Exit Function
End If
End With
ReDim vArr(0 To nEnd - nStart)
For i = 0 To UBound(vArr)
vArr(i) = i + nStart
Next i
For i = UBound(vArr) To 1 Step -1
nRand = Int(Rnd() * (i + 1))
nTemp = vArr(nRand)
vArr(nRand) = vArr(i)
vArr(i) = nTemp
Next i
nCount = 0
For i = 1 To UBound(vResult, 1)
For j = 1 To UBound(vResult, 2)
vResult(i, j) = vArr(nCount)
nCount = nCount + 1
Next j
Next i
RandInt = vResult
End Function
Function cycrnd() As Long
Static a As Variant, n As Long
Application.Volatile 'necessary for this
If IsEmpty(a) Then
a = RandInt(1, 16)
n = LBound(a)
Else
If n < UBound(a) Then n = n + 1 Else n = LBound(a)
End If
cycrnd = a(n)
End Functio