将UDF转换为数组UDF

Question

我已经检查了如何制作单单元数组UDF，我不能理解我所看到的，所以我想也许我最好问一下！

我想知道是否有人可以帮我弄清楚如何将我的UDF转换成可以处理范围的东西，然后可以对输出进行平均。

我创建了一个UDF来查找年龄中的月数，格式如下：

Chronological Age

    8:1
    8:2
    8:9
    8:1
    8:0
    7:10
    8:9

UDF如下所示：

Function GDAgeToMonths(YearsMonths As String) As Integer

Dim Colonz As Integer, Yearz As Integer, Monthz As Integer, Greaterz As Integer

' check if the stings consists of ">" sign
If InStr(YearsMonths, ">") >= 1 Then
    Greaterz = 2
Else
    Greaterz = 1
End If

' check position of ":" or "." sign
If InStr(YearsMonths, ":") >= 1 Then
    Colonz = InStr(YearsMonths, ":")
Else
    Colonz = InStr(YearsMonths, ".")
End If

Yearz = Mid(YearsMonths, Greaterz, Colonz - Greaterz)
Monthz = Right(YearsMonths, Len(YearsMonths) - Colonz)
GDAgeToMonths = Yearz * 12 + Monthz

End Function

所以我在想一些类似的东西：

Function GDAverageAge(AgeRange As Range) As Integer

Dim MonthsRange As Range, AverageMonths As Double

MonthsRange = GDAgeToMonths(AgeRange)

AverageMonths = WorksheetFunction.Average(MonthsRange)

GDAverageRange = GDMonthsToAge(AverageMonths)

End Function

使用下面的函数将月份的平均值返回到年龄：

Function GDMonthsToAge(NumberofMonths As Integer) As String

Dim Yearz As Integer, Monthz As Integer
Yearz = NumberofMonths \ 12
Monthz = NumberofMonths - (12 * Yearz)
GDMonthsToAge = Yearz & ":" & Monthz
End Function

我真的希望这是有意义的！

我真的不知道它本身是否必须是一个数组公式，或者它是否会对范围做一些事情，但我基本上计划使用一个公式来对范围中的每个单元格运行公式的结果进行平均。

我们将非常感谢您的帮助！

谢谢您抽时间见我!

Answer 1

你已经很接近了，你只需要一个For循环：

Function GDAverageAge(Ages As Range) As String
Dim c As Integer, a As Integer, am As Integer, v As Variant
'Counter of number of entries in range
c = 0
'Total value of range in months
a = 0
For Each v In Ages
a = a + GDAgeToMonths(v.Value)
c = c + 1
Next v
am = a / c
GDAverageAge = GDMonthsToAge(am)
End Function

在这里工作-尽情享受！