VBAを使ってセルに関数を入力するマクロを高速化する方法はありませんか？

質問

現在、VBAを用いてセルに関数を入力するマクロを作成しています。
具体的には、他の資料から必要な数値のみを引っ張ってくる関数を入力するマクロです。
作成したマクロは以下の通りです。

参照した数値を貼り付けるファイル：book1.xlsのsheet2
参照するファイル名:book2.xls

book2からファイルを参照する際、"本体"と言う名前のシートのw3セルの値によって、参照する場所が変わるようにマクロを組んでいます。
このせいか、マクロを実行したときに非常に遅い速度でデータが貼り付けられていきます。

参照するデータは月１程度で替わりますので、その度にマクロを実行して数値を引っ張ってきます。
マクロの改良で速度は向上できますでしょうか？

分かりにくい説明で申し訳ございませんが、
よろしくお願いします。

gyo=3
i=1

Worksheets("sheet2").Cells(i, 1) = "=LEFT('[Book2.xls]Sheet1'!$A" & gyo & ",LEN('[Book2.xls]Sheet1'!$A" & gyo & ")-1)"
Worksheets("sheet2").Cells(i, 2) = "=IF(本体!W3=""1"",MID('[Book2.xls]Sheet1'!$D" & gyo & ",6,1),"""")&IF(本体!W3=""2"",MID('[Book2.xls]Sheet1'!$I" & gyo & ",6,1),"""")&IF(本体!W3=""3"",MID('[Book2.xls]Sheet1'!$N" & gyo & ",6,1),"""")&IF(本体!W3=""4"",MID('[Book2.xls]Sheet1'!$S" & gyo & ",6,1),"""")&IF(本体!W3=""5"",MID('[Book2.xls]Sheet1'!$X" & gyo & ",6,1),"""")&IF(本体!W3=""6"",MID('[Book2.xls]Sheet1'!$AC" & gyo & ",6,1),"""")"

Worksheets("sheet2").Cells(i, 3) = "=IF(本体!W3=""1"",IF(MID('[Book2.xls]Sheet1'!$D" & gyo & ",10,1)=""1"",RIGHT('[Book2.xls]Sheet1'!$D" & gyo & ",2),RIGHT('[Book2.xls]Sheet1'!$D" & gyo & ",2)+16),"""") &IF(本体!W3=""2"",IF(MID('[Book2.xls]Sheet1'!$I" & gyo & ",10,1)=""1"",RIGHT('[Book2.xls]Sheet1'!$I" & gyo & ",2),RIGHT('[Book2.xls]Sheet1'!$I" & gyo & ",2)+16),"""")&IF(本体!W3=""3"",IF(MID('[Book2.xls]Sheet1'!$N" & gyo & ",10,1)=""1"",RIGHT('[Book2.xls]Sheet1'!$N" & gyo & ",2),RIGHT('[Book2.xls]Sheet1'!$N" & gyo & ",2)+16),"""")"

Worksheets("sheet2").Cells(i, 4) = "=IF(本体!W3=""4"",IF(MID('[Book2.xls]Sheet1'!$S" & gyo & ",10,1)=""1"",RIGHT('[Book2.xls]Sheet1'!$S" & gyo & ",2),RIGHT('[Book2.xls]Sheet1'!$S" & gyo & ",2)+16),"""")&IF(本体!W3=""5"",IF(MID('[Book2.xls]Sheet1'!$X" & gyo & ",10,1)=""1"",RIGHT('[Book2.xls]Sheet1'!$X" & gyo & ",2),RIGHT('[Book2.xls]Sheet1'!$X" & gyo & ",2)+16),"""")&IF(本体!W3=""6"",IF(MID('[Book2.xls]Sheet1'!$AC" & gyo & ",10,1)=""1"",RIGHT('[Book2.xls]Sheet1'!$AC" & gyo & ",2),RIGHT('[Book2.xls]Sheet1'!$AC" & gyo & ",2)+16),"""")"

Worksheets("sheet2").Cells(i, 5) = "=IF(本体!W3=""1"",'[Book2.xls]Sheet1'!$E" & gyo & ","""")&IF(本体!W3=""2"",'[Book2.xls]Sheet1'!$J" & gyo & ","""")&IF(本体!W3=""3"",'[Book2.xls]Sheet1'!$O" & gyo & ","""")&IF(本体!W3=""4"",'[Book2.xls]Sheet1'!$T" & gyo & ","""")&IF(本体!W3=""5"",'[Book2.xls]Sheet1'!$Y" & gyo & ","""")&IF(本体!W3=""6"",'[Book2.xls]Sheet1'!$AD" & gyo & ","""")"

gyo = gyo + 6
i = i + 1
Loop

回答No.1

noname#99913 · ベストアンサー

セルに何か入力されるたびに再計算が実行されるので遅くなるのだと思います。
　マクロの最初に
'再計算OFF
Application.Calculation = xlManual
　マクロの最後に
'再計算ON
Application.Calculation = xlAutomatic
を付けるといいと思います。

お礼

ご回答ありがとうございます。

早速対策を打ってみました。
結果は、
対策前：約11分
対策後：約6秒

劇的に軽減できて、感動しています。

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