工作表更改事件陷入无限循环-Java 学习之路

我想要以下代码执行以下操作：

_999_在我将值输入特定列后，计算一些内容并使用这些计算值分配单元格值

问题：

在我为另一个单元格分配值后，Worksheet.Change事件将再次触发，该单元格重新启动该子节点并导致无限循环

我如何更改我的代码，使其不会陷入无限循环，每当我向我正在使用的列添加值时，我想要更新的单元格将自动更新？

代码 - 我要添加值的列是C列：

Public Sub Worksheet_Change(ByVal Target As Range)
    Dim xlApp As Application
    Dim ws As Worksheet
    Dim r As Range
    Dim size As Long

    Set xlApp = Excel.Application
    Set ws = xlApp.ThisWorkbook.Worksheets("Sheet1")

    'get length of this individual column
    Set r = ws.Range(ws.Cells(2, 3), ws.Cells(ws.Rows.Count, 3).End(xlUp))
    'test to see if I got the correct column: i do
    r.Select
    size = r.Count

    With xlApp.WorksheetFunction
        'test to see if the offset I got was correct: it is
        r.Offset(0, 1).Select
        'get average of big range
        ws.Cells(3, 10).Value = .Average(r.Offset(0, 1)) ' <- here is where the change event will fire again
                                                         '    presumably because i just changed a value.
        'get standard deviation of big range's population
        ws.Cells(3, 11).Value = .StDev_P(r.Offset(0, 1))
        'test to see if the offset i got was correct: it is
        r.Offset(0, 2).Select
        ws.Cells(3, 12).Value = .Average(r.Offset(0, 2))
        ws.Cells(3, 13).Value = .StDev_P(r.Offset(0, 2))
    End With

    Set xlApp = Nothing
    Set ws = Nothing
    Set r = Nothing
End Sub

谢谢阅读！

1 回答

3
在对工作表进行任何更改之前禁用 Events . 每次更改都会[重新] - 激活宏并绕过你的系统，直到你的excel实例崩溃为止......
```
Application.EnableEvents = False
    'Changes go here
Application.EnableEvents = True
```
回复于 2024-04-30T01:32:47+08:00

工作表更改事件陷入无限循环

1 回答

相关问题