I've been trying to work out how to do permutations within VBA. I came across some code which achieved that. However, there are a few lines of code I don't understand (The entire code is at the bottom)
1)
[vba]
' PERMUTE -- the important part!
For Index = 0 To UBound(myArray)
' pick a random partner
OtherIndex = Rnd() * UBound(myArray)
' swap it with the current entry
TempStr = myArray(OtherIndex)
myArray(OtherIndex) = myArray(Index)
myArray(Index) = TempStr
Next Index
[/vba] Could someone please explain to me how these few lines of codes work? It doesn't really make sense to me... I feel like it is simply going around in circles...
Thanks for all your help!
Kind Regards,
Giri
Entire Code:
[vba]
Option Explicit
Public Sub Comb()
Dim myArray(10)
Dim Index As Integer
Dim msg As String
Dim OtherIndex As Integer
Dim TempStr As String
myArray(1) = Range("I14").Value
myArray(2) = Range("I15").Value
myArray(3) = Range("I16").Value
myArray(4) = Range("I17").Value
myArray(5) = Range("I18").Value
myArray(6) = Range("J14").Value
myArray(7) = Range("J15").Value
myArray(8) = Range("J16").Value
myArray(9) = Range("J17").Value
myArray(10) = Range("J18").Value ' This range was just filled with the letters "A" to "J"
' show starting state
For Index = 0 To UBound(myArray)
msg = msg & myArray(Index)
Next Index
MsgBox "Starting array: " & msg
' PERMUTE -- the important part!
For Index = 0 To UBound(myArray)
' pick a random partner
OtherIndex = Rnd() * UBound(myArray)
' swap it with the current entry
TempStr = myArray(OtherIndex)
myArray(OtherIndex) = myArray(Index)
myArray(Index) = TempStr
Next Index
' show ending state
msg = ""
For Index = 0 To UBound(myArray)
msg = msg & myArray(Index)
Next Index
MsgBox "Ending array: " & msg
The code
For Index = 0 To UBound(myArray) <-----(1)
' pick a random partner
OtherIndex = Rnd() * UBound(myArray) <-----(2)
' swap it with the current entry
TempStr = myArray(OtherIndex) <-----(3)
myArray(OtherIndex) = myArray(Index) <-----(4)
myArray(Index) = TempStr <-----(5)
Next Index
is a loop across the previously filled "myArray".
ad 1) The loop runs from 0 to the upper boundary (VBA function UBound) of the array
ad 2) OtherIndex is set to a random (Rnd()) value within the array.
ad 3) The value in myArray at the position OtherIndex is saved into TempStr
ad 4) in the place of the value indicated by myArray(OtherIndex) saved the value of the current array position (myArray(Index))
ad 5) now swap the the randomly chosen array value (see ad 3) into the current array position.
Isabella, I followed what you wrote and decided to work through the loop using my own personally generated numbers to try and understand it better.
1) Other Index = 2 = B
Index = 1 = A (assuming the loop starts at 1, not 0)
Therefore,
TempStr = B
myArray(OtherIndex - 2)= A myArray(Index - 1) = B
2) Other Index = 3 = C
Index = 2 = B
Therefore,
TempStr = C
myArray(OtherIndex - 3)= B myArray(Index - 2) = C
3) Other Index = 1 = A
Index = 3 = C
Therefore,
TempStr = A
myArray(OtherIndex - 1)= C myArray(Index - 3) = A
And so on...
To me it appears that as of this moment, Array (1), (2) and (3) would be B, C and A respectively.
If this is correct, I have 2 questions:
1) What is the need of Line 4:
myArray(OtherIndex) = myArray(Index)
because it seems that whatever is in Tempstr (Line 3) will appear in myArray(Index) (Line 5). I did try deleting this line but I found that letters were repeating in the final output.
2) Will,
OtherIndex = Rnd() * UBound(myArray)
always produce new numbers that have not been used earlier in the loop?
right now, I fear, I'm not catching on. But let me answer your questions.
Ad question 1: What is the need of line 4?
Answer: The purpose of the code you supplied is to pick a random entry in an array and swap it with the current entry, determined by the variable "Index". If you delete or comment this line, the swap will not be performed.
Ad question2: Will "OtherIndex = Rnd() * UBound(myArray) always produce new numbers..."
Answer: Yes, it will always produce new numbers, that's the point of the random function Rnd().
Giri, no hard feelings, please, honestly, I'm having a hard time believing that you want to use the code snippet you supplied in some more serious sense. Please help me understand the purpose.
Nonetheless, if you want fixed numbers, just for the fun of it, let me show you how turn the array upside down. In the code there are lines
[VBA]myArray(1) = Range("I14").Value
...
myArray(10) = Range("J18").Value[/VBA]
This assumes cells I14 through I18 and cells J14 through J18 contain the letters A,...,E and F,...,J respectively. Jeez, probably coded sometime in the wee hours, when brain cells scream "Ouch!" :-))
BTW, coding is sloppy in that snippet. I'm attaching my workbook with the way I think it should be done. A bit more general and dynamic solution
Reply With Quote
