Why is Excel Sometimes Slow To Respond After Macro Execution

[jeremyjohnolson]

In general, what types of things when executed in VBA code can cause problems with Excel after execution of the code? I am just wondering if there is like a best practices write up out there somewhere, or just pointers you all could give me. I have had issues in Excel 2013 with several different macros that after execution Excel does not seem to work right anymore until I close and then re-open it. It acts almost like it is frozen, but windows still shows it as responding (i.e. it doesn't say "not responding" in task manager) mostly it just responds very slowly. I noticed if I hover over an option on the ribbon bar it may take a second or two before it recognizes that my mouse is there and highlights the option. If I click the option before it is highlighted then Excel doesn't recognize that I did anything...sorry I know this is kind of a vague question but it is because I don't really know where to even start. Just wondering if these vague symtoms might sound familiar to someone with experience that may be able to get more going in the right direction to trouble shoot this because I am at a loss. Thanks! I can post the specific code below, but like I said, I have had this happen with other macros as well, not just this one. Oh, and one last thing, it doesn't do it every time...the problem is intermittent and unpredictable as far as I can tell. Also, I am new to VBA and computer programming in general (I am a CPA by trade, not a programmer) so you will not offend me if you point out very basic concepts that seem obvious (I probably don't know them)...thanks!

Option Explicit

Sub GenerateTimeSheet()
    If ActiveSheet.CodeName <> "Sheet1" And ActiveSheet.CodeName <> "Sheet2" And ActiveSheet.CodeName <> "Sheet3" Then


'TURN OFF SETTINGS AND UNPROTECT
        With Application
            .ScreenUpdating = False
            '.Calculation = xlCalculationManual
        End With
        ActiveWorkbook.Unprotect
        ActiveSheet.Unprotect
        
'INSERT COLUMNS TO PASTE RECAP OF HOURS TEMPORARILY
        Range(Range("UniqueIdentifierTimeSheet").Offset(0, 2), Range("UniqueIdentifierTimeSheet").Offset(0, 11)) _
            .EntireColumn.Insert Shift:=xlToRight
    
'DEFINE TEMPORARY COLUMNS CREATED ABOVE AS RANGE TO DELETE AFTER TIMESHEET IS CREATED
        Dim ColumnsToDelete As Range
        Set ColumnsToDelete = Range(Range("UniqueIdentifierTimeSheet").Offset(0, 2), _
            Range("UniqueIdentifierTimeSheet").Offset(0, 11)).EntireColumn


'MOVE RECAP OF HOURS OUT OF THE WAY IN ORDER TO COPY AND PASTE TIME DATA
        Range("RecapOfHours").Cut Destination:=Range(Range("UniqueIdentifierTimeSheet").Offset(1, 2), _
            Range("UniqueIdentifierTimeSheet").Offset(10, 2))
        
'CLEAR CONTENTS
        Range(Range("UniqueIdentifierTimeSheet").Offset(1, -11), Range("UniqueIdentifierTimeSheet") _
        .Offset(1, -1)).ClearContents


'GENERATE FORMULA IN FIRST ROW OF TIMESHEET CELLS - DAILY TIMES
        'DEFINE VARIABLES
        Dim UniqueIdentifier_MonColumn As Long
        Dim UniqueIdentifier_TueColumn As Long
        Dim UniqueIdentifier_WedColumn As Long
        Dim UniqueIdentifier_ThuColumn As Long
        Dim UniqueIdentifier_FriColumn As Long
        Dim UniqueIdentifier_SatColumn As Long
        Dim Calc_MonColumn As Long
        Dim Calc_TueColumn As Long
        Dim Calc_WedColumn As Long
        Dim Calc_ThuColumn As Long
        Dim Calc_FriColumn As Long
        Dim Calc_SatColumn As Long
        UniqueIdentifier_MonColumn = Range("UniqueIdentifier_Mon").Column
        UniqueIdentifier_TueColumn = Range("UniqueIdentifier_Tue").Column
        UniqueIdentifier_WedColumn = Range("UniqueIdentifier_Wed").Column
        UniqueIdentifier_ThuColumn = Range("UniqueIdentifier_Thu").Column
        UniqueIdentifier_FriColumn = Range("UniqueIdentifier_Fri").Column
        UniqueIdentifier_SatColumn = Range("UniqueIdentifier_Sat").Column
        Calc_MonColumn = Range("Calc_Mon").Column
        Calc_TueColumn = Range("Calc_Tue").Column
        Calc_WedColumn = Range("Calc_Wed").Column
        Calc_ThuColumn = Range("Calc_Thu").Column
        Calc_FriColumn = Range("Calc_Fri").Column
        Calc_SatColumn = Range("Calc_Sat").Column


        'MONDAY
        Range("UniqueIdentifierTimeSheet").Offset(1, -8).FormulaR1C1 = _
        "=IF(SUMIF(C" & UniqueIdentifier_MonColumn & ",RC[8],C" & Calc_MonColumn & ")=0," _
        & """"",SUMIF(C" & UniqueIdentifier_MonColumn & ",RC[8],C" & Calc_MonColumn & "))"
        
        'TUESDAY
        Range("UniqueIdentifierTimeSheet").Offset(1, -7).FormulaR1C1 = _
        "=IF(SUMIF(C" & UniqueIdentifier_TueColumn & ",RC[7],C" & Calc_TueColumn & ")=0," _
        & """"",SUMIF(C" & UniqueIdentifier_TueColumn & ",RC[7],C" & Calc_TueColumn & "))"
        
        'WEDNESDAY
        Range("UniqueIdentifierTimeSheet").Offset(1, -6).FormulaR1C1 = _
        "=IF(SUMIF(C" & UniqueIdentifier_WedColumn & ",RC[6],C" & Calc_WedColumn & ")=0," _
        & """"",SUMIF(C" & UniqueIdentifier_WedColumn & ",RC[6],C" & Calc_WedColumn & "))"
        
        'THURSDAY
        Range("UniqueIdentifierTimeSheet").Offset(1, -5).FormulaR1C1 = _
        "=IF(SUMIF(C" & UniqueIdentifier_ThuColumn & ",RC[5],C" & Calc_ThuColumn & ")=0," _
        & """"",SUMIF(C" & UniqueIdentifier_ThuColumn & ",RC[5],C" & Calc_ThuColumn & "))"
        
        'FRIDAY
        Range("UniqueIdentifierTimeSheet").Offset(1, -4).FormulaR1C1 = _
        "=IF(SUMIF(C" & UniqueIdentifier_FriColumn & ",RC[4],C" & Calc_FriColumn & ")=0," _
        & """"",SUMIF(C" & UniqueIdentifier_FriColumn & ",RC[4],C" & Calc_FriColumn & "))"
        
        'SATURDAY
        Range("UniqueIdentifierTimeSheet").Offset(1, -3).FormulaR1C1 = _
        "=IF(SUMIF(C" & UniqueIdentifier_SatColumn & ",RC[3],C" & Calc_SatColumn & ")=0," _
        & """"",SUMIF(C" & UniqueIdentifier_SatColumn & ",RC[3],C" & Calc_SatColumn & "))"
        
