传递一个范围到Excel用户自定义函数，并将其分配给一个数组 [英] Passing a range to Excel User Defined Function and assigning it to an array

问题描述

我试图

1. 通过两个范围 - 多行单列的 - 在Excel 2007用户定义的函数，


2. 然后将其分配给处理数组。

谁能告诉我如何分配这样的范围到一个数组？

的范围是不恒定的，因为我在不同的细胞对不同的数据使用UDF，所以我不能用E，G，范围（A1：A10）

在code是工作的时候我只是用 Data1.Rows.Cells（1,1）的代替阵列。但我认为这是更好地使用效率的一维数组。

下面是我目前的code

 函数样品（数据1作为范围，数据2为范围）为双 数据1和Data2中的大小
 昏暗行作为整数
 行= Data1.Rows.Count 声明两个一维数组
 昏暗data1Array（行）为双---这里得到错误
 昏暗data2Array（行）为双---这里得到错误 昏暗的差异为双
 昏暗的平均值作为双
 昏暗我作为整数 指定范围阵列
 data1Array =数据1 ---抵达该处错误
 data2Array =数据2 ---抵达该处错误 平均= 0
 差异= 0 对于i = 1到行   的diff = data1Array（ⅰ） -  data2Array㈠   如果DIFF＆LT; 0，则
     差异=差异* -1
   万一   平均=差异+平均 接下来，我 样品平均= /行结束功能
 

解决方案

事情是这样的一维的工作范围，其中包括测试

1. 不平等的范围


2. 单单元格区域（不能使用变种）

样品分

 子测试（）
MSGBOX样品（[A1：A3]，[A5：A7]）
结束小组
 

功能

 函数样品（数据1作为范围，数据2为范围） 昏暗的点¯x
 昏暗的ÿ
 昏暗lngCnt只要
 昏暗dbDiff为双
 昏暗dbAvg为双 如果Data1.rows.Count＆LT;＆GT; Data2.rows.Count然后
 样品=不同范围的大小
 退出功能
 elseif的Data1.rows.Count = 1，则
 样品=单细胞范围
 退出功能
 万一 X = Application.Transpose（数据1）
 Y = Application.Transpose（数据2） 对于lngCnt = 1到UBound函数（X）
   dbDiff = X（lngCnt） -  Y（lngCnt）
   如果dbDiff＆LT; 0，则
     dbDiff = dbDiff * -1
   万一
   dbAvg = dbDiff + dbAvg
 下一个 样品= dbAvg / lngCnt结束功能
 

I am trying to

1. pass two ranges - multiple rows single column - to a user defined function in Excel 2007,
2. then assign it to an array for processing.

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")

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.

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

解决方案

Something like this to work with 1D ranges which includes testing for

1. unequal ranges
2. 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

这篇关于传递一个范围到Excel用户自定义函数，并将其分配给一个数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！