VBA procedure too large

[dshafique]

hi Guys, I have a huge worksheet with a lot of code in it, but everytime i run it, it gives me an error saying the procedure is too large. Is there a work around it? the code is 4000 lines, but most of it is the same thing with the only difference being either the table name, column name, or sheet name

any help would be greatly appreciated.

 

[Joe4]

but most of it is the same thing with the only difference being either the table name, column name, or sheet name

If you have repeating similar code, that is usually a sign that you have not written it in the most efficient way. You should be able to store the differing values in an array, and loop through them in a single block of code, instead of one for each different value.

Also, you should never have that many lines in a single procedure. It is usually advised to break it up into a series of smaller procedures, and then have one main procedures that calls your other smaller procedures and runs them in the correct order.

 

[dshafique]

How can i store it into an array? for example, I have 36 versions of the following procedure:

 'KPI 1    Sheets("CIO Data").Select
    Range("cTable[[#Headers],[Findings]]").Select
    ActiveCell.Offset(1).Select
    ActiveCell.FormulaR1C1 = _
        "=COUNTIFS(KPI[Accountable CIO],[Name],KPI[KPI],""KPI1"",KPI[Findings],""Findings"")"
    ActiveCell.Copy
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    Range("cTable[[#Headers],[No Findings]]").Select
    ActiveCell.Offset(1).Select
    ActiveCell.FormulaR1C1 = _
        "=COUNTIFS(KPI[Accountable CIO],[Name],KPI[KPI],""KPI1"",KPI[Findings],""No Findings"")"
    ActiveCell.Copy
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    Range("cTable[[#Headers],[KPI 1 Total]]").Select
    ActiveCell.Offset(1).Select
    ActiveCell.FormulaR1C1 = "=SUM(cTable[@[Findings]:[No Findings]])"
    ActiveCell.Copy
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False


    Range("cTable[[#Headers],[KPI 1 Percentage]]").Select
    ActiveCell.Offset(1).Select
    ActiveCell.FormulaR1C1 = "=IFERROR([@Findings]/[@[KPI 1 total]], 0.0%)"
    ActiveCell.Copy
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

I have this for 9 different 'KPI' and for 4 different tables, how would i loop this?

 

[Norie]

If you are adding formulas to a table you shouldn't need to copy then down, that should happen automatically.

Also, you don't need all that Select/Selection.

For example this,

   Sheets("CIO Data").Select
    Range("cTable[[#Headers],[Findings]]").Select
    ActiveCell.Offset(1).Select
    ActiveCell.FormulaR1C1 = _
        "=COUNTIFS(KPI[Accountable CIO],[Name],KPI[KPI],""KPI1"",KPI[Findings],""Findings"")"

can probably written like this.

   Sheets("CIO Data").Range("cTable[[#Headers],[Findings]]").Offset(1).FormulaR1C1 = "=COUNTIFS(KPI[Accountable CIO],[Name],KPI[KPI],""KPI1"",KPI[Findings],""Findings"")"

That's from 5 lines to 1 line, and you seem to have a lot of code similar to this.

PS You might even be able to use this.

Sheets("CIO Data").ListObjects("cTable").ListColumns("Findings").DataBodyRange.FormulaR1C1 .FormulaR1C1 = "=COUNTIFS(KPI[Accountable CIO],[Name],KPI[KPI],""KPI1"",KPI[Findings],""Findings"")"

 

[hotabae]

So, refactoring your code is a whole 'nother thing (but still a very good idea).

Here is a somewhat unhelpful link about your issue (64K limit on compiled procedure) https://msdn.microsoft.com/en-us/library/Aa264541

As far as a "quick-fix" for something like this would normally work

Sub formattingSheets()

kpi_1_procedure

kpi_2_procedure

kpi_3_procedure

kpi_4_procedure

kpi_5_procedure

kpi_6_procedure

kpi_7_procedure

kpi_8_procedure

kpi_9_procedure

End Sub

And separately each module like this:

sub kpi_1_procedure()

 'KPI 1    Sheets("CIO Data").Select
    Range("cTable[[#Headers],[Findings]]").Select
    ActiveCell.Offset(1).Select
    ActiveCell.FormulaR1C1 = _
        "=COUNTIFS(KPI[Accountable CIO],[Name],KPI[KPI],""KPI1"",KPI[Findings],""Findings"")"
    ActiveCell.Copy
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    Range("cTable[[#Headers],[No Findings]]").Select
    ActiveCell.Offset(1).Select
    ActiveCell.FormulaR1C1 = _
        "=COUNTIFS(KPI[Accountable CIO],[Name],KPI[KPI],""KPI1"",KPI[Findings],""No Findings"")"
    ActiveCell.Copy
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    Range("cTable[[#Headers],[KPI 1 Total]]").Select
    ActiveCell.Offset(1).Select
    ActiveCell.FormulaR1C1 = "=SUM(cTable[@[Findings]:[No Findings]])"
    ActiveCell.Copy
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False


    Range("cTable[[#Headers],[KPI 1 Percentage]]").Select
    ActiveCell.Offset(1).Select
    ActiveCell.FormulaR1C1 = "=IFERROR([@Findings]/[@[KPI 1 total]], 0.0%)"
    ActiveCell.Copy
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

End Sub

But again, it is useful to reorganize your code to be more efficient as Joe has suggested. This just compartmentalizes everything so Excel can handle it as you expected.

 

[Joe4]

Here is some sample code that shows you how to store the tables and KPI values in an array, and then loop through them.
You should be able to incorporate that logic into your code:

    Dim tbl As Variant
    Dim kpi As Variant
    Dim t As Long
    Dim k As Long
    Dim str As String
    
    tbl = Array("Table1", "Table2", "Table3", "Table4")
    kpi = Array("KP1", "KP2", "KP3")
    
'   Loop through all tables
    For t = LBound(tbl) To UBound(tbl)
'       Loop through all KPI
        For k = LBound(kpi) To UBound(kpi)
'           Build whatever reference string you need
            str = "Table name: " & [COLOR=#ff0000]tbl(t)[/COLOR] & vbCrLf & _
                  "KPI: " & [COLOR=#ff0000]kpi(k)[/COLOR]
            MsgBox str
        Next k
    Next t

Note that the part that I highlighted in red. That is how you reference each current array value within the loop.

 

[dshafique]

Thank you guys for all the suggestions, I'll try them out and hopefully can shrink it by a lot.

 

[dshafique]

Also, Is there a way for me to select multiple specific cells, similar to holding the control key, and selecting cells A1, h7, N3 etc. i feel that will also help shrink a lot of stuff, since i have formatting a handful of ranges,

such as

Range("cTable[[#Totals],[KPI 1 Percentage]]").Select
Range("cTable[[#Totals],[KPI 2 Percentage]]").Select
etc

 

[Norie]

You could try something like this.

Range("cTable[[#Totals],[KPI 1 Percentage]], cTable[[#Totals],[KPI 2 Percentage]]").Select

Though there is no need to select and if you have a whole bunch of KPIs you'd be better of looping.