Why is Writing to cells using for loop very slow

[digifoss]

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

[Lupp]

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.)

[digifoss]

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.

[Lupp]

Glad I could help.
You may consider to mark the answer as correct (clicking on the check mark).

[digifoss]

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