# [SOLVED] To Run Rapidly LibreOffice Macro

Hi!

I work in a LibreOffice Macro. My code work so slowly because of my data. I have around 10,000 data in a sheet and when I write for loop and if statement, my code work is so slowly(It works about 5hours). Is there any methods to run it rapidly?

I added my code that works slowly.

Sub Main

'Sheet 2 'Ürün Ağaçları(Sheet Name)
Doc = ThisComponent
Sheeti2 = Doc.Sheets.getByIndex(2)
Curs = Sheeti2.createCursor
Curs.gotoEndOfUsedArea(True)
NumRows2 = Curs.Rows.Count

'Sheet 5    '3 Aylık Pivot(Sheet Name)
Doc = ThisComponent
Sheeti5 = Doc.Sheets.getByIndex(5)
Curs = Sheeti5.createCursor
Curs.gotoEndOfUsedArea(True)
NumRows5 = Curs.Rows.Count

dim document as object
document = ThisComponent
sheet1 = document.Sheets(5)'3 Aylık Pivot(Sheet Name)
sheet2 = document.Sheets(2)'Ürün Ağaçları(Sheet Name)
sheet3 = document.Sheets(6)'3 Aylık Tahmin(Sheet Name)
sheet4 = document.Sheets(7)'Ağaçla Tahminler(Sheet Name)
sheet5 = document.Sheets(3)'Takım Ağaçları (Sheet Name)

For iCountTree = 0 to NumRows5 '3 Aylık pivottaki satır sayısı
For iCountTree1 = 1 to NumRows2'ürün ağacı satır sayısı

if sheet1.getCellByPosition(0, iCountTree+5).String = sheet2.getCellByPosition(1, iCountTree1).String Then
'Parça Kodunu copy start
With aaA
.Sheet       = 2
.StartColumn = 2
.StartRow    = iCountTree1
.EndColumn   = 2
.EndRow      = iCountTree1
End With
'Parça Kodunu copy finish
'Ürün Kodu copy start
With acA
.Sheet       = 2
.StartColumn = 1
.StartRow    = iCountTree1
.EndColumn   = 1
.EndRow      = iCountTree1
End With
'Ürün Kodu copy finish
'Parça Miktarını copy start
With aeA
.Sheet       = 2
.StartColumn = 4
.StartRow    = iCountTree1
.EndColumn   = 4
.EndRow      = iCountTree1
End With
'Parça Miktarını copy finish

for iCountForEmpty = 1 to NumRows2*2'Bu Sayıyı Ağaçların listesinden fazla olacak şekilde seçtim
if sheet4.getCellByPosition(1, iCountForEmpty).String = empty then
'Parça Kodunu paste start
With abA
.Sheet       = 7
.Column      = 2
.Row         = iCountForEmpty
End With
ThisComponent.Sheets(7).CopyRange(abA, aaA)
'Parça Kodunu paste finish
'Ürün Kodu paste start
.Sheet       = 7
.Column      = 1
.Row         = iCountForEmpty
End With
'Ürün Kodu paste finish
'Parça Miktarını paste start
With afA
.Sheet       = 7
.Column      = 3
.Row         = iCountForEmpty
End With
ThisComponent.Sheets(7).CopyRange(afA, aeA)
'Parça Miktarını paste finish

'For to be empty to value of cells start
'Parça Kodunu copy start
With aaA
.Sheet       = 2
.StartColumn = 15
.StartRow    = 1
.EndColumn   = 15
.EndRow      = 1
End With
'Parça Kodunu copy finish
'Ürün Kodu copy start
With acA
.Sheet       = 2
.StartColumn = 15
.StartRow    = 1
.EndColumn   = 15
.EndRow      = 1
End With
'Ürün Kodu copy finish
'Parça Miktarını copy start
With aeA
.Sheet       = 2
.StartColumn = 15
.StartRow    = 1
.EndColumn   = 15
.EndRow      = 1
End With
'Parça Miktarını copy finish
'For to be empty to value of cells finish

Exit For
else
end if
next iCountForEmpty

Else

end if

next iCountTree1 ...
edit retag close merge delete

You do not need to use ThisComponent.Sheets(7) when copying. You have already defined it as "sheet4", use this variable. Do you copy only data? Or can there be formulas in the cells?

( 2018-12-12 15:03:55 +0100 )edit

Yes @JohnSUN, I copy only data. What can I do to copy paste rapidly?

( 2018-12-12 15:20:40 +0100 )edit

@libremacrouser: Can you explain in general words (not arbitrary and probably badly chosen names) what this code is supposed to achieve?
Quoting @libremacrouser: "Yes @JohnSUN, I copy only data. What can I do to copy paste rapidly?"
You are obviously testing conditions. For simply copying ranges there is the very fast copyRangemethod that every sheet can call. See https://api.libreoffice.org/docs/idl/...

( 2018-12-12 18:28:15 +0100 )edit

@Lupp As far as I understood the above code, this is the usual filtering of data from the table on the third sheet for the coincidence of the values ​​in the column B with the values ​​of column A on the sixth sheet. The result of filtering - the second, third and fifth cells are recorded in the first row on the eighth page, where there is no data in column B.

