Solved

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

Posted on 2016-10-26
6
92 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 52

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 48

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Independent Software Vendors: 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!

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

627 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