# Why is Writing to cells using for loop very slow

Sorry about the formatting, I don't know what went wrong. Using LO 5.4 I have a couple of identical for loops, one in Excel and the other in Calc that do the same thing. This loop in Calc takes about 10 times longer to run than the same loop in Excel. Both Loops are posted below. I suspect that the way I am writing to each cell in Calc may be inefficient - this line:

 ThisComponent.Sheets(0).getCellByPosition(i,j).String = k


Is there a more efficient method of writing to each cell in Calc Basic or do macros in calc run slower than similar macros in Excel by nature ? I am looking at doing all of my spreadsheet consulting with Calc instead of Excel if possible. Just doing some comparisons at this point.

In Calc:

for i = 0 to 3 step 1
for j = 0 to 1500 step 1
k = j + sqr(j)
ThisComponent.Sheets(0).getCellByPosition(i,j).String = k
next j
next i


In Excel:

For c = 1 To 4 Step 1
For r = 1 To 1500 Step 1
k = j + sqrt(j)
Sheet2.Cells(r, c) = k
Next r
Next c

edit retag close merge delete

Sort by » oldest newest most voted

I dont't have any Excel. But I would assume VBA in Excel can be more efficient than StarBasic in Calc for many reasons.

However the BASIC code given by the OQ for Calc is surely not optimized towards efficiency. It creates objects again and again e.g.

I would try something like:

Option Explicit
Sub demo()
Dim i As Long, j As Long, r As Double
Dim myDoc As Object, mySheet As Object, myRange As Object, theDA
myDoc    = ThisComponent
mySheet  = myDoc.Sheets(0)
myRange  = mySheet.GetCellRangeByPosition(0,0,300,400)
theDA    = myRange.GetDataArray
For j = 0 To 400
r        = j+Sqr(j)
For i = 0 To 300
theDA(j)(i) = r
Next i
Next j
myRange.SetDataArray(theDA)
End Sub


Actually I tested. Since I didn't use advanced means to measure the time I had to upscale the numbers of columns and rows. With the IDE closed the code took about 2 s to fill the 12E4 cells.
Judged from my observations the formula =SQRT(ROW()-1) fillöed into the same range also needed 2 s for recalculation. (The copy of the DataArray needs RAMspace, of course.)

more

Thanks for the answer and for the alternative code example. You sample code works very fast, very efficient. It now runs much faster than Excel.

( 2017-12-02 01:52:36 +0100 )edit
1

You may consider to mark the answer as correct (clicking on the check mark).

( 2017-12-02 11:06:42 +0100 )edit

Ok will do. Thanks again for the very helpful code.

( 2017-12-02 17:55:31 +0100 )edit