gpt4 book ai didi

arrays - 将范围传递给 Excel 用户定义函数并将其分配给数组

转载 作者:行者123 更新时间:2023-12-02 10:00:56 25 4
gpt4 key购买 nike

我正在努力

  1. 将两个范围 - 多行单列 - 传递给 Excel 2007 中的用户定义函数,
  2. 然后将其分配给一个数组进行处理。

谁能告诉我如何将这样的范围分配给数组?

范围不是恒定的,因为我在不同的单元格中使用 UDF 来处理不同的数据,因此我不能使用例如 Range("A1:A10")

当我只使用 Data1.Rows.Cells(i, 1) 而不是数组时,代码就可以工作。但我认为为了提高效率,使用一维数组会更好。

这是我当前的代码

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

最佳答案

类似这样的方法可以处理一维范围,其中包括测试

  1. 不等范围
  2. 单个单元格范围(不能使用变体)

样本子

Sub Test()
MsgBox Sample([a1:a3], [a5:a7])
End Sub

功能

 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

关于arrays - 将范围传递给 Excel 用户定义函数并将其分配给数组,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/15940047/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com