On Fri, 1 Aug 2003 12:58:41 +0200, Ulrik Petersen wrote:

Hi.
Does anyone know how to transpose for instance a 2x2 matrix in VBA, Excel
2000?

Try this (it's a small procedure, which fills an array, transposes the
array and places both 'matrices' into an excel-sheet):

Sub Transpose()

Dim MyArray(1 To 10, 1 To 10), i As Integer, j As Integer, _
MyArrayTemp(1 To 10, 1 To 10)

For i = LBound(MyArray, 1) To UBound(MyArray, 1)
For j = LBound(MyArray, 2) To UBound(MyArray, 2)
MyArray(i, j) = CInt(Rnd() * 1000)
Next j
Next i

For i = LBound(MyArray, 1) To UBound(MyArray, 1)
For j = LBound(MyArray, 2) To UBound(MyArray, 2)
MyArrayTemp(j, i) = MyArray(i, j)
Next j
Next i

Range("A1").Activate

For i = LBound(MyArray, 1) To UBound(MyArray, 1)
For j = LBound(MyArray, 2) To UBound(MyArray, 2)
ActiveCell.Offset(i-1, j-1).Value = MyArray(i, j)
Next j
Next i

Range("A15").Activate

For i = LBound(MyArray, 1) To UBound(MyArray, 1)
For j = LBound(MyArrayTemp, 2) To UBound(MyArrayMyArrayTemp, 2)
ActiveCell.Offset(i-1, j-1).Value = MyArrayTemp(i, j)
Next j
Next i

End Sub

Thx Sander, this looks very useful. I'll see if I can implement the proc
on a 199x199 matrix:-)

greetings Ulrik

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

In Sanders' Sub Transpose below, the 4th line from the end has a
typo--"MyArrayMyArrayTemp" should be "MyArrayTemp". In addition,
transferring the arrays to the worksheet element by element is quite
slow; they can be transferred directly, at least in xl2000 and later.
The following Sub TransposeIt (to avoid a procedure with the same name
as a built-in Excel function) illustrates this. It also redeclares i
and j as Long to accommodate larger arrays. I left most of the original
lines of code, commented out, so the changes can be more easily
identified. Watch for word wrap.

Sub TransposeIt()

Dim MyArray(), i As Long, j As Long, _
MyArrayTemp()
ReDim MyArray(1 To 80, 1 To 80)
ReDim MyArrayTemp(1 To 80, 1 To 80)

For i = LBound(MyArray, 1) To UBound(MyArray, 1)
For j = LBound(MyArray, 2) To UBound(MyArray, 2)
MyArray(i, j) = CInt(Rnd() * 1000)
Next j
Next i

For i = LBound(MyArray, 1) To UBound(MyArray, 1)
For j = LBound(MyArray, 2) To UBound(MyArray, 2)
MyArrayTemp(j, i) = MyArray(i, j)
Next j
Next i

'Range("A1").Activate
Set rng = Range("A1")
Range(rng(1, 1), rng(UBound(MyArray), _
UBound(MyArray, 2))).Value = MyArray
'For i = LBound(MyArray, 1) To UBound(MyArray, 1)
' For j = LBound(MyArray, 2) To UBound(MyArray, 2)
' ActiveCell.Offset(i - 1, j - 1).Value = MyArray(i, j)
' Next j
'Next i

Set rng = rng(UBound(MyArrayTemp) + 2, 1)
Range(rng(1, 1), rng(UBound(MyArrayTemp), _
UBound(MyArrayTemp, 2))).Value = MyArrayTemp
'For i = LBound(MyArray, 1) To UBound(MyArray, 1)
' For j = LBound(MyArrayTemp, 2) To UBound(MyArrayTemp, 2)
' ActiveCell.Offset(i - 1, j - 1).Value = MyArrayTemp(i, j)
' Next j
'Next i

End Sub

Alan Beban

Sander Lablans wrote:
On Fri, 1 Aug 2003 12:58:41 +0200, Ulrik Petersen wrote:


Hi.
Does anyone know how to transpose for instance a 2x2 matrix in VBA, Excel
2000?


Try this (it's a small procedure, which fills an array, transposes the
array and places both 'matrices' into an excel-sheet):

Sub Transpose()

Dim MyArray(1 To 10, 1 To 10), i As Integer, j As Integer, _
MyArrayTemp(1 To 10, 1 To 10)

For i = LBound(MyArray, 1) To UBound(MyArray, 1)
For j = LBound(MyArray, 2) To UBound(MyArray, 2)
MyArray(i, j) = CInt(Rnd() * 1000)
Next j
Next i

For i = LBound(MyArray, 1) To UBound(MyArray, 1)
For j = LBound(MyArray, 2) To UBound(MyArray, 2)
MyArrayTemp(j, i) = MyArray(i, j)
Next j
Next i

Range("A1").Activate

For i = LBound(MyArray, 1) To UBound(MyArray, 1)
For j = LBound(MyArray, 2) To UBound(MyArray, 2)
ActiveCell.Offset(i-1, j-1).Value = MyArray(i, j)
Next j
Next i

Range("A15").Activate

For i = LBound(MyArray, 1) To UBound(MyArray, 1)
For j = LBound(MyArrayTemp, 2) To UBound(MyArrayMyArrayTemp, 2)
ActiveCell.Offset(i-1, j-1).Value = MyArrayTemp(i, j)
Next j
Next i

