VBA - 在[Range derived] Variant数组上运行WorksheetFunction? [英] VBA - Run WorksheetFunction on [Range derived] Variant array?
问题描述
问题是,输入的 [范围]
是我假设的一个指针常量。
确实,我可以做一个 WorkSheetFunction
计算在
[range]
输入并将输出放入一个变体。
但是,我确实需要在变体数据上运行更多的通行证。我有一个更高级的计算,将对使用标准excel函数(如平均值和中位数)的数据进行4次转换。
这是我的代码
公共函数RankECDF(ByRef r_values As Range,可选ByVal zeroFlag As Boolean = 0)As Variant()
Dim i As整数,j为整数,N为整数,M为整数
Dim total As Integer
Dim y()As Variant
N = r_values .Rows.Count
M = r_values.Columns.Count
y = r_values.Value'将值从工作表复制到数组
Dim V()As Variant
Dim AltV As Variant
Dim OutV As Variant
Dim OutAltV As Variant
'很有可能使Variant更大以容纳其他数组
ReDim V(1到N,1到M)
ReDim AltV(1到N,1到M)
ReDim OutV(1到N,1到M)
ReDim OutAltV(1到N,1到M)
'第一次通过只是检查零。可以通过实现zeroFlag检查来跳过双循环来加速此过程
total = WorksheetFunction.Sum(r_values)
对于R = 1到N
对于C = 1 To M
如果y(R,C)=然后
V(R,C)=
AltV(R,C)= 0
Else
'如果单元格是
'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)
结束如果
下一个C
下一个R
'第二个循环执行rankecdf转换
对于RA = 1到N
对于CA = 1到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_va lues,1),WorksheetFunction.CountIf(r_values,< =& y(RA,CA)))/ WorksheetFunction.Count(r_values)
下一个CA
下一个RA
如果(zeroFlag)然后
RankECDF = AltV
'RankECDF = OutAltV(1到N,1到M)
Else
RankECDF = V
'RankECDF = OutV(N,M)
End If
结束功能
可以在这里找到问题:
OutV(RA,CA)= WorksheetFunction.Rank(V(RA,CA),V,1)
WorksheetFunction.Rank(y(R,C),r_values, 1)
您不能将数组放在 arg1上
。只要做:
i = y(R,C)
然后:
WorksheetFunction.Rank(i,r_values,1)
它对我很好。
I have a need to run successive passes of built in excel functions on a single matrix of input.
The problem is, the input [range]
is what I assume, a pointer constant.
So sure, I can do a WorkSheetFunction
calculations on the [range]
input and place the output into a variant.
But, I do have a need to run more passes on the variant data. I have a more advanced calculation that is going to run 4 transforms on data that use standard excel functions like average, and median.
Here's my code
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
The problem can be identified right around here:
OutV(RA, CA) = WorksheetFunction.Rank(V(RA, CA), V, 1)
WorksheetFunction.Rank(y(R, C), r_values, 1)
You cannot put an Array on arg1
. Just do:
i = y(R, C)
Then:
WorksheetFunction.Rank(i, r_values, 1)
It worked fine for me
这篇关于VBA - 在[Range derived] Variant数组上运行WorksheetFunction?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!