@IT会議室は、ITエンジニアに特化した質問・回答コミュニティ「QA@IT」に生まれ変わりました。ぜひご利用ください。
- PR -

【VBA】列の移動に時間がかかる

投稿者投稿内容
AERITH
会議室デビュー日: 2005/10/14
投稿数: 15
投稿日時: 2006-03-06 18:12
【OS】Windows XP SP2
【アプリ】Microsoft Office Excel 2003

こんにちは、AERITHです。
皆様のお知恵を拝借したく思います。


VBAにて既存データを集計しようと、マクロを組んでいます。

@まず元となるデータが記載されているシートのコピーを、
 下記のコードで作成(名前はTempWorkSheets)。
 
 'シート数カウント
iSheetCount = Worksheets.Count
'既に入替用ワークシートが存在する場合は削除する
For iCnt = 1 To iSheetCount
If Worksheets(iCnt).Name = TEMP_WORKSHEETS Then

Worksheets(TEMP_WORKSHEETS).Delete

Exit For
End If
Next

'ワークシート追加
Worksheets(BUNSEKI_DATA_ORIGINAL).Copy after:=Worksheets(BUNSEKI_DATA_ORIGINAL)
ActiveSheet.Name = TEMP_WORKSHEETS

ATempWorksheetsにある、対象となる列を入れ替えるために
 Copy→Insert→Delete(元の列)
 で移動しているようにみせています。
 (Cutは列中に結合セルがあるため使用できませんでした)
 
'A列移動
.Columns(StartColumnCell).Copy
.Columns(StartColumnCell + 4).Insert
.Columns(StartColumnCell).Delete
'B列移動
.Range(Columns(StartColumnCell + 5), Columns(StartColumnCell + 6)).Copy
.Range(Columns(StartColumnCell + 9), Columns(StartColumnCell + 9)).Insert
.Range(Columns(StartColumnCell + 5), Columns(StartColumnCell + 6)).Delete
'C列移動
.Columns(StartColumnCell + 11).Copy
.Columns(StartColumnCell + 6).Insert
.Columns(StartColumnCell + 12).Delete

 あとはこれと同様の移動コードがいくつか続きます。
 
 動作自体に問題はないのですが、Aの移動をまとめたメソッドで非常に時間を食われます。
 現状、移動する列は6個(6回)ですが、それだけで体感4〜5分はかかっています。
 
 こういった現象の場合、コードによる高速化は可能でしょうか?
 もし可能であればご教授頂きたいと思います。
 
 よろしくお願いいたします。
 

[ メッセージ編集済み 編集者: AERITH 編集日時 2006-03-06 18:16 ]
じゃんぬねっと
ぬし
会議室デビュー日: 2004/12/22
投稿数: 7811
お住まい・勤務地: 愛知県名古屋市
投稿日時: 2006-03-06 18:44
引用:

AERITHさんの書き込み (2006-03-06 18:12) より:

動作自体に問題はないのですが、(2) の移動をまとめたメソッドで非常に時間を食われます。
現状、移動する列は6個(6回)ですが、それだけで体感4〜5分はかかっています。
こういった現象の場合、コードによる高速化は可能でしょうか?


Column 単位ではなく Range 単位に Copy, Insert, Delete するのが思いつきます。
あとは描画させない、などしか思いつきません。

_________________
C# と VB.NET の入門サイト
じゃんぬねっと日誌
ちゃっぴ
ぬし
会議室デビュー日: 2004/12/10
投稿数: 873
投稿日時: 2006-03-06 19:52
Excel VBA で速度を追求するのであれば、Insert, Delete は絶対に使ってはいけません。

それをやるくらいだったら、空の Sheet に Copy しながら
作成したほうが圧倒的に速いです。
また、作業列を使って、Sort でやるというのも有効な手段です。
AERITH
会議室デビュー日: 2005/10/14
投稿数: 15
投稿日時: 2006-03-07 08:25
じゃんぬねっとさん、ちゃっぴさん、ご返答ありがとうございます。

>Column 単位ではなくRange単位
 試してみた所、気持ち速くなったような気がします。
 描画OFFの方は記載していませんでしたが、既に適用していたものの、
 思ったほどの効果が得られなかったという状況でした。
 OFF機能が効果的に働いてこれだけの時間がかかってる、ということかもしれませんが。

