如何从VBA中的范围对象中选择子范围? [英] How do I select subrange from a range object in VBA?

问题描述

我正在尝试编写一个VBA函数，该函数将为我提供数据碎片的平均值和标准偏差(假设总共有5个碎片).数据可以是行向量或列向量.到目前为止，我已经写下了以下内容:

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

到目前为止，我一直停留在两个地方:a)我正在尝试对数据进行排序，但是Sort函数不能正常工作，如何对范围对象进行排序，该对象是输入的升序?

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)如何从范围对象中选择一个子范围，以便可以计算其平均值和Stdev?

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.

推荐答案

正如我在评论中所说，您的问题中有太多问题".但是，这是我对您的标题"问题的回答:

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

这篇关于如何从VBA中的范围对象中选择子范围?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！