VBA code optimalization

Question

I have code in VBA and with large number of rows it use too much memory - it can take few GB and make crush. Code take number ask server for XLM find some data and write them than go to another number. If there is more than 500 rows it crush. Could you help me optimalize code to handle about 10 000 rows? Thank you for your help Marek

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

`Application.DisplayAlerts = FaTruelse 'obnoví varovné hlášky` - that's a new boolean :) — Vityata, Oct 05 '17 at 09:36
Any way you can import data and set your ico cell without adding and deleting a sheet evry time. maybe reuse a sheet ? — MacroMarc, Oct 05 '17 at 09:46

Vityata · Answer 1 · 2017-10-05T12:13:02.373

0

Remove Sheets("ares").Activate and change Integer to Long. It should be enough.

You do not need to activate worksheet, as far as you are referring to a worksheet everywhere correctly.

Concerning the Integer, it gives you a minor speed up - Why Use Integer Instead of Long?

edited Oct 05 '17 at 12:13

answered Oct 05 '17 at 09:37

Vityata

42,633
8
55
100

I did it and it might be little useful but not enought. If I make it run for 1000 rows final file is about 60 000 KB big while normal exel with same data is few hunderts KB big, but I was not able to find out why it is so big. – Marek Mach Oct 05 '17 at 12:12

VBA code optimalization

1 Answers1