Here’s an odd one, I think. I have code that creates a two-dimensional array and I want to use
Application.WorksheetFunction.Transpose to switch the rows and columns.
Dim aTest(0 To 3, 0 To 1) As Long
Dim vaTrans As Variant
Dim i As Long, j As Long
For i = 0 To 3
For j = 0 To 1
aTest(i, j) = (10 ^ i) * (j + 1)
vaTrans = Application.WorksheetFunction.Transpose(aTest)
No problem. I check the locals window at Stop and it seems to have done the trick. I didn’t know it converted it to one-based arrays, but I guess that’s OK.
I ran into a situation today where my two-dimensional array only had one column dimension. When I ran it through Transpose, it came back as a one-dimensional array.
That’s not what I want. And it broke my code. I can’t tell you how much code I have that uses Transpose. Honestly, I can’t! That’s the problem. If I knew, I would go fix it.
Although the above simple example doesn’t demonstrate it, I need to Transpose because I’m using a dynamic array. I have to keep the last dimension variable or Redim Preserve won’t work. But in reality the dynamic portion of my array is the “row” portion, so I transpose it at the end. I guess I’ll have to go steal Chip’s transpose code and try to find all the places I’ve used this method.
So who’s got code that searches all the VBA on my hardrive?