Ask Your Question

Why is Writing to cells using for loop very slow

asked 2017-12-01 23:24:13 +0200

digifoss gravatar image

updated 2017-12-02 01:25:34 +0200

Lupp gravatar image

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 flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2017-12-02 01:20:51 +0200

Lupp gravatar image

updated 2017-12-02 01:38:56 +0200

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

edit flag offensive delete link 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.

digifoss gravatar imagedigifoss ( 2017-12-02 01:52:36 +0200 )edit

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

Lupp gravatar imageLupp ( 2017-12-02 11:06:42 +0200 )edit

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

digifoss gravatar imagedigifoss ( 2017-12-02 17:55:31 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-12-01 23:24:13 +0200

Seen: 101 times

Last updated: Dec 02 '17