Please, a Code to solve this exercise of arrys

  • Create a VBA subroutine named JumbleArray that will completely randomize/jumble a preexisting (m x n) array.

    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).

    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: (See attached picture)

    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:

    1. k = 1
    2. For i = 1 To nr
    3. For j = 1 To nc
    4. A(k) = < some function of i, j, and nr, and you should reference array D >
    5. k = k + 1
    6. Next j
    7. 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.

  • Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post

    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.

    How to use code tags

    Note: no apostrophe in the tags, just used for demonstration here.


    your code goes between these tags


    Or, just highlight all of the code and press the <> in the post menu above button to add the code tags.