1

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
braX
  • 11,506
  • 5
  • 20
  • 33
Haudhi Alfi Yasin
  • 11
  • 1
  • 3
    Honestly, your procedure is a bit of a mess. You are using so many `GoTo` statement it's nearly impossible to figure out your logic. You have `Loop` statements mixed inside of `If` statements. That's never good. Look at the things that are repetitive in your code like where it's determining OK or NG and turn that into a function instead. (you will have to get rid of all the `GoTo` statements first.) – braX Jan 24 '20 at 06:43
  • 2
    If you follow what is mentioned in [Getting error Procedure too large in VBA Macros (Excel)](https://stackoverflow.com/questions/11450232/getting-error-procedure-too-large-in-vba-macros-excel) then you should be good to go! :) – Siddharth Rout Jan 24 '20 at 06:46
  • There are very many "Select" statements, but I can't see anything which actually uses Selection or Activecell. What are the Select statements actually doing? You may wish to read: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Chris Melville Jan 24 '20 at 07:38

1 Answers1

0

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)

Dominique
  • 16,450
  • 15
  • 56
  • 112