[LibreOffice/OpenOffice Basic] シートへの高速なアクセス

本ページは「Excel で学ぶ Visual Basic for Applications (VBA)」内のページ「シートへの高速なアクセス」を
LibreOffice/OpenOffice Basic に対応させたものである。

前ページにおいて、LibreOffice/OpenOffice Basic から LibreOffice/OpenOffice Calc のシート (表) にアクセスする方法を学んだ。

通常の用途ではこれで構わないのだが、研究などで書き込むセルの数が増えるとどんどん実行速度が遅くなってしまう。
それを避けるためのテクニックをここでは紹介しよう。

画面更新を抑制する
配列に貯めたデータを直接書き込む
セルへの書き込み時間の比較
セルの内容を配列に読み込む

本章で解説するセルアクセス方法を用いた「高校数学からはじめるディープラーニング」および
「Excel / OpenOffice で学ぶフーリエ変換入門」では
マクロをソース閲覧可能な形で用いています。

画面更新の抑制

まず、セルへのアクセスを高速化するための常道として、画面更新を抑制することが多くの場合用いられる。
これは、セルへ値を書き込むのが終るまで画面更新を抑制しておき、
書き込み終了後に画面更新を再開するというもので、簡単であるがそれなりの効果がある。

前ページの「Sin 関数の値をセルに書き込む」という例で効果を確認してみよう。
「プログラムを書き始めるまでの準備」を参考に以下の Basic プログラムを実行してみて欲しい。

Option Explicit

Sub Main()
Dim x1 As Double, x2 As Double
Dim n As Long, nd As Long
Dim x As Double, y As Double, dx As Double

  x1 = -3.14
  x2 = 3.14
  nd = 30000

  dx = (x2 - x1) / nd

  ThisComponent.addActionLock()   ' 画面更新抑制
  For n = 0 To nd - 1    ' nd 個のデータを書き込む
    x = x1 + dx * n
    y = Sin(x)
    ThisComponent.Sheets(0).getCellByPosition(0, n).Value = x
    ThisComponent.Sheets(0).getCellByPosition(1, n).Value = y
  Next n
  ThisComponent.removeActionLock()  ' 画面更新抑制解除
End Sub

このプログラムは -3.14 ～ 3.14 までの sin(x) の値を 30000 個のセルに書き込むプログラムである。
かかる時間の違いがわかりやすくなるよう、書き込むセルの数をわざと多くしている。

赤い字で表示された 2 行が画面更新に関する設定である。
セルへの書き込みを始める前に ThisComponent.addActionLock() を実行して画面更新を無効にし、
書き込み終了後に ThisComponent.removeActionLock() を実行して画面更新を有効に戻してことがわかる。

手元のコンピュータでは

ActionLock の設定なし (画面更新有効)	約 90 秒
ActionLock の設定あり (画面更新無効)	約 31 秒

のように、ActionLock の設定を行うことで約 60 秒の時間が短縮された。
(なお、本ページ末尾のプログラムを実行することで皆さんも時間を計測できる)

しかし、同じプログラムが Excel + VBA では 3 秒程度で実行できていたことを考えると、
LibreOffice/OpenOffice Basic によるセルへのアクセスは非常に遅いことがわかる。
この方法では多数のデータを記入する際に使い物にならないので、次に学ぶ方法が必須となるだろう。

配列に貯めたデータを直接書き込む

さらに効果的なのが、「配列に貯めたデータを直接書き込む」という方法である。
セルへのアクセスが遅い LibreOffice/OpenOffice では必須と言って良い方法であろう。

これまでと全く同じ例をこの方法で実行したのが以下である。

Option Explicit

Sub Main()
Dim x1 As Double, x2 As Double
Dim n As Long, nd As Long
Dim x As Double, y As Double, dx As Double

  x1 = -3.14
  x2 = 3.14
  nd = 30000

  dx = (x2 - x1) / nd

Dim celldata() As Variant
ReDim celldata(nd - 1)   ' 0...nd-1  で定義された配列を確保
    
  For n = 0 To nd - 1     ' nd 個のデータを書き込む
    x = x1 + dx * n
    y = Sin(x)
    celldata(n) = Array(x, y)     ' 二列分のデータを Array() でくくって全て配列に格納
                                  ' 一列分のデータであっても Array() でくくる必要あり
  Next n

  ThisComponent.addActionLock()
    
  ThisComponent.Sheets(0).getCellRangeByPosition(0, 0, 1, nd - 1).setDataArray(celldata)
                       ' 実際のセルへの書き込み

  ThisComponent.removeActionLock()

  Erase celldata   ' 確保した配列を解放
