How do I select subrange from a range object in VBA?

Question

I am trying to write a VBA function that would give me the average and stdev of a fractile of data (assuming total 5 fractiles). The data would be either a row vector or a Column vector. I have written down the following till now :

    Function AverageFractile(ret As Range, fractile As Integer) As Variant

    Dim lenRange As Integer
    Dim startIndex As Integer
    Dim endIndex As Integer
    Dim subRange As Variant

    'Arrange the range object in ascending Order, doesn't work
    ' ret = Sort(ret, XlSortOrder = xlAscending)

    'Getting the indices that would be used to slice the input range to get the relevant fractile
    lenRange = Application.WorksheetFunction.Max(ret.Rows.Count, ret.Columns.Count)
    startIndex = (lenRange * (fractile - 1)) \ 5 + 1
    endIndex = (lenRange * fractile) \ 5

'    subRange = ret(Cells(startIndex,1),Cells(endIndex,1))
'    This is not working 


    End Function

I am stuck at two places till now:- a) I am trying to sort the data, but the function Sort is not working, how do I sort the range object which is the input in ascending order?

b) How do I select a subrange from range object, so that I can calculate its average and Stdev?

Appreciate the help, I have been trying for a couple of hours now and haven't been able to work it out.

Answer 1

As I said in my comment, there's "too many questions" in your question. However, here is my answer to your "title" question:

Function subRange(r As Range, startPos As Integer, endPos As Integer) as Range
    Set subRange = r.Parent.Range(r.Cells(startPos), r.Cells(endPos))
End Function