'GENERATE FORMULA IN FIRST ROW OF TIMESHEET CELLS - TOTAL
        Range("UniqueIdentifierTimeSheet").Offset(1, -2).FormulaR1C1 = _
            "=SUM(RC[-6]:RC[-1])"


'GET NUMBER OF ROWS BY WHICH TO OFFSET EACH COPY AND PASTE BELOW
        Dim OffSetRows As Long
        OffSetRows = Range("TimeEntries_Mon").Rows.Count


'COPY AND PASTE TIME DATA TO TIMESHEET - CLIENT NO., NAME, & S.C.
        Range("TimeEntries_Mon").Copy Range("UniqueIdentifierTimeSheet").Offset(1, -11)
        Range("TimeEntries_Tue").Copy Range("UniqueIdentifierTimeSheet").Offset(OffSetRows + 1, -11)
        Range("TimeEntries_Wed").Copy Range("UniqueIdentifierTimeSheet").Offset(OffSetRows * 2 + 1, -11)
        Range("TimeEntries_Thu").Copy Range("UniqueIdentifierTimeSheet").Offset(OffSetRows * 3 + 1, -11)
        Range("TimeEntries_Fri").Copy Range("UniqueIdentifierTimeSheet").Offset(OffSetRows * 4 + 1, -11)
        Range("TimeEntries_Sat").Copy Range("UniqueIdentifierTimeSheet").Offset(OffSetRows * 5 + 1, -11)


'COPY AND PASTE TIME DATA TO TIMESHEET - SHORT DESCRIPTION
        Range("DesMon").Copy Range("UniqueIdentifierTimeSheet").Offset(1, -1)
        Range("DesTue").Copy Range("UniqueIdentifierTimeSheet").Offset(OffSetRows + 1, -1)
        Range("DesWed").Copy Range("UniqueIdentifierTimeSheet").Offset(OffSetRows * 2 + 1, -1)
        Range("DesThu").Copy Range("UniqueIdentifierTimeSheet").Offset(OffSetRows * 3 + 1, -1)
        Range("DesFri").Copy Range("UniqueIdentifierTimeSheet").Offset(OffSetRows * 4 + 1, -1)
        Range("DesSat").Copy Range("UniqueIdentifierTimeSheet").Offset(OffSetRows * 5 + 1, -1)


