Procedure too large vba ошибка как исправить

I received this error message —Procedure too large — in VBA. What is the reason and way out for this error?

Dirk Vollmar's user avatar

Dirk Vollmar

173k53 gold badges255 silver badges316 bronze badges

asked Sep 20, 2010 at 12:01

Sourav's user avatar

You probably have one or more gigantic procedures/functions and I think VBA has a limit of 64k or something per procedure.

You fix it by splitting that procedure up into multiple procedures that can then be called by the one procedure.

So instead of having:

 Sub GiantProcedure()
      ... ' lots and lots of code
 End Sub

You’d have something like:

 Sub GiantProcedure()
      ... ' a little bit of common code
      Proc1()
      Proc2()
      Proc3()

 End Sub

 Sub Proc1()
      ... ' quite a bit of code
 End Sub

 Sub Proc2()
      ... ' quite a bit of code
 End Sub

 Sub Proc3()
      ... ' quite a bit of code
 End Sub

answered Sep 20, 2010 at 12:07

Hans Olsson's user avatar

Hans OlssonHans Olsson

54.2k15 gold badges94 silver badges116 bronze badges

1

The idea of GiantProcedure didn’t work for me, using Microsoft Powerpoint 2013.
Then I added a «call» before each «proc». Like this:

Sub GiantProcedure()

  Call Proc1()
  Call Proc2()
  Call Proc3()

End Sub

Now, it works.

answered Apr 2, 2018 at 16:17

Siamak's user avatar

SiamakSiamak

3351 silver badge11 bronze badges

Not sure why the top voted post is recommended as the giant procedure code didn’t work for me either. What did end up working was this:

Sub MacroName

Call ProcedureName1
Call ProcedureName2
etc...

End Sub

Sub ProcedureName1
'insert your vba code here

End Sub

The Call statement pulled each of the subs as long as I called the correct named sub.

answered Dec 6, 2020 at 0:31

Deniz Balta's user avatar

Agreed, procedures should be split into smaller subs instead of one gigantic piece of code.

I have seen the Procedure too large errors on 32 bit installations of Excel, when they run / compile perfectly fine on the 64 bit installation the code was written on — so if it is a case of Procedure too large error [suddenly] occurring and it used to work before, check if the Excel installation you are trying to run the code on is 32 bit, and try on a 64 bit installation instead.

answered Feb 16, 2021 at 23:41

Ben Carroll's user avatar

Use a 32-bit version of an Office 2010 program
Or Divide the module into parts

answered Oct 6, 2021 at 13:55

abdomohamed's user avatar

My employees get the same message on their weaker computers, but I don’t on mine. So it’s either the fact that the memory on your computer is less than 16GB or otherwise it’s the fact that you have an intel processor that’s too weak. An i7 intel processor that’s after the 8th generation should definitely get the job done. Just get a better computer processor and better memory on your computer and it will work.

answered May 17, 2021 at 20:45

Ahmedakhtar11's user avatar

Источник

Return to VBA Code Examples

This article will explain the VBA procedure too large error.

vba procedure too large

There are 2 main reasons that this error can appear in Excel VBA.

Check Excel Version

This error can occur if you are running a 32-bit version of Excel, but you have a very large procedure which would be better off being run in a 64-bit environment.

Just because your PC is 64-bit does not necessarily mean that your version of the Microsoft Office is 64-bit, so you will need to check your Excel version.

In the Ribbon, select File, then (1) select Account and then (2) About Excel.

vba procedure account

Check in the information that appears about Excel – it will let you know if you are running the 32-bit or 64-bit version of Office.

vba procedure excel version

If you need to upgrade to the 64-bit version, make sure that it will not effect any add-ins or other macros that you use. Most add-ins and macros need to be customized to run on the 64-bit version of office and older add-ins may not run at all.

Decrease the Size of Your Procedure

