vba - 在宏中使用工作表函数

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函数参数是一个数组和一个 double 。尝试更改您的数据类型。