vba 将范围传递给 Excel 用户定义函数并将其分配给数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15940047/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Passing a range to Excel User Defined Function and assigning it to an array
提问by MrA2Z
I am trying to
我在尝试着
- pass two ranges - multiple rows single column- to a user defined function in Excel 2007,
- then assign it to an array for processing.
- 将两个范围 -多行单列- 传递给 Excel 2007 中的用户定义函数，
- 然后将其分配给一个数组进行处理。
Can anybody tell me how to assign such range to an array?
谁能告诉我如何将这样的范围分配给数组？
The range is not constant as I am using an UDF in different cells for different data so I cannot use e,g, Range("A1:A10")
范围不是恒定的，因为我在不同的单元格中为不同的数据使用 UDF，所以我不能使用例如， Range("A1:A10")
The code is working when I just use Data1.Rows.Cells(i, 1)instead of arrays. But I think it is better to use one dimensional arrays for efficiency.
当我只使用Data1.Rows.Cells(i, 1)而不是数组时，代码正在工作。但我认为最好使用一维数组来提高效率。
Here is my current code
这是我当前的代码
Function Sample(Data1 As Range, Data2 As Range) As Double
'Size of Data1 and Data2
Dim rows As Integer
rows = Data1.Rows.Count
'Declaring two one dimensional arrays
Dim data1Array(rows) As Double --- Getting error here
Dim data2Array(rows) As Double --- Getting error here
Dim diff As Double
Dim average As Double
Dim i As Integer
'Assigning Range to Array
data1Array = Data1 --- Getting Error here
data2Array = Data2 --- Getting Error here
average = 0
diff = 0
For i = 1 To rows
diff = data1Array(i) - data2Array(i)
If diff < 0 Then
diff = diff * -1
End If
average = diff + average
Next i
Sample = average/rows
End Function
回答by brettdj
Something like this to work with 1D ranges which includes testing for
像这样的东西可以处理一维范围，包括测试
- unequal ranges
- single cell ranges (can't use variants)
- 不等范围
- 单个单元格范围（不能使用变体）
sample sub
样本子
Sub Test()
MsgBox Sample([a1:a3], [a5:a7])
End Sub
function
功能
Function Sample(Data1 As Range, Data2 As Range)
Dim X
Dim Y
Dim lngCnt As Long
Dim dbDiff As Double
Dim dbAvg As Double
If Data1.rows.Count <> Data2.rows.Count Then
Sample = "Different range sizes"
Exit Function
ElseIf Data1.rows.Count = 1 Then
Sample = "Single cell range"
Exit Function
End If
X = Application.Transpose(Data1)
Y = Application.Transpose(Data2)
For lngCnt = 1 To UBound(X)
dbDiff = X(lngCnt) - Y(lngCnt)
If dbDiff < 0 Then
dbDiff = dbDiff * -1
End If
dbAvg = dbDiff + dbAvg
Next
Sample = dbAvg / lngCnt
End Function
