Unique Random Numbers using VBA

by

Here’s a method of guaranteeing unique integer random numbers. Inline comments describe the method.

Function UniuqeRandom(Mn As Long, Mx As Long, Sample As Long) As Long()
    Dim dat() As Long
    Dim i As Long, j As Long
    Dim tmp As Long

    ' Input validation checks here
    If Mn > Mx Or Sample > (Mx - Mn + 1) Then
        ' declare error to suit your needs
        Exit Function
    End If

    ' size array to hold all possible values
    ReDim dat(0 To Mx - Mn)

    ' Fill the array
    For i = 0 To UBound(dat)
        dat(i) = Mn + i
    Next

    ' Shuffle array, unbiased
    For i = UBound(dat) To 1 Step -1
        tmp = dat(i)
        j = Int((i + 1) * Rnd)
        dat(i) = dat(j)
        dat(j) = tmp
    Next

    'original biased shuffle
    'For i = 0 To UBound(dat)
    '    tmp = dat(i)
    '    j = Int((Mx - Mn) * Rnd)
    '    dat(i) = dat(j)
    '    dat(j) = tmp
    'Next

    ' Return sample
    ReDim Preserve dat(0 To Sample - 1)
    UniuqeRandom = dat
End Function

use it like this

Dim low As Long, high As Long

Dim rng As Range
Dim dat() As Long

Set rng = Range(Cells(1, 1), Cells(1, 1).End(xlToRight))
dat = UniuqeRandom(low, high, rng.Columns.Count)
rng.Offset(1, 0) = dat

Note: see this Wikipedia article regarding shuffle bias

The edit fixed one source of bias. The inherent limitations of Rnd (based on a 32 bit seed) and Modulo bias remain.

More Related Contents:

  1. VBA comma separated cells, needt last entry only
  2. Test or check if sheet exists
  3. How to copy Outlook mail message into excel using VBA or Macros
  4. Wait for shell command to complete [duplicate]
  5. For Loop not fully cycling in excel VBA [duplicate]
  6. Are there disadvantages in putting code into Userforms instead of modules?
  7. Save each sheet in a workbook to separate CSV files
  8. Is VBA an OOP language, and does it support polymorphism?
  9. Export each sheet to a separate csv file
  10. ShowAllData method of Worksheet class failed
  11. Detect if range is empty
  12. Checking if a worksheet-based checkbox is checked
  13. Assign keyboard shortcut to run procedure
  14. Faster way to remove ‘extra’ spaces (more than 1) from a large range of cells using VBA for Excel
  15. Uncheck all checkboxes across entire workbook via CommandButton
  16. return csv file as recordset
  17. VBA workbooks.Close without being prompted to if the user wants to save?
  18. VBA equivalent to Excel’s mod function
  19. Copy a selected range to another worksheet
  20. Benchmarking VBA Code
  21. Excel macro – paste only non empty cells from one sheet to another
  22. The MacScript function is not working well in Office for Mac 2016! Any ideas?
  23. Pointers needed for speeding up nested loop macro in VBA
  24. ‘1004’: “The sort reference is not valid.”
  25. Create hyperlink to another sheet
  26. Reducing WithEvent declarations and subs with VBA and ActiveX
  27. Trying to open the workbook in separate instance
  28. Convert Early Binding VBA to Late Binding VBA : Excel to Outlook Contacts
  29. Function Overloading and UDF in Excel VBA
  30. VBA: Modify chart data range

Leave a Comment