ExcelVBAの実行が極めて遅い †
ページ | OpenOffice.org FAQの登録ページ |
---|---|
投稿者 | Kuma |
分類 | |
優先順位 | |
状態 | |
カテゴリー | |
投稿日 | 2011-02-06 21:14:02 (日) |
OS | Win2K,Vine5.2_Linux |
依存するページ | |
バージョン |
メッセージ †
回答ページでは行末に「~」を付加する必要はありません |
OOo3.3.0になって、ExcelVBAで作成したシート上の「ボタン」が、マウスクリックで自動実行されるようになりました。
しかし、その実行速度は極めて遅く、CPU速度1.8GHzのPCで実行した場合、マクロの完了までに2〜3分位かかっています。
(Excelで実行した場合は1〜2秒で完了のマクロです。)
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 では、そのままで5秒で完了(エラーなし)
OOo 3.00 では、40秒過ぎた時に
1行目処理中(社長・大宮)に「年月データ入力ミス」でストップ
『初期化』を以下のように修正すると、OOo 3.00 で、
【1秒】で1行目処理中(社長・大宮)に「年月データ入力ミス」でストップ
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
VBAでは、プロパティ未指定で、既定プロパティ[Value]が採られ、
セルデータ書式「日付」に合わせて、Windowsプロパティ[短い日付書式: yyyy/mm/dd]で
編集された "2010/11/06" が取り出されます。
それで、これが10文字でOK。
OOo.Basic では、「シリアル値:40488」として取り出されます。
この数値を Len関数 に通しているので結果は5文字でNG。
ymd = Format(Sheets(sheet名1).Cells(n, 1).Value, "yyyy/mm/dd")
とするか、
[2010データシート]のA列:出発年月日の書式を yyyy/mm/dd で設定してから
ymd = Sheets(sheet名1).Cells(n, 1).Text
とすれば、OOo3.0.0でもエラーなく完了しました。
上記の「初期化」の修正がしてあれば
OOo3.0.0 で 2秒で完了
Excel2007 で 1秒で完了
[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でも同じマクロを手動登録して走らせたのですが、やはり同じ様に遅かったので、改良による影響では無いと分かりました。
貴重なご教示有難うございました。