faq/6/138

ExcelVBAの実行が極めて遅い †

ページ	OpenOffice.org FAQの登録ページ
投稿者	Kuma
分類	Q&A要望不具合メンテナンス
優先順位	緊急重要普通低
状態	質問回答中完了完了回答待ち議論中Issues重複却下保留再現不可
カテゴリー	インストール全体Writer ワープロCalc 表計算Impress プレゼンテーションDraw 図形描画Base データベースHTMLエディタMath 数式エディタLinux版StarSuiteその他
投稿日	2011-02-06 21:14:02 (日)
OS	Win2K,Vine5.2_Linux
依存するページ
バージョン	3.4.53.4.43.4.33.4.23.4.13.4.03.3.53.3.43.3.33.3.23.3.13.3.03.2.13.2.03.1.13.1.03.0.13.0.02.4.32.4.22.4.12.42.3.12.32.2.12.22.12.0.42.0.32.0.22.0.12.01.1.51.1.41.1.31.1.21.11.0.31.0.21.0.11.0Dev3.3Dev3.2Dev3.1Dev3.0不明

メッセージ †

OOo3.3.0になって、ExcelVBAで作成したシート上の「ボタン」が、マウスクリックで自動実行されるようになりました。
しかし、その実行速度は極めて遅く、CPU速度1.8GHzのPCで実行した場合、マクロの完了までに２〜３分位かかっています。
（Excelで実行した場合は１〜２秒で完了のマクロです。）

Windows版(2000で使用)もLinux版(Vine5.2で使用)も同じような状況です。

これが正常な所要時間とは思えず、何か不具合があるように思えます。
問題のExcelファイル「ExcelVBAsample.xls」を添付しましたので、他の方も同じかどうか試して頂けないでしょうか？

早くないPCで一分くらい †

はにゃ? (2011-02-06 23:30:03 (日))

とても数秒では終わらず、1分くらい掛かりました。
実行コードを見る限りこんなもんじゃないでしょうか。OOo Basic は Excel VBA と比べて遅いことは知られていますが、OOo で VBA のコードを実行しても Basic のエンジンは OOo Basic のものと同じです。オブジェクト用のラッパーが利用される分だけさらに遅くなっているはずです。
また、OOo Basic で OOo API を利用してコードを書く場合には早くなるように気をつけますが、その対策がなされていませんし。

OOo.Basic は遅いです。そういうもんです。 †

K.Tsunoda (2011-02-07 01:19:47 (月))

こんにちは。

参考：「OOo.Basic は、とてつもなく遅い！」
http://blog.livedoor.jp/addinbox/archives/51208845.html

中身の無い Forループ

For i = 1 To 36500
Next i

を36,500回 実行しただけで経過時間(0秒060)が計測できてしまう位に
OOo.Basic は遅いです。

[Celeron M520 1.6GHz]
Excel2007 では、そのままで５秒で完了（エラーなし）

OOo 3.00 では、４０秒過ぎた時に
１行目処理中（社長・大宮）に「年月データ入力ミス」でストップ

『初期化』を以下のように修正すると、OOo 3.00 で、
【１秒】で１行目処理中（社長・大宮）に「年月データ入力ミス」でストップ

Sub 初期化()

'作業シートAの文字削除
'For i = 1 To 100
'  For j = 1 To 2
'    Sheets(sheet名3).Cells(i, j).Clear
'  Next
'Next
With Sheets(sheet名3)
  .Range(.Cells(1, 1), .Cells(100, 2)).Clear
End With

'総合集計シートの前回集計データの削除
'For i = 1 To 100
'  For j = 1 To 100
'    Sheets(sheet名4).Cells(i + 3, j).Clear
'  Next
'Next
With Sheets(sheet名4)
  .Range(.Cells(4, 1), .Cells(103, 100)).Clear
End With

元々のマクロ自体でもシェープアップの必要性がある事が判るかと思います。

尚、OOo3.0.0 でエラーストップする理由までは見てません。

こういうところが VBA と OOo.Basic の細かな違い †

K.Tsunoda (2011-02-07 02:09:41 (月))

エラーストップの原因は下記です。

Sub 月番号編集転記(cnt, k, n, 検出列番号)
'2010データシートの出張年月日データから月データを抽出する関数
Dim a(10)
iretu = 検出列番号
ymd = Sheets(sheet名1).Cells(n, 1)   '← ここ
nlen = Len(ymd)
If nlen <> 10 Then
  MsgBox "年月データ入力ミス"
  End
