《エクセル2000》VBAの処理が遅いのはなぜ？

質問

ここのサイトの皆さんのご協力により、何とかVBAが完成しました。

一応問題なく動いてはいるのですが、処理速度がどうも遅いように感じられます。

画面の動きを見る限りでは、時間がかかっているのはデータの転記の部分のようです。

VBAの処理の概要は「コピー元ファイルのコピー元シートから、コピー先ファイルのコピー先シートへ、セルの内容を値でコピーする」というものです。

転記箇所はたった8箇所なのに、
1.コピー先ファイルをオープン
2.コピー先シートの一番下の空白行を探す
3.8箇所を次々に転記する

…という一連の処理に、20秒近くかかってしまいます。
なぜでしょうか。

転記部分のソースはこのような作りです。

'（１）コピー元ファイルのコピー元シートをアクティブにし、コピー

Windows("コピー元.xls").Activate
Sheets("コピー元").Activate
ActiveSheet.Range("B4").Select
Selection.Copy

'（２）ペースト（コピー先シート、セルは選択済み…だと思います）　＆　アクティブセル移動

Windows("コピー先.xls").Activate
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveCell.Offset(0, 4).Select
Application.CutCopyMode = False

なお2つのファイルはどちらもサーバ上の、同じフォルダの中にあります。
原因が思い当たる方、どうかよろしくお願いします。

回答No.2

hana-hana3 · ベストアンサー

※　高速化の基本

コピー元
Ａ：
Windows("コピー元.xls").Activate
Sheets("コピー元").Range("B4").Copy
Ｂ：
Windows("コピー元.xls").Sheets("コピー元").Range("B4").Copy

コピー先
Ｃ：
Windows("コピー先.xls").Activate
Selection.PasteSpecial
Application.CutCopyMode = False
Ｄ：
コピー先を指定した方が可読性が高まる。
「Windows("コピー先.xls").Activate」だけでは、どのシートが開いているかが解らないので、コピーミスを生じる。
Windows("コピー先.xls").Sheets("コピー先").Range("B4").PasteSpecial

コピーだけなら、一行で可能。
Windows("コピー元.xls").Sheets("コピー元").Range("B4").Copy Windows("コピー先.xls").Sheets("コピー先").Range("B4")

○ Activate/Select を多用すると速度低下。
○ ActiveSheet/Selection も前行でシートや範囲が指定されていれば特に必要なし。
○ サーバー上のファイルになると、転送時間や混雑状況から自分のＰＣ内のファイルよりは時間が掛かるのは仕方ない。

※コピーの例

'コピー先シートの一番下の空白行を探す
Dim Line As Long
Line = Windows("コピー先.xls").Sheets("コピー先").Range("A65536").End(xlUp).Row + 1

'次々に転記する
With Windows("コピー元.xls").Sheets("コピー元")
.Range("B4").Copy _
Windows("コピー先.xls").Sheets("コピー先").Range("A" & Line).Offset(0, 10)
.Range("B6").Copy _
Windows("コピー先.xls").Sheets("コピー先").Range("A" & Line).Offset(0, 20)
.Range("B8").Copy _
Windows("コピー先.xls").Sheets("コピー先").Range("A" & Line).Offset(0, 30)
.Range("B10").Copy _
Windows("コピー先.xls").Sheets("コピー先").Range("A" & Line).Offset(0, 40)
End With

お礼

こんにちは、ご回答ありがとうございます。

＞サーバー上のファイルになると、転送時間や混雑状況から自分のＰＣ内のファイルよりは時間が掛かるのは仕方ない。

これは私も考えましたが、いくら何でも20秒は長いですよね？

そこですみません、質問です。
文末の「コピー先シートの一番下の空白行を探す」「次々に転記する」部分についてです。

１）参考書を引き引き、コードを読ませて頂きました。
その中で「Range("A" & Line).」の部分がどのような仕組みになっているのかがよくわかりません。
"A"とは列のことで、それとLineに格納してある数字を連結して、セルの場所を表しているのでしょうか？

また、この作業ですが、ファイルの仕様上、空白行を探すキーとしているセル（列）は一番左ではありません。
ですがコピーするセルはA列からになっています。
その場合、Lineの取り扱いはこのままのコードで大丈夫でしょうか？

２）コピーは「値」で行いたいのですが、文末のコードの場合、それは反映されるでしょうか？

回答No.6

KenKen_SP

＞Range("○")の○部分はどのように記述すればいいのでしょうか。

