VBA code to have random order list

[vekan]

Hey guys,
I'm new here but I've been referring MrExcel quite a few times for solutions over several vba conundrums but I can't find an answer to this one yet.

I have 4 cells which should populate with 1, 2, 3 or 4 in a random sequence everytime the script is run.
For example,

Run 1	Run2	Run3
2	1	3
4	3	1
1	4	2
3	2	4

<tbody>
</tbody>

Any help?

 

[JoeMo]

From your examples I presume you don't want any duplicates among the 4 values.
Try this (change range of 4 cells where noted by comment):

Sub Random4()
'Assumes data range is A1:A4, change to suit
Dim R As Range, d As Object, c As Range, ct As Long
Set R = Range("A1:A4") '<--- change range to suit
Set d = CreateObject("Scripting.Dictionary")
For Each c In R
    Do
        c.Value = Int((4 * Rnd) + 1)
        If Not d.exists(c.Value) Then
            ct = ct + 1
            d.Add c.Value, ct
            Exit Do
        End If
    Loop
Next c
End Sub

 

[kalak]

try

Sub rando()
Const n& = 4
Dim a, i, x, y
a = Evaluate("row(1:" & n & ")")
Randomize
For i = 1 To n
    x = Application.RandBetween(i, n)
    y = a(i, 1): a(i, 1) = a(x, 1):  a(x, 1) = y
Next i
Range("A1").Resize(n) = a
End Sub

 

[vekan]

From your examples I presume you don't want any duplicates among the 4 values.
Try this (change range of 4 cells where noted by comment):

Sub Random4()
'Assumes data range is A1:A4, change to suit
Dim R As Range, d As Object, c As Range, ct As Long
Set R = Range("A1:A4") '<--- change range to suit
Set d = CreateObject("Scripting.Dictionary")
For Each c In R
    Do
        c.Value = Int((4 * Rnd) + 1)
        If Not d.exists(c.Value) Then
            ct = ct + 1
            d.Add c.Value, ct
            Exit Do
        End If
    Loop
Next c
End Sub

Hey Joe,
Thanks so much for the code. Works flawlessly after a bit of customization.
I would like to incorporate this piece of code into my academic project. Would it be alright with you? I would obviously be crediting this piece of code to you in a comment.

 

[vekan]

try

Sub rando()
Const n& = 4
Dim a, i, x, y
a = Evaluate("row(1:" & n & ")")
Randomize
For i = 1 To n
    x = Application.RandBetween(i, n)
    y = a(i, 1): a(i, 1) = a(x, 1):  a(x, 1) = y
Next i
Range("A1").Resize(n) = a
End Sub

Thanks for the code kalak.
I just couldn't understand your code and it's way too advanced for my poor skill set to be able to understand and if required, explain.
I appreciate the response though. Thanks!

 

[JoeMo]

Hey Joe,
Thanks so much for the code. Works flawlessly after a bit of customization.
I would like to incorporate this piece of code into my academic project. Would it be alright with you? I would obviously be crediting this piece of code to you in a comment.

You are welcome. I've always posted in the spirit of anything I put up is in the public domain - I have no problem with you using it in your project, with or without attribution.

 

[kalak]

Thanks for the code kalak.
I just couldn't understand your code and it's way too advanced for my poor skill set to be able to understand and if required, explain.
I appreciate the response though. Thanks!

That code is just an example of the Fisher-Yates-Knuth random shuffle which has for long been the standard way of producing unbiased, non-repeating random sequences, which is what you appear to want. https://en.wikipedia.org/wiki/Fisher–Yates_shuffle

It's also considered the fastest method, most efficient with computer storage space and takes minimal numbers of calculations to produce its result. Takes about 1 second to produce 100,000 non-repeating random integers, for example.

Of course it doesn't matter to me which method you prefer for this thread, but if you do any work in this area it may be in your own interests to learn the standard method, which is actually quite easy to understand. And especially so if it's an academic project ...

But if you do really wish to use your indicated preference of the trial and error method of repeating and re-repeating random numbers until you get it right, then a couple of suggestions:

