[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 503
  • Last Modified:

Excel 2010 always does "updating cells" before it runs my macro. How can I turn that off?

I have a spreadsheet with about 6000 rows, 200 columns and a lot of buttons with associated macros.
Recently it started to do "updating cells" before it executes a macro.  This takes 15-20 seconds.  How can I turn that functionality off?  Thanks
0
Dwight Baer
Asked:
Dwight Baer
  • 5
2 Solutions
 
Dwight BaerAuthor Commented:
Here is the file.

To replicate:  Go to the "Scriptures" worksheet.  Then click on any of the buttons at the top of the columns, e.g. A, B, C, D etc.

The macro is designed to sort the spreadsheet by that column.  It used to work just fine, but now it takes 15-20 seconds while it does "updating cells".
Scripture-140824-trying-to-fix-the-updat
0
 
RayData AnalystCommented:
Dwight,
Maybe I'm slow today, but the download is not an XLS or XLSM file.  Can you please provide the Excel file?

Otherwise, what are the chances that calculation has been set to manual (purposefully or not).  It would do a calculation prior to the VBA running.  If you need direction on checking/changing that setting try the link below (don't want to re-invent anything).
http://www.howtogeek.com/162219/how-to-change-the-automatic-calculation-and-multi-threading-features-in-excel-2013/
0
 
Dwight BaerAuthor Commented:
I think the file name was truncated when I uploaded it previously.

It had been set to "Automatic" calculation.  I tried changing it to "manual" just for fun;  it didn't help.

Here's the file uploaded with a shorter name.
Scripture-140826.xlsm
0
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!

 
Dwight BaerAuthor Commented:
Any luck downloading that file?  Can you replicate the "updating cells" problem?
0
 
Zack BarresseCEOCommented:
The code in your workbook is highly inefficient. I don't know who coded that, but I bet they pulled half their scalp out writing that code. I may have cried if I had to do that. There is a ton of redundant code, most of which can be utilized in a single procedure call.

For your specific question, take for example the code behind the button in column A:
Public Sub ButtonA_Click()
    Rows("4:4").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Sort Key1:=Range("A4"), Order1:=xlAscending, Header:=xlNo, _
                   OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                   DataOption1:=xlSortNormal
    Range("A4").Select
End Sub

Open in new window


This is very badly written code. Probably pieced together from the macro recorder, and I'm sure it worked once upon a time well. This is a good example of not needing to select anything, and why the Last Cell in Excel can lead you astray sometimes. Since you're using 2010 (anything 2007 and beyond), you can utilize the worksheets Sort method, as opposed to the Range objects Sort method (which the above code is using).

I would do two things to the code. 1) Utilize the worksheets Sort method instead, which tends to be a bit faster. 2) Use a function to determine the last row/column of the sheet.

Here is the code I changed it to, which worked much faster, although it still takes a few seconds for Excel to work:
Public Sub ButtonA_Click()
    Me.Sort.SortFields.Clear
    Me.Sort.SortFields.Add _
            Key:=Me.Range("A4:A" & LastRow(Me)), _
            SortOn:=xlSortOnValues, _
            Order:=xlAscending, _
            DataOption:=xlSortNormal
    Me.Sort.SetRange Range("A4", Me.Cells(LastRow(Me), LastColumn(Me)))
    Me.Sort.Header = xlNo
    Me.Sort.MatchCase = False
    Me.Sort.Orientation = xlTopToBottom
    Me.Sort.SortMethod = xlPinYin
    Me.Sort.Apply
End Sub

Open in new window

This uses two functions, which can be placed in any standard module:
Public Function LastColumn(ByVal Sheet As Worksheet) As Long
    If WorksheetFunction.CountA(Sheet.Cells) = 0 Then Exit Function
    LastColumn = Sheet.Cells.Find( _
                 What:="*", _
                 After:=Sheet.Cells(1, 1), _
                 LookIn:=xlFormulas, _
                 LookAt:=xlPart, _
                 SearchOrder:=xlByColumns, _
                 SearchDirection:=xlPrevious _
                                  ).Column
End Function

Public Function LastRow(ByVal Sheet As Worksheet) As Long
    If WorksheetFunction.CountA(Sheet.Cells) = 0 Then Exit Function
    LastRow = Sheet.Cells.Find( _
              What:="*", _
              After:=Sheet.Cells(1, 1), _
              LookIn:=xlFormulas, _
              LookAt:=xlPart, _
              SearchOrder:=xlByRows, _
              SearchDirection:=xlPrevious _
                               ).Row
End Function

