Randomize values in a collection

Posted on 2009-12-31
Last Modified: 2012-05-08
Hello, Does anyone know of a quick way to randomize the values in a collection?  I have a collection of 400 cell addresses (A1 through U2) that I need to randomize.

Question by:JamesCbury
    LVL 81

    Accepted Solution

    Public Sub RandomizeCollection()

        Dim SortedValues As Collection
        Dim RandomValues As Collection
        Dim Index As Long
        Set SortedValues = New Collection
        For Index = 1 To 10
            SortedValues.Add Index
        Next Index
        Set RandomValues = New Collection
        For Index = 1 To SortedValues.Count
            AddValueToRandomCollection SortedValues.Item(Index), RandomValues
        Next Index

    End Sub

    Public Sub AddValueToRandomCollection( _
          ByVal Value As Variant, _
          ByVal Values As Collection _

    ' Add the parameter Value to the collection in a random position.

       Dim Index As Long

       Index = Rnd * (Values.Count + 1) + 1
       If Index > Values.Count Then
          Values.Add Value
          Values.Add Value, Before:=Index
       End If

    End Sub


    Author Closing Comment

    Very cool
    LVL 85

    Expert Comment

    by:Mike Tomlinson
    Another one...
    Option Explicit
    Private Sub CommandButton1_Click()
        Dim col As New Collection
        col.Add "a"
        col.Add "b"
        col.Add "c"
        col.Add "d"
        ' etc.
        Dim itm As Variant
        Debug.Print "Original:"
        For Each itm In col
            Debug.Print itm
        ShuffleCollection col
        Debug.Print "Shuffled:"
        For Each itm In col
            Debug.Print itm
    End Sub
    Private Sub ShuffleCollection(ByVal C As Collection)
        Static seeded As Boolean
        If Not seeded Then
            seeded = True
            Randomize Timer
        End If
        Dim c2 As New Collection
        While C.Count > 0
            c2.Add C(1)
            C.Remove 1
        Dim index As Long
        While c2.Count > 0
            index = Int((c2.Count - 1 + 1) * Rnd + 1)
            C.Add c2(index)
            c2.Remove index
    End Sub

    Open in new window


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now