CODEWITHSUNDEEP
excelvba
Haudhi Alfi Yasin
Haudhi Alfi Yasin

Reputation: 11

Compile Error: Procedure Too Large (VBA Error)

My VBA program is very long, I can't run the program. There is only the writing error "Compile error: Procedure too large". I have tried changing it into two procedures, but I don't understand how to apply it to my system. can anyone help me in making this into two procedures? or can this program be shortened? Thank you, I really appreciate if you want to help me. Here is the some piece

Private Sub Worksheet_Change(ByVal Target As Range)

Dim varF1 As Variant
Dim i, cel, num As Integer
Dim rtn, myFile, str As String

On Error GoTo Err_cmm1_Click

  Select Case Target.Address(False, False)
    Case "B3"
    Range("B5").Select

    Case "B5"
    str = Range("B5")
    Range("B7") = Trim(Mid(str, Range("I3"), Range("J3") - Range("i3") + 1))
    Range("E7") = Mid(str, Range("I5"), Range("J5") - Range("I5") + 1)

    myFile = ThisWorkbook.Path & "\Part\" & Range("B7") & ".jpg"
    If Dir(myFile) = "" Then
    Else
    Image1.Picture = LoadPicture(myFile)
    End If

    myFile = ThisWorkbook.Path & "\Part\" & Range("B7") & "-1.jpg"
    If Dir(myFile) = "" Then
    Else
    Image2.Picture = LoadPicture(myFile)
    End If

    myFile = ThisWorkbook.Path & "\PIS\" & Range("B7") & ".jpg"
    If Dir(myFile) = "" Then
    Else
    Image3.Picture = LoadPicture(myFile)
    End If
    Range("B13").Select

    Case "B7"
    myFile = ThisWorkbook.Path & "\Part\" & Range("B7") & ".jpg"
    If Dir(myFile) = "" Then
    Else
    Image1.Picture = LoadPicture(myFile)
    End If

    myFile = ThisWorkbook.Path & "\Part\" & Range("B7") & "-1.jpg"
    If Dir(myFile) = "" Then
    Else
    Image2.Picture = LoadPicture(myFile)
    End If

    myFile = ThisWorkbook.Path & "\PIS\" & Range("B7") & ".jpg"
    If Dir(myFile) = "" Then
    Else
    Image3.Picture = LoadPicture(myFile)
    End If
    Range("E7").Select

    Case "E7"
    Range("B13").Select

    Case "B17"
    Range("C13").Select

    Case "C17"
    Range("D13").Select

    Case "B13"
    If Range("B63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("B14").Select
    End If

    Case "B14"
    If Range("B63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("B15").Select
    End If

    Case "B15"
    If Range("B63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("B16").Select
    End If

    Case "B16"
    If Range("B63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("B17").Select
    End If

    Case "B17"

    Range("C13").Select
      If Range("C10") = "" Then
      rtn = MsgBox("‘‡”»’è‚Æ‚µ‚ćŠi‚Å‚·‚©?‡Ši‚Ìê‡Au‚Í‚¢vA•s‡Ši‚Ìê‡Au‚¢‚¢‚¦v‚ð‘I‘ð‚µ‚Ä‰º‚³‚¢B", vbYesNo)
        i = 1
        Do While (Worksheets("Record").Cells(i, 1))  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If

    Case "C13"
    If Range("C63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("C14").Select
    End If

    Case "C14"
    If Range("C63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("C15").Select
    End If

    Case "C15"
    If Range("C63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("C16").Select
    End If

    Case "C16"
    If Range("C63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("C17").Select
    End If

    Case "C17"

    Range("D13").Select
      If Range("D10") = "" Then
      rtn = MsgBox("‘‡”»’è‚Æ‚µ‚ćŠi‚Å‚·‚©?‡Ši‚Ìê‡Au‚Í‚¢vA•s‡Ši‚Ìê‡Au‚¢‚¢‚¦v‚ð‘I‘ð‚µ‚Ä‰º‚³‚¢B", vbYesNo)
        i = 1
        Do While (Worksheets("Record").Cells(i, 1))  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If

    Case "D13"
    If Range("D63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("D14").Select
    End If

    Case "D14"
    If Range("D63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("D15").Select
    End If

    Case "D15"
    If Range("D63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("D16").Select
    End If

    Case "D16"
    If Range("D63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("D17").Select
    End If

    Case "D17"

    Range("E13").Select
      If Range("E10") = "" Then
      rtn = MsgBox("‘‡”»’è‚Æ‚µ‚ćŠi‚Å‚·‚©?‡Ši‚Ìê‡Au‚Í‚¢vA•s‡Ši‚Ìê‡Au‚¢‚¢‚¦v‚ð‘I‘ð‚µ‚Ä‰º‚³‚¢B", vbYesNo)
        i = 1
        Do While (Worksheets("Record").Cells(i, 1))  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If

    Case "E13"
    If Range("E63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("E14").Select
    End If

    Case "E14"
    If Range("E63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("E15").Select
    End If

    Case "E15"
    If Range("E63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("E16").Select
    End If

    Case "E16"
    If Range("E63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("E17").Select
    End If

    Case "E17"

    Range("F13").Select
      If Range("F10") = "" Then
      rtn = MsgBox("‘‡”»’è‚Æ‚µ‚ćŠi‚Å‚·‚©?‡Ši‚Ìê‡Au‚Í‚¢vA•s‡Ši‚Ìê‡Au‚¢‚¢‚¦v‚ð‘I‘ð‚µ‚Ä‰º‚³‚¢B", vbYesNo)
        i = 1
        Do While (Worksheets("Record").Cells(i, 1))  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If

    Case "F13"
    If Range("F63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("F14").Select
    End If

    Case "F14"
    If Range("F63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("F15").Select
    End If

    Case "F15"
    If Range("F63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("F16").Select
    End If

    Case "F16"
    If Range("F63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("F17").Select
    End If

    Case "F17"

    Range("G13").Select
      If Range("G10") = "" Then
      rtn = MsgBox("‘‡”»’è‚Æ‚µ‚ćŠi‚Å‚·‚©?‡Ši‚Ìê‡Au‚Í‚¢vA•s‡Ši‚Ìê‡Au‚¢‚¢‚¦v‚ð‘I‘ð‚µ‚Ä‰º‚³‚¢B", vbYesNo)
        i = 1
        Do While (Worksheets("Record").Cells(i, 1))  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If

    Case "G13"
    If Range("G63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("G14").Select
    End If

    Case "G14"
    If Range("G63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("G15").Select
    End If

    Case "G15"
    If Range("G63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("G16").Select
    End If

    Case "G16"
    If Range("G63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("G17").Select
    End If

    Case "G17"

    Range("H13").Select
      If Range("H10") = "" Then
      rtn = MsgBox("‘‡”»’è‚Æ‚µ‚ćŠi‚Å‚·‚©?‡Ši‚Ìê‡Au‚Í‚¢vA•s‡Ši‚Ìê‡Au‚¢‚¢‚¦v‚ð‘I‘ð‚µ‚Ä‰º‚³‚¢B", vbYesNo)
        i = 1
        Do While (Worksheets("Record").Cells(i, 1))  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If

    Case "H13"
    If Range("H63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("H14").Select
    End If

    Case "H14"
    If Range("H63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("H15").Select
    End If

    Case "H15"
    If Range("H63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("H16").Select
    End If

    Case "H16"
    If Range("H63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("H17").Select
    End If

    Case "H17"

    Range("I13").Select
      If Range("I10") = "" Then
      rtn = MsgBox("‘‡”»’è‚Æ‚µ‚ćŠi‚Å‚·‚©?‡Ši‚Ìê‡Au‚Í‚¢vA•s‡Ši‚Ìê‡Au‚¢‚¢‚¦v‚ð‘I‘ð‚µ‚Ä‰º‚³‚¢B", vbYesNo)
        i = 1
        Do While (Worksheets("Record").Cells(i, 1))  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If

    Case "I13"
    If Range("I63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("I14").Select
    End If

    Case "I14"
    If Range("I63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("I15").Select
    End If

    Case "I15"
    If Range("I63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("I16").Select
    End If

    Case "I16"
    If Range("I63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("I17").Select
    End If

    Case "I17"

    Range("J13").Select
      If Range("J10") = "" Then
      rtn = MsgBox("‘‡”»’è‚Æ‚µ‚ćŠi‚Å‚·‚©?‡Ši‚Ìê‡Au‚Í‚¢vA•s‡Ši‚Ìê‡Au‚¢‚¢‚¦v‚ð‘I‘ð‚µ‚Ä‰º‚³‚¢B", vbYesNo)
        i = 1
        Do While (Worksheets("Record").Cells(i, 1))  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If

    Case "J13"
    If Range("J63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("J14").Select
    End If
    Case "J14"
    If Range("J63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("J15").Select
    End If

    Case "J15"
    If Range("J63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("J16").Select
    End If

    Case "J16"
    If Range("J63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("J17").Select
    End If

    Case "J17"

    Range("K13").Select
      If Range("K10") = "" Then
      rtn = MsgBox("‘‡”»’è‚Æ‚µ‚ćŠi‚Å‚·‚©?‡Ši‚Ìê‡Au‚Í‚¢vA•s‡Ši‚Ìê‡Au‚¢‚¢‚¦v‚ð‘I‘ð‚µ‚Ä‰º‚³‚¢B", vbYesNo)
        i = 1
        Do While (Worksheets("Record").Cells(i, 1))  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If

    Case "K13"
    If Range("K63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("K14").Select
    End If

    Case "K14"
    If Range("K63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("K15").Select
    End If

    Case "K15"
    If Range("K63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("K16").Select
    End If

    Case "K16"
    If Range("K63")  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If
    Else
    Range("K17").Select
    End If

    Case "K17"

    Range("L13").Select
      If Range("L10") = "" Then
      rtn = MsgBox("‘‡”»’è‚Æ‚µ‚ćŠi‚Å‚·‚©?‡Ši‚Ìê‡Au‚Í‚¢vA•s‡Ši‚Ìê‡Au‚¢‚¢‚¦v‚ð‘I‘ð‚µ‚Ä‰º‚³‚¢B", vbYesNo)
        i = 1
        Do While (Worksheets("Record").Cells(i, 1))  ""
         i = i + 1
         Loop
        If rtn = vbYes Then
         Range("G1") = "OK"
         GoTo step1
         Else
          Range("G1") = "NG"
          GoTo step1
        End If
        End If


Case Else
End Select

Exit Sub

step1:

Touroku

Exit_cmm1_Click:
    Exit Sub

Err_cmm1_Click:
    MsgBox Err.Description
    Resume Exit_cmm1_Click



 End Sub

Upvotes: 1

Views: 1466

Answers (1)

Dominique
Dominique

Reputation: 17565

Sorry to tell you, but I'm advising you to redesign this piece of code, it's really difficult, even not impossible to read without losing track of what you're doing.

Beside that, you do have some things you can easily shorten, like the following ones:

    If rtn = vbYes Then
     Range("G1") = "OK"
     GoTo step1
     Else
      Range("G1") = "NG"
      GoTo step1
    End If

Replace this by:

    If rtn = vbYes Then
     Range("G1") = "OK"
     Else
      Range("G1") = "NG"
    End If
    Goto step1

Or this one:

If Dir(myFile) = "" Then
Else

Replace it by:

If Dir(myFile) <> "" Then

(As you see, no Else needed)

Upvotes: 0

Related Questions