Getting error Procedure too large in VBA Macros (Excel)

by

You will get that error if your procedure is more than 64kb. These are some of the things that you can to compact your code

1) Get rid of repetitive code. See this example

Sub Sample()
    Range("A1") = "Blah Blah"
    Range("A2") = "Blah Blah"
    Range("A3") = "Blah Blah"
    Range("A4") = "Blah Blah"
    Range("A5") = "Blah Blah"
    Range("A6") = "Blah Blah"
    Range("A7") = "Blah Blah"
End Sub

This code can be written as 

Sub Sample()
    For i = 1 To 7
        Range("A" & i) = "Blah Blah"
    Next i
End Sub

Another example

Sub Sample()
    Range("A1") = (Range("A1") * 10) + (Range("A1") + 30) + (Range("A1") / 30)
    Range("A5") = (Range("A5") * 10) + (Range("A5") + 30) + (Range("A5") / 30)
    Range("A11") = (Range("A11") * 10) + (Range("A11") + 30) + (Range("A11") / 30)
    Range("A6") = (Range("A6") * 10) + (Range("A6") + 30) + (Range("A6") / 30)
    Range("A8") = (Range("A8") * 10) + (Range("A8") + 30) + (Range("A8") / 30)
    Range("A56") = (Range("A56") * 10) + (Range("A56") + 30) + (Range("A56") / 30)
End Sub

This code can be written as

Sub Sample()
    Range("A1") = GetVal(Range("A1"))
    Range("A5") = GetVal(Range("A5"))
    Range("A11") = GetVal(Range("A11"))
    Range("A6") = GetVal(Range("A6"))
    Range("A8") = GetVal(Range("A8"))
    Range("A56") = GetVal(Range("A56"))
End Sub

Function GetVal(rng As Range) As Variant
    GetVal = (rng.Value * 10) + (rng.Value + 30) + (rng.Value / 30)
End Function

This will ensure that you cut down on space and do not write repetitive code.

2) If you generated the code via the macro then you may get something like this. Get rid of the useless code like ActiveWindow.ScrollRow = 8968

Option Explicit

'~~> This procedure fills Excel's 10000 cells with random values and then removes the duplicates
Sub FillExcelCells()
    Dim rowCount As Long

    '~~> Activate the necesary Sheet
    Sheets("Sheet1").Activate

    '~~> Loop through all the cells and store random numbers
    For rowCount = 1 To 10000
        Sheets("Sheet1").Range("A" & rowCount).Select
        Sheets("Sheet1").Range("A" & rowCount).Value = Int((10000 - 1) * Rnd() + 1)
    Next rowCount

    '~~> Sort the Range
    Sheets("Sheet1").Range("A1").Select
    Sheets("Sheet1").Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False

    Range(Selection, Selection.End(xlDown)).Select
    ActiveWindow.SmallScroll Down:=-39
    ActiveWindow.ScrollRow = 9838
    ActiveWindow.ScrollRow = 9709
    ActiveWindow.ScrollRow = 9449
    ActiveWindow.ScrollRow = 8968
    ActiveWindow.ScrollRow = 8319
    ActiveWindow.ScrollRow = 7245
    ActiveWindow.ScrollRow = 6003
    ActiveWindow.ScrollRow = 4818
    ActiveWindow.ScrollRow = 4040
    ActiveWindow.ScrollRow = 3317
    ActiveWindow.ScrollRow = 3076
    ActiveWindow.ScrollRow = 2521
    ActiveWindow.ScrollRow = 2298
    ActiveWindow.ScrollRow = 2113
    ActiveWindow.ScrollRow = 1724
    ActiveWindow.ScrollRow = 1372
    ActiveWindow.ScrollRow = 1038
    ActiveWindow.ScrollRow = 872
    ActiveWindow.ScrollRow = 668
    ActiveWindow.ScrollRow = 538
    ActiveWindow.ScrollRow = 464
    ActiveWindow.ScrollRow = 446
    ActiveWindow.ScrollRow = 427
    ActiveWindow.ScrollRow = 409
    ActiveWindow.ScrollRow = 390
    ActiveWindow.ScrollRow = 353
    ActiveWindow.ScrollRow = 334
    ActiveWindow.ScrollRow = 297
    ActiveWindow.ScrollRow = 279
    ActiveWindow.ScrollRow = 242
    ActiveWindow.ScrollRow = 223
    ActiveWindow.ScrollRow = 205
    ActiveWindow.ScrollRow = 168
    ActiveWindow.ScrollRow = 149
    ActiveWindow.ScrollRow = 112
    ActiveWindow.ScrollRow = 94
    ActiveWindow.ScrollRow = 57
    ActiveWindow.ScrollRow = 20
    ActiveWindow.ScrollRow = 1

    Selection.Sort Key1:=Sheets("Sheet1").Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

    '~~> Delete duplicates
    For rowCount = 10000 To 2 Step -1
        Sheets("Sheet1").Range("A" & rowCount).Select
        If Range("A" & rowCount).Value = Range("A" & rowCount - 1).Value Then
            Sheets("Sheet1").Rows(rowCount).Delete shift:=xlUp
        End If
    Next rowCount
