当前位置: 首页 > 知识库问答 >
问题:

编译错误:过程太大(VBA错误)

祖迪
2023-03-14

我的VBA程序很长,我无法运行该程序。只有编写错误“编译错误:过程太大”。我试着把它改成两个过程,但我不知道如何将它应用到我的系统中。有人能帮我把这个分成两个步骤吗?或者这个节目可以缩短吗?谢谢你,如果你想帮助我,我真的很感激。这是一块

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

共有1个答案

贝研
2023-03-14

很抱歉告诉你,但我建议你重新设计这段代码,它确实很难,甚至不是不可能在阅读时不忘记你正在做什么。

除此之外,你确实有一些可以很容易缩短的东西,比如下面这些:

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

替换为:

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

或者这个:

If Dir(myFile) = "" Then
Else

替换为:

If Dir(myFile) <> "" Then

(如您所见,不需要其他

 类似资料:
  • 我仍然离我要做的很远,但在选择和设置付款期限和到期期限的前几行得到了一个代码 错误是编译错误-对象必需。我不知道是什么。WIP代码复制如下 请帮助编辑#1-2017年9月19日上午3点10分,我的第一个VBA代码终于运行了。它很慢,但它在奔跑。我在代码行“Workbooks(”himanshu.xlsm“).sheet1”.range(“j2”).copy Workbooks(“himanshu.

  • 问题内容: 当我在Netbeans中构建并运行程序时,它可以正常工作。但是当我尝试“ mvn compile”时,使用相同的pom.xml文件会出现以下错误: 我的Java版本不是1.3,这里是“ mvn -version”的结果 这是第53行: 问题答案: 问题是在Maven2中默认使用和 您可以通过将其添加到pom中来解决此问题: 将其放在最顶层的父pom中是很实际的,这样您派生的pom不需要

  • 我遇到了一个初学者的编译错误: 我的简单程序: 我尝试使用以下命令编译它: gcc-g-Wall-ansi launch_瓷砖。c-o tiles\u程序 并得到这些错误: 启动_tiles。c: 在函数“main”中: launch_tiles. c: 17:19:错误:预期')'之前';'令牌 launch_tiles. c: 17:19:错误:太少的参数函数'fget' /usr/inclu

  • 当我退出我的应用程序时,这个folloeing错误已经产生。

  • 我是Scala和SBT的新手,所以我可能会遗漏一些明显的东西。 我试图编译http://www.scalafx.org/docs/quickstart/上的HelloWorld示例

  • 编译模块com。实例我的包裹。GWT。HelloGWT [INFO]验证新编译的单元 [INFO]在第一次过程中忽略了1个单元,其中包含编译错误 [INFO]编译时将-strict或-logLevel设置为TRACE或DEBUG以查看所有错误 [INFO]在文件:/home/ilsurih/Projects/maven hibernate/src/main/java/com/example/myP