こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

”ブック間の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))

'ブックが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

投稿日時 - 2007-06-01 11:57:34

QNo.3047783

暇なときに回答ください

質問者が選んだベストアンサー

こんにちは。

件数(回数)が少ない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 wAdras 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

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

投稿日時 - 2007-06-02 12:59:00

お礼

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

投稿日時 - 2007-06-04 09:10:24

ANo.2

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(2)

ANo.1

こんにちは。

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

■ 画面描写を停止する

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

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

  ループの先頭で再計算を Manual にして再計算発生に
  よる実行速度の低下を防ぐもの。
  
  コードの実行完了時、エラー発生時に Automatic に
  戻す処理を忘れないで下さい。

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

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

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

 

投稿日時 - 2007-06-01 12:38:42

お礼

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

投稿日時 - 2007-06-01 13:13:24

あなたにオススメの質問