[Solved] My macro became very slow

Creating a macro - Writing a Script - Using the API

[Solved] My macro became very slow

Postby Yarsk » Wed Nov 02, 2011 5:52 pm

Hello,

I've been writing a quite small macro (about 200 lines, and 400 loops "for i=1 to x"). It was working perfectly until I wrote more code in the Calc sheet itself.

When I launched the macro again, it took about 20sec to write the results in the 96 cells. I tried to launch the same macro from a new Calc sheet and it wrote the 96 results in less than a second.

Is there a way to get the speed back ?

Thanks
Last edited by Yarsk on Fri Nov 04, 2011 12:49 am, edited 1 time in total.
OOo 3.3.0 Windows 7
Yarsk
 
Posts: 7
Joined: Wed Nov 02, 2011 5:34 pm

Re: My macro became very slow

Postby gerard24 » Wed Nov 02, 2011 6:53 pm

It was working perfectly until I wrote more code in the Calc sheet itself.

And if you run the macro with "Autocalculate" disabled ?
LibreOffice 4.1.3 on Windows Vista
gerard24
Volunteer
 
Posts: 537
Joined: Sat Oct 30, 2010 5:12 pm
Location: France

Re: My macro became very slow

Postby Yarsk » Wed Nov 02, 2011 7:16 pm

As long as before. It seems to depend on the number of cells the macro writes in.

I cut some unnecessary code and it is a bit faster.It seems to depend on the overall quantity of code in the Calc sheet. But I don't use in other cells what is written by the macro. Those results are read-only.
OOo 3.3.0 Windows 7
Yarsk
 
Posts: 7
Joined: Wed Nov 02, 2011 5:34 pm

Re: My macro became very slow

Postby Villeroy » Wed Nov 02, 2011 7:55 pm

Instead of accessing each cell one by one:
Code: Select all   Expand viewCollapse view
a() = myRange.getDataArray()
for each r in a()
  for each c in r()
    if vartype(c)=5 then c = c*2
  next
next
myRange.setDataArray(a())

This accesses a whole range of cells, does the calculation in memory and then accesses the same range a second time.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17278
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: My macro became very slow

Postby Yarsk » Wed Nov 02, 2011 8:22 pm

Not sure it would fit my macro. It works like this:

for i=1 to 96
a=Sheet.getCellByposition(col,row+i).getValue
result=a*x
Sheet.getCellByPosition(col+1,row+i).setvalue(result)
next i

How would you use your DataArray in this configuration ?
OOo 3.3.0 Windows 7
Yarsk
 
Posts: 7
Joined: Wed Nov 02, 2011 5:34 pm

Re: My macro became very slow

Postby Villeroy » Wed Nov 02, 2011 8:58 pm

If this idiotic Basic language could handle arrays properly by reference it could be as simple as:
Code: Select all   Expand viewCollapse view
Sub Main
myRange = ThisComponent.Sheets.getCellRangeByPosition(0,1,0,96,0)
a() = myRange.getDataArray()
for each r in a()
  for each c in r()
    if vartype(c)=5 then c = c*2
  next
next
myRange.setDataArray(a())
End Sub

This code runs without error but does not change anything.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17278
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: My macro became very slow

Postby FJCC » Wed Nov 02, 2011 9:23 pm

While I was writing u my little example, Villeroy posted his. This code takes the values in A2:A97, multiplies them by 2 and stores them in B2:B97. As you can see in Villeroy's code, DataArray is an array of arrays. The outer arrays are the rows of the data range and the inner ones are the columns.
Code: Select all   Expand viewCollapse view
Row = 1
Col = 0
a = 2
Sheet = ThisComponent.Sheets.getByIndex(0)
oRange = Sheet.getCellRangeByPosition(Col, Row, Col + 1, Row + 95)
DataArray = oRange.DataArray
For i = 0 to 95
   DataArray(i)(1) = DataArray(i)(0) * a
next i
oRange.DataArray = DataArray
AOO 3.4 or 4.0 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 3672
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: My macro became very slow

Postby Yarsk » Wed Nov 02, 2011 10:12 pm

I still don't get how to setup this DataArray in my macro. I tried to simplify it but it is a bit more complicated:

(it is a simulation of an 18th century ship broadside)

