”ブック間の3D集計の式をExcelVBAでセルに入力”で実行時間が遅くて困っています

質問

枝番号の一番大きいファイルパス("C:\Documents and Settings\集計ファイル_3.xls")を引数で受け取り、
数式セルを対象にフォントカラー番号よって、
シート内参照の数式、もしくはブック間の3D集計をセルに入力する
モジュールを作成しています。
現在実行に1時間以上かかってしまい困っているのですが、
なんとか改善する方法をご存知ないでしょうか？
アイデアだけでもかまいませんので、是非何かご教授よろしくお願いいたします。

'引渡値　oTargetSheet ：処理対象のシート
'引渡値　s3DFormura ：集計したい枝番最大ブックのパス

Dim oFomulaRange As Range
Dim oFomulaCell As Range
Dim sFormura As String
Dim sCurrentFile As String
Dim iMaxFileNo As Integer
Dim iFileNameStart As Integer
Dim i As Integer

'数式セルのみ選択します
Set oFomulaRange = oTargetSheet.Cells.SpecialCells(xlCellTypeFormulas, 23)

'引数より枝番号を取得　例）3
iMaxFileNo = CInt(Mid(s3DFormura, Len(s3DFormura) - 4, 1))

'ブックが１つしかない場合は何もせずExit
If iMaxFileNo = 1 Then Exit Sub

