获取错误在VBA宏（Excel）中过程过大 [英] Getting error Procedure too large in VBA Macros (Excel)

查看：227
本文介绍了获取错误在VBA宏（Excel）中过程过大的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述



我正在使用MS-Excel 2003

解决方案

如果您的程序超过64kb，您将收到该错误。这些是您可以压缩代码的一些东西



1）摆脱重复代码。参见这个例子

  Sub Sample（）
 Range（A1）=Blah Blah
范围（A2）=Blah Blah
范围（A3）=Blah Blah
范围（A4）=Blah Blah
范围（A5 ）=Blah Blah
范围（A6）=Blah Blah
范围（A7）=Blah Blah
 End Sub

此代码可以写成

 子样本（）
对于i = 1至7 
范围（A& i）=Blah Blah
下一个i 
 End Sub

另一个例子

 子样本（）
范围（A1）=（范围（A1）* 10）+（范围（A1）+ 30）+（范围（A1）/ 30）
范围（A5）=（范围（A5）* 10）+（范围（A5）+ 30）+（范围（A5）/ 30）
范围（A11 =（范围（A11）* 10）+（范围（A11）+ 30）+（范围（A11）/ 30）
范围（A6）= ）*（范围（A6）+ 30）+（范围（A6）/ 30）
范围（A8）=（范围（A8）* 10）+ （一个（范围（A56）+ 30）+（范围（A8）/ 30）
范围（A56）=（范围（A56）* 10）+ （范围（A56）/ 30）
 End Sub

作为

  Sub Sample（）
 Range（A1）= GetVal（Range（A1））
范围（A5）= GetVal（范围（A5））
范围（A11）= GetVal（范围（A11））
范围（A6）= GetVal（Range（A56））
 Range（A8）= GetVal（Range（A8））
 Range（A56）= GetVal b $ b End Sub 
 
函数GetVal（rng As Range）As Variant 
 GetVal =（rng.Value * 10）+（rng.Value + 30）+（rng.Value / 30 ）
结束函数

这将确保您减少空间，不要重复写入代码。



2）如果您通过宏生成代码，那么您可能会得到这样的东西。摆脱无用的代码，如 ActiveWindow.ScrollRow = 8968 

 选项显式
 
'~~>此过程用随机值填充Excel的10000个单元格，然后删除副本
 Sub FillExcelCells（）
 Dim rowCount As Long 
 
'~~>激活必需工作表
表单（Sheet1）。激活
 
'~~>循环遍历所有单元格并存储随机数
对于rowCount = 1至10000 
表（Sheet1）。范围（A& rowCount）。选择
表格（Sheet1 ）.Range（A& rowCount）.Value = Int（（10000  -  1）* Rnd（）+ 1）
下一个rowCount 
 
'~~>排序范围
表（Sheet1）。范围（A1）。选择
表格（Sheet1）。范围（选择，选择.End（xlDown））。选择
 Application.CutCopyMode = False 
 