'DEFINE RANGE TO USE BELOW
    Dim UniqueIdentifier_Last As Range
    Set UniqueIdentifier_Last = Range("UniqueIdentifierTimeSheet").Offset(OffSetRows * 6, 0)




'COPY AND PASTE RANGES CONTAINING FORMULAS DOWN TO REST OF CELLS FOR TIMESHEET CREATION
        'COPY & PASTE UNIQUE IDENTIFIER FORMULA
        Range("UniqueIdentifierTimeSheet").Offset(1, 0).Copy Range(Range("UniqueIdentifierTimeSheet") _
            .Offset(2, 0), UniqueIdentifier_Last)


        'COPY & PASTE WEEKLY TIME ENTRY FORMULAS
        Range(Range("UniqueIdentifierTimeSheet").Offset(1, -8), Range("UniqueIdentifierTimeSheet") _
            .Offset(1, -2)).Copy Range(Range("UniqueIdentifierTimeSheet").Offset(2, -8), _
            UniqueIdentifier_Last.Offset(0, -2))


'REMOVE DUPLICATES OF NEWLY COPIED TIME ENTRIES
        Range(Range("UniqueIdentifierTimeSheet").Offset(0, -11), UniqueIdentifier_Last.Offset(1, 0)) _
        .RemoveDuplicates Columns:=12, Header:=xlYes


'SORT TIMESHEET
        ActiveSheet.Sort.SortFields.Clear
        ActiveSheet.Sort.SortFields.Add Key _
            :=Range(Range("UniqueIdentifierTimeSheet").Offset(1, -11), UniqueIdentifier_Last.Offset(0, -11)), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        ActiveSheet.Sort.SortFields.Add Key:=Range(Range("UniqueIdentifierTimeSheet").Offset(1, -10), _
        UniqueIdentifier_Last.Offset(0, -10)), SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        With ActiveSheet.Sort
            .SetRange Range(Range("UniqueIdentifierTimeSheet").Offset(0, -11), UniqueIdentifier_Last)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With




'COPY AND PASTE VALUES
'        Range("BI3:BN52").Copy
'        Range("BI3:BN52").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False




'NAME TIMESHEET RANGE NAME = "WeeklyTimeSheet" (PRIMARILY FOR USE ON ALL SHEET)
    Dim TempWeeklyTimeSheetRng As Range
    Set TempWeeklyTimeSheetRng = Range("UniqueIdentifierTimeSheet").CurrentRegion
    ActiveSheet.Names.Add Name:="WeeklyTimeSheet", RefersTo:=TempWeeklyTimeSheetRng _
    .Offset(2, 0).Resize(TempWeeklyTimeSheetRng.Rows.Count - 2)


'DEFINE ROW BELOW TIMESHEET TO WHICH TO MOVE RECAP OF HOURS
        Dim RecapOfHoursRow As Long
        RecapOfHoursRow = Range("WeeklyTimeSheet").Row + Range("WeeklyTimeSheet").Rows.Count + 1
        
'MOVE RECAP OF HOURS BACK TO UNDER TIMESHEET
        Range("RecapOfHours").Cut Destination:=Range(Cells(RecapOfHoursRow, Range("UniqueIdentifierTimeSheet").Column - 11), _
        Cells(RecapOfHoursRow + 9, Range("UniqueIdentifierTimeSheet").Column - 11))


'AUTO FIT CELLS
        Columns("A:BF").AutoFit
        Columns("BN:BO").AutoFit


'HIDE CERTAIN COLUMNS
        Range("UniqueIdentifier_Mon").EntireColumn.Hidden = True
        Range("UniqueIdentifier_Tue").EntireColumn.Hidden = True
        Range("UniqueIdentifier_Wed").EntireColumn.Hidden = True
        Range("UniqueIdentifier_Thu").EntireColumn.Hidden = True
        Range("UniqueIdentifier_Fri").EntireColumn.Hidden = True
        Range("UniqueIdentifier_Sat").EntireColumn.Hidden = True
        Range("UniqueIdentifierTimeSheet").EntireColumn.Hidden = True
        
'DELETE TEMPORARY COLUMNS CREATED
        ColumnsToDelete.Delete