End Sub

On Fri, 01 Aug 2003 13:06:30 -0700, Alan Beban wrote:

In Sanders' Sub Transpose below, the 4th line from the end has a
typo--"MyArrayMyArrayTemp" should be "MyArrayTemp". In addition,
transferring the arrays to the worksheet element by element is quite
slow; they can be transferred directly, at least in xl2000 and later.
The following Sub TransposeIt (to avoid a procedure with the same name
as a built-in Excel function) illustrates this. It also redeclares i
and j as Long to accommodate larger arrays. I left most of the original
lines of code, commented out, so the changes can be more easily
identified. Watch for word wrap.

Sub TransposeIt()

Dim MyArray(), i As Long, j As Long, _
MyArrayTemp()
ReDim MyArray(1 To 80, 1 To 80)
ReDim MyArrayTemp(1 To 80, 1 To 80)

For i = LBound(MyArray, 1) To UBound(MyArray, 1)
For j = LBound(MyArray, 2) To UBound(MyArray, 2)
MyArray(i, j) = CInt(Rnd() * 1000)
Next j
Next i

For i = LBound(MyArray, 1) To UBound(MyArray, 1)
For j = LBound(MyArray, 2) To UBound(MyArray, 2)
MyArrayTemp(j, i) = MyArray(i, j)
Next j
Next i

'Range("A1").Activate
Set rng = Range("A1")
Range(rng(1, 1), rng(UBound(MyArray), _
UBound(MyArray, 2))).Value = MyArray
'For i = LBound(MyArray, 1) To UBound(MyArray, 1)
' For j = LBound(MyArray, 2) To UBound(MyArray, 2)
' ActiveCell.Offset(i - 1, j - 1).Value = MyArray(i, j)
' Next j
'Next i

Set rng = rng(UBound(MyArrayTemp) + 2, 1)
Range(rng(1, 1), rng(UBound(MyArrayTemp), _
UBound(MyArrayTemp, 2))).Value = MyArrayTemp
'For i = LBound(MyArray, 1) To UBound(MyArray, 1)
' For j = LBound(MyArrayTemp, 2) To UBound(MyArrayTemp, 2)
' ActiveCell.Offset(i - 1, j - 1).Value = MyArrayTemp(i, j)
' Next j
'Next i

End Sub

Alan Beban

Sander Lablans wrote:
On Fri, 1 Aug 2003 12:58:41 +0200, Ulrik Petersen wrote:


Hi.
Does anyone know how to transpose for instance a 2x2 matrix in VBA, Excel
2000?


Try this (it's a small procedure, which fills an array, transposes the
array and places both 'matrices' into an excel-sheet):

Sub Transpose()

Dim MyArray(1 To 10, 1 To 10), i As Integer, j As Integer, _
MyArrayTemp(1 To 10, 1 To 10)

For i = LBound(MyArray, 1) To UBound(MyArray, 1)
For j = LBound(MyArray, 2) To UBound(MyArray, 2)
MyArray(i, j) = CInt(Rnd() * 1000)
Next j
Next i

For i = LBound(MyArray, 1) To UBound(MyArray, 1)
For j = LBound(MyArray, 2) To UBound(MyArray, 2)
MyArrayTemp(j, i) = MyArray(i, j)
Next j
Next i

Range("A1").Activate

For i = LBound(MyArray, 1) To UBound(MyArray, 1)
For j = LBound(MyArray, 2) To UBound(MyArray, 2)
ActiveCell.Offset(i-1, j-1).Value = MyArray(i, j)
Next j
Next i

Range("A15").Activate

For i = LBound(MyArray, 1) To UBound(MyArray, 1)
For j = LBound(MyArrayTemp, 2) To UBound(MyArrayMyArrayTemp, 2)
ActiveCell.Offset(i-1, j-1).Value = MyArrayTemp(i, j)
Next j
Next i

End Sub

Just noted the typo myself. Of course very silly...

I haven't got experience with filling area's directly from VB-code into a
worksheet (guess I never needed them), so the corrections you perform are
quite educative. Thx.

Just a quick question, did you deliberately NOT assign a datatype to the
variable 'arr' in your ArrayTranspose-function, further in this thread?

SL

Sander Lablans wrote:
[snip]
Just noted the typo myself. Of course very silly...

I haven't got experience with filling area's directly from VB-code into a
worksheet (guess I never needed them), so the corrections you perform are
quite educative. Thx.

Just a quick question, did you deliberately NOT assign a datatype to the
variable 'arr' in your ArrayTranspose-function, further in this thread?

SL

Not deliberately; Excel automatically assigns the Variant type when no
type is explicitly mentioned. Since I did explicitly declare another
variable in the function as a Variant type, I assume that the variable
'arr' wasn't in an earlier version of the function, and that when I
later added it I didn't bother to be explicit. I have now explicitly
declared it in my working version of the function, and it will be
included next time I update the file at the website (I just did that
last week, so it might be awhile).

I am not scrupulous about declaring Variant variables as explicitly
Variant variables, though I suppose there are some arguments for that
practice. It does cause one to think about each variable type
assignment, and perhaps protects against possible future changes in
Excel's declaration conventions.

Thanks for mentioning it.

Alan Beban