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
Improve this question
4
  • 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.) Commented Jan 24, 2020 at 6:43
  • 2
    If you follow what is mentioned in Getting error Procedure too large in VBA Macros (Excel) then you should be good to go! :) Commented Jan 24, 2020 at 6: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: stackoverflow.com/questions/10714251/… Commented Jan 24, 2020 at 7:38
  • Looking through your code, there are numerous places where a Sub could be created. For example Case "B5" could be broken out into a Sub. Read up on Select Case statements, you can pass in multiple conditions per Select (an OR statement) that would trim a huge chunk of your code (stackoverflow.com/questions/21319788/…). Commented Mar 1 at 16:10

1 Answer 1

Reset to default
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)

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.