Slow running macro Add In

[patsdavixen]

Hi,

I have created a Macro Add In that has 5 buttons and calculates atleast a 100 cells in two seperate workbooks. It also formats the worksheets. The problem I have is that after I put all the macros together in one module, it runs extremely slowly and the excel also goes into a "Not Responding" mode while the macro runs. I worked on this Add-In for weeks with a hope that it will speed up my work but now it's becoming such a disappointment. Could someone please help me? I have tried to search other threads for methods to speed the Add-In up but nothing seems to work. I'm not sure if I'm using the speed up codes accurately. I've included a snippet of my code below as an example of the formatting macro only:

Sub Formatting()
    
    Dim xlCalc As XlCalculation
    xlCalc = Application.Calculation
    Application.Calculation = xlCalculationManual
    On Error GoTo CalcBack
    Application.ScreenUpdating = False
    Sheets("Individual Current Tracker").Select
    Range("A1:AI1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=LEN(TRIM(A1))=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.399945066682943
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("E2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "[$-409]dd-mmm-yy;@"
    Range("R2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "[$-409]dd-mmm-yy;@"
    Range("T2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "[$-409]dd-mmm-yy;@"
    Range("X2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "[$-409]dd-mmm-yy;@"
    Range("AF2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "[$-409]dd-mmm-yy;@"
    Range("AG2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "[$-409]dd-mmm-yy;@"
    Range("K2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "0.00"
    Range("L2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "0.00"
    Range("M2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "0.00"
    Range("U2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "0.00"
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection.Font
        .Name = "Calibri"
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    With Selection.Font
        .Name = "Calibri"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Columns("A:A").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Columns("A:AI").EntireColumn.AutoFit
    With Selection
        .HorizontalAlignment = xlGeneral
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A1:AI1").Select
     Range(Selection, Selection.End(xlDown)).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A1:AI1").Select
    Sheets("Individual Historical Tracker").Select
    Range("E2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "[$-409]dd-mmm-yy;@"
    Range("R2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "[$-409]dd-mmm-yy;@"
    Range("T2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "[$-409]dd-mmm-yy;@"
    Range("X2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "[$-409]dd-mmm-yy;@"
    Range("AF2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "[$-409]dd-mmm-yy;@"
    Range("AG2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "[$-409]dd-mmm-yy;@"
    Range("K2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "0.00"
    Range("L2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "0.00"
    Range("M2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "0.00"
    Range("U2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "0.00"
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Columns("A:A").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Columns("A:AI").EntireColumn.AutoFit
    With Selection
        .HorizontalAlignment = xlGeneral
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A1:AI1").Select
     Range(Selection, Selection.End(xlDown)).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A1").Select
    Application.ScreenUpdating = True
    
    Application.Calculation = xlCalc
    Exit Sub
CalcBack:
    Application.Calculation = xlCalc
End Sub

 

[Damon Ostrander]

Hi patsdavixen,

Recorded macros tend to run orders of magnitude slower than macros that are well-written. In your case the primary problem is the use of the Select method and Selection object, which is almost never necessary in written code. It slows down the macro considerably because every time an object is selected the screen must be refreshed to represent the highlighting of the cell being selected, etc. Chip Pearson gives some good tips for speeding things up, including a brief description of how to eliminate unnecessary use of selections at his website: Optimize VBA.