How does one transpose a VBA array? In the following the result of lines 3
and 5 are the same.

Sub zz()
Dim x(3, 2)
3 Debug.Print UBound(x, 1), UBound(x, 2)
WorksheetFunction.Transpose (x)
5 Debug.Print UBound(x, 1), UBound(x, 2)
End Sub
--
Thanks for your help

Hi Les,

Option Base 1

Sub zz()
Dim MyArray() As Variant

ReDim MyArray(3, 2)

Debug.Print UBound(MyArray, 1), UBound(MyArray, 2)
MyArray = Application.Transpose(MyArray)
Debug.Print UBound(MyArray, 1), UBound(MyArray, 2)

End Sub

If you declare the array with with fixed elements, I don't believe it
can be transposed, since that would mean redimensioning the array
which you can't do by definition with a fixed array. Technically the
above code will still work without the initial Dim line, but I thought
I would leave it in for clarity.

Cheers,
Ivan.

On Apr 2, 1:07*am, LesHurley
wrote:
How does one transpose a VBA array? *In the following the result of lines 3
and 5 are the same.

Sub zz()
Dim x(3, 2)
3 *Debug.Print UBound(x, 1), UBound(x, 2)
* * WorksheetFunction.Transpose (x)
5 *Debug.Print UBound(x, 1), UBound(x, 2)
End Sub
--
Thanks for your help

Ivyleaf wrote:
. . .
If you declare the array with with fixed elements, I don't believe it
can be transposed, since that would mean redimensioning the array
which you can't do by definition with a fixed array. . . .

Well, of course it can be transposed; it just can't be transposed into
itself.

Dim x(3, 2)
y = Application.Transpose(x)

works fine.

Alan Beban

OK Guys, both of those suggestions work, mostly!
Inthe following code, it appears that Y must always be Variant. Also x may
not be ReDim'ed as x(3.1) for then the second Debug.Print produces a
"Subscript out of range" error. I wat a Function to return a 3-d vector in
either a horizontal or vertical range of cells irrespective of the
orientation of an argument passed from EXCEL.
Dana DeLouis solved this problem for me but I don't understand his answer
so I don't know how to apply his solution in other cases.

Sub zz()
Dim x() As Double
Dim y() As Variant
ReDim x(1, 3)

Debug.Print UBound(x, 1), UBound(x, 2)
y = Application.Transpose(x)
Debug.Print UBound(y, 1), UBound(y, 2)
End Sub
--
Thanks for your help

LesHurley wrote:
OK Guys, both of those suggestions work, mostly!
Inthe following code, it appears that Y must always be Variant. Also x may
not be ReDim'ed as x(3.1) for then the second Debug.Print produces a
"Subscript out of range" error. I wat a Function to return a 3-d vector in
either a horizontal or vertical range of cells irrespective of the
orientation of an argument passed from EXCEL.
Dana DeLouis solved this problem for me but I don't understand his answer
so I don't know how to apply his solution in other cases.

Sub zz()
Dim x() As Double
Dim y() As Variant
ReDim x(1, 3)

Debug.Print UBound(x, 1), UBound(x, 2)
y = Application.Transpose(x)
Debug.Print UBound(y, 1), UBound(y, 2)
End Sub
The easiest solution might be to understand Dana DeLouis's answer. What
was it?

Alan Beban

Hi Alan,

I realise that a fixed array can still be transposed, I was referring
to the fact it couldn't be transposed to itself... maybe I should have
been more explicit. Generally I would try to avoid using two arrays
though when I only need one which is why I suggested the dynamic array
method.

Les, if you are working with a range, why not leave it as a range
definition? For example:

Sub HRange()
Dim BigNum As Integer, SmlNum As Integer
Dim NewRange As Range

BigNum = Application.Max(Selection.Rows.Count,
Selection.Columns.Count)
SmlNum = Application.Min(Selection.Rows.Count,
Selection.Columns.Count)

Set NewRange = Selection.Cells(1).Resize(SmlNum, BigNum)

MsgBox "Horizontal range is: " & NewRange.Address
End Sub

Sub VRange()
Dim BigNum As Integer, SmlNum As Integer
Dim NewRange As Range

BigNum = Application.Max(Selection.Rows.Count,
Selection.Columns.Count)
SmlNum = Application.Min(Selection.Rows.Count,
Selection.Columns.Count)

Set NewRange = Selection.Cells(1).Resize(BigNum, SmlNum)

MsgBox "Vertical range is: " & NewRange.Address
End Sub

If you were calling this through a UDF, you could obviously pass a
range and orientation enabling you to combine these two into one.

Cheers,
Ivan.

On Apr 2, 9:16*am, Alan Beban wrote:
LesHurley wrote:
OK Guys, both of those suggestions work, mostly!
Inthe following code, it appears that Y must always be Variant. Also x may
not be ReDim'ed as x(3.1) for then the second Debug.Print produces a
"Subscript out of range" error. I wat a Function to return a 3-d vector in
either a horizontal or vertical range of cells irrespective of the
orientation of an argument passed from EXCEL.
*Dana DeLouis solved this problem for me but I don't understand his answer
so I don't know how to apply his solution in other cases.

Sub zz()
Dim x() As Double
Dim y() As Variant
ReDim x(1, 3)

Debug.Print UBound(x, 1), UBound(x, 2)
* * y = Application.Transpose(x)
Debug.Print UBound(y, 1), UBound(y, 2)
End Sub

The easiest solution might be to understand Dana DeLouis's answer. What
was it?

Alan Beban- Hide quoted text -

- Show quoted text -

Hi Les,

Here is an example of what I meant as a Function. Obviously this is
useless as is since I can't imagine you want a function to return the
range as a text string, but I thought it might still demonstrate the
concept:

