Worksheet_Change设置目标范围很慢
我有一个excel宏,用于在基于另一个SO问题here的“Worksheet_Change”函数中的Excel中管理按钮可见性。Worksheet_Change设置目标范围很慢
问题是,虽然宏工作,它使更新Excel工作表相当滞后。我已成功地缓慢牵制,一条线路:
Set rUpdated = Range(Target.Dependents.Address)
这将通过后面的脚本进行迭代更新为变量的单元格区域。如果我用这一行调用一个脚本,我发现这是所有延迟的地方。这似乎是一条相当简单的路线,但有没有更好的方法来做到这一点?
披露:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rUpdated As Range
Dim shp As Shape
Dim rCell As Range
Set rUpdated = Range(Target.Dependents.Address)
If Not rUpdated Is Nothing Then
For Each rCell In rUpdated
If rCell.Column = 1 Then
'Look at each shape in the sheet and cross-reference with rCell.
For Each shp In Target.Parent.Shapes
If shp.TopLeftCell.Row = rCell.Row Then
shp.Visible = (rCell.Value <> "")
Exit For 'Exit the loop - the correct button has been found.
End If
Next shp
End If
Next rCell
End If
End Sub
我替换下面一行(和同伴线)配置:
On Error Resume Next
ActiveSheet.Shapes("buttonRow" & Target.Row).Visible = (ActiveSheet.Cells(Target.Row, 1).Value <> "")
然而,为了得到这个工作,我首先需要重命名我的形状。我使用这个功能来做到这一点:
Function renamebuttons()
For Each shp In ActiveSheet.Shapes
shp.name = "buttonRow" & shp.TopLeftCell.Row
Next shp
End Function
我运行该功能一次,并删除它。一旦完成,我的形状现在可以通过名称来引用,并且不再导致在每个形状和每个目标相关的循环中延迟。工作表中经历的延迟现在是最小的。
所以,如果我理解正确的话,你想使一个按钮可见,如果该行作为细胞被改变。我唯一能想到的就是减慢速度,那就是检查很多rCell
或Shapes
。我不知道你的文档的结构是什么。所以我的想法是:我不会每次都浏览所有的形状,我会以一种模式命名它们,你可以用它们所在的行来标识它们,所以你使用名称来解决它们(即Row2
为行2
中的按钮)。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rUpdated As Range
Dim shp As Shape
Dim rCell As Range
Dim obj As OLEObject
Set rUpdated = Range(Target.Dependents.Address)
If Not rUpdated Is Nothing Then
For Each rCell In rUpdated
If rCell.Column = 1 Then
On Error Resume Next
Set obj = ActiveSheet.OLEObjects("Row" & rCell.Row)
If Err.Number = 0 Then
obj.Visible = (rCell.Value <> "")
End If
End If
Next rCell
End If
End Sub
通过所有rCells或Shapes不会导致缓慢。如果我将sub限制在单行“Set rUpdated = Range(Target.Dependents.Address)”中,那就是我得到所有缓慢的地方。我测试了你的代码,它运行的时间差不多。 – fileinster
当它的这一行,你应该在一个干净的'Workbook'中测试它只有几个参考,看看性能是否糟糕。还有什么东西在后面跑? – UGP
在只有单元格公式复制的干净工作簿中进行测试。只有VBA代码是worksheet_change宏中的这一行。无可否认,可能会有很多依赖项,因为公式中有很多对其他单元格的引用,但是我无法更改它,因为它会破坏工作表的功能和用途。例如,一个公式引用了8个单元,并且在单个单元更新中总共可以更新总共16个单元。没有VBA的性能问题。 – fileinster
你引用的单元格是否总是在同一行?如果是这样的话,编写一个自定义函数可能会更快,该函数将更改单元格的地址并检查当前行中的公式 – Zac
否,引用的单元格可能是电子表格的整个高度。不过,我只关心一行。我只需要一个比调用'Target.Dependents'更好的方法。 – fileinster