Excel自定义公式未按预期工作

Question

我基本上对创建自定义excel公式很陌生。我有以下自定义excel公式：

Function Calculate(Quarter As String) As Variant
Dim wFn As WorksheetFunction
Dim Rng As Range
Calculate = "Error - Quarter should be Q1, Q2, Q3 or Q4"
Set wFn = Application.WorksheetFunction

Select Case Quarter
Case "Q1":
'Rng = "K" & RowNumber: "P" & RowNumber'
Rng = K77: P77
Calculate = wFn.Sum(Rng)
Case "Q2":
'Rng = "R" & RowNumber: "X" & RowNumber'
Rng = R77: X77
Calculate = wFn.Sum(Rng)
Case "Q3":
'Rng = "Z" & RowNumber: "AE" & RowNumber'
Rng = Z77: AE77
Calculate = wFn.Sum(Rng)
Case "Q4":
'Rng = "AG" & RowNumber: "AM" & RowNumber'
Rng = AG77: AM77
Calculate = wFn.Sum(Rng)

End Select
End Function

但是，当我尝试在工作表的单元格上使用此公式时，输出为0。我试图参考这个SO后更改公式，基于下拉框中的选择。

有人能帮忙调试一下吗？

Answer 1

您没有正确引用单元格。范围K77:P77是一个对象，因此必须是Set
正如您所写的，我认为(如果没有Option Explicit(它会将K77视为变量，将P77视为未定义的函数(至少这是我遇到的错误(。

该函数假定输入函数时范围在同一张表上。

Function Calculate(Quarter As String) As Variant
Dim wFn As WorksheetFunction
Dim Rng As Range

Set wFn = Application.WorksheetFunction

Select Case Quarter
Case "Q1":
'Rng = "K" & RowNumber: "P" & RowNumber'
Set Rng = Range("K77:P77")
Calculate = wFn.Sum(Rng)
Case "Q2":
'Rng = "R" & RowNumber: "X" & RowNumber'
Set Rng = Range("R77:X77")
Calculate = wFn.Sum(Rng)
Case "Q3":
'Rng = "Z" & RowNumber: "AE" & RowNumber'
Set Rng = Range("Z77:AE77")
Calculate = wFn.Sum(Rng)
Case "Q4":
'Rng = "AG" & RowNumber: "AM" & RowNumber'
Set Rng = Range("AG77: AM77")
Calculate = wFn.Sum(Rng)
Case Else
'Returning errors from functions:
'http://www.cpearson.com/excel/writingfunctionsinvba.aspx
Calculate = CVErr(xlErrNA)

End Select
End Function