End Sub

ポイントは赤字で示されている。まず、データを配列にあらかじめ全て格納しておく。
なお、１列にしか書き込まない場合でも「Array(x)」などとして Array ステートメントを用いる必要がある。

そして、setDataArray を用いてこれを一気に書き込みたいセル範囲に書き込むのである。

実際に実行してみると、一瞬で処理が終了することがわかる。次節のプログラムで時間を比較してみよう。

セルへの書き込み時間の比較

以下に挙げるやや長いプログラムを実行すると、異なるセル書き込み方法にかかる時間を計測することができる。
みなさんも「プログラムを書き始めるまでの準備」を参考にコピー＆貼り付けにより実行してみて欲しい。

手元のコンピュータでは、以下のように配列を丸ごと書き込む方法が圧倒的に高速であることがわかった。
現実問題、多数のデータを書き込む際、実用になるのは配列を丸ごと書き込む方法のみであることがわかるだろう。

ちなみに、Excel+VBA で同じプログラムを実行した時の結果はこちらにあるが、
LibreOffice/OpenOffice に比べ圧倒的に高速である。VBA プログラムを LibreOffice/OpenOffice に移植する際は
この速度の差をどう埋めるかがポイントとなるだろう。

画面更新有効: 230 [秒]

画面更新無効: 44 [秒]

配列丸ごと書き込み: 1 [秒]

Option Explicit

Sub Main()
Dim x1 As Double, x2 As Double
Dim n As Long, nd As Long
Dim x As Double, y As Double, dx As Double

Dim t1 As Double, t2 As Double

  x1 = -3.14
  x2 = 3.14
  nd = 30000

  dx = (x2 - x1) / nd

Dim celldata() As Variant
ReDim celldata(nd - 1)   ' 0...nd-1  で定義された配列を確保
    
  For n = 0 To nd - 1     ' nd 個のデータを書き込む
    x = x1 + dx * n
    y = Sin(x)
    celldata(n) = Array(x, y)     ' 二列分のデータを Array() でくくって全て配列に格納
                                  ' 一列分のデータであっても Array() でくくる必要あり
  Next n
  
Dim celldata_row()  ' データを celldata() から読み取るための配列

  t1 = Timer
  For n = 0 To nd - 1    ' nd 個のデータを書き込む
    celldata_row = celldata(n)
    ThisComponent.Sheets(0).getCellByPosition(0, n).Value = celldata_row(0)
    ThisComponent.Sheets(0).getCellByPosition(1, n).Value = celldata_row(1)
  Next n
  t2 = Timer
  MsgBox ("画面更新有効: " & t2 - t1 & " [秒]") 
 
  ThisComponent.addActionLock()   ' 画面更新抑制
  t1 = Timer
  For n = 0 To nd - 1    ' nd 個のデータを書き込む
    celldata_row = celldata(n)
    ThisComponent.Sheets(0).getCellByPosition(0, n).Value = celldata_row(0)
    ThisComponent.Sheets(0).getCellByPosition(1, n).Value = celldata_row(1)
  Next n
  t2 = Timer
  ThisComponent.removeActionLock()  ' 画面更新抑制解除
  MsgBox ("画面更新無効: " & t2 - t1 & " [秒]")

  ThisComponent.addActionLock()   ' 画面更新抑制
  t1 = Timer
  ThisComponent.Sheets(0).getCellRangeByPosition(0, 0, 1, nd - 1).setDataArray(celldata)
  t2 = Timer
  ThisComponent.removeActionLock()  ' 画面更新抑制解除
  MsgBox ("配列丸ごと書き込み: " & t2 - t1 & " [秒]")

  Erase celldata   ' 確保した配列を解放
End Sub

セルの内容を配列に読み込む

なお、配列をセルに書き込む方法と対になる、「セルの内容を配列に読み込む」方法についても簡単に触れておこう。
こちらも1セルずつ読み取る方法に比べて圧倒的に高速である。

Dim celldata()
Dim celldata_row()

celldata() = ThisComponent.Sheets(0).getCellRangeByPosition(0, 0, 2, 1).getDataArray()

celldata_row()=celldata(0)
' 1行目が celldata_row(0), celldata_row(1), celldata_row(2) として読み取れる

celldata_row()=celldata(1)
' 2行目が celldata_row(0), celldata_row(1), celldata_row(2) として読み取れる

←[LibreOffice/OpenOffice Basic] シートへのアクセス

Excel で学ぶ Visual Basic for Applications (VBA)に戻る