vba - 调用使用函数的子例程的按钮出现问题

Question

我目前正在使用 VBA 进行编码并遇到了问题。

我创建了一个调用多个子例程的按钮。它运行完美，直到它到达一个使用函数的子程序。当我在开发人员选项卡中运行子程序时，它运行良好；但是，当我使用按钮调用子例程时，出现此错误：

“编译错误：找不到方法或数据成员。”

你知道如何解决这个问题吗？

下面是正在使用的子程序和函数：

Sub DiscountRate()

    'Set Dimensions
    Dim ws As Worksheet
    Dim i As Integer
    Dim Counter As Integer
    Dim Value As Integer
    Dim tempcount As Double

    'Set parameters
    Set SummarySheet = Sheets("Summary & Inputs")
    Set ExpectedCompSheet = Sheets("Expected Compensation")
    Set WorkYears = SummarySheet.Range("C40")
    Set DiscountSheet = Sheets("Treasury Rates")
    Set DiscountRateRange = DiscountSheet.Range("B7:L7")

    'Clear column
    ExpectedCompSheet.Range("H13:I113").Clear
    'Clear color
    ExpectedCompSheet.Range("H13:I113").Interior.Color = xlNone


    'If prejudgment interest is filled, count and fill discount rate cells
    i = 1
    For Counter = 1 To WorkYears
        If IsEmpty(ExpectedCompSheet.Cells(12 + Counter, 7)) = False Then
            ExpectedCompSheet.Cells(12 + Counter, 8).Interior.ColorIndex = 16
            ExpectedCompSheet.Cells(12 + Counter, 9).Interior.ColorIndex = 16
            i = i + 1
        End If
    Next Counter

    'Create increasing range to sum fractions of years
    IncreasingRange = ExpectedCompSheet.Range(ExpectedCompSheet.Cells(12 + i, 5), _
                                              ExpectedCompSheet.Cells(11 + i + Counter, 5))

    'Find discount rate associated with increasing years
    For Counter = 1 To (WorkYears + 2 - i)
        IncreasingRange = ExpectedCompSheet.Range(ExpectedCompSheet.Cells(12 + i, 5), _
                                                  ExpectedCompSheet.Cells(11 + i + Counter, 5))
        tempcount = Application.WorksheetFunction.Sum(IncreasingRange)
        ExpectedCompSheet.Cells(11 + i + Counter, 8) = getclosest(DiscountRateRange, tempcount)
    Next Counter


    For Counter = 1 To (WorkYears + 2 - i)
        If ExpectedCompSheet.Cells(11 + i + Counter, 8) = 0.08 Then
            ExpectedCompSheet.Cells(11 + i + Counter, 8) = "1 Month"
            ExpectedCompSheet.Cells(11 + i + Counter, 9) = DiscountSheet.Range("B9") / 100
        ElseIf ExpectedCompSheet.Cells(11 + i + Counter, 8) = 0.25 Then
            ExpectedCompSheet.Cells(11 + i + Counter, 8) = "3 Month"
            ExpectedCompSheet.Cells(11 + i + Counter, 9) = DiscountSheet.Range("C9") / 100
        ElseIf ExpectedCompSheet.Cells(11 + i + Counter, 8) = 0.5 Then
            ExpectedCompSheet.Cells(11 + i + Counter, 8) = "6 Month"
            ExpectedCompSheet.Cells(11 + i + Counter, 9) = DiscountSheet.Range("D9") / 100
        ElseIf ExpectedCompSheet.Cells(11 + i + Counter, 8) = 1 Then
            ExpectedCompSheet.Cells(11 + i + Counter, 8) = "1 Year"
            ExpectedCompSheet.Cells(11 + i + Counter, 9) = DiscountSheet.Range("E9") / 100
        ElseIf ExpectedCompSheet.Cells(11 + i + Counter, 8) = 2 Then
            ExpectedCompSheet.Cells(11 + i + Counter, 8) = "2 Year"
            ExpectedCompSheet.Cells(11 + i + Counter, 9) = DiscountSheet.Range("F9") / 100
        ElseIf ExpectedCompSheet.Cells(11 + i + Counter, 8) = 3 Then
            ExpectedCompSheet.Cells(11 + i + Counter, 8) = "3 Year"
            ExpectedCompSheet.Cells(11 + i + Counter, 9) = DiscountSheet.Range("G9") / 100
        ElseIf ExpectedCompSheet.Cells(11 + i + Counter, 8) = 5 Then
            ExpectedCompSheet.Cells(11 + i + Counter, 8) = "5 Year"
            ExpectedCompSheet.Cells(11 + i + Counter, 9) = DiscountSheet.Range("H9") / 100
        ElseIf ExpectedCompSheet.Cells(11 + i + Counter, 8) = 7 Then
            ExpectedCompSheet.Cells(11 + i + Counter, 8) = "7 Year"
            ExpectedCompSheet.Cells(11 + i + Counter, 9) = DiscountSheet.Range("I9") / 100
        ElseIf ExpectedCompSheet.Cells(11 + i + Counter, 8) = 10 Then
            ExpectedCompSheet.Cells(11 + i + Counter, 8) = "10 Year"
            ExpectedCompSheet.Cells(11 + i + Counter, 9) = DiscountSheet.Range("J9") / 100
        ElseIf ExpectedCompSheet.Cells(11 + i + Counter, 8) = 20 Then
            ExpectedCompSheet.Cells(11 + i + Counter, 8) = "20 Year"
            ExpectedCompSheet.Cells(11 + i + Counter, 9) = DiscountSheet.Range("K9") / 100
        ElseIf ExpectedCompSheet.Cells(11 + i + Counter, 8) = 30 Then
            ExpectedCompSheet.Cells(11 + i + Counter, 8) = "30 Year"
            ExpectedCompSheet.Cells(11 + i + Counter, 9) = DiscountSheet.Range("L9") / 100
        End If
    Next Counter

End Sub

Function getclosest(ByVal rng As Range, tgt As Double) As Double
    t = WorksheetFunction.Max(rng)
    For Each r In rng
        u = Abs(r - tgt)
        If u < t Then
            t = u
            getclosest = r
        End If
    Next
End Function

我正在使用带有代码的按钮：

Sub CalculateExpectedComp()

Call Module1.ExpectedCompYear
Call Module1.ExpectedCompPeriod
Call Module1.ExpectedCompAge
Call Module1.ExpectedCompPaymentAnnual
Call Module1.ExpectedCompYearFrac
Call Module1.ExpectedCompPartialYearComp
Call Module1.PrejudgmentInterest
Call Module1.DiscountRate
Call Module1.PresentValueFactor
Call Module1.PV
Call Module1.CumulativeValue
Call Module1.Formatting

End Sub

运行“调用 Module1.DiscountRate”时会出现此问题。

标签： vbaexcel