transpose

[LesHurley]

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

 

[Ivyleaf]

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.

 

[Alan Beban]

. . .
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

 

[LesHurley]

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

 

[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

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

Alan Beban

 

[Ivyleaf]

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.

 

[Ivyleaf]

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.

 

[LesHurley]

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.

 

[LesHurley]

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.

 

[Dana DeLouis]

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