asked on

Excel -- "after update" (calling VBA function from single cell)

Experts:

In a previous question, expert Rgonzo1971 assisted me in writing a VBA function (see below).

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Not Intersect(Target, Range("A1")) Is Nothing Then
    Range("A2:A" & Rows.Count).RowHeight = 15
    If Target <> "" Then
        aRows = Split(Target, ",")
        For Each rw In aRows
            Rows(rw).AutoFit
        Next
    End If
End If
Application.ScreenUpdating = True
End Sub

Open in new window

While the VBA works great, it appears it "slows down" performance on the spreadsheet. That is, any change on the XLS results in running the VBA.

Instead, I need to run the VBA only when a single cell (i.e., B1) is changed. How can the code be rewritten so that only a change in cell B1 calls the VBA... but not any other change in the spreadsheet?

Thank you,
EEH

Rgonzo1971

then try

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Not Intersect(Target, Range("B1")) Is Nothing Then
    Range("A2:A" & Rows.Count).RowHeight = 15
    If Target <> "" Then
        aRows = Split(Target, ",")
        For Each rw In aRows
            Rows(rw).AutoFit
        Next
    End If
End If
Application.ScreenUpdating = True
End Sub

Open in new window

Regards

ExpExchHelp

ASKER

Rgonzo1971:

Thanks for the response... actually, I made that change (from A1 to B1) in the XLS already. I ended up copying the VBA code from the previous question (vs. the XLS). My bad...

Anyhow, the XLS performance appears to be much slower with the code. Right now, I'm transferring data from an old spreadsheet into the new XLS (with VBA). Any copying/pasting job into cells below row #2 seem to take a long time.

Thus, I was under the impression that any change in any other cell also calls the VBA. Is that not the case?

EEH

Martin Liss

You say that "any change on the XLS results in running the VBA" and you are right, because any change always executes the Worksheet_Change event. However line 3 in the code determines where the change was made and if it's not "B1" then nothing else happens.

Does it slow things down if a change is made to some other cell? If so then the slowdown is due to some other process.

ExpExchHelp

ASKER

On the same computer, I have two spreadsheets open (I display them on dual monitors).

- Any operation (i.e., scrolling up/down, placing cursor into a cell, etc.) on the old spreadsheet (without the VBA) appears to work at a normal speed.

- Any operation (again, scrolling and cursor placements) in the new spreadsheet -- the one with the VBA -- takes noticeably longer. For example, when scrolling up, on the XLS, there's seems to be a delay and it looks "jumpy".

So, while the VBA is called only when I enter new row values in cell B1, overall operation of the XLS is noticeably slower. Given that spreadsheets are on the same computer, I can only think of the VBA having some overall impact.

Is there any way the VBA could be rewritten so that the VBA is not called [attempted to be called (for lack of better terms)] unless B1 changes?

Thanks,
EEH

ASKER CERTIFIED SOLUTION

[ fanpages ]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ExpExchHelp

ASKER

[ fanpages ]

Thanks for chiming in... I appreciate it.

While I cannot accurately measure changes in performance on the spreadsheet, I believe that the new XLS (with modified) VBA works slightly better now.

Also, as suggested, I have limited the number of rows to A500.

Again, I thank everyone for the contributions... 'much appreciated.

EEH

Excel -- &quot;after update&quot; (calling VBA function from single cell)

Excel -- "after update" (calling VBA function from single cell)