The other main reason is that your Procedure is, simply, too large! You need to try and decrease the size of your procedure by using Loops or Nested Procedures instead of having the code all in a single procedure.

You can for example break down your code into separate procedures and then call the separate procedures in your main procedure.

For example:

vba procedure call procedures

Источник

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

Источник

Процедура слишком большая

Я получил это сообщение об ошибке —Procedure too large — в VBA. В чем причина и выход этой ошибки?

2010-09-20 12:01

8
ответов

Вероятно, у вас есть одна или несколько гигантских процедур / функций, и я думаю, что VBA имеет ограничение в 64 КБ или что-то на процедуру.

Вы исправляете это, разделяя эту процедуру на несколько процедур, которые затем могут быть вызваны одной процедурой.

Поэтому вместо того, чтобы:

 Sub GiantProcedure()
      ... ' lots and lots of code
 End Sub

У вас будет что-то вроде:

 Sub GiantProcedure()
      ... ' a little bit of common code
      Proc1()
      Proc2()
      Proc3()

 End Sub

 Sub Proc1()
      ... ' quite a bit of code
 End Sub

 Sub Proc2()
      ... ' quite a bit of code
 End Sub

 Sub Proc3()
      ... ' quite a bit of code
 End Sub

2010-09-20 12:07

Идея GiantProcedure не работала для меня, используя Microsoft Powerpoint 2013. Затем я добавил «вызов» перед каждым «процессом». Как это:

Sub GiantProcedure()

  Call Proc1()
  Call Proc2()
  Call Proc3()

End Sub

Теперь это работает.

2018-04-02 16:17

Не уверен, почему рекомендуется публикация, получившая наибольшее количество голосов, поскольку гигантский код процедуры для меня тоже не работал. В итоге сработало вот что:

Sub MacroName

Call ProcedureName1
Call ProcedureName2
etc...

End Sub

Sub ProcedureName1
'insert your vba code here

End Sub

Оператор Call вытягивал каждую из подпрограмм, пока я вызывал правильную именованную подпрограмму.

2020-12-06 03:31

Решено, что процедуры следует разбивать на более мелкие подпрограммы, а не на один гигантский кусок кода.

Я видел слишком большие ошибки процедуры в 32-битных установках Excel, когда они отлично запускаются / компилируются на 64-битной установке, код был написан, поэтому, если это случай процедуры, слишком большая ошибка [внезапно] возникает, и она раньше работала, проверьте, является ли установка Excel, на которой вы пытаетесь запустить код, 32-разрядной версией, и попробуйте вместо нее 64-разрядную установку.

2021-02-17 02:41

Используйте 32-разрядную версию программы Office 2010 или разделите модуль на части

2021-10-06 16:55

Мои сотрудники получают то же сообщение на своих более слабых компьютерах, а я — на моем. Так что либо тот факт, что объем памяти на вашем компьютере меньше 16 ГБ, либо тот факт, что у вас слишком слабый процессор Intel. Процессор i7 Intel после 8-го поколения определенно должен справиться со своей задачей. Просто купите лучший компьютерный процессор и лучшую память на вашем компьютере, и он будет работать.

2021-05-17 23:45

Источник

I received this error message —Procedure too large — in VBA. What is the reason and way out for this error?

Dirk Vollmar's user avatar

Dirk Vollmar

172k53 gold badges255 silver badges315 bronze badges

asked Sep 20, 2010 at 12:01

Sourav's user avatar

You probably have one or more gigantic procedures/functions and I think VBA has a limit of 64k or something per procedure.

You fix it by splitting that procedure up into multiple procedures that can then be called by the one procedure.

So instead of having:

 Sub GiantProcedure()
      ... ' lots and lots of code
 End Sub

You’d have something like:

 Sub GiantProcedure()
      ... ' a little bit of common code
      Proc1()
      Proc2()
      Proc3()

 End Sub

 Sub Proc1()
      ... ' quite a bit of code
 End Sub

 Sub Proc2()
      ... ' quite a bit of code
 End Sub

 Sub Proc3()
      ... ' quite a bit of code
 End Sub