事前に今回の処理対象となる2つのブックが開かれているのが前提です。
#3 の uozakura203 さんのコードのように変数で保持しておくのがいいと思います。

ここで、変数の型についてですが、何のデータが代入されるか限定できない場合は Variant 型 になります。数値、、などと限定できるなら、それにあった型で宣言します。

また、変数を別々に 8 個宣言しても良いのですが、配列変数を使えば 1 回の宣言で OK ですし、コードがすっきりします。

　　Dim Buf(7) as Variant '配列変数で要素数は8個（0-7）

　　'データを配列変数に保持
　　With Workbooks("コピー元.xls").Sheets("コピー元")
　　　　Buf(0) = .Range("A1").Value
　　　　Buf(1) = .Range("E1").Value
　　　　Buf(2) = .Range("G1").Value
　　　　'（略）
　　End With

　　'データ転記
　　With Workbooks("コピー先.xls").Sheets("コピー先")
　　　　.Range("A1").Value = Buf(0)
　　　　.Range("E1").Value = Buf(1)
　　　　.Range("G1").Value = Buf(2)
　　　　'（略）
　　End With

余談ですが、Set ステートメントを使うと次のように書くことができます。ただ、処理スピードは上記に比べ劣るかもしれません。その一方で、コードの可読性は高まります。

　　Dim SH1 as Worksheet, SH2 as Worksheet

　　'オブジェクト変数にセット
　　Set SH1 = Workbooks("コピー元.xls").Sheets("コピー元")
　　Set SH2 = Workbooks("コピー先.xls").Sheets("コピー先")
　　'転記
　　SH1.Range("A1").Value = SH2.Range("A1").Value
　　SH1.Range("B1").Value = SH2.Range("B1").Value

このような書き方でしたら、一々アクティブセルを移動させる必要はありませんよね。不要な Select や Activate などのオブジェクトへのアクセスをできるだけ減らすことで大分高速化できると思います。

お礼

こんばんは。お礼が遅くなりまして…

お陰様で、ずいぶんと高速化することができました。
ファイルのオープンとクローズがあるので、あっという間に終了…とはいきませんが、懸案の部分は別物のように早く動いています。

いつもいつもお世話になっております。ありがとうございました。

回答No.5

hana-hana3

＞"A"とは列のことで、それとLineに格納してある数字を連結して、セルの場所を表しているのでしょうか？

そうです。
Line=100 なら、Range("A100") と指定したのと同じ意味になります。

＞ファイルの仕様上、空白行を探すキーとしているセル（列）は一番左ではありません。

指定するのは、"A65536" でも "Z65536" でも構いませんが、「誤りのない動作」をさせるためには、記入行として確定出来る列（基準）を選択するのが良いでしょう。

＞コピーは「値」で行いたいのですが

式も入ってしまうので、シートの切り替えを行うと速度低下の原因になるので、＃３さんのアドバイスのように「変数」に格納して仕舞えば良いでしょう。

' F列基準で最終行取得
Line = Windows("コピー先.xls").Sheets("コピー先").Range("F65536").End(xlUp).Row + 1

' A列基準でオフセット設定
With Windows("コピー先.xls").Sheets("コピー先").Range("A" & Line)
.Offset(0, 0) = a1
.Offset(0, 12) = a2
.Offset(0, 15) = a3
End With

お礼

お礼が遅くなり、すみません。
あれから少しずつですが、変数もわかるようになってきてはいます。

一番最後の「A列基準でオフセット設定」は面白いですね。こんなシンプルな使い方もできるのですね。

何度も、どうもありがとうございました。

回答No.4

KenKen_SP

うーん、、ファイルのオープンに時間がかかっている気がしますが、、
他にも高速化のための方法がありますが、今回該当しそうな部分だけをとりあえず、補足いたします。

＞「値でコピーしたい」場合、このソースでよろしいでしょうか？

(2)-C をお読み下さい。値の貼り付けなら値を直接セルに書込んだ方が、クリップボードを経由するより高速なのは明らかです。#1 の popesyu さんのコードがこのことについて触れておられます。

(1) 変数の宣言をする
　　変数の宣言をおこなわずに変数を使うと、全てバリアント型という
　　一番メモリを消費する型で処理される。処理速度低下につながる。
　　適切な型で変数の宣言を行う。

　　Dim strSheetName as String
　　strSheetName = "Sheet1"

(2) 何度もオブジェクトを呼びださない（重要）
　　オブジェクト（セルやシートなど）へのアクセスは時間がかかる。