Open in new window


There are a number of changes I would recommend for this workbook, but I'd start with recommending you not use ActiveX controls. They're a little more complex to work with, plus they won't work on a Mac machine. The buttons look to merely serve as a way to sort the data anyway, so they're basically all using the same code by principle, just using a different Key for the Sort method.

However, because I wouldn't want to manage all of those buttons, I would change how I initiated the sort in the first place. Since you have Freeze Panes turned on, you can just have a single button in the frozen space in the top-left. I'd use the active cell as the parameter, so it would automatically sort by whatever column the active cell was in. One procedure. One button. 100x less buttons to maintain. 100x less procedures to maintain. 1000x less hassle.

The single routine would be this:
Public Sub SortScriptures()

    Dim Scriptures As Worksheet
    Dim SortKey As Range
    Dim MyLastRow As Long
    Dim MyLastColumn As Long

    Set Scriptures = ThisWorkbook.Worksheets("Scriptures")
    If ActiveSheet.Name <> Scriptures.Name Then Exit Sub
    MyLastRow = LastRow(Scriptures)
    MyLastColumn = LastColumn(Scriptures)
    Set SortKey = Intersect(ActiveCell.EntireColumn, Scriptures.Rows(4)).Resize(MyLastRow - ActiveCell.Row - 1)

    Scriptures.Sort.SortFields.Clear
    Scriptures.Sort.SortFields.Add _
            Key:=SortKey, _
            SortOn:=xlSortOnValues, _
            Order:=xlAscending, _
            DataOption:=xlSortNormal
    Scriptures.Sort.SetRange Scriptures.Range("A4", Scriptures.Cells(MyLastRow, MyLastColumn))
    Scriptures.Sort.Header = xlNo
    Scriptures.Sort.MatchCase = False
    Scriptures.Sort.Orientation = xlTopToBottom
    Scriptures.Sort.SortMethod = xlPinYin
    Scriptures.Sort.Apply

End Sub

Open in new window


The code needs the two functions above. Assign that to a button, then it'll sort the sheet by whatever column the active cell is in. The thing to remember about your data is you have 1,622,733 cells in the range needing to be sorted, a lot of it with unique values. It's going to take a little processing time to get all that data sorted perfectly every time you click the button.

If you want to suppress the message in the status bar, you can use a routine like this:
Sub TOGGLEEVENTS(blnState As Boolean)
    'Originally written by Zack Barresse
    Application.DisplayAlerts = blnState
    Application.EnableEvents = blnState
    Application.ScreenUpdating = blnState
    If blnState Then Application.CutCopyMode = False
    If blnState Then Application.StatusBar = False
End Sub

Open in new window


Then you implement it in the code something like this:
Public Sub SortScriptures()

    Dim Scriptures As Worksheet
    Dim SortKey As Range
    Dim MyLastRow As Long
    Dim MyLastColumn As Long

    Set Scriptures = ThisWorkbook.Worksheets("Scriptures")
    If ActiveSheet.Name <> Scriptures.Name Then Exit Sub
    MyLastRow = LastRow(Scriptures)
    MyLastColumn = LastColumn(Scriptures)
    Set SortKey = Intersect(ActiveCell.EntireColumn, Scriptures.Rows(4)).Resize(MyLastRow - ActiveCell.Row - 1)

    Call TOGGLEEVENTS(False)
    Scriptures.Sort.SortFields.Clear
    Scriptures.Sort.SortFields.Add _
            Key:=SortKey, _
            SortOn:=xlSortOnValues, _
            Order:=xlAscending, _
            DataOption:=xlSortNormal
    Scriptures.Sort.SetRange Scriptures.Range("A4", Scriptures.Cells(MyLastRow, MyLastColumn))
    Scriptures.Sort.Header = xlNo
    Scriptures.Sort.MatchCase = False
    Scriptures.Sort.Orientation = xlTopToBottom
    Scriptures.Sort.SortMethod = xlPinYin
    Scriptures.Sort.Apply
    Call TOGGLEEVENTS(True)
    
End Sub

Open in new window


Sorting any column with the above code takes anywhere between 1-8 seconds for me. Honestly I don't think you're going to get much faster than that with a data set like this.

HTH

Regards,
Zack Barresse
0
 
Dwight BaerAuthor Commented:
Thanks Zack.

Just got back from vacation.

Since this has been open a long time, and your answer seems very thorough, I'll close this question without studying it carefully for now.  I'll look at it tomorrow in detail.

I really appreciate your help.
0
 
Dwight BaerAuthor Commented:
Thanks!
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now