范围（Selection，Selection.End（xlDown））。选择
 ActiveWindow.SmallScroll Down：=  -  39 
 ActiveWindow.ScrollRow = 9838 
 ActiveWindow.ScrollRow = 9709 
 ActiveWindow.ScrollRow = 9449 
 ActiveWindow.ScrollRow = 8968 
 ActiveWindow.ScrollRow = 8319 
 ActiveWindow.ScrollRow = 7245 
 ActiveWindow.ScrollRow = 6003 
 ActiveWindow.ScrollRow = 4818 
 ActiveWindow.ScrollRow = 4040 
 ActiveWindow.ScrollRow = 3317 
 ActiveWindow.ScrollRow = 3076 
 ActiveWindow.ScrollRow = 2521 
 ActiveWindow.ScrollRow = 2298 
 ActiveWindow.ScrollRow = 2113 
 ActiveWindow.ScrollRow = 1724 
 ActiveWindow.ScrollRow = 1372 
 ActiveWindow.ScrollRow = 1038 
 ActiveWindow.ScrollRow = 872 
 ActiveWind ow.ScrollRow = 668 
 ActiveWindow.ScrollRow = 538 
 ActiveWindow.ScrollRow = 464 
 ActiveWindow.ScrollRow = 446 
 ActiveWindow.ScrollRow = 427 
 ActiveWindow.ScrollRow = 409 
 ActiveWindow.ScrollRow = 390 
 ActiveWindow.ScrollRow = 353 
 ActiveWindow.ScrollRow = 334 
 ActiveWindow.ScrollRow = 297 
 ActiveWindow.ScrollRow = 279 
 ActiveWindow .ScrollRow = 242 
 ActiveWindow.ScrollRow = 223 
 ActiveWindow.ScrollRow = 205 
 ActiveWindow.ScrollRow = 168 
 ActiveWindow.ScrollRow = 149 
 ActiveWindow.ScrollRow = 112 
 ActiveWindow.ScrollRow = 94 
 ActiveWindow.ScrollRow = 57 
 ActiveWindow.ScrollRow = 20 
 ActiveWindow.ScrollRow = 1 
 
 Selection.Sort Key1：= Sheets （Sheet1）。Range（A1），Order1：= xlAscending，Header：= xlGuess，_ 
 OrderCustom：= 1，MatchCase：= False，Orientation：= xlTopToBottom，_ 
 DataOption1： = xlSortNormal 
 
 ~~>删除重复
对于rowCount = 10000到2步骤-1 
表格（Sheet1）。范围（A& rowCount）。选择
如果Range（A& rowCount ）.Value = Range（A& rowCount  -  1）.Value Then 
 Sheets（Sheet1）。Rows（rowCount）.Delete shift：= xlUp 
 End If 
 Next rowCount 
 End Sub

以上可以写成

 '~~>此过程使用随机值填充Excel的10000个单元格，然后删除副本
 Sub FillExcelCells（）
 Dim rowCount As Long 
 
带表格（Sheet1）
' ~~>循环遍历所有单元格并存储随机数
对于rowCount = 1 To 10000 
 .Range（A& rowCount）.Value = Int（（10000  -  1）* Rnd（）+ 1） 
下一个rowCount 
 
'~~>排序范围
 .Range（A1：A10000）。排序Key1：=。范围（A1），Order1：= xlAscending，标题：= xlGuess，_ 
 OrderCustom：= 1，MatchCase： = False，方向：= xlTopToBottom，DataOption1：= xlSortNormal 
 
'~~>删除重复
对于rowCount = 10000到2步-1 
如果.Range（A& rowCount）.Value = .Range（A& rowCount  -  1）.Value Then 
 .Rows（rowCount）.Delete shift：= xlUp 
 End If 
 Next rowCount 
 End with 
 End Sub 
  pre> 
 
 
  3）声明对象，以便您不必继续重复。参见此示例
 子样本（）
范围（A1）。选择
 ActiveCell.FormulaR1C1 =sdasds
 Range（A1）。选择
 With Selection.Interior 
 .Pattern = xlSolid 
 .PatternColorIndex = xlAutomatic 
 .Color = 65535 
 .TintAndShade = 0 
 .PatternTintAndShade = 0 
结束
 Selection.Font.Bold = True 
 Selection.Font.Italic = True 
 Selection.Font。下划线= xlUnderlineStyleSingle 
带选择
 .Horizo​​ntalAlignment = xlGeneral 
 .VerticalAlignment = xlBottom 
 .WrapText = True 
 .Orientation = 0 
 .AddIndent = False 
 .IndentLevel = 0 
 .ShrinkToFit = False 
 .ReadingOrder = xlContext 
 .MergeCells = False 
 End with 
 End Sub 
  
这可以写成
  Sub Sample（）
 Dim ws作为工作表，rng作为范围
 
设置ws = Sheet1 
 
设置rng = ws.Range（A1）
 
带有rng 
 .FormulaR1C1 =sdasds
