Randomizer Modification Help.......

[Ardy]

Hello All:
I needed a VB function that would rank a list of students that sign up
in our robotics after school program. Found one in the net. I am
trying to modify it to be able to user input two of the variables but
am getting errors no Mather what I do. Can somebody take look at this
and guide me of what it is I am doing wrong……

The approach the individual who wrote this is in two parts he wrote a
function that it does the randomizing of numbers and then he wrote a
VB code that fills in the variables needed and uses the function to do
it. My goal is to modify the VB code that calls the function and pass
the variables…..

My goal is to modify varrRandomNumberList = UniqueRandomNumbers(9, 1,
9) to varrRandomNumberList = UniqueRandomNumbers(AnalNum, 1,
CulmNum). I am using the InputBox to aquire the number from the user
and assign it to variables AnalNum and CulmNum. It is not working
well and keeps giving me Compile error “ByRef argument type mismatch”

------------------------------------------------------
Function----------------------------------------------------------------------------
Function UniqueRandomNumbers(NumCount As Long, LLimit As Long, ULimit
As Long) As Variant
' creates an array with NumCount unique long random numbers
' in the range LLimit - ULimit (including)
Dim RandColl As Collection, i As Long, varTemp() As Long
UniqueRandomNumbers = False
If NumCount < 1 Then Exit Function
If LLimit > ULimit Then Exit Function
If NumCount > (ULimit - LLimit + 1) Then Exit Function
Set RandColl = New Collection
Randomize
Do
On Error Resume Next
i = CLng(Rnd * (ULimit - LLimit) + LLimit)
RandColl.Add i, CStr(i)
On Error GoTo 0
Loop Until RandColl.Count = NumCount
ReDim varTemp(1 To NumCount)
For i = 1 To NumCount
varTemp(i) = RandColl(i)
Next i
Set RandColl = Nothing
UniqueRandomNumbers = varTemp
Erase varTemp
End Function
----------------------------------------------------------------------
END FUNCTION--------------------------------------------

The code that calls the function

--------------------------------------------------------VB
CODE---------------------------------------------------------------------
' example use:
Sub DoUniqueRandomNumbers()
Dim varrRandomNumberList As Variant, AnalNum As Variant, CulmNum As
Variant, X As Byte
'
' AnalNum = InputBox(prompt:="Enter Total Number of Students. ")
' If AnalNum = "" Then Exit Sub
' CulmNum = AnalNum
' MsgBox ("The Number Is " & AnalNum & "...." & CulmNum)
' MakeColumn X
'
varrRandomNumberList = UniqueRandomNumbers(9, 1, 9)
Range(Cells(2, 1), Cells(10 + 0, 1)).Value = _
Application.Transpose(varrRandomNumberList)
End Sub
--------------------------------------------------------------END VB
CODE--------------------------------------------------------

 

[Prof Wonmug]

I can't help you with your function, but if you just want to put a
list in random order, this quick little procedure will do it:


1. Load the data into Column B.

2. In A1, enter "=rand()".

3. Copy down (Double-click on fill handle). Leave the column selected.

4. Convert formulas to values. This is not necessary, but makes the
results easier to read. Otherwise, the rand functions will recalculate
after the sort. The best way I have found to do this is to copy the
cells onto themselves and select paste values.
a. If the column was not left selected from the previous step,
select it.
b. Copy to clipboard (Ctrl-C).
c. Paste onto self (Ctrl-V).
d. Click on Paste Options pop-up (lower left corner of column).
e. Select Values only.

5. Sort:
a. Select A1.
b. Select all (Crtl-Shift-End).
c. Deselect any extra rows or columns.
d. Click sort, select smallest to largest.

6. Delete column A.

 

[Ardy]

Thanks.....
I know I have been doing that in past few years......I thought I
should automate this so it would make it cleaner and less steps. Also
learn something along the way. now my curiosity has gotten the best
of me to know why it it giving me the error. in principal it should
work. So I am hoping a savy programer read this post and help....

I do thank you for your help.....

Ardy

 

[Prof Wonmug]

Thanks.....
I know I have been doing that in past few years......I thought I
should automate this so it would make it cleaner and less steps.

Well, you could record these few steps using the macro recorder and
then modify as needed.

 

[Dave Peterson]

YOUR function...
not You're

You're function is looking for longs for all 3 passed parms:

Function UniqueRandomNumbers(NumCount As Long, LLimit As Long, _
ULimit As Long) As Variant


But you declared both AnalNum and CulmNum as Variants.

So you can use clng() to convert them to what the function is looking for:

varrRandomNumberList = UniqueRandomNumbers(CLng(AnalNum), 1, CLng(CulmNum))

=========
You may want to look at the way J.E. McGimpsey returns unique random integers.
He has some extra validation and a pretty good way of randomizing the list.

http://www.mcgimpsey.com/excel/udfs/randint.html

 

[Ardy]

Dave;
Thanks that did the trick, had to do couple of twicks but now I have
a good working copy.

Thanks
Ardy