Make sense of Arrays after Transpose

[ExcelMonkey]

I have a transpose function I am using on a 2D array. I am setting up the
array, printing output to immediate window, transposing array, printer
revised output to Immediate window. The results are not making sense to me.
Why is the output the same after I transpose the array.

Expected Output in Immediate Window:
Fred
10

Fred
Jack
Joe

Actual Output in Immediate Window:
Fred
10

Fred
10

Sub MakeSenseOfArrays()
Dim Array1 As Variant

ReDim Array1(0 To 1, 0 To 2)
'2D Array 2rowsX3columns
'Should look like this
'Fred Jack Joe
'10 20 30
Array1(0, 0) = "Fred"
Array1(0, 1) = "Jack"
Array1(0, 2) = "Joe"
Array1(1, 0) = 10
Array1(1, 1) = 20
Array1(1, 2) = 30

For X = 0 To UBound(Array1, 1)
'Print Rows of fist column
Debug.Print Array1(X, 0)
Next

Transpose2D (Array1)
'Should now be a 2D Array of 3rowsX2columns
'Should look like this
'Fred 10
'Jack 20
'Joe 30
Debug.Print ""
For X = 0 To UBound(Array1, 1)
Debug.Print Array1(X, 0)
Next
End Sub
***********************************
Function Transpose2D(vaData) As Variant
'Transpose the input array swapping rows for columns
Dim i As Long, j As Long
Dim vaTransposed As Variant
ReDim vaTransposed(LBound(vaData, 2) To UBound(vaData, 2), _
LBound(vaData, 1) To UBound(vaData, 1)) As Variant
For i = LBound(vaData, 1) To UBound(vaData, 1)
For j = LBound(vaData, 2) To UBound(vaData, 2)
vaTransposed(j, i) = vaData(i, j)
Next j
Next i
Transpose2D = vaTransposed
End Function

Thanks

EM

 

[Alan Beban]

I have a transpose function I am using on a 2D array. I am setting up the
array, printing output to immediate window, transposing array, printer
revised output to Immediate window. The results are not making sense to me.
Why is the output the same after I transpose the array.

Because nothing in your code changes Array1. Either of two fixes will
give you the results you expect.

Change Transpose2D (Array1) to

Array1 = Transpose2D(Array1)

Or

Change Transpose2D (Array1) to

Transpose2d Array1

and add in the function just before Transpose2D = vaTransposed

vaData = vaTransposed

Alan Beban

 

[ExcelMonkey]

Thanks!. One more question. When I try to bubble sort this transposed array
using the bubble sort function I can't seem to get the output to look sorted
based on column1. Note I changed the names in the array to start off with an
unsorted list. Why is this?

EM

Sub MakeSenseOfArrays()
Dim Array1 As Variant

ReDim Array1(0 To 1, 0 To 2)
'2D Array 2rowsX3columns
'Should look like this
'Fred Jack Joe
'10 20 30
Array1(0, 0) = "Joe"
Array1(0, 1) = "Jack"
Array1(0, 2) = "Fred"
Array1(1, 0) = 10
Array1(1, 1) = 20
Array1(1, 2) = 30

For X = 0 To UBound(Array1, 1)
'Print Rows of fist column
Debug.Print Array1(X, 0)
Next

Array1 = Transpose2D(Array1)
'Should now be a 2D Array of 3rowsX2columns
'Should look like this
'Fred 10
'Jack 20
'Joe 30
Debug.Print ""
For X = 0 To UBound(Array1, 1)
Debug.Print Array1(X, 0) & " " & Array1(X, 1)
Next
Debug.Print ""
Array1 = BubbleSort2D(Array1, 1)
For X = 0 To UBound(Array1, 1)
Debug.Print Array1(X, 0) & " " & Array1(X, 1)
Next
End Sub
Function Transpose2D(vaData) As Variant
'Transpose the input array swapping rows for columns
Dim i As Long, j As Long
Dim vaTransposed As Variant
ReDim vaTransposed(LBound(vaData, 2) To UBound(vaData, 2), _
LBound(vaData, 1) To UBound(vaData, 1)) As Variant
For i = LBound(vaData, 1) To UBound(vaData, 1)
For j = LBound(vaData, 2) To UBound(vaData, 2)
vaTransposed(j, i) = vaData(i, j)
Next j
Next i
Transpose2D = vaTransposed
End Function

Function BubbleSort2D(List As Variant, col As Long)
' Sorts an array using bubble sort algorithm
Dim First As Integer, Last As Integer
Dim i As Integer, j As Integer
Dim Temp As Integer

First = LBound(List, 1)
Last = UBound(List, 1)
For i = 1 To Last - 1
For j = i + 1 To Last
If List(i, col) > List(j, col) Then
For k = 1 To UBound(List, 2)
Temp = List(j, k)
List(j, k) = List(i, k)
List(i, k) = Temp
Next k
End If
Next j
'Application.StatusBar = "Sorting " & Round(i / Last * 100, 0) & "%"
Next i
BubbleSort2D = List
End Function

 

[Alan Beban]

Thanks!. One more question. When I try to bubble sort this transposed array
using the bubble sort function I can't seem to get the output to look sorted
based on column1. Note I changed the names in the array to start off with an
unsorted list. Why is this?

I think your code is sorting on the second column--your array is
0-based. I'm not sure I understand the bubble sort but it seems to work
in this case if you make the folowing changes:

Change Array1 = BubbleSort2D(Array1, 1) to
Array1 = BubbleSort2D(Array1, 0)

and in the bubble sort function change

Dim Temp As Integer to Dim Temp As String
For i = 1 To Last - 1 to For i = 0 To Last - 1
For k = 1 To UBound(List, 2) to For k = 0 To UBound(List, 2)

You might more generally use

Array1 = BubbleSort2D(Array1, LBound(List, 1))
For i = LBound(List, 1) To Last - 1
For k = LBound(List, 2) To UBound(List, 2)

Alan Beban

 

[ExcelMonkey]

It worked but I am not sure exactly how. Just out of curiosity Alan, do you
know of another sort function which I can use on a 2D array by defining a
particular column?

Thanks.

EM

 

[Alan Beban]

It worked but I am not sure exactly how. Just out of curiosity Alan, do you
know of another sort function which I can use on a 2D array by defining a
particular column?

Thanks.

No, but if you post just that question, I would guess you'll get some
responses. A number of regular responders are familiar with sort routines.

By the way, in your posted code I think it's simply that you were
sorting on the second column; in a 0-based array, 0 is the first column,
1 is the second column. You were sorting on the 1 column, i.e., the
second column. The changes I made were simply to have it sort on the
first column, the 0-column, as you said you wanted.

Regards,
Alan