'TO FORMAT WEEKLY TIMESHEET
    'CONDITIONAL FORMATTING - SHADE EVERY OTHER ROW
    Application.Goto Reference:="WeeklyTimeSheet"
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=MOD(ROW(),2)=1"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.14996795556505
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    'NOT CONDITIONAL FORMATTING - JUST DIRECTLY SHADING TOTAL HOURS ROW
    With Range("TotalHours").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
        .PatternTintAndShade = 0
    End With
    
'SELECT CELL TO END ON
    Range("UniqueIdentifierTimeSheet").Offset(0, -11).Select
    
'TURN SETTINGS BACK ON AND PROTECT
        ActiveSheet.Protect
        ActiveWorkbook.Protect Structure:=True, Windows:=False
        With Application
            .ScreenUpdating = True
            '.Calculation = xlCalculationAutomatic
        End With
    Else
        MsgBox "Macro only valid on time input sheets"
    End If
End Sub

 

[jeremyjohnolson]

I got done changing it to only calc at one point where it needs it, other than that, I have auto calc turned off and it is noticeably faster now. I haven't had the screen updating problem either and I have run the macro several times, BUT like I said before it is hit and miss, sometimes it happens and sometimes it doesn't...so time will tell.

 

[davidam]

Just curious, what is it that you are expecting? Is is a screen updating issue?

 

[jeremyjohnolson]

Well, like I said in the opening post, my question is kind of vague...I feel like you are asking me to narrow it down to a specific question and I am not sure I can do that... I think it might be related to the screen updating issue, not sure. All I know is sometimes after I execute the macro Excel starts behaving poorly (as previously described). It seems like it is not updating the screen, or rather hanging up and updating it slowly because it does ussually eventually start to update be keeps hanging up until I restart Excel. Sorry I can't be more specific than that. I would if I could, but when it comes down to it I really don't know what question to ask because I don't know what's happening...I feel like I am going to the doctor and just telling him some vague symtoms in the hopes that there is some expert out there that will recognize it and say, "Ah, I know what's happening..."

I mean, I can't think of any question to ask specifically because as near as I can tell, there is nothing wrong with my code and it executes and does just exactly what I want it to do...BUTsometimes after it is done Excel seems like it is tired and needs a rest or something. I almost wish there was something in my code that was in error because then it would make sense, but I don't think there is...it runs through just fine, no error messages and the end result is what I want it to do. That is why I am wondering if there are just some best practices or general "good ideas" to do in VBA that I am missing here to keep Excel from disliking the code and keep it all running efficiently.

Also, I have had this same type of thing happen with other, completely different, macros I have written in Excel 2013. The only thing I can think of that they have in common is turning off auto calc, so I think that might be the culprit, but not sure...

 

[jeremyjohnolson]

In fact, the first time I noticed it was in a macro that I had originally written in Excel 2007 and used for quite a while without any problems at all, it would always execute in the blink of an eye and nothing strange would happen afterward...no problems. Then we upgraded our work computers to Excel 2013 and the same macro that worked in 2007 starting showing this problem in 2013. Also, sometimes it would take like 20 seconds to execute the macro, which in macro time feels like 20 minutes! But even that was not consistent, i.e. sometimes even in 2013 it would execute in a second or two, but never as fast as it did in 2007 version. Then I went through and cleaned up a bunch of the code, getting rid of .selects...and what not and it sped up a lot, and seemed to fix the problem, but now I am having it again. So I think it is only related to Excel 2013 disabling screen updating and it is not a consistent problem but happens only sometimes, so it is very much a mystery and even more of a mystery what to do about fixing it since I can't even narrow down for certain what the problem even is! very frustrating!

OK, I am done ranting for now.

 

[GooberTron]

The way I would describe the problem is that Excel is delayed by one input event. So the interface seems to lag by one click or movement. So I think behind the scenes the work has been done but the interface / window isn't updated to reflect it. I don't know if it is definitely linked to Application.ScreenUpdating but it seems to follow macros that use it. Like I said, never had this problem on 2007 or 2010. Doesn't happen every time either. I did also wonder whether it is related to 2013 allowing multiple parent application windows whereas 2010 and before didn't.

 

[ttdk1]

Minimizing the excel instance or certain workbooks can also speed up macro execution.

 

[davidam]

I have always felt that turning off screen updating has had a positive effect on macro speed. I do know that Excel takes over resources and does not let them go even when the macro execution is completed. I will often use task manager to exit from one large excel file before opening another one because this seems to free up those resources quite effectively.