Reading a vertical OR horizontal range to VBA array, then doubling its length.

[JeffreyWeir]

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:

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

I read at http://vbadud.blogspot.com/2007/12/array-dimensioning-in-visual-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.
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?

 

[JoePublic]

Yes, it does.

 

[JeffreyWeir]

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/ReturningArraysFromVBA.aspx:

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.

And also got something recent from Daily Dose of Excel that I vaguely recall:
http://www.dailydoseofexcel.com/archives/2011/06/09/transpose-changes-array-dimension/

Thanks

 

[JeffreyWeir]

Playing around a bit further, I see I can dynamically double the length of a vector based on a worksheet range using this:

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