VBAで離れたセルの値を一括で代入する方法について

お世話になります。
VBA初心者です。

現在
Sub test()

Dim base As Worksheet
Set base = ThisWorkbook.Worksheets(1)

Dim checkBook As Workbook
Set checkBook = Workbooks("チェック .xlsm")
Dim checkSheet As Worksheet

checkSheet.Cells(2, 12).Value = base.Cells(8, 49).Value
checkSheet.Cells(2, 13).Value = base.Cells(8, 54).Value
checkSheet.Cells(2, 14).Value = base.Cells(8, 55).Value
checkSheet.Cells(2, 15).Value = base.Cells(8, 58).Value
checkSheet.Cells(2, 18).Value = base.Cells(10, 49).Value
（同じように値を入れるコードが続く）

End Sub

のようなプログラムを（一部簡略化してあります）を記述しています。

このように一つずる値を入れるプログラムを記述すると、Excel2013では処理が遅くなると別の質問で教えて頂きまして、やりかたを調べていたのですがよくわからず再度ご質問させて頂いております。

ご教示よろしくお願いいたします。

質問者からの補足コメント

• 

  ご教示ありがとうございます。
  サイトの記事を読んでみたのですが、
  私の場合、”主語”が複数あるのでどうあてはめていいかわかりません・・。

  No.1の回答に寄せられた補足コメントです。 補足日時：2016/10/12 12:23

• 

  ご教示ありがとうございます。
  昨日、
  https://oshiete.goo.ne.jp/qa/9457146.html
  こういったご質問をさせていただきました。
  
  質問の内容が異なってきたので改めてご質問させていただきました。
  あらかじめ質問文に記述しておけばよかったですね、すいません。

  No.2の回答に寄せられた補足コメントです。 補足日時：2016/10/12 12:39

• 

  ご教示ありがとうございます。
  
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual
  ・・・・セルの代入
  ・・・・セルの代入
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Application.Calculation = xlCalculationAutomatic
  
  とすでに記述をしていたため、代入の改善策をさがしておりました。
  言葉足らずですいません。
  
  連続してないと無理なのですね・・。
  おっしゃるとおり、見やすい配置にするためにあえて代入をと考えておりましたので、セルの配置も厳しいです・・。

  No.3の回答に寄せられた補足コメントです。 補足日時：2016/10/12 13:31

• 

  ご教示ありがとうございます。
  お礼が遅れてすいません。
  
  Excel2013ですと処理に約１０秒かかります。
  Excel2007ですと約５秒で終わります。

  No.4の回答に寄せられた補足コメントです。 補足日時：2016/10/14 09:57

No.5 ベストアンサー 回答者： WindFaller 回答日時： 2016/10/15 00:04

やはり聞いてよかったと思います。

5秒が10秒ですか、Excel2013 になった理由でマクロで、そのようなことはにならないような気がします。念のため、ファイルの肥大化はしていませんよね。

私もExcel 2013に変えた時に、若干おそいような気がしましたが、すぐに慣れました。こういう問題は、総合的な判断が必要とされますから、今のやり取りの中では、当て推量ばかりになってしまいます。

>Application.ScreenUpdating = False
>Application.EnableEvents = False
>Application.Calculation = xlCalculationManual

これで、直らない時は、マクロ自身に問題を求めるべきではないかもしれません。

MSDNの方は、こちらから紹介しましたので、後は、ご自身がどう判断されるかですが、マクロとしての解決方法は示しましたが、変わらないとしたら、他の要因を探すべきかもしれません。

例えば、
・アドインやCOMアドインなどが邪魔していないか。
・外部リンク、OLEオブジェクト・オートシェイプの残骸、名前定義の残骸（※)
・自動保存が頻繁に行われている。
・Pコードの存在（コンパイルした中間コード)。
・エアロ（Aero/ Aero Glass)がある。
・OneDrive の利用を停止してみる。（←デフォルトでは使うようになっていますが、これを使えば確実に遅くなります。)
・ネットワーク関連の問題。

※これらが直接、マクロを遅らせるということはありません。

Excelは、それぞれの機能別にメモリの割り振りがあるせいか、一定量まではなんの問題もなく、そこそこ動くのに、ある一定量を越えると、途端にブレーキが掛かってしまうということがあります。

参考
http://blog.eset-smart-security.jp/2015/07/excel …

この回答へのお礼

表現があいまいですいませんでした。

ご教示いただいた方法にも当てはまるところがないようですので、このまま使ってみようと思います。

ありがとうございました。

お礼日時：2016/10/15 09:18