answered Sep 20, 2010 at 12:07

Hans Olsson's user avatar

Hans OlssonHans Olsson

54.1k15 gold badges93 silver badges115 bronze badges

1

The idea of GiantProcedure didn’t work for me, using Microsoft Powerpoint 2013.
Then I added a «call» before each «proc». Like this:

Sub GiantProcedure()

  Call Proc1()
  Call Proc2()
  Call Proc3()

End Sub

Now, it works.

answered Apr 2, 2018 at 16:17

Siamak's user avatar

SiamakSiamak

3171 silver badge11 bronze badges

Not sure why the top voted post is recommended as the giant procedure code didn’t work for me either. What did end up working was this:

Sub MacroName

Call ProcedureName1
Call ProcedureName2
etc...

End Sub

Sub ProcedureName1
'insert your vba code here

End Sub

The Call statement pulled each of the subs as long as I called the correct named sub.

answered Dec 6, 2020 at 0:31

Deniz Balta's user avatar

Agreed, procedures should be split into smaller subs instead of one gigantic piece of code.

I have seen the Procedure too large errors on 32 bit installations of Excel, when they run / compile perfectly fine on the 64 bit installation the code was written on — so if it is a case of Procedure too large error [suddenly] occurring and it used to work before, check if the Excel installation you are trying to run the code on is 32 bit, and try on a 64 bit installation instead.

answered Feb 16, 2021 at 23:41

Ben Carroll's user avatar

Use a 32-bit version of an Office 2010 program
Or Divide the module into parts

answered Oct 6, 2021 at 13:55

abdomohamed's user avatar

My employees get the same message on their weaker computers, but I don’t on mine. So it’s either the fact that the memory on your computer is less than 16GB or otherwise it’s the fact that you have an intel processor that’s too weak. An i7 intel processor that’s after the 8th generation should definitely get the job done. Just get a better computer processor and better memory on your computer and it will work.

answered May 17, 2021 at 20:45

Ahmedakhtar11's user avatar

Return to VBA Code Examples

This article will explain the VBA procedure too large error.

vba procedure too large

There are 2 main reasons that this error can appear in Excel VBA.

Check Excel Version

This error can occur if you are running a 32-bit version of Excel, but you have a very large procedure which would be better off being run in a 64-bit environment.

Just because your PC is 64-bit does not necessarily mean that your version of the Microsoft Office is 64-bit, so you will need to check your Excel version.

In the Ribbon, select File, then (1) select Account and then (2) About Excel.

vba procedure account

Check in the information that appears about Excel – it will let you know if you are running the 32-bit or 64-bit version of Office.

vba procedure excel version

If you need to upgrade to the 64-bit version, make sure that it will not effect any add-ins or other macros that you use. Most add-ins and macros need to be customized to run on the 64-bit version of office and older add-ins may not run at all.

Decrease the Size of Your Procedure

The other main reason is that your Procedure is, simply, too large! You need to try and decrease the size of your procedure by using Loops or Nested Procedures instead of having the code all in a single procedure.

You can for example break down your code into separate procedures and then call the separate procedures in your main procedure.

For example:

vba procedure call procedures

Структура файла Excel следующая:  
отображаются нужные строки с именами в зависимости от выбранных условий в табличке.  

  Нашел стандартный код для реализации:  

  Private Sub Worksheet_SelectionChange(ByVal Target As Range)  