带.Interior 
 .Pattern = xlSolid 
 .PatternColorIndex = xlAutomatic 
 .Color = 65535 
 .TintAndShade = 0 
 .PatternTintAndShade = 0 
结束
 .Font.Bold = True 
 .Font.Italic = True 
 .Font.Underline = xlUnderlineStyleSingle 
 .Horizo​​ntalAlignment = xlGeneral 
 .VerticalAlignment = xlBottom 
 .WrapText = True 
 .Orientation = 0 
 .AddIndent = False 
 .IndentLevel = 0 
 .ShrinkToFit = False 
 .ReadingOrder = xlContext 
 .MergeCells = False 
 End with 
 End Sub 
  
  4）如果需要，分解你的程序。并从第一个
 
 
 
  5）呼叫第二个步骤。避免使用。选择和 .Activate 他们不仅使您的代码缓慢，而且在广泛使用的代码中也占用了大量空间。 如何避免使用Excel中的选择VBA宏 
 
I am getting Procedure too Large Error in a VBA macro.



I am using MS-Excel 2003.
 解决方案 
You will get that error if your procedure is more than 64kb. These are some of the things that you can to compact your code



1) Get rid of repetitive code. See this example
Sub Sample()
    Range("A1") = "Blah Blah"
    Range("A2") = "Blah Blah"
    Range("A3") = "Blah Blah"
    Range("A4") = "Blah Blah"
    Range("A5") = "Blah Blah"
    Range("A6") = "Blah Blah"
    Range("A7") = "Blah Blah"
End Sub

This code can be written as 
Sub Sample()
    For i = 1 To 7
        Range("A" & i) = "Blah Blah"
    Next i
End Sub

Another example
Sub Sample()
    Range("A1") = (Range("A1") * 10) + (Range("A1") + 30) + (Range("A1") / 30)
    Range("A5") = (Range("A5") * 10) + (Range("A5") + 30) + (Range("A5") / 30)
    Range("A11") = (Range("A11") * 10) + (Range("A11") + 30) + (Range("A11") / 30)
    Range("A6") = (Range("A6") * 10) + (Range("A6") + 30) + (Range("A6") / 30)
    Range("A8") = (Range("A8") * 10) + (Range("A8") + 30) + (Range("A8") / 30)
    Range("A56") = (Range("A56") * 10) + (Range("A56") + 30) + (Range("A56") / 30)
End Sub

This code can be written as
Sub Sample()
    Range("A1") = GetVal(Range("A1"))
    Range("A5") = GetVal(Range("A5"))
    Range("A11") = GetVal(Range("A11"))
    Range("A6") = GetVal(Range("A6"))
    Range("A8") = GetVal(Range("A8"))
    Range("A56") = GetVal(Range("A56"))
End Sub

Function GetVal(rng As Range) As Variant
    GetVal = (rng.Value * 10) + (rng.Value + 30) + (rng.Value / 30)
End Function

This will ensure that you cut down on space and do not write repetitive code.



2) If you generated the code via the macro then you may get something like this. Get rid of the useless code like ActiveWindow.ScrollRow = 8968
Option Explicit

