Worksheet_Change setting target range is slow

Question

I have an excel macro used to manage button visibility in Excel in the "Worksheet_Change" function based from another SO question here.

The problem is the although the macro works it makes updating the Excel sheet rather laggy. I have managed to pin down the slowness to a single line:

Set rUpdated = Range(Target.Dependents.Address)

This sets the range of cells updated to a variable to be iterated through later in the script. If I call a script with just this line I found this is where all the delay is. It seems a rather simple line, but is there a better way to do it?

Full disclosure:

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

Will your referenced cells always be in the same row? If so, it might be quicker to write a custom function which takes address of changed cell and checks the formulas in current row — Zac, Aug 02 '17 at 09:50
No, the referenced cells could be the full height of the spreadsheet. However I only really care about a single row. I just need a better way than calling `Target.Dependents` — fileinsert, Aug 08 '17 at 07:43

score 0 · Answer 1 · answered Aug 02 '17 at 10:12

0

So if i understood it correctly you want to make a button visible if the cell in the row as been changed. The only things i can think of to slow it down are, that is has to check many rCell or Shapes. I dont know what the structure of your document is. So my Idea would be: instead of going through all shapes every time, i would name them in a pattern that you can identify them with the row they are in so you use the name to address them (i.e Row2 for the Button in Row 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

answered Aug 02 '17 at 10:12

Plagon

2,689
1
11
23

Going through all rCells or Shapes doesn't appear to cause slowness. If I limited the sub to the single line `Set rUpdated = Range(Target.Dependents.Address)` that is where I get all the slowness. I tested your code and it ran with similar lag. – fileinsert Aug 08 '17 at 07:43
When its this line, you should test it in a clean `Workbook` with only a few reference and see if the performance is stil bad. Is there anything else running in the back? – Plagon Aug 08 '17 at 07:54
Tested in a clean workbook with only the cell formulas copied across. Only VBA code is this single line in the worksheet_change macro. Admittedly there could be a lot of dependents as there are a lot of references to other cells in the formulas, but I can't change this as it would break the functionality and purpose of the sheet. For example one formula references 8 cells, and there can be up to 16 cells in total updated on a single cell update. No performance issue with no VBA. – fileinsert Aug 08 '17 at 12:37

fileinsert · Accepted Answer · 2017-08-31T10:05:07.307

I replaced that config with the following single line (and companion line):

On Error Resume Next
ActiveSheet.Shapes("buttonRow" & Target.Row).Visible = (ActiveSheet.Cells(Target.Row, 1).Value <> "")

However to get this to work I first needed to rename all my shapes. I used this function to do that:

Function renamebuttons()
    For Each shp In ActiveSheet.Shapes
        shp.name = "buttonRow" & shp.TopLeftCell.Row
    Next shp
End Function

I ran that function once and deleted it. Once done my shapes can now be referred to by name and I no longer incur the delay of cycling through every shape and every target dependent. The delay experienced in the worksheet is now minimal.

Worksheet_Change setting target range is slow

2 Answers2