End Sub

The above can be written as

'~~> This procedure fills Excel's 10000 cells with random values and then removes the duplicates
Sub FillExcelCells()
    Dim rowCount As Long

    With Sheets("Sheet1")
        '~~> Loop through all the cells and store random numbers
        For rowCount = 1 To 10000
            .Range("A" & rowCount).Value = Int((10000 - 1) * Rnd() + 1)
        Next rowCount

        '~~> Sort Range
        .Range("A1:A10000").Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

        '~~> Delete duplicates
        For rowCount = 10000 To 2 Step -1
            If .Range("A" & rowCount).Value = .Range("A" & rowCount - 1).Value Then
                .Rows(rowCount).Delete shift:=xlUp
            End If
        Next rowCount
    End With
End Sub

3) Declare you Objects so that you don’t have to keep on repeating them. See this example

Sub Sample()
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "sdasds"
    Range("A1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.Font.Bold = True
    Selection.Font.Italic = True
    Selection.Font.Underline = xlUnderlineStyleSingle
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub

This can be written as

Sub Sample()
    Dim ws As Worksheet, rng As Range

    Set ws = Sheet1

    Set rng = ws.Range("A1")

    With rng
        .FormulaR1C1 = "sdasds"
        With .Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        .Font.Bold = True
        .Font.Italic = True
        .Font.Underline = xlUnderlineStyleSingle
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub

4) Break Up your procedure if need be. and call the 2nd procedure from the 1st

5) Avoid using .Select and .Activate They not only make your code slow but also take a lot of space in your code if used extensively. How to avoid using Select in Excel VBA macros

More Related Contents:

  1. Workbooks.Open Method in VBA
  2. VBA – Run Time Error 1004 ‘Application Defined or Object Defined Error’
  3. Consolidating worksheets into one
  4. delete Autofilter Field resullt using the header Name
  5. VBA macro that search for file in multiple subfolders
  6. So, I have 6 “master” files to then divide into 40 separate files
  7. Getting ScriptControl to work with Excel 2010 x64
  8. Application.Match gives type mismatch
  9. Count and Highlight keywords within phrases
  10. Transpose a range in VBA
  11. Detect merged cells in VBA Excel with MergeArea
  12. Using VBA code, how to export Excel worksheets as image in Excel 2003?
  13. Parse HTML content in VBA
  14. How to add a DocumentProperty to CustomDocumentProperties in Excel?
  15. VBA EXCEL To Prompt User Response to Select Folder and Return the Path as String Variable [duplicate]
  16. Should I turn .CutCopyMode back on before exiting my sub procedure?
  17. Excel VBA – QueryTable AfterRefresh function not being called after Refresh completes
  18. Excel VBA: AutoFill Multiple Cells with Formulas
  19. VBA.Find and adding a value
  20. excel vba- extract text between 2 characters
  21. Round function in Excel, worksheet function vs VBA
  22. How to continue the code on the next line in VBA
  23. Excel VBA Userform – Execute Sub when something changes
  24. Count the number of rows in another sheet [duplicate]
  25. Getting Excel to refresh data on sheet from within VBA
  26. VBA Password Input with Cancel Function
  27. Finding first blank row, then writing to it
  28. How do I get a list of unique values from a range in Excel VBA?
  29. How to get selected value in multicolumn listbox
  30. Query my excel worksheet with VBA

Leave a Comment