'~~> This procedure fills Excel's 10000 cells with random values and then removes the duplicates
Sub FillExcelCells()
    Dim rowCount As Long

    '~~> Activate the necesary Sheet
    Sheets("Sheet1").Activate

    '~~> Loop through all the cells and store random numbers
    For rowCount = 1 To 10000
        Sheets("Sheet1").Range("A" & rowCount).Select
        Sheets("Sheet1").Range("A" & rowCount).Value = Int((10000 - 1) * Rnd() + 1)
    Next rowCount

    '~~> Sort the Range
    Sheets("Sheet1").Range("A1").Select
    Sheets("Sheet1").Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False

    Range(Selection, Selection.End(xlDown)).Select
    ActiveWindow.SmallScroll Down:=-39
    ActiveWindow.ScrollRow = 9838
    ActiveWindow.ScrollRow = 9709
    ActiveWindow.ScrollRow = 9449
    ActiveWindow.ScrollRow = 8968
    ActiveWindow.ScrollRow = 8319
    ActiveWindow.ScrollRow = 7245
    ActiveWindow.ScrollRow = 6003
    ActiveWindow.ScrollRow = 4818
    ActiveWindow.ScrollRow = 4040
    ActiveWindow.ScrollRow = 3317
    ActiveWindow.ScrollRow = 3076
    ActiveWindow.ScrollRow = 2521
    ActiveWindow.ScrollRow = 2298
    ActiveWindow.ScrollRow = 2113
    ActiveWindow.ScrollRow = 1724
    ActiveWindow.ScrollRow = 1372
    ActiveWindow.ScrollRow = 1038
    ActiveWindow.ScrollRow = 872
    ActiveWindow.ScrollRow = 668
    ActiveWindow.ScrollRow = 538
    ActiveWindow.ScrollRow = 464
    ActiveWindow.ScrollRow = 446
    ActiveWindow.ScrollRow = 427
    ActiveWindow.ScrollRow = 409
    ActiveWindow.ScrollRow = 390
    ActiveWindow.ScrollRow = 353
    ActiveWindow.ScrollRow = 334
    ActiveWindow.ScrollRow = 297
    ActiveWindow.ScrollRow = 279
    ActiveWindow.ScrollRow = 242
    ActiveWindow.ScrollRow = 223
    ActiveWindow.ScrollRow = 205
    ActiveWindow.ScrollRow = 168
    ActiveWindow.ScrollRow = 149
    ActiveWindow.ScrollRow = 112
    ActiveWindow.ScrollRow = 94
    ActiveWindow.ScrollRow = 57
    ActiveWindow.ScrollRow = 20
    ActiveWindow.ScrollRow = 1

    Selection.Sort Key1:=Sheets("Sheet1").Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

    '~~> Delete duplicates
    For rowCount = 10000 To 2 Step -1
        Sheets("Sheet1").Range("A" & rowCount).Select
        If Range("A" & rowCount).Value = Range("A" & rowCount - 1).Value Then
            Sheets("Sheet1").Rows(rowCount).Delete shift:=xlUp
        End If
    Next rowCount
End Sub

The above can be written as
'~~> This procedure fills Excel's 10000 cells with random values and then removes the duplicates
Sub FillExcelCells()
    Dim rowCount As Long

    With Sheets("Sheet1")
        '~~> Loop through all the cells and store random numbers
        For rowCount = 1 To 10000
            .Range("A" & rowCount).Value = Int((10000 - 1) * Rnd() + 1)
        Next rowCount

        '~~> Sort Range
        .Range("A1:A10000").Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

        '~~> Delete duplicates
        For rowCount = 10000 To 2 Step -1
            If .Range("A" & rowCount).Value = .Range("A" & rowCount - 1).Value Then
                .Rows(rowCount).Delete shift:=xlUp
            End If
        Next rowCount
    End With
End Sub

3) Declare you Objects so that you don't have to keep on repeating them. See this example
Sub Sample()
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "sdasds"
    Range("A1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.Font.Bold = True
    Selection.Font.Italic = True
    Selection.Font.Underline = xlUnderlineStyleSingle
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub

This can be written as
Sub Sample()
    Dim ws As Worksheet, rng As Range

    Set ws = Sheet1

    Set rng = ws.Range("A1")

    With rng
        .FormulaR1C1 = "sdasds"
        With .Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        .Font.Bold = True
        .Font.Italic = True
        .Font.Underline = xlUnderlineStyleSingle
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub

4) Break Up your procedure if need be. and call the 2nd procedure from the 1st



5) Avoid using .Select and .Activate They not only make your code slow but also take a lot of space in your code if used extensively. How to avoid using Select in Excel VBA macros


                        
这篇关于获取错误在VBA宏（Excel）中过程过大的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
相关文章
Office最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