Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
transpose matrices
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
transpose matrices
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
transpose matrices
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
transpose matrices
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
transpose matrices
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i square matrices? | Excel Worksheet Functions | |||
inverse matrices | Excel Discussion (Misc queries) | |||
matrices | Excel Discussion (Misc queries) | |||
How to solve y =f(x,y) using matrices? | Excel Worksheet Functions | |||
Combining certain matrices | Excel Discussion (Misc queries) |