excel - Worksheet_Change 目标范围太慢
问题描述
我有一个用于更改公式的 excel 宏。问题是虽然宏可以工作,但它使更新 Excel 工作表相当滞后。有什么建议吗?
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Columns.Count < Me.Columns.Count Then
If Target.Column = 4 Then
If Target.Row >= 49 And Target.Row <= 178 Then
Dim r As Integer
For r = 49 To 178
'AD = 30
Dim MatType As String
MatType = Cells(r, 4).Value
If MatType = "" Then
Cells(r, 30).Value = "0"
Else
MatType = LCase(MatType)
'Plechy
'Trubky
'Jine
If MatType = "pzs" Or MatType = "pzt" Or MatType = "Tahokov" Then
Cells(r, 30).Value = "=(I" & r & " * J" & r & "*L" & r & ") * 2/1000000"
ElseIf MatType = "jac" Or MatType = "jao" Or MatType = "tr" Or MatType = "u" Or MatType = "kr" Or MatType = "L" Or MatType = "op" Or MatType = "Trubky_spec" Then
Cells(r, 30).Value = "=(F" & r & "*I" & r & "*L" & r & ")/1000000"
Else
Cells(r, 30).Value = "0"
End If
End If
Next
End If
End If
End If
Application.EnableEvents = True
End Sub
解决方案
这只会循环那些改变:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo safeout
Application.EnableEvents = False
Dim rng As Range
Set rng = Intersect(Range("D49:D178"), Target)
If Not rng Is Nothing Then
Dim rngCell As Range
For Each rngCell In rng
Dim r As Long
r = rngCell.Row
'AD = 30
Dim MatType As String
MatType = LCase$(rngCell.Value)
'Plechy
'Trubky
'Jine
Select Case MatType
Case "pzs", "pzt", "Tahokov"
Cells(r, 30).Value = CDbl(Cells(r, "I")) * Cells(r, "J") * Cells(r, "L") * 2 / 1000000
Case "jac", "jao", "tr", "u", "kr", "L", "op", "Trubky_spec"
Cells(r, 30).Value = CDbl(Cells(r, "I")) * Cells(r, "F") * Cells(r, "L") / 1000000
Case Else
Cells(r, 30).Value = 0
End Select
Next
End If
safeout:
Application.EnableEvents = True
End Sub
推荐阅读
- java - 从父类扩展子类说没有默认构造函数?
- python - 为什么在将 bigquery 表数据写入谷歌云存储时访问被拒绝?
- javascript - 如何重置 webrtc 状态?
- javascript - 在成功的 sql 语句后尝试重定向到页面时出现白屏
- mysql - 如何将原始 SQL 转换为 Yii2,如查找查询
- python - 如何使用 flask-sqlalchemy 访问多个表?
- sql - 从内部连接中选择列
- iot - 物联网中的句法与语义互操作性
- java - 类型上的 CDI 抛出 NPE
- java - 如何解决 VScode UnsupportedClassVersionError?