How do I select subrange from a range object in VB

2019-09-06 17:05发布

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.

2条回答
【Aperson】
2楼-- · 2019-09-06 17:30

It's not uncommon (for me, at least) to need a reference to a sub-range of a larger range. The Range constructor (property? looks like a constructor to me in this context) appears to only accept string arguments of the "A4:G7" variety--not even "R4C1:R7C7" style, which is easier to code on the fly from row and column indices.

I suppose VBA does not allow overloading, but it's exasperating that MS doesn't see fit to provide useful functions for this kind of thing. AFAIK (which isn't that far for me in VBA) there is no range-like constructor that takes row and column indices, or cell pairs for that matter. The .Rows and .Columns properties don't do what I expected them to do either. Other than .Rows.Count and .Columns.Count, I'm not sure what they are good for (is my inexperience showing? That's ok--help me out.)

I got tired of writing the simple but tedious code to convert row and column indices to the "A4:G7" style strings inline, so I wrote a simple function to take care of it.

In the calling function:

dim row1 as long, col1 as long, row2 as long, col2 as long
dim subRng as Range
...
[Insert absolutely brilliant code here to compute row1, etc.,
 or in my case, hack away until it works]

set subRng = getSubRange(row1, col1, row2, col2, bigRange)
...
[do great things with subRng]

Here's the getSubRange utility function:

Function getSubRange(iRow1 As Long, iCol1 As Long, _
                     iRow2 As Long, iCol2 As Long, _
                     sourceRange As Range) As Range
' Returns a sub-range of the source range (non-boolean version of makeSubRange().
' Inputs:
'   iRow1       -  Row and colunn indices in the sourceRange of
'   iCol1          the upper left and lower right corners of
'   iRow2          the requested subrange.
'   iCol2
'   sourceRange - The range from which a sub-range is requested.
'
' Return: Reference to a sub-range of sourceRange bounded by the input row and
'         and column indices.
' Notes: A null range will be returned if the following is not true.
'        1 <= iRow1 <= SourceRange.Rows.count
'        1 <= iRow2 <= SourceRange.Rows.count
'        1 <= iCol1 <= SourceRange.Columns.count
'        1 <= iCol2 <= SourceRange.Columns.count

   Const AM1 = 64 'Ascii value of 'A' = 65; Asc('A') - 1 = 64
   Dim rangeStr As String

   If (1 <= iRow1) And (iRow1 <= sourceRange.Rows.Count) And _
      (1 <= iRow2) And (iRow2 <= sourceRange.Rows.Count) And _
      (1 <= iCol1) And (iCol1 <= sourceRange.Columns.Count) And _
      (1 <= iCol2) And (iCol2 <= sourceRange.Columns.Count) Then
      rangeStr = Chr(AM1 + iCol1) & CStr(iRow1) & ":" _
               & Chr(AM1 + iCol2) & CStr(iRow2)
      Set getSubRange = sourceRange.Range(rangeStr)
   Else
      Set getSubRange = Nothing
   End If

End Function 'getSubRange()

Don't forget that the sub-range is a reference to part of the source range, not a copy of a part of it: any change you make to the sub-range is a change to the source range. But I guess that's obvious enough.

查看更多
别忘想泡老子
3楼-- · 2019-09-06 17:31

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
查看更多
登录 后发表回答