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

 

[davidam]

I see that you have commented out this:

'.Calculation = xlCalculationManual

That means that every time you load anything the whole sheet will calculate, take out the apostrophe and see what happens.
I suggest you also take out the one near the end that turns it back to automatic
</pre>

 

[jeremyjohnolson]

Yeah, I had that in there to speed it up, but then when I finished writing all my code I realized that I need to keep autocalc on or it did not execute the code properly because it needs to used calculated numbers in the code before it is done being executed. So if I turn it off, I end up with the wrong result in the end.

 

[davidam]

You can probably figure out where to put it in so that it calculates just as you need it to. you can also turn it off at the beginning and then slip in things like:
Range("A1:A5").Calculate
This issue can have a tremendous impact on a big sheet.
You can also turn off calculation at the beginning and at just the right place in the code just put the word: Calculate; which would run one calculation of the whole sheet

 

[jeremyjohnolson]

Nice, thank you for that bit of information...I didn't even think about that. It was a lot faster when I had auto calc turned off, so I am sure it will make a huge difference in speed. I can figure out where it needs to be turned on. I remember noticing it when I was stepping through with F8...I just need to go back and do that again. Thanks!

 

[GooberTron]

I've had similar graphics re-draw issues in Excel 2013, never in 2010. I'm convinced it is something to do with toggling Application.ScreenUpdating between false and true. I've tended to remove those lines if I can for shorter macros where it was more cosmetic than speed related.

 

[jeremyjohnolson]

I had was wondering if it was something to do with screenupdating as well because it acts as if the screen is not updating. I mean the program itself is not frozen, it just doesn't change what's on the screen with user input. I know this because one time I was in the middle of recording a macro and I had the VBA editor open so I could see everything I was doing. In the editor, it was still responding for instance, as I would scroll the mouse it would add the annoying scroll code in the editor, but on the Excel screen Excel was not responding to any scrolls or anything.

 

[davidam]

If screen updating is off, you cannot see a cell value change or anything like that...it almost seems like the machine is broken or the program has frozen. You can also use screen updating at different places throughout the program. I have programs that download a bunch of data and then place it in different locations; I will have screen updating come on just for a line or two so that I can see the new data being placed and then switch it right off again...it works very well.

 

[jeremyjohnolson]

davidam - have you ever had any issues with screenupdating not turning back on even when you told it too? Are you using 2013?

 

[davidam]

I am using 2010. I have only had issues with screen updating if I stop a program part way through, or program a pause or an exit before turning it back on, something like that--usually while building a new program. If you tell a program to update, it will.