End If

ＶＢＡでは、プロパティ未指定で、既定プロパティ[Value]が採られ、
セルデータ書式「日付」に合わせて、Windowsプロパティ[短い日付書式: yyyy/mm/dd]で
編集された "2010/11/06" が取り出されます。
それで、これが１０文字でＯＫ。

OOo.Basic では、「シリアル値：40488」として取り出されます。
この数値を Len関数 に通しているので結果は５文字でＮＧ。

ymd = Format(Sheets(sheet名1).Cells(n, 1).Value, "yyyy/mm/dd")
とするか、

[2010データシート]のＡ列：出発年月日の書式を yyyy/mm/dd で設定してから
ymd = Sheets(sheet名1).Cells(n, 1).Text
とすれば、OOo3.0.0でもエラーなく完了しました。

上記の「初期化」の修正がしてあれば
OOo3.0.0 で ２秒で完了
Excel2007 で １秒で完了
[Celeron M520 1.6GHz]

ついでに 3.3.0 でのエラー †

はにゃ? (2011-02-07 02:35:58 (月))

For 文の繰り返し回数を見ていませんでしたが、10,000 回もありましたか・・・。とはいえ、Clear が遅い気もしますね。

添付されているファイルを Linux 上の OOo 3.3.0 で開いて、ウィンドウをリサイズすると以下のエラーで落ちました。

terminate called after throwing an instance of 
'com::sun::star::script::provider::ScriptFrameworkErrorException'

stack trace を見ると VBA 関連で落ちているようです。イベントも設定されていないようですが、まぁ、いいんですけど。

Clear の速度 †

はにゃ? (2011-02-07 03:01:30 (月))

Clear が遅いようなので少し調べてみました。下記の部分を見ると最終的に clearContents メソッドでセルの内容を削除しています。
http://svn.services.openoffice.org/opengrok/xref/DEV300_m98/sc/source/ui/vba/vbarange.cxx#1580
下記のコードで少し所要時間を測ってみました。

Sub Main
 oSheet = ThisComponent.getSheets().getByIndex(0)
 n = com.sun.star.sheet.CellFlags
 m = n.VALUE + n.DATETIME + n.STRING + n.FORMULA + n.HARDATTR + n.EDITATTR + n.FORMATTED
 t1 = GetSystemTicks()
 For i = 0 To 99 step 1
   For j = 0 To 99 step 1
     oSheet.getCellByPosition(i, j)'.clearContents(m)
   Next
 Next
 t2 = GetSystemTicks()
 msgbox t2 - t1
End Sub

実行すると、セルの内容をクリアしないようにコメントアウトして 1.5 秒、クリアすると 31 秒。ボトルネックはここのようですね。

Formatも・・・ †

K.Tsunoda (2011-02-07 03:11:41 (月))

＞ とはいえ、Clear が遅い気もしますね。
セルへの書き出し系は、どれも遅いですよね。
ホントに気を使う所です。

あとは、Format関数が遅いですね。
大量ループの中で使うときには、【そのレイアウト専用】の自作関数を
作った方が断然早いです。

Format(数値, "00000")
とかだったら
Right("00000" & CStr(数値), 5)
ですね。もう少し複雑なレイアウトならユーザー定義関数仕立てで。

処理時間短縮のボトルネックが判らない時には、Format関数を疑ってみると
良いです。
http://blog.livedoor.jp/addinbox/archives/51199327.html

OOo3.2.1でも遅かった †

Kuma (2011-02-07 09:14:51 (月))

はにゃ? さん、K.Tsunoda さん、コードの検証までして頂き有難うございました。
処理時間が遅いのは異常では無かったのですね、大変良く分かりました。

OOo3.2.1までは、Excelファイルのマクロを動作させるためには、そのボタンに「イベント」を手動登録する手間が必要ですが、OOo3.3.0はその手間が不要となっていて素晴らしい!!と思いました。
しかし実行速度が遅く、その改良による影響が関係しているのかと安直に考えてしまいました。

その後、試しにOOo3.2.1でも同じマクロを手動登録して走らせたのですが、やはり同じ様に遅かったので、改良による影響では無いと分かりました。

貴重なご教示有難うございました。