Results 1 to 6 of 6

Thread: Random Unique Number Generator Excel VBA

  1. #1
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10

    Random Unique Number Generator Excel VBA

    Here's a way to generate unique random numbers from a minimum value to a maximum value

    Code:
    Sub GenerateRandomUnique()
    
        Dim lng As Long
        Const lngMax As Long = 100
        Const lngMin As Long = 1
        With CreateObject("Scripting.Dictionary")
            Do While .Count <> lngMax
                lng = Rnd * (lngMax - lngMin + 1) + 1
                .Item(lng) = Empty
            Loop
            MsgBox Join(.Keys, " ")
        End With
        
    End Sub
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Last edited by Admin; 10-17-2012 at 07:07 AM.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by Excel Fox View Post
    Here's a way to generate unique random numbers from a minimum value to a maximum value
    Assuming you meant the "from a minimum to a maximum value" to mean the user can set those values to their own values, I think your code is not correct. I believe this modification to it will perform correctly...
    Code:
    Sub GenerateRandomUnique()    Dim lng As Long
        Const lngMax As Long = 100
        Const lngMin As Long = 1
        With CreateObject("Scripting.Dictionary")
            Do While .Count <= lngMax - lngMin          'removed the greater than symbol
                lng = Rnd * (lngMax - lngMin) + lngMin  'removed the +1
                .Item(lng) = Empty
            Loop
            MsgBox Join(.Keys, " ")
        End With
    End Sub
    Last edited by Rick Rothstein; 10-17-2012 at 09:17 PM.

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Thanks Rick for correcting that.

    Similar discussions by Rajan at Get Random List : | LinkedIn

    and

    Get Random List : « excelpoweruser
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by Excel Fox View Post
    Thanks Rick for correcting that.

    Similar discussions by Rajan at Get Random List : | LinkedIn

    and

    Get Random List : « excelpoweruser
    Here is something I originally posted to the old compiled VB newsgroups back in 1999 (according to the acticle header) which was subsequently picked up by this compiled VB website. My code is the subroutine named RandomizeArray embedded within the "housing" the website owner put it in...

    [VBnet Helpers] Pure VB: Generating a Random Array of Unique Numbers

  6. #6
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by Rick Rothstein View Post
    Here is something I originally posted to the old compiled VB newsgroups back in 1999 (according to the acticle header) which was subsequently picked up by this compiled VB website. My code is the subroutine named RandomizeArray embedded within the "housing" the website owner put it in...

    [VBnet Helpers]* Pure VB: Generating a Random Array of Unique Numbers
    Actually, instead of making you wade through the demo code at the above site, here is one of the few ways I have presented my randomizing routine in the past...

    The following is a generalized shuffling routine that I have posted in the past over in the compiled VB newsgroups, but it works fine in the VBA world of Excel as well. Assign your values to an array and then pass that into the RandomizeArray subroutine and it will put elements of the array into a random order and return the randomized elements back in the original array that was passed to it. So, to read out the randomize list, just read the array from its lower bound to whatever number of random elements you need (up to the maximum of the array's upper bound). It only visits *each* array element *once* so it is quick. The code takes care of running the Randomize statement one time only (which is all that is necessary).

    Code:
    Sub RandomizeArray(ArrayIn As Variant)
      Dim X As Long, RandomIndex As Long, TempElement As Variant
      Static RanBefore As Boolean
      If Not RanBefore Then
        RanBefore = True
        Randomize
      End If
      If VarType(ArrayIn) >= vbArray Then
        For X = UBound(ArrayIn) To LBound(ArrayIn) Step -1
          RandomIndex = Int((X - LBound(ArrayIn) + 1) * Rnd + LBound(ArrayIn))
          TempElement = ArrayIn(RandomIndex)
          ArrayIn(RandomIndex) = ArrayIn(X)
          ArrayIn(X) = TempElement
        Next
      Else
        'The passed argument was not an array, so put error handler here, such as . . .
        Beep
      End If
    End Sub
    After passing your array into the RandomizeArray subroutine, its elements will be randomly reordered. The passed array may be of any normal type -- integer, string, single, etc. The neat thing is, if you pass an already randomized array to this routine, those randomly ordered elements will be randomize -- sort of like shuffling an already shuffled deck of cards.
    Last edited by Rick Rothstein; 10-18-2012 at 01:04 PM.

Similar Threads

  1. Dynamic Worksheet Generator Sheet Copy
    By mfaisalrazzak in forum Excel Help
    Replies: 2
    Last Post: 03-01-2013, 05:38 PM
  2. Unique Random Number In Ascending Order
    By marreco in forum Excel Help
    Replies: 8
    Last Post: 11-04-2012, 04:15 PM
  3. Replies: 2
    Last Post: 01-07-2012, 12:11 AM
  4. Create Random Number Generator VBA
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 1
    Last Post: 12-01-2011, 10:51 AM
  5. Generate random numbers in Excel
    By Mahesh in forum Excel Help
    Replies: 3
    Last Post: 10-06-2011, 11:24 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •