excel - Worksheet_Change 未触发

Question

我不确定为什么 worksheet_change 没有触发。尽管代码没有给出错误，但 Excel 工作表并没有改变。好像代码没有运行。

我有一系列相互依赖的函数，它们都由 worksheet_change 子调用。我确保在进行更改时禁用和启用事件以防止“堆栈空间不足”错误。

Function get_used()

Dim Header As Range
Dim Cell As Range
Dim Used As Double
Dim EndCol As Integer

EndCol = Sheets("Monthly Cash Balances").Range("L2").End(xlToRight).Column
Set Header = Range(Cells(2, 12), Cells(2, EndCol))

For Each Cell In Header
    If Cell.Value = "Actual" Then
    Used = Used + Cell.Offset(14, 0)
    End If
Next Cell

get_used = Used

End Function


Function get_rmonths(ByVal start_date As Date, ByVal end_date As Date)

Dim Header As Range
Dim Cell As Range
Dim RMonths As Integer
Dim StartCol As Integer
Dim EndCol As Integer

EndCol = Sheets("Monthly Cash Balances").Range("L3").End(xlToRight).Column
Set Header = Range(Cells(2, 12), Cells(2, EndCol))

For Each Cell In Header
    If Cell.Value <> "Actual" And Cell.Offset(1, 0).Value >= start_date And Cell.Offset(1, 0).Value <= end_date Then
        RMonths = RMonths + 1
    End If
Next Cell

get_rmonths = RMonths

End Function


Function SLdist(Amount As Double)

Dim Used As Double
Dim RAmount As Double
Dim DAmount As Double
Dim RMonths As Integer
Dim EndCol As Integer
Dim Header As Range
Dim Cell As Range

EndCol = Sheets("Monthly Cash Balances").Range("L3").End(xlToRight).Column
Set Header = Range(Cells(2, 12), Cells(, EndCol))

Used = get_used()
RAmount = Amount - Used
RMonths = get_rmonths(Sheets("Monthly Cash Balances").Range("F16").Value, Sheets("Monthly Cash Balances").Range("G16").Value)
DAmount = RAmount / RMonths

For Each Cell In Header
    If Cell.Value <> "Actual" And Cell.Offset(1, 0).Value >= start_date And Cell.Offset(1, 0).Value <= end_date Then
        Cell.Offset(14, 0).Value = RAmount
    End If
Next Cell

End Function


Private Sub Worksheet_Change(ByVal Target As Range)

Dim Amount As Double

Amount = Sheets("Monthly Cash Balances").Range("D16").Value

Application.EnableEvents = False

SLdist Amount

Application.EnableEvents = True

End Sub

标签： excelvba