我在一个VBA宏中遇到了过程太大错误
。
我正在使用MS-Excel 2003。
我在一个VBA宏中遇到了过程太大错误
。
我正在使用MS-Excel 2003。
如果你的过程超过64kb,你将会得到这个错误。以下是一些可以使您的代码紧凑的方法:
1) 消除重复的代码。 请参考此示例。
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
这段代码可以写成
Sub Sample()
For i = 1 To 7
Range("A" & i) = "Blah Blah"
Next i
End Sub
另一个例子
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
这段代码可以写成
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
这将确保你减少空间占用,避免编写重复的代码。
2) 如果您通过宏生成代码,则可能会得到类似于此的内容。 去除无用代码,如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
可以将上述内容写为:
'~~> 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)声明对象,以免不断重复它们。参见此示例。
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
这可以写成:
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) 如有需要,可将过程拆分,并从第一步调用第二个过程。
5) 避免使用 .Select
和 .Activate
,它们不仅会使您的代码变慢,而且如果广泛使用,还会占据大量代码空间。参考:如何在Excel VBA宏中避免使用Select
Round(ThisWorkbook.Worksheets("Sheet5").Range("NamedRange").Value, 5)
。我用一个单独的 integer
变量替换了它们,这样 Round(...)
只被调用了一次。这也使得这个方法更容易维护。现在它可能运行得更快了。 - Codes with Hammer宏的大小限制为64kb,超过此限制,Excel将会显示错误信息。
当我编写一个调用多个其他宏的宏时,遇到了一个问题,Excel没有给出任何解释或错误信息,导致Excel无法完全计算工作簿以满足资源需求。
我推测需要考虑所有宏链中所有宏的长度之和。