　　A) With ステートメントや Set ステートメントを活用する。

　　Workbook("Book1.xls").Sheets("Sheet1").Range("A1").Value = 10
　　Workbook("Book1.xls").Sheets("Sheet1").Range("E1").Value = 50
　　　　↓
　　With Workbook("Book1.xls").Sheets("Sheet1")
　　　　.Range("A1").Value = 10
　　　　.Range("E1").Value = 50
　　End With

　　または

　　Dim Sh as Worksheet
　　Set Sh = Workbook("Book1.xls").Sheets("Sheet1")
　　With Sh
　　　　～
　　End With

　　B）Select や Activate をなるべくしない

　　Workbook("Book1.xls").Activate
　　Sheets("Sheet1").Activate
　　Range("A1").Activate
　　Selection.Copy
　　　　↓
　　Workbook("Book1.xls").Sheets("Sheet1").Range("A1").Copy

　　C）値の貼り付けなら Copy しない

　　Range("A1").Copy
　　Range("E1").SpecialPaste Paste:=xlValues
　　　　↓
　　Range("E1").Value = Range("A1").Value

お礼

ありがとうございます。
内容もさることながら、こんなに盛りだくさんな文章なのに、とても読みやすいですね。羨ましいです。

＞うーん、、ファイルのオープンに時間がかかっている気がしますが、、

おそらくこの部分も関係していると思います。
ただ「ファイルをオープンするだけ」のVBAを作って実行してみたところ、待ち時間は何とか許容範囲内でしたので、今回はこの部分には手をつけないことにしました。

そしてさらにまたまた質問なのですが…すみません。
もしもう無理、ということでしたらどうか仰って下さい。

■今回、最下段書いていただいた(c)の方法を使いたいと思います。
データの転記はファイルをまたいで行われますので、少なくともファイル名及びシート名から指定する必要があると思いますが、Range("○")の○部分はどのように記述すればいいのでしょうか。

最初は\でつなげればと考えましたが、それはファイル名の指定までなんじゃ、と思いまして…

また、参考資料に「Rangeはアクティブファイルのみ有効」と書いてありました。
2つのファイルを同時にアクティブにすることはできるのでしょうか。

独学部分が多々あるため、とんでもなくピントのずれた質問でしたらすみません。
よろしくお願いいたします。

回答No.3

noname#109516

多分画面表示と、コピーに時間がかかっているのではないですか。
私なら3.の処理を次のようにします。
Windows("コピー元.xls").Activate
a1=Sheets("コピー元").Cells(4,2).Value
a2=
a3=

a8=
と先ずコピーするセルの値を変数a1～a8に取得します。
Cells(y,x)はアドレスをR1C1形式で指定します。(R,C)です。
次に
Workbook("コピー先.xls").Sheets("コピー先").Cells(コピー先1).Value=a1
Workbook("コピー先.xls").Sheets("コピー先").Cells(コピー先2).Value=a2
の要領でa8まで繰り返します。

これでそれなりに早くなると思いますが。
画面表示を変えず、セルをSelectせず処理をしています。

お礼

こんにちは、ご回答ありがとうございます。
R1C1形式、調べました。使ったことはありませんが、便利そうですね。
そしてまたまた質問が…

１）a1～a8は変数、ということでしょうか？
もしその場合、最初に宣言が必要かと思われますが、データ型の種類は何が最適でしょうか？

２）他の方とかぶりますが、「値でコピーしたい」場合、このソースでよろしいでしょうか？

回答No.1

popesyu

そらまぁマクロの記録で作ったものそのままでは速度は期待できません。
例えばそのコードを下記のように修正するとか。
Workbooks("コピー先.xls").Worksheets("Sheet1").Range("a1") = Workbooks("コピー元.xls").Worksheets("Sheet1").Range("a1")

A1のものをA1に写すだけの見本なので色々と工夫する必要はあるでしょうが。

お礼

早速のご回答、ありがとうございます。

＞そらまぁマクロの記録で作ったものそのままでは速度は期待できません。

ばれましたか（笑）
初心者なもので、手書きと記録で作った部分が混在しています。
また、マクロの記録で作ったものはいくつかあるのですが、内容は似たようなものなのに、なぜか速度がバラバラで不思議だなと思っていました。

閑話休題、すみませんさらに質問です。
セルの内容は値で貼り付ける必要があるのですが、書いて頂いたコードはその仕様に対応しているでしょうか？