Results 1 to 4 of 4

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

  1. #1
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    356
    Articles
    0

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

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

  2. #2
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    170
    Articles
    0
    Yes, it does.
    Circumference of a circle = 2πr²



    ²the circle's radius

  3. #3
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    356
    Articles
    0
    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:

    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/arch...ray-dimension/

    Thanks

  4. #4
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    356
    Articles
    0
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •