Solved

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

Posted on 2016-10-26
6
70 Views
Last Modified: 2016-10-26
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
0
Comment
Question by:ExpExchHelp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 41860495
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
0
 

Author Comment

by:ExpExchHelp
ID: 41860512
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
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 41860530
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:ExpExchHelp
ID: 41860551
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
0
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 500 total points
ID: 41860562
As mentioned, yes, the Worksheet_Change() event code will be executed on every change to any cell in the same worksheet, but if the cell is not [B1] then the additional code statements will not execute.

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.

In your (existing) Visual Basic for Applications code that copies data between workbooks (/worksheets) you could try setting Application.EnableEvents initially to False, then return to True after the routine has concluded.

For example,

Application.EnableEvents = False
'
'
' Your existing routine to copy data between workbook/worksheets
'
'
Application.EnableEvents = True


If you have a local error handling routine, or an Exit label in your code, you will need to make sure that Application.EnableEvents is set to True before any exit or end from the subroutine/function.

Additionally, I would be tempted to change Rgonzo1971's code to this:

Private Sub Worksheet_Change(ByVal Target As Range)

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

End Sub

Open in new window


Finally, the code statement...

    Range("A2:A" & Rows.Count).RowHeight = 15

This will set the rowheight of every row in the worksheet every time cell [B1] is changed.

This is going to take some time if you have a worksheet (in MS-Excel 2007 onwards) with 1,048,576 rows!

Perhaps you should consider limiting this statement to just the number of used rows in the worksheet; not every row regardless of being used or not.
0
 

Author Comment

by:ExpExchHelp
ID: 41860629
[ 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
1

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question