Solved

Huge performance difference when filling a workbook with VBA

Posted on 2010-09-01
12
366 Views
Last Modified: 2012-05-10
Hi,

I have developed a small VBA application that reads a text file which contains values for cells in Excel.

The problem is that i see huge perfomance differences:

- Filling a blank workbook takes about 3 Minutes (12 columns, 40000 rows = 480000 cells filled)
- But if iI use a filled worbook, rename the filled sheet, insert a new sheet with the filling target sheet name and start my filling program again using the modified workbook as target, then the filling completes in about 5 seconds.

Additionally i found out that the time for filling of a blank workbook goes down to ~45sec (~25%) when reducing the rows amout to 20000 (50%)
So filling of the last cell takes much longer than filling of the first cell.

Why is it faster to fill a huge Workbook than to fill a blank one, a why is filling of the last cell so much more expensive?
Is there a way to work around this behavior?


Environment: Win XP Pro, Excel 2003 Pro
0
Comment
Question by:finaris
12 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33575395
Well, without seeing your code it will be nearly impossible to say :)
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 33575404
A reason I found that would make this operation extremely slow is when you have linked values, usually with vlookup, and are performing row operations. I don't know if that's the case, but it's a possible reason.
Also, the time taken to perform any long task in windows will always grow exponentially. This is because the resources you're using (which are freely and abundantly available at first) become more and more scarce, forcing the system to start "rationing" RAM and CPU with the rest of the operations it's running.

It would help if you could post a simple sample, or even just your VBA code. Maybe we can help speed it up.
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 33575416
Why is because, I believe, when filling it from blank, you are redefining constantly the UsedRange property, something Excel stores somewhere when working with a worksheet, constantly.

When you modify a sheet which already has its USedRange defined accordingly, this does not play a role.

Workaround:

Depending on how you fill your worksheet, you could use something like this

Dim mArray as Variant
'Create blank book ...
mArray = Workbooks("MyBook.xls").Worksheets("Sheet1").Range("A1:L40000")
'Fill your array
For i = 1 to 12
    For j = 1 to 40000
       mArray(j,i) = i*j 'Just for demonstration
    Next j
Next i
Workbooks("MyBook.xls").Worksheets("Sheet1").Range("A1:L40000") = mArray
0
 
LVL 12

Expert Comment

by:Alan3285
ID: 33575424
Hi finaris,

You don't provide your code, so difficult to be sure, but try re-writing it so that it fills the bottom right cell first (perhaps even just with a dummy value like 99999 before starting at the top and getting back down there and over-writing the dummy value).

It might be that starting with an empty sheet and moving out from A1 means you are constantly expanding the usedrange of the sheet and that adds additional processing time.

Give it a go and see if it helps - the additional code might be just a single line thus:

Worksheet("MySheetName").Range("X5000").Value = 99999

Obviously inserting your own values for sheetname and range.

If you don't know where it will end up (bottom right), try using a value that will always be far below and to the right of what might happen if you can.

HTH,

Alan.
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 33575452
Just tested 2 methods of doing the same:

Method 1 (as described above) --> 1 second

Sub Test1()
Dim mArray As Variant
'Create blank book ...
Set mb = Workbooks.Add
Set mSh = mb.Worksheets("Sheet1")
mArray = mSh.Range("A1:L40000")
'Fill your array
For i = 1 To 12
    For j = 1 To 40000
       mArray(j, i) = i * j 'Just for demonstration
    Next j
Next i
mSh.Range("A1:L40000") = mArray
End Sub

Method 2 (writng cell per cell) --> more than 3 minutes, I actually stopped it after 3 minutes, it was still busy on the 1st column.

Sub Test1()
Dim mArray As Variant
'Create blank book ...
Set mb = Workbooks.Add
Set mSh = mb.Worksheets("Sheet1")
'Fill your array
For i = 1 To 12
    For j = 1 To 40000
       mSh.Cells(j, i) = i * j 'Just for demonstration
    Next j
Next i
End Sub

0
 

Author Comment

by:finaris
ID: 33575765
Some source code simplified to the problem below:

Public Sub test()
Dim t1 As Currency
Dim t2 As Currency
Const rows As Long = 20000

t1 = Timer

 Dim mArray(1 To rows, 1 To 12) As String
 'Create blank book ...
 Application.ScreenUpdating = False
 
 'Fill your array
 For i = 1 To 12
     For j = 1 To rows
        mArray(j, i) = i & "/" & j
     Next j
 Next i
 ActiveSheet.Range("A1:L" & rows).FormulaR1C1 = mArray
 t2 = Timer
 Debug.Print "Execution completed in " & (t2 - t1) & "sec"
 
 Application.ScreenUpdating = True
End Sub

In the meantime i found out that the quickness of filling a filled sheet results for equal cell values already existing in the sheet.

BUT, still the even bigger problem, regarding the example code above:

when manipulating the rows CONSTANT from e.g. 5000 to 10000, the overall runtime becomes about 4 times as high
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 17

Accepted Solution

by:
calacuccia earned 500 total points
ID: 33575871
The is three times faster (look at the Variant array declaration and the initialization of the array).

Public Sub test1()
Dim t1 As Currency
Dim t2 As Currency
Dim mArray As Variant
Const rows As Long = 20000

t1 = Timer

 
 'Create blank book ...
 Application.ScreenUpdating = False
 mArray = ActiveSheet.Range("A1:L" & rows)
 'Fill your array
 For i = 1 To 12
     For j = 1 To rows
        mArray(j, i) = i & "/" & j
     Next j
 Next i
 ActiveSheet.Range("A1:L" & rows).FormulaR1C1 = mArray
 t2 = Timer
 Debug.Print "Execution completed in " & (t2 - t1) & "sec"
 
 Application.ScreenUpdating = True
End Sub
0
 

Author Comment

by:finaris
ID: 33576457
looks a bit better, but does not solve the base problem.

I tried your example code with the following results:

Execution completed in 0.8047sec   for  rows = 10000
Execution completed in 7.293sec     for rows = 20000

I dont understand why the required time is 9 times higher when i just double the amount of cells.
0
 

Author Comment

by:finaris
ID: 33576680
ok the last thing i figured out myself, this was because the value the test entered was partly interpreted as a date and partly not.

replacing "/" by "a" causes the algorithem to consume equal time as the one above.
0
 
LVL 17

Assisted Solution

by:calacuccia
calacuccia earned 500 total points
ID: 33576815

Doing this goes makes the ratio completely different again:
 mArray(j, i) = i + j

The exponential raise when you increase the complexity of the task must have to do with available RAM and Excel checking dependencies ; formula chains when you create them and so on ... as pointed out by Cluskit in the 2nd post, there is an exponential behaviour.

The more simply operation summing two numbers and just having to write a number into the range apparently does not make Excel heavily rely on RAM, so there is no huge difference between 10k rows and 20k rows, however creating more demanding cell values can make the exponential behaviour be triggered.
0
 

Author Comment

by:finaris
ID: 33578252
That's what i've experienced too, numbers are inserted quickly there just seems to be an issue with the strings.

Any idea for a workaround of this?

I'll try to split the work into multiple parts and reopen the workbook multiple times. As it seems the overhead of that operation should be little in comparison to the exponential growth of time.
0
 

Author Closing Comment

by:finaris
ID: 33588038
Part of probelm still remains
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now