(a) put in a "randomize" line to ensure you don't get repeating patterns of "randomization" when you may not want to.
(b) Instead of using the scripting dictionary (do you really understand how this works?) to filter out repeating integers, try using booleans instead, which are much faster (with integers), probably easier for the average user to understand, and work on all computers (the scripting dictionary won't usually work on a Mac).
A boolean-modified trial-and-error code for example is:

Sub Random4_mod()
'Assumes data range is A1:A4, change to suit
Dim R As Range, d() As Boolean, c As Range, ct As Long
Set R = Range("A1:A4") '<--- change range to suit
ReDim d(4) ' = CreateObject("Scripting.Dictionary")
For Each c In R
    Do
        c.Value = Int((4 * Rnd) + 1)
        If Not d(c.Value) Then '.exists(c.Value) Then
            ct = ct + 1
            d(c.Value) = True '.Add c.Value, ct
            Exit Do
        End If
    Loop
Next c
End Sub

 

[vekan]

That code is just an example of the Fisher-Yates-Knuth random shuffle which has for long been the standard way of producing unbiased, non-repeating random sequences, which is what you appear to want. https://en.wikipedia.org/wiki/Fisher–Yates_shuffle

It's also considered the fastest method, most efficient with computer storage space and takes minimal numbers of calculations to produce its result. Takes about 1 second to produce 100,000 non-repeating random integers, for example.

Of course it doesn't matter to me which method you prefer for this thread, but if you do any work in this area it may be in your own interests to learn the standard method, which is actually quite easy to understand. And especially so if it's an academic project ...

But if you do really wish to use your indicated preference of the trial and error method of repeating and re-repeating random numbers until you get it right, then a couple of suggestions:

(a) put in a "randomize" line to ensure you don't get repeating patterns of "randomization" when you may not want to.
(b) Instead of using the scripting dictionary (do you really understand how this works?) to filter out repeating integers, try using booleans instead, which are much faster (with integers), probably easier for the average user to understand, and work on all computers (the scripting dictionary won't usually work on a Mac).
A boolean-modified trial-and-error code for example is:

Sub Random4_mod()
'Assumes data range is A1:A4, change to suit
Dim R As Range, d() As Boolean, c As Range, ct As Long
Set R = Range("A1:A4") '<--- change range to suit
ReDim d(4) ' = CreateObject("Scripting.Dictionary")
For Each c In R
    Do
        c.Value = Int((4 * Rnd) + 1)
        If Not d(c.Value) Then '.exists(c.Value) Then
            ct = ct + 1
            d(c.Value) = True '.Add c.Value, ct
            Exit Do
        End If
    Loop
Next c
End Sub

Thank you for posting that wealth of information kalak. I sincerely do appreciate it.
Since my project is due this Thursday, I felt it would be necessary to use code that would be simple...although you have made a pretty compelling point about the efficiency of code.
Since I already incorporated Joe's code in the project and it runs fine, I'd hate to bugger it up by modifying it any further but I will incorporate the boolean code that you mentioned.
And you're right...I do not yet know what a scripting dictionary is but I will look it up. Also, thanks for the tip on it's issue with the Mac. Seems like you have a wealth of experience with excel on both OS.
Since the university supports mostly Windows for this course, I will go ahead and use the scripting dictionary. But I will keep what you mentioned in mind.

Again...my sincerest thanks. ray:

 

[kalak]

If you really want to use the scripting dictionary for your academic project, then a couple of further remarks could be useful:

(a) Don't omit the randomize line, otherwise with your stated request
"I have 4 cells which should populate with 1, 2, 3 or 4 in a random sequence everytime the script is run"
you may get your "random" sequence displaying in the same order each time you run your code anew. i.e. not very random.

(b) Since it's for academic work, your project would look better if you avoid the unnecessary and highly inefficient nested loop

For Each c In R
    Do
        ...
    Loop
Next c

and also avoid the redundant counter ct since the scripting dictionary has its own built-in counter.
Something like

Sub xxx()
Const n As Long = 4
Dim d As Object, c As Long
Set d = CreateObject("Scripting.Dictionary")
Randomize
Do
    c = Int((n * Rnd) + 1)
    If Not d.exists(c) Then
        d(c) = 1
        Range("A" & d.Count) = c
    End If
Loop Until d.Count = n
End Sub

although with a couple of very minor wording changes you could change this latter code to become the much faster and more efficient Boolean approach.