'ブック間集計の場合のブックパス途中までをセット
'例）"C:\Documents and Settings\[集計ファイル_"
iFileNameStart = InStrRev(s3DFormura, "\")
sCurrentFile = sCurrentFile & "'" & Left(s3DFormura, iFileNameStart) & "["
sCurrentFile = sCurrentFile & Mid(s3DFormura, iFileNameStart + 1, Len(s3DFormura) - iFileNameStart - 5)

For Each oFomulaCell In oFomulaRange
With oFomulaCell
Select Case .Font.ColorIndex

Case 10
'数式の生成
sFormura = "=SUM("
For i = 1 To iMaxFileNo
If i > 1 Then sFormura = sFormura & ","
sFormura = sFormura & sCurrentFile & i & ".xls]計'!"
sFormura = sFormura & .Address(ReferenceStyle:=xlR1C1)
Next i
sFormura = sFormura & ")"
'セルに数式を入力
'例）=SUM('C:\Documents and Settings\[集計ファイル_1.xls]計'!!$A$1
' ,'C:\Documents and Settings\[集計ファイル_2.xls]計'!!$A$1
' ,'C:\Documents and Settings\[集計ファイル_3.xls]計'!!$A$1)
.Formula = sFormura

Case 14
'文字色を緑に変更
.Font.ColorIndex = 10
'10と同じ処理
sFormura = "=SUM("
For i = 1 To iMaxFileNo
If i > 1 Then sFormura = sFormura & ","
sFormura = sFormura & sCurrentFile & i & ".xls]計'!"
sFormura = sFormura & .Address(ReferenceStyle:=xlR1C1)
Next i
sFormura = sFormura & ")"
.Formula = sFormura

Case 43
'文字色を青に変更
.Font.ColorIndex = 5
'=IF(数量<>0,ROUNDUP(金額/数量,0),0)
.FormulaR1C1 = "=IF(RC[1]<>0,ROUNDUP(RC[2]/RC[1],0),0)"

End Select
End With
Next

回答No.2

pkh4989 · ベストアンサー

こんにちは。

件数(回数)が少ないExcelのセル参照及び書き込みの時は、普段遅いとは感じません。
その参照及び書き込み件数(回数)が多くなると、非常に遅くなります。
特にセルに色を設定する処理が遅いですね。

以下の処理を試してみてください。
セル「A1～J10000」へ単純に数字を書き込み処理です。
test1は、セルにそのまま書き込み、test2は配列を利用して書き込みした処理です。

Sub test1()
　　Dim wR　　 As Long
　　'
　　Application.ScreenUpdating = False
　　With ActiveSheet
　　　　For wR = 1 To 10000
　　　　　　For wC = 1 To 10
　　　　　　　　.Cells(wR, wC) = wR * wC
　　　　　　Next
　　　　Next
　　End With
　　Application.ScreenUpdating = True
　　MsgBox "終了"
End Sub

Sub test2()
　　Dim wR　　 As Long
　　Dim tBuf　　As Variant
　　'
　　tBuf = ActiveSheet.Range("A1:J10000")
　　For wR = 1 To 10000
　　　　For wC = 1 To 10
　　　　　　tBuf(wR, wC) = wR * wC
　　　　Next
　　Next
　　ActiveSheet.Range("A1:J10000") = tBuf
　　MsgBox "終了"
End Sub

test1とtest2の処理速度の差が全然違うのが分かります。
ようするに、Excelのセルをそのまま参照及び、書き込みするのではなく、配列及び変数などを利用すれば、
早く処理が出来ると思います。
但し、セルに色を設定するのは、配列又は変数には出来ません。

気が付いたところがあれば、

With oFomulaCell
Select Case .Font.ColorIndex

Case 10
'数式の生成
sFormura = "=SUM("
For i = 1 To iMaxFileNo
If i > 1 Then sFormura = sFormura & ","
sFormura = sFormura & sCurrentFile & i & ".xls]計'!"
sFormura = sFormura & .Address(ReferenceStyle:=xlR1C1) '← ここですね
Next i

変数を使えば、毎回セルを参照しなくても良いと思います。

Dim wAdr as String

With oFomulaCell
　　wAdr = .Address(ReferenceStyle:=xlR1C1) '←変数に代入
　　Select Case .Font.ColorIndex
　　Case 10
　　　　'数式の生成
　　　　sFormura = "=SUM("
　　　　For i = 1 To iMaxFileNo
　　　　　　If i > 1 Then
　　　　　　　　sFormura = sFormura & ","
　　　　　　　　sFormura = sFormura & sCurrentFile & i & ".xls]計'!"
　　　　　　　　sFormura = sFormura & wAdr '←変数値を代入
　　　　　　End If
　　　　Next i

セルの参照及び書き込みの件数(回数)が多いと思いますので、なるべく、変数又は配列を利用するように
変えてみてください。

お礼

本当です。
test2のほうが全然速いです！
10000レコードごときで体感できるほど違うもんなんですね！
ActiveSheet.Range("A1:J10000") = tBuf
で書き込む際にtest1と同じことなのかと思い、
かえって配列の１手間が無駄そうに思ったのですが、
全然理解が浅いってことなんですね！
まだちょっと不思議な感じはしてしまうのですが。。。
ありがとうございます。
大変勉強になります。
左寄せになってしまった読みにくいコードを読んでいただいて
貴重なお時間を本当にありがとうございます。
今日は、上記を踏まえコードを修正してみたいと思います。
pkh4989さん、わかりやすい丁寧なご指導ありがとうございました。

回答No.1

KenKen_SP

こんにちは。

あまりコードの内容はみてませんが、一般論で言えば、

■ 画面描写を停止する

　　Application.ScreenUpdating = False
　　～処理～
　　Application.ScreenUpdating = True

■ 数式の再計算を停止する

　　ループの先頭で再計算を Manual にして再計算発生に
　　よる実行速度の低下を防ぐもの。

　　コードの実行完了時、エラー発生時に Automatic に
　　戻す処理を忘れないで下さい。

　　Application.Calculation = xlCalculationManual
　　～処理～
　　Application.Calculation = xlCalculationAutomatic

　　注意）
　　ループ処理の中でセルの値が変化し、その値を使った
　　数式の計算結果を参照しながら条件分岐させるような
　　場合は、適所で再計算を実行する必要があり。

Excel VBA は、この２点だけでもパフォーマンスの向上が
非常に期待できます。

お礼

早速のご回答ありがとうございます。
ご指摘の方法（画面描写停止、数式再計算停止）は、
メインルーチンのほうで制御しておりまして、
書き込みさせていただいたのはサブルーチンになっております。
このサブルーチンのロジックそのものがあまりスマートではなく
遅いのではなかろうかと思って、ご質問させていただいた次第です。