Home of site/Basic Macro for Calc

Macroの杜(LibreOffice Basic編 / Calc)


Basic,Javascript,Python,VBAコードでの比較
< 戻る

CC-)[Calc]Cellの値を高速取得
【Picture】実行結果

Sub FastGetData
	Dim oDoc as Object
	Dim oSheet1 as Object
	Dim oRange as Object
	Dim sCol as Long, eCol as Long
	Dim sRow as Long, eRow as Long
	Dim oDataArry() as Variant
	Dim oTmp() as Variant
	DIm oLastNo as Long
	Dim oSTime as Long, oETime as Long, oTime as Long
	Dim oDisp as String
		oSTime = Hour(now())*60*60 + Minute(now())*60 + Second(now())
		oDoc = ThisComponent
		oSheet1 = oDoc.getSheets().getByIndex(0)
		sCol = 0
		eCol = 2
		sRow = 0
		eRow = 1048575
		oRange = oSheet1.getCellRangeByPosition(sCol, sRow, eCol, eRow)
		oDataArry = oRange.getDataArray()
		oLastNo = UBound(oDataArry)
		oTmp() = oDataArry(0)
		oDisp = oTmp(0) & Chr(9) & oTmp(1) & Chr(9) & oTmp(2)
		oDisp = oDisp & Chr(13) & "  ・" & Chr(13) & "  ・" & Chr(13) & "  ・"
		oTmp() = oDataArry(oLastNo)
		oDisp = oDisp & Chr(13) & oTmp(0) & Chr(9) & oTmp(1) & Chr(9) & oTmp(2)
		oETime = Hour(now())*60*60 + Minute(now())*60 + Second(now())
		oTime = oETime - oSTime
		oDisp = oDisp & Chr(13) & Chr(13) & "処置時間 = " & oTime & "[sec]"
		msgbox(oDisp,0,"LO7.2.5.2")
End Sub


【解説】
【Picture】実行結果
oDataArry = oRange.getDataArray()　：A1～C1048576　のデータ群を取得
oTmp() = oDataArry(0)　： 1行目の値を oTmp(0),oTmp(0),oTmp(0)　として取得


【Picture】実行結果
A1～C1048576　のデータを高速で取得するコード。
For Loopで入力値の形式を確認しながら取得するより圧倒的に高速。

とは言え、EXCEL上のVBAでの同じ処置と比べるとまだまだです。
もし、速度を求めるならば PythonマクロやJavaScriptマクロの利用を検討しましょう。

処置時間(実測値)で以下です。

	Python : 1～2[sec]

	JavaScript : 3～4[sec]

	LO Basic : 7～8[sec]

	LO上のVBAコード : 38～39[sec]

	For Loopコード : 360～370[sec]




【Picture】実行結果
Excel VBAのコードは以下ですが、LOで実行するとLO Basicより5倍近い時間が掛かります。

Option VBASupport 1 
Sub VBACode()
	Dim oDataArry()
	oSTime = Hour(now)*60*60 + Minute(now)*60 + Second(now)
	oDataArry = WorkSheets("Sheet1").Range("A1:C1048576").Value
	oLastNo = UBound(oDataArry,1)
	oDisp = oDataArry(1,1) & Chr(9) & oDataArry(1,2) & Chr(9) & oDataArry(1,3) & Chr(13) & _
	oDataArry(oLastNo,1) & Chr(9) & oDataArry(oLastNo,2) & Chr(9) & oDataArry(oLastNo,3)
	oETime = Hour(now)*60*60 + Minute(now)*60 + Second(now)
	oTime = oETime - oSTime
	oDisp = oDisp & Chr(13) & Chr(13) & "処置時間 = " & oTime
	msgbox oDisp,0,"VBA"
End Sub


【Picture】実行結果
for Loop構文でデータを取得する場合のコードは以下


Sub LoopCode()
	Dim oDoc as Object
	Dim oSheet as Object
	Dim oDataArry(1048575,2) as Variant
	Dim i as long, oLastRow as Long
	Dim oSTime as Long, oETime as Long, oTime as Long
	Dim oDisp as String
		oSTime = Hour(now())*60*60 + Minute(now())*60 + Second(now())
		oDoc = ThisComponent
		oSheet = oDoc.getSheets().getByIndex(0)
		
		oLastRow = 1048575
		for i = 0 to oLastRow
			oDataArry(i,0) = oSheet.getCellByPosition(0, i).Value
			oDataArry(i,1) = oSheet.getCellByPosition(1, i).Value
			oDataArry(i,2) = oSheet.getCellByPosition(2, i).Value
		next
		
		oDisp = oDataArry(0,0) & Chr(9) & oDataArry(0,1) & Chr(9) & oDataArry(0,2)
		oDisp = oDisp & Chr(13) & "  ・" & Chr(13) & "  ・" & Chr(13) & "  ・"
		oDisp = oDisp & Chr(13) & oDataArry(oLastRow,0) & Chr(9) & oDataArry(oLastRow,1) & Chr(9) & oDataArry(oLastRow,2)
		oETime = Hour(now())*60*60 + Minute(now())*60 + Second(now())
		oTime = oETime - oSTime
		oDisp = oDisp & Chr(13) & Chr(13) & "処置時間 = " & oTime & "[sec]"
		msgbox(oDisp,0,"LO7.6.2.1")
End Sub
inserted by FC2 system