transpose
 

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

transpose
 

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

transpose
 

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

transpose
 

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

transpose
 

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

transpose
 

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 -

transpose
 

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 -

transpose
 

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 -

transpose
 

Ivy, I would leave it as a range if that were possible, but with a compound,
re-entrant EXCEL formula such as xx(xx(a,b),xx(c,d)) the results of the two
inner functions are VBA arrays and they constitute the arguments for the
outer function. That would still not be much of a problem if I were satisfied
to have all the arguments and answer oriented the same direction. But I
don't want any limitations on argument orientation.
--
Thanks for your help


"Ivyleaf" wrote:

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 -

transpose
 

Hi. May I suggest the following idea? When you get stuck on similar ideas
on vba arrays, sometimes a good technique is to step thru the code and look
at the "Local" window. In vba, that's View | Locals Window. Step thru code
with F8.
In your original code, you were passing a Range to the function, but you
said that using Va(1), Va(2)...etc was working.
This is confusing because when a range is passed, it usually requires two
dimensions. Example..Va(1,1), or whatever.
There are many ways to write the code, so it's up to you to decide.
Step thru this code to note some differences by looking at the Locals
window.
Notice that v & h use two indexes when refering to a range.
Notice that in the 'a example, the lowest index is 1. However, if you use
'b, then the lowest index is 0.
A three item Vector could be passed using 'c or 'd, but it's ugly.

The function you most likely need is UBound, and LBound. Unfortunately, one
has to trap errors when testing for dimensions in vba.
Note that one of the many bugs (at least on my system) with 2007 is that
UBound & LBound is not listed in the autocomplete feature of vba.
Anyway, try to transpose each vector once, then once again, to see the
changes in the locals window.

Sub Demo()
Dim h, v, a, b, c(), d()
v = Range("A2:A4") 'Verticle
h = Range("A1:C1") 'Horizontal
a = [{1,2,3}]
b = Array(3, 4, 5)

ReDim c(-1 To 1) 'Ugly
ReDim d(-7 To -5) 'Really ugly
Stop
End Sub

--
HTH :)
Dana DeLouis



"LesHurley" wrote in message
...
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 -