Solved

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

Posted on 2016-10-26
6
31 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
6 Comments
 
LVL 48

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 45

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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now