>Insert, Delete は絶対に使ってはいけません。
 根本的な問題はそこなんですね。
 ちゃっぴさんに提示して頂いた、空のSheetsにコピーしながらという手法で試してみようと思います。
 お二方、どうもありがとうございました。
AERITH
会議室デビュー日: 2005/10/14
投稿数: 15
投稿日時: 2006-03-08 09:16
Delete、Insertを使わずにCopyのみで以下のように対応してみました。

Worksheets(BUNSEKI_DATA_ORIGINAL).Columns(StartColumnCell).Copy Destination:=Worksheets(TEMP_WORKSHEETS).Columns(a)
Worksheets(BUNSEKI_DATA_ORIGINAL).Columns(StartColumnCell + 1).Copy Destination:=Worksheets(TEMP_WORKSHEETS).Columns(b)
Worksheets(BUNSEKI_DATA_ORIGINAL).Columns(StartColumnCell + 2).Copy Destination:=Worksheets(TEMP_WORKSHEETS).Columns(c)




時間を計測してみたところ
以前までの手法では4分54秒、
上記の手法で2分14秒
でした。

分単位であることが解消なかったのは残念ですが、
それでも半分以下の時間で出来るようになったのは非常に嬉しいです。
心なし負荷も軽くなったような気もします。

じゃんぬねっとさん、ちゃっぴさん、どうもありがとうございました。
ちゃっぴ
ぬし
会議室デビュー日: 2004/12/10
投稿数: 873
投稿日時: 2006-03-08 14:44
引用:
コード:
Worksheets(BUNSEKI_DATA_ORIGINAL).Columns(StartColumnCell).Copy Destination:=Worksheets(TEMP_WORKSHEETS).Columns(a) 
Worksheets(BUNSEKI_DATA_ORIGINAL).Columns(StartColumnCell + 1).Copy Destination:=Worksheets(TEMP_WORKSHEETS).Columns(b) 
Worksheets(BUNSEKI_DATA_ORIGINAL).Columns(StartColumnCell + 2).Copy Destination:=Worksheets(TEMP_WORKSHEETS).Columns(c) 




この書き方をみていると・・・ほかにも結構高速化の手段がありますね。

コード:
Worksheets(BUNSEKI_DATA_ORIGINAL)



なんて毎回書かずに、2回以上使うものは、変数に入れてやりましょう。

コード:
Dim objOrginalSheet As Excel.Worksheet
Dim objTempSheet    As Excel.Worksheet

Set objOrginalSheet = Worksheets(BUNSEKI_DATA_ORIGINAL)
Set objTempSheet = Worksheets(TEMP_WORKSHEETS)

objOrginalSheetColumns(StartColumnCell).Copy _
    Destination:=objTempSheet.Columns(a) 



引用:
上記の手法で2分14秒



めちゃくちゃ遅いですね。Copy だけなら
数式大量に使っていたりしてませんか?
だったら、自動再計算をとめてから実行しましょう。
AERITH
会議室デビュー日: 2005/10/14
投稿数: 15
投稿日時: 2006-03-09 09:28
ご提示頂いた方法で変数化を試みてみましたが結果は変わりませんでした。
(また前回、時間は間違って"全体"の処理時間を記載していましたので
ここのコピー箇所のみの時間を計測し直したところ1分48秒でした)

なお、元となるデータは会社のイントラから表示させたデータをエクセルに貼り付け、
ボタンを押して集計という流れになっていますので数式等は一切ありません。
一応コピーのコードの前に

ActiveSheet.EnableCalculation = False

というのを追加してはみましたが。


Webから貼り付け、というのがマズイんでしょうか…
todo
ぬし
会議室デビュー日: 2003/07/23
投稿数: 682
投稿日時: 2006-03-09 10:58
値だけのコピーなら

.Range("B1:B10000").Value = .Range("A1:A10000").Value

.Range("B1:B10000").Formula = .Range("A1:A10000").Formula

が速いでしょう。

http://www.officetanaka.net/excel/vba/speed/s11.htm

スキルアップ/キャリアアップ(JOB@IT)