Posts by chareta

Hi Carim,

Thank you for your answer but I have to come up with an index formula that must use i, j and nr.

The formula that I have to come up must use i, j and nr.

Hi,

Please I need help in the following exercise. I have to poblate the Vector A(k, 1) with elements of the Array B(i, j) by transposing the elements without using the Transpose VBA function. That means that I have to create a formula that transposes B(i, j) for example for a 4x3 Array.

Option Explicit
Option Base 1
Sub JumbleArray()
'Place your code here
Dim i As Integer, j As Integer, k As Integer, Temp As Integer
Dim nr As Integer, nc As Integer, n As Integer, B() As Variant, A() As Variant, rn As Integer
nr = Selection.Rows.Count
nc = Selection.Columns.Count
n = nr * nc
ReDim B(nr, nc) As Variant
ReDim A(n, 1) As Variant
For i = 1 To nr
    For j = 1 To nc
        B(i, j) = Selection.Cells(i, j)
    Next j
Next i
k = 1
For i = 1 To nr
    For j = 1 To nc
        A(k, 1) = B(j - i + nr * i - nr + 1, j)
        k = k + 1
    Next j
Next i
For j = 1 To n
    rn = WorksheetFunction.RandBetween(1, n - j + 1)
    Temp = A(n - j + 1, 1)
    A(n - j + 1, 1) = A(rn, 1)
    A(rn, 1) = Temp
Next j
For i = 1 To nr
    For j = 1 To nc
            B(i, j) = A(k, 1)
        k = k + 1
    Next j
Next i
End Sub

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:

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.