如何优化此代码？

debugcn 发表于 Dev

法伊桑·法鲁克（M Faizan Farooq）

每当单元格值（1,2）更改时，它将复制该值和单元格（10,19）的值并将其分别粘贴到A和B列中当单元格（1,2,3）的值时，My Excel VBA使Excel挂起）变化：

Dim a As Variant

Dim j As Integer
Dim b As Variant
Dim l As Integer




Private Sub Worksheet_Change(ByVal Target As Range)

If Cells(j + 3, 1).Value = Cells(j + 2, 1).Value Then
          j = j
          Else
          j = j + 1

        End If

  If l < j Then
b = Cells(10, 19).Value
 Cells(j + 1, 2).Value = b

End If
l = j
a = Cells(1, 2).Value
Cells(j + 3, 1).Value = a


End Sub

Private Sub Combobox1_Change()
 Cells(1, 2) = Combobox1.Value

End Sub

如何防止这种情况发生？

肖恩·C

首先，您的格式使其难以阅读。
其次，更改值时不会禁用事件，否则会导致最终的堆栈溢出或内存不足错误，这会降低excel的速度。

格式化和改进的代码：

Dim a 
Dim j As Integer
Dim b 
Dim l As Integer

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, "A:A") Is Nothing Then Exit Sub 'don't run unless change in column A

Application.EnableEvents = False 'stop executing this code until we are done
If Cells(j + 3, 1).Value = Cells(j + 2, 1).Value Then
    j = j
Else
    j = j + 1
End If

If l < j Then
    b = Cells(10, 19).Value
    Cells(j + 1, 2).Value = b
End If

l = j
a = Cells(1, 2).Value
Cells(j + 3, 1).Value = a
Application.EnableEvents = True
End Sub

Private Sub Combobox1_Change()
Cells(1, 2) = Combobox1.Value
End Sub

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。