Code: Select all   Expand viewCollapse view
   for i_group=1 to 2
   
      base_integrity=Sheet.getCellByposition(col+12,row+1109).getValue
                integrity=base_integrity
   
      for i_broadside=1 to 6
         
         reduction=Sheet.getCellByposition(col,row+1119).getValue         
         
         for i_battery=1 to 4
            
            dmg=Sheet.getCellByposition(col,row+1124).getValue
            resistance=Sheet.getCellByposition(col,row+1129).getValue
            hits_nbr=Sheet.getCellByposition(col,row+1130).getValue
            
            for i_shot=1 to hits_nbr
                                               
               P=integrity/base_integrity                                 
                          if dmg-reduction*P<0 then
                  dmg=0
               elseif P=0 then
                  dmg=0
               else
                  dmg=(0.25+0.75*P)*(1-resistance)*(dmg-reduction*P)
               endif         
               integrity=integrity-dmg
               battery_dmg=battery_dmg+dmg
               
            next i_shot
            
            'Sheet.getCellByPosition(col,row+144).setvalue(battery_dmg)
            'Sheet.getCellByPosition(col,row+146).setvalue(integrity)
            broadside_dmg=broadside_dmg+battery_dmg            
            row=row+4               
                        
         next i_battery
         
         'Sheet.getCellByPosition(write_col,write_row+150).setvalue(broadside_dmg)         
         row=row-16   
         col=col+2
         
      next i_broadside
   
      row=row+50      
                col=col-12
      
   next i_group

   
End Sub


When I remove the "setvalue" lines, the macro runs in less than a second. Otherwise it takes 15sec to write the 96 values. Do you think I could use the DataArray to store all the 96 values, and write them after ?
OOo 3.3.0 Windows 7
Yarsk
 
Posts: 7
Joined: Wed Nov 02, 2011 5:34 pm

Re: My macro became very slow

Postby B Marcelly » Thu Nov 03, 2011 9:44 am

Hi,
Try this...
Code: Select all   Expand viewCollapse view
ThisComponent.addActionLock
ThisComponent.lockControllers
' ---
' --- insert here your slow code
' ---
ThisComponent.unlockControllers
ThisComponent.removeActionLock
Bernard

OpenOffice.org 1.1.5 / Apache OpenOffice 4.0.1 / LibreOffice 4.1.0
MS-Windows 7 Home SP1
B Marcelly
 
Posts: 773
Joined: Mon Oct 08, 2007 1:26 am
Location: France, Paris area

Re: My macro became very slow

Postby Yarsk » Thu Nov 03, 2011 9:42 pm

So I tested all your propositions:

Villeroy's code works but doesn't write the results in the cells.

FJCC's code gives me an error message.

B Marcelly's code doesn't speed the macro.



I don't understand how the DataArray works.
OOo 3.3.0 Windows 7
Yarsk
 
Posts: 7
Joined: Wed Nov 02, 2011 5:34 pm

Re: My macro became very slow

Postby Yarsk » Thu Nov 03, 2011 10:53 pm

Ok I found why FJCC's code didn't work: there was no value in DataArray(0)(0). Changing "DataArray(i)(0) * a" to "DataArray(i)(0) + a" made the code work.

BTW naming the DataArray "DataArray" was quite confusing.

And I found that changing ".DataArray" by ".Data" also works. What are those extensions ?

I think I'm on a good way to let my macro run faster.
OOo 3.3.0 Windows 7
Yarsk
 
Posts: 7
Joined: Wed Nov 02, 2011 5:34 pm

Re: My macro became very slow

Postby Yarsk » Fri Nov 04, 2011 12:42 am

Wonderful ! It works.

I used this type of code:

Code: Select all   Expand viewCollapse view
Sub Main
   Dim oDoc As Object
   Dim oSheet As Object
   Dim i_1 as integer
   Dim i_2 as integer
   Dim row as integer
   Dim col as integer
   Dim result_1 as single
   Dim result_2 as single

   oDoc = StarDesktop.CurrentComponent   
   oSheet = oDoc.getCurrentController.getactivesheet()
   oCellRange = oSheet.getCellRangeByPosition(5,10,5+12,10+40)
   oData = oCellRange.Data
   row=0
   col=0
   result_1=123
   result_2=17
   
   for i_1=1 to 6
      'Calculations
      for i_2=1 to 4
         'Calculations
         oData(row)(col)=result_1
         oData(row+1)(col)=result_2
         row=row+10
      next i_2
      row=row-40
      col=col+2
   next i_1
   oCellRange.Data = oData
End Sub


Thanks a lot for your tips guys ! My macro runs in less than a second again.
OOo 3.3.0 Windows 7
Yarsk
 
Posts: 7
Joined: Wed Nov 02, 2011 5:34 pm

Re: My macro became very slow

Postby Charlie Young » Fri Nov 04, 2011 12:55 am

Yarsk wrote:And I found that changing ".DataArray" by ".Data" also works. What are those extensions ?

I think I'm on a good way to let my macro run faster.


.Data may be used if your data are strictly numeric, as in this case. DataArray is good for both doubles and strings. There is also FormulaArray, which handles strings much the way .Data does numbers, though it also has other uses, with formulas :!: , and it may be used in conjunction with DataArray to convert formulas to their values without the need of doing clipboard Copy and Paste Special.

So if oRange contains any combination of cells with formulas returning either text or numbers, the formulas can be converted to their results with

Code: Select all   Expand viewCollapse view
oRange.setFormulaArray(oRange.DataArray)


and that will also leave any constant values undisturbed.
Apache OpenOffice 4.0.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1435
Joined: Fri May 14, 2010 1:07 am


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 3 guests