VBA代码优化

Question

我的代码是用VBA编写的，它的行数很大，占用的内存太多--它只占用很少的GB空间就会造成崩溃。代码取数字，向服务器请求XLM，找到一些数据并将其写入，然后转到另一个数字。如果超过500行，它就会崩溃。你能帮我优化代码来处理大约10,000行吗？谢谢你的帮助，马瑞克

Sub ares()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False 'potlačí obnovování obrazovky
Application.DisplayAlerts = False 'potlačí varovné hlášky
Application.DisplayStatusBar = False
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False

Dim i As Integer
Dim row As Integer
Dim column As Integer

For i = 2 To 15000

    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "ares"
    Sheets("ares").Activate

    On Error GoTo ErrorHandler
    ActiveWorkbook.XmlImport URL:="http://wwwinfo.mfcr.cz/cgi-bin/ares/darv_bas.cgi?ico=" & Worksheets("ico").Cells(i, 1).Value, ImportMap:=Nothing, Overwrite:=True, Destination:=Range("$A$1")

    If Worksheets("ares").Cells(2, 10).Value = "" Then

        Worksheets("ico").Cells(i, 2).Value = "OK"


        row = 2
        column = 3
        Do While Worksheets("ares").Cells(row, 1).Value <> ""
            If Worksheets("ares").Cells(row, 167).Value <> "" Then
                Worksheets("ico").Cells(i, column).Value = Worksheets("ares").Cells(row, 167).Value
                column = column + 1
            End If

        row = row + 1
        Loop
    Else
        Worksheets("ico").Cells(i, 2).Value = Worksheets("ares").Cells(2, 10).Value
    End If
ErrorResume:

    Sheets("ares").Delete

Next i

Application.ScreenUpdating = True 'zapne obnovování obrazovky
Application.DisplayAlerts = FaTruelse 'obnoví varovné hlášky
Application.Calculation = xlCalculationAutomatic
Application.DisplayStatusBar = True
Application.EnableEvents = True
ActiveSheet.DisplayPageBreaks = True


Exit Sub
ErrorHandler:
    Worksheets("ico").Cells(i, 2).Value = "Jiná chyba"
Resume ErrorResume

End Sub

Answer 1

删除Sheets("ares").Activate并将Integer更改为Long。这应该足够了。

您不需要激活工作表，只要您在任何地方都正确地引用了工作表。

关于整数，它给了你一个很小的加速- Why Use Integer Instead of Long?