CODEWITHSUNDEEP
vbaexcel
Steven Martin
Steven Martin

Reputation: 3272

vba sub slow after first execution

I have seen others with similar questions with no real answer.

When i run a sub that imports data onto 3 worksheets and then compiles a 4th sheet from those 3 worksheets it runs in just over 17secs.

The main part of the 17 secs is writing 10,000 rows (26 columns each) of data to the output sheet in 13.86sec doing just over 720 rows a second.

The problem arises when i run this sub again without closing excel , it takes forever! the import stage is no slower but writing to the output sheets slows down to a consistently poor 17 rows a second. (35X decrease in speed)

i have tested every thing i know about VBA and i cannot find out why this is.

the start of the Sub always erases each sheet using

If sh.AutoFilterMode Then sh.AutoFilterMode = False
sh.Columns.Hidden = False
sh.Rows.Hidden = False
sh.Cells.Clear  
sh.Cells.Delete

i have always used this to clear sheets and never had an issue, until i found a fix for my problem , by actually deleting the 4 sheets and recreating them ,this fixes the terrible slowdown in speed.

What is left behind on my sheets that the above code is not removing and causing this slowdown issue?

I have also left the sheets full of data and saved and closed the workbook, and i still get perfect performance on the first run after opening excel so its not the actual data on the sheets.

NB - The rest of the code is just standard loops and writing to cells and i am 100% sure that its not the problem with this issue

Upvotes: 3

Views: 1035

Answers (2)

developerX
developerX

Reputation: 21

It dates back a long time, but anyway: Today I have faced the same issue with my VBA-tool (Excel 2010, invoking via UF, Import of > 40000 lines of data and performing of some calculations): -> Runtime at 1st run: 40sec and 11MIN at 2nd run (after the UF was closed and opened again). I was able to solve the issue by erasing explicitly the arrays I have used in the code via the "Erase" command and setting the WorkBook-object to "Nothing" explicitly before the UF is closed.

Example:

*Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If CloseMode = vbFormControlMenu Then Logger_End

    Erase allAsmFileREVsArray()
    Erase allAsmFileURLsArray()
    
    Set xlWorkBook = Nothing
End If

End Sub*

Maybe it helps...

Upvotes: 1

PaichengWu
PaichengWu

Reputation: 2689

based on you description, i think the possible solutions as below.

  1. put application.screenupdating = false at the first of your sub, and application.screenupdating = true before exit sub.
  2. put application.calculation = xlCalculationManual at first of your sub, and application.calculation = xlCalculationAutomatic before exit sub.

  3. Using array variable. If you want to input a 100000 x 26 to range("A1:Z100000"), the normal and slow way as below

    for i = 1 to 100000
    for j = 1 to 26
        cells(i,j).value = 123
    next j
next i

    the fast way is

    dim ary(100000 - 1, 26 -1) as variant
'make an array
for i = lbound(ary, 1) to ubound(ary, 1)
    for j = lbound(ary, 2) to ubound(ary, 2)
       ary(i, j) = 123
    next j
next i

range("a1").resize(ubound(ary, 1) - lbound(ary, 1) + 1, ubound(ary, 2) - lbound(ary, 2) +1).value = ary

Upvotes: 0

Related Questions