excel - 有没有办法将字符串传递给 Average 函数？

Question

我有一个包含车辆速度列的工作表，我想从该列中获取特定范围(发生转弯的范围)并计算所有转弯组合的平均速度。
我已经有两个数组中的转牌范围，一个用于转牌开始，一个用于转牌结束。我为此目的编写的代码如下所示:

Dim strTurnAvg As String
Dim k As Long
strTurnAvg = ""
For k = LBound(TurnStarts) To UBound(TurnStarts)
    If TurnStarts(k) <> TurnEnds(k) Then 'the code for detecting turns rarely messes up and takes the same cell as the start and end of a turn. This is used to ignore such instances
        If strTurnAvg = "" Then strTurnAvg = "Range(" & TurnStarts(k) & ":" & TurnEnds(k) & ").Value" Else strTurnAvg = strTurnAvg & ", Range(" & TurnStarts(k) & ":" & TurnEnds(k) & ").Value"
    End If
Next

Dim result As Double
result = Application.WorksheetFunction.Average(strTurnAvg) '<- This results in a Run-time error '1004', Unable to get the Average property of the WorksheetFunction class

我尝试手动放置 strTurnAvg结果变成了一个平均命令，一切正常，但是当我尝试使用变量传递它时，它给了我运行时错误。
有没有办法以另一种方式实现这个过程，或者我做错了什么？

Answer 1

尝试以下操作:

Dim TurnAvg As Range

Dim k As Long
For k = LBound(TurnStarts) To UBound(TurnStarts)
    If TurnStarts(k) <> TurnEnds(k) Then 'the code for detecting turns rarely messes up and takes the same cell as the start and end of a turn. This is used to ignore such instances
        If TurnAvg Is Nothing Then
            Set TurnAvg = Range(TurnStarts(k), TurnEnds(k))
        Else
            Set TurnAvg = Union(TurnAvg, Range(TurnStarts(k), TurnEnds(k)))
        End If
    End If
Next

Dim Result As Double
Result = Application.WorksheetFunction.Average(TurnAvg) 

它使用 Union 收集所有范围进入 TurnAvg .因此，您使用实际范围和其中的数据。您不能使用字符串，因为您只能使用数字而不是文本来计算。 Average函数需要数字来计算。
您可能想要指定您的范围在哪个工作簿/工作表中

ThisWorkbook.Worksheets("Sheet1").Range(TurnStarts(k), TurnEnds(k))

否则 VBA 可能会选择错误的工作簿或错误的工作表。