Yes, it does.
Hi there. I need to read some data from a worksheet range that could either be horizontal (x columns by 1 row) or vertical (1 column by x rows) into a variant array, then redim preserve it so that is longer or wider.
While I've cobbled together something, I don't really understand it:
I read at http://vbadud.blogspot.com/2007/12/a...ual-basic.html that if you use the Preserve keyword, you can resize only the last array dimension. Hence the application.transpose part of my code.Code:Public Function IncreaseRange(rngInput As Range) Dim a a = rngInput If UBound(a, 2) > UBound(a, 1) Then ReDim Preserve a(1, 1 To 50) Else a = Application.Transpose(rngInput) ReDim Preserve a(1 To 50) End If IncreaseRange = a End Function
But I don't understand why I have to use use ReDim Preserve a(1, 1 To 50) for horizontal ranges and ReDim Preserve a(1 To 50) for vertical ranges. Does transposing a worksheet range do something funny to it that I'm not aware of, like turning it from a 2d to a 1d array?
Yes, it does.
Out of many, one people
Hi Joe. Thanks. THis prompted me to google further, and I found the following on Chip Pearson's site at http://www.cpearson.com/excel/Return...sFromVBA.aspx:
And also got something recent from Daily Dose of Excel that I vaguely recall:Orienting An Array
If your UDF creates a 1-dimensional array as its result, it can orient the array as either a row vector or a column vector so that is will be properly displayed in the worksheet cells without requiring the user to wrap your UDF result in a TRANSPOSE function. If the function was called from a row vector of cells (e.g., A1:E1), it does not need to be transposed. If the function was called from a column vector of cells (e.g., A1:A5), the array needs to be transposed. The code below looks at Application.Caller.Rows.Count and if this is greater than 1, it tranposes the array before returning it to the caller. Note that this should be done only with single-dimensional arrays and only when the UDF is being called from a worksheet range. Therefore, you should first test Application.Caller with IsObject and then test Application.Caller.Rows.Count and Application.Caller.Columns.Count to test if it is being called from a row or column vector. For example,
Function Test(NN As Long) Dim Result() As Long Dim N As Long ReDim Result(1 To NN) For N = 1 To NN Result(N) = N Next N If Application.Caller.Rows.Count > 1 Then Test = Application.Transpose(Result) Else Test = Result End If End Function
You can, of course, forego this and return the array as-is and leave it up to the user to use the TRANSPOSE function to properly orient the array.
http://www.dailydoseofexcel.com/arch...ray-dimension/
Thanks
Playing around a bit further, I see I can dynamically double the length of a vector based on a worksheet range using this:
Code:Public Function ReDim2(rngInput As Range) Dim a a = rngInput If rngInput.Columns.Count = 1 Then a = Application.WorksheetFunction.Transpose(rngInput) Else a = Application.WorksheetFunction.Transpose( _ Application.WorksheetFunction.Transpose(rngInput)) End If ReDim Preserve a(1 To 2 * UBound(a)) ReDim2 = a End Function
Bookmarks