( 2018-12-12 19:37:23 +0100 )edit

@JohnSUN I editted my question. I added a picture on it. I try to your code in a simple example and it worked great. After that I tried it my real LibreOffice and I got this error which in the picture. I again try to understand what is your code. It is really great and I could see it how is work in my simple example. If I could complete my code, it would be really great. Can you tell me how can I solve my problem?

( 2018-12-13 07:09:11 +0100 )edit

@JohnSUN and I couldn't understand what is their mean --> i7 = (UBound(oData2)+1) * (UBound(oData5)+1) oRange7 = oSheet.getCellRangeByPosition(1, 1, 3, i7)

( 2018-12-13 07:21:47 +0100 )edit

It's almost the same as what you wrote here: for iCountForEmpty = 1 to NumRows2*2'Bu Sayıyı Ağaçların listesinden fazla olacak şekilde seçtim You took just double the number of rows. I thought about the case when all the codes in 'Ürün Ağaçları'.B:B and in '3 Aylık Pivot'.A:A would be the same and asked for a size that was enough for all matches, each with each.

When you substituted real data, it turned out that "each with each one" is more than a million values, but sheet of Calc has only 1048576 lines.

So, just replace

i7 = (UBound(oData2)+1) * (UBound(oData5)+1)

with

i7 = 1048576

( 2018-12-13 07:50:01 +0100 )edit

Thanks for your reply @JohnSUN. I got it what is mean of i7 = (UBound(oData2)+1) * (UBound(oData5)+1). Sorry I think I ask you much questions. I try to understand its basic to improve my question and myself. Can you tell me about the error that I editted its picture in my question? In addition I couldn't understand --> oRange7 = oSheet.getCellRangeByPosition(1, 1, 3, i7) I am not sure about (1.1.3.i7). And I don't know why it is work in an my simple example and doesn't work in my real file that I write it in a comment it is up two comments.

( 2018-12-13 08:12:56 +0100 )edit

Error - the number i7 turned out to be very large, we demanded from the macro to select on the sheet 'Ağaçla Tahminler' a range of cells larger than there is.

(1,1,3,i7) - mean "columns 1,2 and 3 (B, C and D) from second row (you skip header in your macros) to last needed row (to last row on sheet?)"

( 2018-12-13 08:31:20 +0100 )edit

Sort by » oldest newest most voted

I hope that this variant do same and work something rapidly:

Sub JohnSUNsCopy
Dim oSheets As Variant  ' All sheets of ThisComponent '
Dim oSheet As Variant   ' Any single sheet '
Dim oData2 As Variant   ' All data from Ürün Ağaçları Sheet '
Dim oData5 As Variant   ' All data from 3 Aylık Pivot Sheet '
Dim oData7 As Variant   ' Data to paste to Ağaçla Tahminler Sheet '
Dim oCursor As Variant  ' You know what it is, you used it in your code '
Dim i2 As Long, i5 As Long  ' Variables for cycles  '
Dim i7 As Long          ' Count trees '
Dim nEndRow7 As Long    ' The number of the last non-empty row on sheet 7 column 1 '
Dim oRange7 As Variant
oSheets = ThisComponent.getSheets()
oSheet = oSheets.getByIndex(2)
oCursor = oSheet.createCursor()
oCursor.gotoEndOfUsedArea(True)
oData2 = oCursor.getDataArray() ' All cells of Ürün Ağaçları Sheet in the array '

oSheet = oSheets.getByIndex(5)
oCursor = oSheet.createCursor()
oCursor.gotoEndOfUsedArea(True)
oData5 = oCursor.getDataArray() ' All cells of 3 Aylık Pivot Sheet in the array '

oSheet = oSheets.getByIndex(7)
i7 = (UBound(oData2)+1) * (UBound(oData5)+1)
oRange7 = oSheet.getCellRangeByPosition(1, 1, 3, i7)
oData7 = oRange7.getDataArray()
nEndRow7 = LBound(oData7)

For i2 = LBound(oData2) To UBound(oData2)
For i5 = LBound(oData5) To UBound(oData5)
If oData5(i5)(0)  = oData2(i2)(1) Then
For i7 = nEndRow7 To UBound(oData7)
If (IsEmpty(oData7(i7)(0)) Or Trim(oData7(i7)(0))="") Then
oData7(i7)(0) = oData2(i2)(1)
oData7(i7)(1) = oData2(i2)(2)
oData7(i7)(2) = oData2(i2)(4)
nEndRow7 = i7+1
Exit For
EndIf
Next i7
EndIf
Next i5
Next i2
oRange7.setDataArray(oData7)
End Sub

more

Thanks @JohnSUN. Thanks to you I solved my problem. Anymore my macro run great and it works for only 15minutes. It is really great for me because before your solution, it was almost 5 hours.

( 2018-12-15 21:08:30 +0100 )edit

Glad I could help. The algorithm could be accelerated even more (perhaps even several times faster). But to do this, you need to optimize the search for matching values ​​that should be copied, and determine in advance all the blank lines on which to write the result. This would make the code so complex that I could not explain all its details.

( 2018-12-15 21:22:10 +0100 )edit