Huge performance difference when filling a workbook with VBA
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
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
Patrick Matthews
Well, without seeing your code it will be nearly impossible to say :)
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.
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.
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").Wo
'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").Wo
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").Wo
'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").Wo
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").R
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.
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").R
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.
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
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
ASKER
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
'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
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
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
'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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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.
replacing "/" by "a" causes the algorithem to consume equal time as the one above.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
Part of probelm still remains