在宏中使用工作表函数

Question

我在VBA中有一个整数数组，我希望从中得到上四分位数和下四分位数。

我想使用这种方法获取它们：https://msdn.microsoft.com/en-us/library/office/ff836118.aspx

文档建议您可以使用数组来执行此操作，但是当我试图运行代码(如下所示)时，会发现一个错误，即无法获得WorksheetFunction类的四分位数属性。

请协助。

Dim totalsalesthatday() As String
Dim doINeedTo As Boolean
Dim totalsalesthatdayAverage As Integer
Dim randomnumberthingy As Integer
Dim quartile1 As Integer
Dim quartile3 As Integer
Dim iqr As Integer
Dim upper As Integer
Dim lower As Integer
quantity = 0
For Each queryaddress In worksheetname.Range("A2:A21")
query = queryaddress.Value
offsetnum = 0
If offsetnum = 0 Then
doINeedTo = True
End If
For Each daysoftheweek In Sheets
quantity = 0
If InStr(1, daysoftheweek.Name, worksheetnamename, vbTextCompare) > 0 And daysoftheweek.ListObjects.Count > 0 Then
Set itemaddress = daysoftheweek.Columns(5).Find(query, , xlValues, xlWhole)
If Not itemaddress Is Nothing Then
        firstAddress = itemaddress.Address
        Do
Set itemrow = itemaddress.EntireRow
quantity = quantity + itemrow.Columns(6).Value
Set itemaddress = daysoftheweek.Columns(5).FindNext(itemaddress)
Loop While Not itemaddress Is Nothing And itemaddress.Address <> firstAddress
End If
   offsetnum = offsetnum + 1
   ReDim Preserve totalsalesthatday(offsetnum)
   totalsalesthatday(offsetnum) = daysoftheweek.ListObjects.Item(1).ListRows.Count
   queryaddress.Offset(0, offsetnum).Value = quantity
   worksheetname.Range("A1").Offset(0, offsetnum).Value = daysoftheweek.Name
End If
Next
If doINeedTo Then
quartile1 = WorksheetFunction.Quartile(totalsalesthatday, 1)
quartile3 = WorksheetFunction.Quartile_Inc(totalsalesthatday, 3)
iqr = quartile3 - quartile1
upper = quartile3 + (iqr * 1.5)
lower = quartile1 - (iqr * 1.5)

所讨论的错误位于以下一行：quartile1 = WorksheetFunction.Quartile(totalsalesthatday, 1)

Answer 1

.Quartile函数参数是一个数组和一个双参数。尝试更改数据类型。