No.4 回答者： WindFaller 回答日時： 2016/10/13 12:28

こんにちは。

>このように一つずる値を入れるプログラムを記述すると、Excel2013では処理が遅くなると別の質問で教えて頂きまして、

私は、Excel 2013ですが、顕著にそのような状況下に陥ったことはまだありませんね。

それに、オブジェクトを一つずつ処理しているわけではなく、基本的にValue to Value で、そんなに遅くなるのでしょうか。計算式でも入っているということでしょうか。トータルな時間がどのぐらい掛かっているかということですね。時々、ここの質問で、1秒2秒は長すぎる、いや、200m秒でも長過ぎるとおっしゃった質問者があります。

https://oshiete.goo.ne.jp/qa/9457146.html
に出てくる話は、ごくごく一般論ですが、別に、Value to Value に言及しているわけでもありません。Range**.Copy -> Range*.PasteSpecial との比較では、Value to Value は、値オンリーでデータ量が軽いですから、どちらが速いかは、今の状態でははっきり言えません。

>Application.ScreenUpdating = False
>Application.EnableEvents = False
>Application.Calculation = xlCalculationManual

通常、これで十分です。
もう一つ二つ、特殊なものがありますが、通常は使わないです。

>連続してないと無理なのですね・・。
Rangeのコピーと分散であってもValue　to Value のコピーでは、どちらが速いかは分かりませんね。

私の場合は、一旦、配列変数に入れてしまうのですが、今回は、そういうメリットがあるのか分かりません。（配列は、癖に近いです)

VBAとしては、別な話になります。

「マクロを高速化しよう!」（読み物)
http://vbae.odyssey-com.co.jp/column2/s21401.html

MSDNのほうは、私の忘れている話が一杯あります。
「VBA マクロの高速化」
https://msdn.microsoft.com/ja-jp/library/office/ …

ここを読んでいて思い出したことですが、Range型の範囲を一旦変数に置いたほうがよいです。

たぶん、実際は、この塊が下方向にずれていくでしょうから、Offset 関数を使えばよいと思います。別に、今回に限ってで書くわけではなく、私は、だいたいこんな書き方になります。

'//
Sub Test1()
　Dim Arr()
　Dim c
　Dim i As Long, j As Long
　Dim R1 As Range, R2 As Range
　Dim base As Worksheet, checkSheet As Worksheet
　Set base = Worksheets("Sheet1")
　Set checkSheet = Worksheets("Sheet2")
　i = 0: j = 0
　With base
　　Set R1 = .Range("AW8,BB8,BC8,BF8,AW10")
　　For Each c In R1
　　　ReDim Preserve Arr(i)
　　　Arr(i) = c.Value
　　　i = i + 1
　　Next c
　End With
　With checkSheet
　　Set R2 = .Range("L2,M2,N2,O2,R2")
　　For Each c In R2
　　　c.Value = Arr(j)
　　　j = j + 1
　　Next c
　End With
End Sub
'//
なお、
.Range("AW8,BB8,BC8,BF8,AW10")
は、マクロとテキストエディタで作りました。

No.3 回答者： tatsu99 回答日時： 2016/10/12 13:25

https://oshiete.goo.ne.jp/qa/9457146.html
の回答では、解決案として2つの方法が提示されています。

方法１：
rangeオブジェクトを使う。
Range("A1:A20").Value = Range("B1:B20").Value
のようにすると、一括して代入できます。
しかしながら、今回はセルが連続していないので、これは使用できません。
データのセルの配置をrangeオブジェクトが使用しやすいように変えれば話は別ですが、
たぶん、それは無理かとおもいます。

方法２：
・一々セルを更新表示させない。
スタート前に、Application.ScreenUpdating = False を入れ、画面の更新表示を止める。
終了前にApplication.ScreenUpdating = True で戻して置く。
これが、対処可能な方法になります。
以下のようなコードになります。
-----------------------------------------------------------
Application.ScreenUpdating = False 'マクロの先頭
・・・・セルの代入
・・・・セルの代入
Application.ScreenUpdating = True 'マクロの最後

No.2 回答者： くんこば 回答日時： 2016/10/12 12:28

ごめんなさいね。

With使ったらBaseかchecksheetのどちらかを書かなくていいよ、ということです。

これを使わないと遅くなる、というのは初めて聞いたよ。

No.1 回答者： くんこば 回答日時： 2016/10/12 12:10

遅くなるかな？

http://officetanaka.net/excel/vba/beginner/16.htm
こういう書き方も出来ます。
ループには出来そうにないですしね。