宏运行缓慢
问题描述:
我有一个简单的宏,用来在一分钟内运行。但现在运行速度非常缓慢。大约需要一个小时才能运行。这是因为我使用的循环?有人能帮我看看出了什么问题吗?宏运行缓慢
Sub Runtable()
Sheets("RateTable").Cells(1, "A") = "ID"
Sheets("RateTable").Cells(1, "B") = "Section"
Sheets("RateTable").Cells(1, "C") = "Gender"
Sheets("RateTable").Cells(1, "D") = "Age"
'
LastID = Sheets("Input").Cells(2, 22)
For ID = 0 To LastID
LastSet = Sheets("Input").Cells(2, 19)
For myRow = 2 To LastSet
Sheets("RateTable").Cells(ID * (LastSet - 1) + myRow, 1) = Sheets("Input").Cells(ID + 2, 1)
Next myRow
Next ID
'
Dim myMyRow As Long
Dim OutputMyRow As Long
OutputMyRow = 2
LastID = Sheets("Input").Cells(2, 22)
LastSection = Sheets("Input").Cells(2, 21)
LastAge = Sheets("Input").Cells(2, 20)
For ID = 0 To LastID
For Section = 0 To LastSection
For myMyRow = 2 To LastAge
Sheets("RateTable").Cells(OutputMyRow, 2).Value = Sheets("Input").Cells(Section - FirstID + 2, "N").Value
OutputMyRow = OutputMyRow + 1
Next myMyRow
Next Section
Next ID
'
EndGenderLoop = Sheets("Input").Cells(2, 23)
For myRow = 2 To EndGenderLoop
Sheets("RateTable").Cells(myRow, 3) = Sheets("Input").Cells(2, 17)
Next myRow
'
EndAgeLoop = Sheets("Input").Cells(2, 24)
For AgeCurve = 0 To EndAgeLoop
'
For myRow = 2 To 52
Sheets("RateTable").Cells(AgeCurve * 51 + myRow, 4) = Sheets("Input").Cells(myRow, 10)
Next myRow
Next AgeCurve
'
End Sub
答
使用状态栏来确定代码放慢的位置。 Here's one site with simple code(在链接失败的情况下包含在下面),但还有很多其他的。对于代码来说,与以前相比,现在运行速度慢了60倍,可能表明计算机出了问题。你重新启动了吗?你能恢复到以前的备份状态吗?
Option Explicit
Sub StatusBar()
Dim x As Integer
Dim MyTimer As Double
'Change this loop as needed.
For x = 1 To 250
'Dummy Loop here just to waste time.
'Replace this loop with your actual code.
MyTimer = Timer
Do
Loop While Timer - MyTimer < 0.03
Application.StatusBar = "Progress: " & x & " of 250: " & Format(x/250, "Percent")
DoEvents
Next x
Application.StatusBar = False
End Sub
关闭计算并在开始时禁用事件和屏幕更新,请务必在最后关闭所有内容。 –