VBA Pass User selected 2d Range vs static array in place of Arr

Question

I have a need to convert the following function to accept a user selected 2 dimensional range [datatype] in place of Arr [ the arbitrary custom defined Array] that is used in it's place. I want to do this so I can run worksheet functions on the data. I know it can be done when the range object is selected, but my data at one points need to be converted to a variant, then re assigned to a [new] range on the new worksheet (aka 'put the array values on the worksheet), then reread back as a range when it does (aka ' load the worksheet values back into the array)

Sub SortViaWorksheet()
    Dim Arr(1 To 5) As String ' this is the array to be sorted
    Dim WS As Worksheet ' temporary worksheet
    Dim R As Range
    Dim N As Long

    ' fill up the array with some
    ' aribtrary values.
    Arr(1) = "aaa"
    Arr(2) = "zzz"
    Arr(3) = "mmm"
    Arr(4) = "ttt"
    Arr(5) = "bbb"

    Application.ScreenUpdating = False

    ' create a new sheet
    Set WS = ThisWorkbook.Worksheets.Add

    ' put the array values on the worksheet
    Set R = WS.Range("A1").Resize(UBound(Arr) - LBound(Arr) + 1, 1)
    R = Application.Transpose(Arr)

    ' sort the range
    R.Sort key1:=R, order1:=xlAscending, MatchCase:=False

    ' load the worksheet values back into the array
    For N = 1 To R.Rows.Count
        Arr(N) = R(N, 1)
    Next N

    ' delete the temporary sheet
    Application.DisplayAlerts = False
    WS.Delete
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

    ' test/debug/confirmation
    For N = LBound(Arr) To UBound(Arr)
        Debug.Print Arr(N)
    Next N   
End Sub

In other words, I want to be able to pass in a user selected 2d range that will be converted to a variant, then export this variant to a 2d range in a worksheet that is reloaded back into a range for vba to work with and run functions on. I understand this example uses .sort, which requires a 1d array. I do not intend on using sort, but rather things like worksheet functions on this new range, such as rank, countif, average, max, min, median, etc

My friend pointed out that the example above uses a 1d range as input and exports a 1d array to the worksheet. So I would need the ability to select a 2d range as input and export a 2d range to a worksheet that is subsequently reread as a new range.

possible duplicate of [VBA - Run WorksheetFunction on \[Range derived\] Variant array?](http://stackoverflow.com/questions/24877205/vba-run-worksheetfunction-on-range-derived-variant-array) — David Zemens, Jul 22 '14 at 14:01
your original answer didn't get to the root of the issue. If I can export a 2d selection to a new worksheet and reload the worksheet into a new range, I can run excel functions on them. — thistleknot, Jul 22 '14 at 15:14
Well you gave up on that question -- I was engaged in the comments with you, trying to fully understand the problem. Next thing I know you have asked 2 more questions which are essentially part of the same original problem. What specific part has you stuck? — David Zemens, Jul 22 '14 at 15:45
You have `Set R = ..` and `R=` in consecutive lines thus overwriting the first statement. — John Alexiou, Jul 22 '14 at 16:23

score 1 · Answer 1 · answered Jul 22 '14 at 15:50

You can convert a worksheet range to an array and back again using the range's Value property. For example to read the current workbook selection into an array, process it, and write it back:

Sub Example()
    Dim myArr() As Variant
    myArr = Selection.Value

    For i = LBound(myArr, 1) To UBound(myArr, 1)
        For j = LBound(myArr, 2) To UBound(myArr, 2)
            myArr(i, j) = myArr(i, j) + 5
            Debug.Print CStr(i) & "_" & CStr(j) & ": " & myArr(i, j)
        Next j
    Next i

    Selection = myArr
End Sub

To pass the range into a function for processing:

Sub TestMain()
    Selection = TestProcess(Selection)
End Sub

Function TestProcess(userRange As Range) As Variant
    Dim result() As Variant

    result = userRange.Value

    For i = LBound(result, 1) To UBound(result, 1)
        For j = LBound(result, 2) To UBound(result, 2)
            result(i, j) = result(i, j) + 5
            Debug.Print CStr(i) & "_" & CStr(j) & ": " & result(i, j)
        Next j
    Next i

    TestProcess = result
End Function

score 0 · Answer 2 · answered Jul 22 '14 at 18:20

Is this what you want to do for example:

Public Sub ProcessRange(ByRef r As Range)
    Dim vals() As Variant
    vals = r.Value
    Dim rows As Integer, cols As Integer
    rows = r.rows.Count: cols = r.Columns.Count
    Dim x As Double
    x = WorksheetFunction.Average(vals)
    ' 3.66666
End Sub

when called by ProcessRange Range("B4").Resize(3, 3) in SCR

VBA Pass User selected 2d Range vs static array in place of Arr

2 Answers2