If Range(«f3»).Value = «Да» Then  
Rows(«2»).EntireRow.Hidden = True  
Rows(«3»).EntireRow.Hidden = True  
Else  
Rows(«2»).EntireRow.Hidden = False  
Rows(«3»).EntireRow.Hidden = False  
End If  
End Sub  

    Все бы ничего, но условий пара десятков, а строк попадающих под Скрыть/Показать около сотни. Т.е код сам простой, но его доволно много, т.к. много If-ов и вот этих строк Rows(«N»).EntireRow.Hidden = True/False.  

  В итоге на полпути компилятор стал ругаться, выдавая «Procedure too large».  

  Стал гуглить, на форумах всплыла подсказка, мол, если свыше 1000 строк в коде, то процедура считается перегруженной и следует разбить код на несколько процедур, а затем соединить их событием клика.  
И описали:  

  Private Sub A  
…  
End Sub  

  Private Sub B  
…  
End Sub  

  Private Sub cmbGo_Click()  
A:B  
End Sub  

    Я было обрадовался, но не тут то было.  
Фигня в том, что Worksheet_SelectionChange — это базовая процедура, обращающаяся к своим переменным и каким-то библиотекам.  
Т.е. переименовать ее на №1, №2 и т.д. — нельзя.  
Тогда напрашивалось очевидное: обернуть блоки кода в новые процедуры, обозначая в каждом блоке, что работаем в этом самом Worksheet_SelectionChange:  

  Private Sub A  
Private Sub Worksheet_SelectionChange(ByVal Target As Range)  
…  
End Sub  
End Sub  

  Private Sub B  
Private Sub Worksheet_SelectionChange(ByVal Target As Range)  
…  
End Sub  
End Sub  

  но и так васёк ругается, видимо нельзя содержать процедуру в процедуре:(  

      Вот и дошли до вопроса:    
КАК БЫТЬ?  
Если никак иначе кроме разбивки на подпроцедуры не обойти ошибку «Procedure too large», то как грамотно разделять их выполняя по сути все ту же Worksheet_SelectionChange?  
Есть ли другие решения задачи?

title keywords f1_keywords ms.prod ms.assetid ms.date ms.localizationpriority

Procedure too large

vblr6.chm1018969

vblr6.chm1018969

office

1de6ac64-fc0c-9833-024c-44b3b5c68a79

06/08/2017

medium

When compiled, the code for a procedure can’t exceed 64K. This error has the following cause and solution:

  • Code for this procedure exceeds 64K when compiled. Break this, and any other large procedures, into two or more smaller procedures.

For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).

[!includeSupport and feedback]

Procedure Too Large

Procedure Too Large Error Message Fix

How to Fix Compile Error: Procedure Too Large

The error message «Compile error: Procedure too large» appears in Excel 2007, 2010, and 2013 when you try to execute more than 3,000 lines of code in a single module. This pop-up error message is annoying for developers who are building complex programs. According to the Microsoft Website, this error message occurs when you try to execute code «that exceeds 64kb when compiled.» I found a simple solution that fixes this error message and allows you to execute an unlimited amount of code in VBA.

Fix: Link Multiple Modules Together in VBA
To bypass the Procedure Too Large error message, simply link two modules or subprocedures together from separate workbooks.

1. In Workbook1, open up Workbook2 with the below code:
Workbooks.Open Filename:=»C:UsersDesktopVBATutorialCode LessonsReference Second Module» & «Workbook2.xlsm»

2. In Workbook1, run the code for the Module in Workbook2 from Workbook1:
Application.Run «Workbook2.xlsm» & «!SecondModule»
3. !SecondModule is the name of the module from the second workbook as pictured below:
Link Module

Video Fix

If you want to watch me link two modules together in VBA from different workbooks, then watch the below video from my Youtube. I recommend that you switch the video quality to 1080P HD. If you are on mobile, please watch the video on My Youtube because embedded videos on my website have worse quality on mobile than the video on Youtube.

Источник

Понравилась статья? Поделить с друзьями:
  • Probe open ошибка фанук
  • Prado 150 ошибка p0093
  • Procedure formcreate sender tobject ошибка
  • Pro tools ошибка 6117
  • Pro tools run out of cpu power ошибка