VBA - 在 [范围派生] 变体数组上运行 WorksheetFunction？

我需要在单个输入矩阵上连续运行内置 Excel 函数。

问题是，输入[range]我假设是一个指针常量。

所以当然，我可以做一个WorkSheetFunction的计算[range]输入并将输出放入变体中。

但是，我确实需要对变体数据运行更多遍。我有一个更高级的计算，将对使用标准 Excel 函数（如平均值和中位数）的数据运行 4 次转换。

这是我的代码

Public Function RankECDF(ByRef r_values As Range, Optional ByVal zeroFlag As Boolean = 0) As Variant()

Dim i As Integer, j As Integer, N As Integer, M As Integer

Dim total As Integer

Dim y() As Variant

N = r_values.Rows.Count
M = r_values.Columns.Count

y = r_values.Value    'copy values from sheet into an array

Dim V() As Variant
Dim AltV As Variant

Dim OutV As Variant
Dim OutAltV As Variant

'quite possible to makes the Variant larger to hold the "other arrays"

ReDim V(1 To N, 1 To M)
ReDim AltV(1 To N, 1 To M)

ReDim OutV(1 To N, 1 To M)
ReDim OutAltV(1 To N, 1 To M)

'first pass just checks for zero's.  Could speed this process up by implementing the zeroFlag check to skip the double loop

total = WorksheetFunction.Sum(r_values)
For R = 1 To N
    For C = 1 To M
        If y(R, C) = "" Then
            V(R, C) = ""
            AltV(R, C) = 0
        Else
            'would error if cell was ""
            'V(R, C) = WorksheetFunction.Average(WorksheetFunction.Rank(y(R, C), r_values, 1), WorksheetFunction.CountIf(r_values, "<=" & y(R, C))) / WorksheetFunction.Count(r_values)
            V(R, C) = y(R, C)
            AltV(R, C) = y(R, C)
        End If
    Next C
Next R

'second loop does rankecdf conversions
For RA = 1 To N
    For CA = 1 To M
       'OutV(RA, CA) = 1
       'OutV(RA, CA) = WorksheetFunction.Rank(V(RA, CA), V, 1)

       'OutAltV(RA, CA) = 2
       'OutAltV(RA, CA) = WorksheetFunction.Average(WorksheetFunction.Rank(y(RA, CA), r_values, 1), WorksheetFunction.CountIf(r_values, "<=" & y(RA, CA))) / WorksheetFunction.Count(r_values)
    Next CA
Next RA

If (zeroFlag) Then
    RankECDF = AltV
    'RankECDF = OutAltV(1 to N, 1 to M)
Else
    RankECDF = V
    'RankECDF = OutV(N, M)
End If

End Function

问题可以在这里找到：

OutV(RA, CA) = WorksheetFunction.Rank(V(RA, CA), V, 1)

WorksheetFunction.Rank(y(R, C), r_values, 1)

你不能把数组放在arg1。做就是了：

i = y(R, C)

Then:

WorksheetFunction.Rank(i, r_values, 1)

对我来说效果很好