Basic,Javascript,Python,VBAコードでの比較
< 戻る
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
【解説】
oDataArry = oRange.getDataArray() :A1~C1048576 のデータ群を取得
oTmp() = oDataArry(0) : 1行目の値を oTmp(0),oTmp(0),oTmp(0) として取得
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]
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
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