excel - 为什么我的 Excel VBA 代码在执行 Range("K2:K" & LR).Calculate 函数时不断重启宏？

Question

我在 Excel VBA 中运行一个宏，当它尝试执行 Range("K2:K" & LR).Calculate 代码行时，我一直在重新启动宏的模块之一。我以前在宏中使用过 .calculate 函数，但从未发生过这种情况。我不确定为什么在这种情况下会一直发生这种情况。有人可以看看我的代码，看看你是否能找出这种奇怪的重启行为的原因？计算已在同一项目的先前模块中使用 Application.Calculation = xlCalculationManual 设置为手动。

Private Sub L2Leadership_Selection_Change()

Dim L2Name As String
Dim Vertical_Selection As String
Dim L2Leadership_Selection As String
Dim Dept As String

Vertical_Selection = Hiring_Validation_Form.Vertical_Selection.Value
L2Leadership_Selection = Hiring_Validation_Form.L2Leadership_Selection.Value

Workbooks(ToolName).Activate
Sheets(2).Select
Range("I2:K50").Select
Selection.Clear

Sheets("Sheet1").Activate
Columns("O:P").Clear
Range("N41:N100").Clear
Range("K2:L3").Clear
Range("K2").Select
ActiveCell.Value = L2Leadership_Selection
Selection.TextToColumns Destination:=Range("K2"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
    :="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("K3").Select
ActiveCell.FormulaR1C1 = "=TRIM(R[-1]C)"
Range("L3").Select
ActiveCell.FormulaR1C1 = "=TRIM(R[-1]C)"
L2Name = Cells(3, 11).Value
Dept = Cells(3, 12).Value

'Filter CC Mapping by Vertical & L2
Sheets("CC Mapping").Select
Range("D1").Select
On Error Resume Next
ActiveSheet.ShowAllData
ActiveSheet.Range("$A$1:$J$25683").AutoFilter Field:=4, Criteria1:=Vertical_Selection
ActiveSheet.Range("$A$1:$J$25683").AutoFilter Field:=5, Criteria1:=L2Name
ActiveSheet.Range("$A$1:$J$25683").AutoFilter Field:=7, Criteria1:="<>Inactive", Criteria2:="<>Non-CS"
On Error GoTo 0

'Copies Dept Name & CC results
ActiveSheet.Range("$A$2:$B$25683").SpecialCells(xlCellTypeVisible).Copy
Sheets(2).Select
Range("I2").Select
Selection.PasteSpecial Paste:=xlPasteValues

'Sets Definition for Department Name
LR = Cells(Rows.Count, 9).End(xlUp).Row
Range("K2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""CC ""&RC[-2]&"" - ""&RC[-1])"
Range("K2").Select
On Error Resume Next
Selection.AutoFill Destination:=Range("K2:K" & LR), Type:=xlFillDefault
Range("K2:K" & LR).Calculate   'This is the line that when stepping through line by line, it jumps back up to the Private Sub L2Leadership_Selection_Change() line and restarts the macro when this line is executed.
On Error GoTo 0

'ActiveSheet.Range("K2:K" & LR).Calculate
ActiveWorkbook.Names("DeptName").RefersToR1C1 = "=Data!R2C11:R20C11"


End Sub

我还尝试使用 Application.Calculation = xlCalculationAutomatic 将此特定模块设置为自动计算，但由于某些奇怪的原因，它通常不会在我在代码中指定的工作表之间切换。例如，当我希望它引用 Sheets("Sheet1").select 时，它不会从启动宏时当前为一个的工作表切换到工作表 ("Sheet1")，因此没有任何代码应该发生在该工作表上。

标签： excelvba