Function SetRange(TheRange As Range, Vertical As Boolean)
Dim Dimensions(-1 To 0) As Integer

Dimensions(0) = Application.Max(TheRange.Rows.Count,
TheRange.Columns.Count)
Dimensions(-1) = Application.Min(TheRange.Rows.Count,
TheRange.Columns.Count)

Set TheRange = TheRange.Cells(1).Resize(Dimensions(Vertical),
Dimensions(Not Vertical))

SetRange = TheRange.Address
End Function

Cheers,
Ivan.

On Apr 2, 10:30*am, Ivyleaf wrote:
Hi Alan,

I realise that a fixed array can still be transposed, I was referring
to the fact it couldn't be transposed to itself... maybe I should have
been more explicit. Generally I would try to avoid using two arrays
though when I only need one which is why I suggested the dynamic array
method.

Les, if you are working with a range, why not leave it as a range
definition? For example:

Sub HRange()
* * Dim BigNum As Integer, SmlNum As Integer
* * Dim NewRange As Range

* * BigNum = Application.Max(Selection.Rows.Count,
Selection.Columns.Count)
* * SmlNum = Application.Min(Selection.Rows.Count,
Selection.Columns.Count)

* * Set NewRange = Selection.Cells(1).Resize(SmlNum, BigNum)

* * MsgBox "Horizontal range is: " & NewRange.Address
End Sub

Sub VRange()
* * Dim BigNum As Integer, SmlNum As Integer
* * Dim NewRange As Range

* * BigNum = Application.Max(Selection.Rows.Count,
Selection.Columns.Count)
* * SmlNum = Application.Min(Selection.Rows.Count,
Selection.Columns.Count)

* * Set NewRange = Selection.Cells(1).Resize(BigNum, SmlNum)

* * MsgBox "Vertical range is: " & NewRange.Address
End Sub

If you were calling this through a UDF, you could obviously pass a
range and orientation enabling you to combine these two into one.

Cheers,
Ivan.

On Apr 2, 9:16*am, Alan Beban wrote:



LesHurley wrote:
OK Guys, both of those suggestions work, mostly!
Inthe following code, it appears that Y must always be Variant. Also x may
not be ReDim'ed as x(3.1) for then the second Debug.Print produces a
"Subscript out of range" error. I wat a Function to return a 3-d vector in
either a horizontal or vertical range of cells irrespective of the
orientation of an argument passed from EXCEL.
*Dana DeLouis solved this problem for me but I don't understand his answer
so I don't know how to apply his solution in other cases.

Sub zz()
Dim x() As Double
Dim y() As Variant
ReDim x(1, 3)

Debug.Print UBound(x, 1), UBound(x, 2)
* * y = Application.Transpose(x)
Debug.Print UBound(y, 1), UBound(y, 2)
End Sub

The easiest solution might be to understand Dana DeLouis's answer. What
was it?

Alan Beban- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Alan & Ivy, If you search this DG for LesHurley (no spaces) you will find on
about the third line Re-entrant Function with 17 posts. If you review these
you will see my problem and several suggestions for solution. Dana DeLouis
suggested the only one that did everything I wanted it too. But I have
several other similar Routines that have the same problems. I just don't
know how to apply Dana's solution to them because I don't understand it. I
will see what I can do with your suggestions.
--
Thanks for your help


"Ivyleaf" wrote:

Hi Alan,

I realise that a fixed array can still be transposed, I was referring
to the fact it couldn't be transposed to itself... maybe I should have
been more explicit. Generally I would try to avoid using two arrays
though when I only need one which is why I suggested the dynamic array
method.

Les, if you are working with a range, why not leave it as a range
definition? For example:

Sub HRange()
Dim BigNum As Integer, SmlNum As Integer
Dim NewRange As Range

BigNum = Application.Max(Selection.Rows.Count,
Selection.Columns.Count)
SmlNum = Application.Min(Selection.Rows.Count,
Selection.Columns.Count)

Set NewRange = Selection.Cells(1).Resize(SmlNum, BigNum)

MsgBox "Horizontal range is: " & NewRange.Address
End Sub

Sub VRange()
Dim BigNum As Integer, SmlNum As Integer
Dim NewRange As Range

BigNum = Application.Max(Selection.Rows.Count,
Selection.Columns.Count)
SmlNum = Application.Min(Selection.Rows.Count,
Selection.Columns.Count)

Set NewRange = Selection.Cells(1).Resize(BigNum, SmlNum)

MsgBox "Vertical range is: " & NewRange.Address
End Sub

If you were calling this through a UDF, you could obviously pass a
range and orientation enabling you to combine these two into one.

Cheers,
Ivan.

On Apr 2, 9:16 am, Alan Beban wrote:
LesHurley wrote:
OK Guys, both of those suggestions work, mostly!
Inthe following code, it appears that Y must always be Variant. Also x may
not be ReDim'ed as x(3.1) for then the second Debug.Print produces a
"Subscript out of range" error. I wat a Function to return a 3-d vector in
either a horizontal or vertical range of cells irrespective of the
orientation of an argument passed from EXCEL.
Dana DeLouis solved this problem for me but I don't understand his answer
so I don't know how to apply his solution in other cases.

Sub zz()
Dim x() As Double
Dim y() As Variant
ReDim x(1, 3)

Debug.Print UBound(x, 1), UBound(x, 2)
y = Application.Transpose(x)
Debug.Print UBound(y, 1), UBound(y, 2)
End Sub

The easiest solution might be to understand Dana DeLouis's answer. What
was it?

Alan Beban- Hide quoted text -

- Show quoted text -