[[20081227112116]]　『VBAで再計算設定』（STEP）　ページの最後に飛ぶ

 『VBAで再計算設定』（STEP）

 いつも参考にさせていただいてます。

 よく似た質問は見かけるのですが、私の作ったブックも計算式が多くて
 ひとつ入力する度に再計算で時間がかかり、ストレスを感じます。
 手動にして必要なときだけ再計算すればいいのですけど、多数の人が入力するので
 出来れば、特定のセル（列）に入力したときと、他のシートに移動（ブック保存の際ではなくて。）するときだけ
 自動計算されるようなことはVBAで可能でしょうか？

 VBAは初心者なんですけど、どなたかご指導お願いいたします。

 ちなみに、ひとつのブックでシートが15く轤｢あり、相互にリンクされています。 
 Excel2003でOSはXPです。

 シートタブで、右クリックで「コードの表示」で表示された部分に下記を
貼り付けた後、一度他のシートを選択して、戻って見てください。

 '--- このシートを選択した際に、手動計算モード
 Private Sub Worksheet_Activate()
    Application.Calculation = xlCalculationManual
 End Sub

 '--- 他のシートを選択した際に、自動計算モード
 Private Sub Worksheet_Deactivate()
    Application.Calculation = xlCalculationAutomatic
 End Sub

 '--- A列（必要に応じて変更）を選択するとこのシートのみ計算を実行
 Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Columns("A")) Is Nothing Then
        Exit Sub
    End If
    ActiveSheet.Calculate
 End Sub

 ブック全体を計算する必要がある場合、
    ActiveSheet.Calculate
 を
    Application.Calculate
 に変更してください。
 (Mook)

 MOOKさん、とても素早いご回答ありがとうございます。感激です。

 希望の形になりました。

 もう一歩突っ込んで、
 「他のシートを選択したとき、何か入力していた場合のみ」自動計算、
 というのは可能でしょうか？
 今のままでも満足ですが、何も入力せずに、シートを見るだけで自動計算されるので
 改善できればと･･･。

 (STEP)

 掲示板でのやり取りであとからいろいろな条件が出てくるというのは、
 やり取りが長引く原因の一つですので、なるべく最初にやりたいことを詳細に書かれた
 方が良いと思います。

 まず全体の確認ですが、自動計算をしたくないシートはある特定のシートだけで
 よいのでしょうか。
 またその変更の頻度は、そのシートと他のシートとそれぞれどのようなものですか？

 >何も入力せずにシートを見るだけで自動計算される
 とありますが、通常はどのような使用の仕方をしているのでしょうか。
 見ただけでは、計算しないと思うのですが・・・。
 (Mook)

 MooKさん ありがとうございます。　返答が遅くなり、申し訳ありません。

 お問い合わせについてですが、細かく内容を説明すると
 ファイルは営業数値管理用のものです。

 営業グループごとブック(2グループの為2ブック)と、全体を集計、情報検索するための集計ブックがあり、
 営業グループのブックには各担当者(6名)ごとに数値を含めた詳細の情報入力用シートと、
 それにリンクして月別に数値が反映される一覧シート、更にそこからリンクしたグループ数値月別集計のシート、
 情報入力シートを集計ブックで一覧(集計)しやすくする為に各担当者の情報入力用シートからリンクして
 ひとつのシートに集計した作業用シートがあります。

 担当者ごとの、情報入力用シートは内容が頻繁に入力されます。担当者ごと、グループ全体の数値一覧シートは
 閲覧が主ですが一部実際の結果に合わせて数値を調整するセルがあり、時々入力します。

 運用方に問題があるかもわかりませんが、数式が多いからか、VLOOKUPなどで検索している時間が長いからか、自動計算に時間がかかります。
 今回教えていただいたコードを、すべてのシートに設定して、情報入力用シートの入力に関してはスムーズになったのですが、
 欲を言えば、何も入力せずに他のシートへ移動した際に再計算されるところが防げないかと考えた次第です。

 >何も入力せずにシートを見るだけで自動計算される
 説明が不十分でした。
 「何も入力せずに、シートを移動した際に自動計算される」つまり、

 ①情報入力用シートに入力
 ②担当別の月別一覧シート閲覧
 ③グループの月別一覧シート閲覧

 ①から②へ情報入力した後に移動した際は当然再計算が必要ですが、
 ②から③への移動、また情報を入力せずに、各担当者ごとの一覧シートを
 それぞれ閲覧した際の再計算、という意味です。
 それぞれの担当者が入力、閲覧する為、手動で再計算を行うような設定にはしたくありません。

 ちなみに今のところ、各ブックのSIZEは約5,000KBですがやはり大きすぎるでしょうか？

 >情報入力シートを集計ブックで一覧(集計)しやすくする為に各担当者の情報入力用シートからリンクして
 ひとつのシートに集計した作業用シートがSIZEを大きくしているのはわかっているのですが…。
 (STEP)

 全体の使用まで確認できていませんがざっと読んだところ、もともとの
 「あるシートが変更された場合」という条件が本来の目的ではなかったようですね。

 今回の場合、シート単位での制御でなければブックで計算を手動にし、計算をしたい
 部分が変更された場合のみ再計算をする方が良さそうです。
 ThisWorkbook の下に、
 Private Sub Workbook_Activate()
    Application.Calculation = xlCalculationManual
 End Sub

 Private Sub Workbook_Deactivate()
    Application.Calculation = xlCalculationAutomatic
 End Sub

 とし、各シートで
 Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Columns("A")) Is Nothing Then
        Exit Sub
    End If
    ActiveSheet.Calculate
 End Sub
 のに必要な部分だけを書けばいいでしょう。

 もし、この部分が共通であれば、各シートに書くより ThisWorkbook 下に書いた方が
 効率的でしょう。
 Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Select Case Sh.Name
    Case "担当別の月別一覧シート閲覧", "グループの月別一覧"
        Exit Sub
    Case Else '--- 上記以外のシートはここに来る
        If Intersect(Target, Sh.Columns("A")) Is Nothing Then
            Exit Sub
        End If
    End Select
    Application.Calculate
 End Sub

 運用に関してもいろいろと気になるところはありますが、EXCEL ファイルはいつか
 壊れるというつもりで（実際結構な頻度で壊れます）、バックアップはしっかり
 とっておいた方がよいかと思います。
 本来であれば、DB で処理をしたいようなケースに見えますが・・・。

 再計算のタイミングや、計算範囲はセル単位からブックまで自由に制御できます。
 ですが、どの部分が変わったら「どこだけ」を再計算したいというのは STEP さん
 しかわからないことですから、制御の仕方を習得し細かく設定をしてのが結果的
 には最良の手段だと思います。
 (Mook)

 Mookさん　詳細のご説明ありがとうございます。

 いろいろ考えながら試していたので返答が遅くなりました。

 やはりこちらの説明が不十分だったと思うのですが
 新しくご提示いただいたコード利用の場合、思うような結果が得られません。

 前段で説明したようにブック内には数種のシートがあり、

 ①入力用シート（個人別）
 頻繁に入力され、一度に多くのセルに入力されます。そのうち1列はシート内に参照先がある、参照元となっている為
 その1列に入力された場合のみ、その都度（そのシートのみ）再計算したい。それ以外のセルは他の
 シートへの参照元になっている為、シートを移動したときにまとめて再計算したい。

 新しくご提示いただいたコードだと、参照列に入力された時に再計算されるのですが、その後他のセルに
 入力してから、他シートへ移動したときに、再計算されない為にそこの部分が他シートで参照できません。

 ブックを手動計算にしておいて、指定列の変更があったときと、シートを移動したときにシート全体に
 何か変更があった場合にブック全体の再計算をするようにしたいです。

 Private Sub Workbook_Deactivate()
    Application.Calculation = xlCalculationAutomatic
 End Sub
 と
 Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Columns("A")) Is Nothing Then
        Exit Sub
    End If
    ActiveSheet.Calculate
 End Sub

 の組み合わせで出来るのでしょうか?いろいろやってみたのですがわかりません。

 ②個人月別一覧　③グループ一覧
 殆どは閲覧に使用。月ごとの数値調整等で一部入力するセルがあり、同シート内で参照もあるので、
 新しくご提示いただいたような形で、指定のセルに入力があったときにそのシートのみ再計算、
 シート移動の際に、シート内に変更があった場合はブック全体の再計算、としたいです。

 ただ、グループ一覧の方は指定セルが散らばって、数も多いのでいるので、難しそうです。

 基本を知らないのであくまで想像ですが、

 Private Sub Workbook_Deactivate()
 の後に、If構文でActiveSheetに何もなかった場合を除いてブックの再計算

 If "ActiveSheet" Is Nothing Then
        Exit Sub
    End If
    Application.Calculate
 End Sub

 みたいなかんじかな？と思ったのですが･･･

 VBAでコードを作ったりするのは、Webなどでざっと見たのですがレベルが違いすぎて
 (専門用語の意味がわからず)サッパリ着いていけませんでした。

 あと、「DBで処理」のDBって何でしょうか？よろしければ教えてください。

 (STEP)

 そんなに難しいことではないので、基本を理解したほうが早いかと思います。

 今回設定しているのは、VBA の中でもイベント処理といわれるもので指定された
 条件で、処理が実行されます。
 ★シートイベント
 Private Sub Worksheet_Change(ByVal Target As Range)
   はシートのセルが変更されたとき

 ★ブックイベント
 Private Sub Workbook_Deactivate()
   は表示が他のブックにうつったとき、
 Private Sub Workbook_Activate()
   は表示がこのブックにうつったとき、
 Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   は、ブック内のシートで変更があったとき：先のシート処理をブックで行う

 などです。
 この中で、
 Application.Calculation = xlCalculationAutomatic
   は、計算を自動モードに設定
 Application.Calculation = xlCalculationManual
   は、計算を手動モードに設定
 Application.Calculate
   は、指定したタイミングで計算を実行
 ということです。

 If Intersect(Target, Sh.Columns("A")) Is Nothing Then
      Exit Sub
 End If
 は変更したセルがA列でなければ、処理を終了するというもので、
 ここで、処理の対象を絞っています。

 このような処理を組み合わせて、希望の処理を行うように設定します。

 ちなみに、DB はDatabase のことで、手軽なところでは Access や
 FileMaker などがあります。
 本格的にやるのであれば、MySQL や PostgreSQL、Oracle などがあります。

 インタフェースとして EXCEL を使用してデータ管理は DB でやる
 ような方法もありますので、興味があればいろいろと見てみると
 よいでしょう。
 (Mook)

 最後の部分に関しては、大域変数でデータを管理しないと処理ができません。
 あまり美しくないですが標準モジュールで
 Public flgCalc As Boolean
 のように宣言し、

 計算を必要とした変更があったら、それを検知した場所で
 flgCalc = True

 Workbook のイベント処理で
 Private Sub Workbook_Deactivate()
     If flgCalc = True Then  ' <= 追加
         Application.Calculate
         flgCalc = False
     End If ' <= 追加
 End Sub

 のように、全体を通して制御を管理する必要があります。
 If "ActiveSheet" Is Nothing Then
 は、文法的に問題がありますし、ActiveSheet は単に現在アクティブなシート
 を指すだけなので変更があったかどうかは後からは知ることができません。
 # If 判定が抜けていたので修正^^;;
 (Mook)

コメント返信：

[ 一覧(最新更新順) ]

[[20081227112116]] 『VBAで再計算設定』（STEP） ページの最後に飛ぶ

[[20081227112116]]　『VBAで再計算設定』（STEP）　ページの最後に飛ぶ