Compile Error: Procedure Too Large help needed

Question

I'm working on a large Excel project that requires entering a lot of data spread out over the worksheet that needs to be entered as quick as possible. To try and aide with the entry, I've created a number of UserForms that the user would enter the data into. One such form returns the above "Process Too Large" error when trying to transfer the data.

I understand why the error pops up - it's far too long. I've included the code for one such entry (slightly modified of course) and was wondering how I would be able to truncate it?

    Dim ws As Worksheet
    Dim i As Long
    Set ws = ThisWorkbook.Sheets("STOCK")

' 101
If entry101.Value <> "" Then
Dim NUM101 As String

If com101.Value <> "" Then
    NUM101 = "# - " & UCase(com101.Value)
Else
    NUM101 = ""
End If

    If cmb101.Value = "FULL" Then
        ws.Range("_101").Value = UCase(code101.Value) & " " & Chr(10) & UCase(com101.Value) & " - FULL " & Chr(10) & " "
    End If
    If cmb101.Value = "OUT OF STOCK" Then
        ws.Range("_101").Value = UCase(com101.Value) & " OUT OF STOCK " & Chr(10) & UCase(code101.Value) & " " & Chr(10) & " "
    End If
    If cmb101.Value = "SHIPPED" Then
        ws.Range("_101").Value = UCase(code101.Value) & " " & Chr(10) & " - SHIPPED " & Chr(10) & NUM101
    End If
    If cmb101.Value = "DAMAGED" Then
        ws.Range("_101").Value = UCase(code101.Value) & " DAMAGED " & Chr(10) & " "" & Chr(10) & NUM101"
    End If
    If cmb101.Value = "LOW STOCK" Then
        ws.Range("_101").Value = UCase(com101.Value) & " LOW-STOCK " & Chr(10) & UCase(code101.Value) & " " & Chr(10) & " "
    End If
    If cmb101.Value = "RETURN" Then
        ws.Range("_101").Value = UCase(code101.Value) & " " & Chr(10) & "RETURNED - " & UCase(com101.Value) & " " & Chr(10) & " "
    End If
    If cmb101.Value = "" Then
        ws.Range("_101").Value = UCase(code101.Value) & Chr(10) & " - UNKNOWN CONDITION"
    End If
End If

The UserForm has two text boxes ("code101" & "com101") and a single ComboBox ("cmb101") for each entry. The above code needs to be applied to a range from "_101" to "_143" so needs to repeat 43 times.

Any help would be greatly appreciated. Thank you all.

Controls on userforms can be referred to by name like `Me.Controls("entry101").Value` This means you can use a loop from 101 to 143 and substitute the loop counter into your control names like `Me.Controls("entry" & i).Value` Same approach for your named ranges. — Tim Williams, Mar 05 '20 at 03:58
Have you seen [THIS](https://stackoverflow.com/questions/11450232/getting-error-procedure-too-large-in-vba-macros-excel/11450945#11450945)? — Siddharth Rout, Mar 05 '20 at 04:19

score 0 · Accepted Answer · answered Mar 05 '20 at 05:05

Something like this (untested):

Dim ws As Worksheet, vCom, vCode
Dim i As Long, s, num As String
Set ws = ThisWorkbook.Sheets("STOCK")

For i = 101 To 143
    If Me.Controls("entry" & i).Value <> "" Then

        vCom = UCase(Me.Controls("com" & i).Value)
        vCode = UCase(Me.Controls("code" & i).Value)

        num = IIf(vCom <> "", "# - " & vCom, "")
        s = ""

        Select Case Me.Controls("cmb" & i).Value
            Case "FULL": s = vCode & " " & Chr(10) & vCom & " - FULL " & Chr(10) & " "
            Case "OUT OF STOCK": s = vCom & " OUT OF STOCK " & Chr(10) & vCode & " " & Chr(10) & " "
            Case "SHIPPED": s = vCode & " " & Chr(10) & " - SHIPPED " & Chr(10) & num
            'etc
            'etc
        End Select
        If Len(s) > 0 Then ws.Range("_" & i).Value = s
    End If
Next i

Absolutely fantastic! Perfect. Thank you very much. – Tokuri Mar 05 '20 at 19:38 — Tokuri, Mar 05 '20 at 19:38

Compile Error: Procedure Too Large help needed

1 Answers1