LinkBack URL
About LinkBacks
Hi guys I need some help with this:
Create a VBA subroutine named JumbleArray that will completely randomize/jumble a preexisting (m x n) array.
This is an example of what you are trying to accomplish:
1.PNG
Durstenfeld’s Algorithm is an easy way to randomize an (ntot x 1) vector of ntot total elements (ntot = the product of m and n, or m x n). In this problem, you are asked to randomize an array – multiple rows and columns, so you'l have to first convert the array to a vector. In the Assignment 1 folder you will find a document called “Durstenfeld’s Algorithm.pdf”. Here is a sample flow diagram for the implementation of Durstenfeld’s Algorithm, although you will have to modify this a little bit to implement into your subroutine.
HINT: The "rn = choose random number between 1 and (n-j+1)" line has confused many learners! This means that each time through, your algorithm needs to reassign a new random number between 1 and (n-j+1). You can do this using WorksheetFunction.RandBetween(1,n-j+1).
2.PNG
Your solution should utilize Durstenfeld’s Algorithm. However, you will first have to “decompose” your (m x n) array (I'll call this array D) into an (ntot x 1) vector (I'll call this vector A), as shown here:
3.PNG
Note that for this example, ntot would be (4 x 3) = 12. Each column is kept together, and columns are just “stacked” on top of each other. HINT: You should come up with a simple formula in which you can populate A as a function of i (row index), j (column index), and number of rows, and it will reference array D. Once A is populated, you can perform Durstenfeld’s Algorithm on A to completely randomize it.
To do this "stacking", you'll want to come up with a simple single formula that relates i, j, and k, where k will go from 1 to ntot. Maybe something like this:
k = 1
For i = 1 To nr
For j = 1 To nc
A(k) = < some function of i, j, and nr, and you should reference array D >
k = k + 1
Next j
Next i
There are other ways to do this, but this might be the most intuitive. Next, you'll perform Durstenfeld's Algorithm on vector A, and the final step is to “recompose” the original (m x n) array (but now randomized) by reversing the decomposition process (by using a formula similar to the one you used for “stacking”, above, but for the reverse - I'll let you come up with that!).
Your sub should work on selections that have words and not just numbers! Also, your subroutine should work on any size array anywhere on the worksheet.
Register To Reply
Originally Posted by nisarga
Bookmarks