Excel (VBA)

Excel VBAに関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(指定なし : 指定なし)
セル内の計算式の答えが変わった時にマクロを実行したい。
投稿日時: 21/10/26 19:58:15
投稿者: su_ko

現在VBAを勉強しています。
自分で作成した構文は以下の様になっています。
 
セルA1にある計算式の答えが変更された場合に、Macro1 を実行する

Private Sub Worksheet_Calculate()
    Static A1
    If A1 <> Range("A1").Value Then
        A1 = Range("A1").Value
        Macro1
    End If
End Sub
 
実行するマクロは
 
Sub Macro1()
    Range("L1").Value = Range("K1")
    Range("J1").Value = Range("I1")
End Sub
 
となります。
 
これでA行に対する動きは問題無くできたのですが
このシートには40行のデータが入っています。
 
40行分の構文を書くのが大変だと思い
どうにかして構文を纏めたいと思っています。
 
条件としましては、A列の中には計算結果が変わる行と
計算結果が変わらない行が混在するため、計算結果が変わった列のみに
マクロを実行したいと考えています。
 
経験が浅く、どの様に対応したらよいか途方に暮れています。
 
何か良きアドバイスを頂ければと思っています。
宜しくお願い致します。

回答
投稿日時: 21/10/26 21:40:44
投稿者: WinArrow
投稿者のウェブサイトに移動

 
数式セルにおいて、
数式の結果が変わったことを検知するイベントは無いと思います。
数式が入っているセルを判断することは可能です。
そして、数式の内容を分析して、参照セルを取り出すことも可能です。
参照セルが変わったときのイベントは用意されています。
でも、参照が配列だったりすると、結構面倒ですが・・・・
 
それから、揮発性関数は使っていませんよね?
揮発性関数が使われていると、数式と関係のないセルの値が変わっても、
掲示のプロシジャは実行されてしまいます。
 
 
OSとExcelのバージョンは、必ず、選択してくださいね・・・・
 
 

回答
投稿日時: 21/10/26 23:23:10
投稿者: simple

すみません、やりたいことは漠然とは理解するのですが、
背景となることも教えてもらうとありがたいと思っています。
 

引用:
これでA行に対する動きは問題無くできたのですが
このシートには40行のデータが入っています。

「行」と、「列」という言葉は正確ですか?A行というのがわかりません。
 
(Q1)端的に言って、そのシートは何をするものなんですか?
(Q2)シートのレイアウトは概要どのようなものですか?
(Q3)そのシートのセルの相互の参照関係は、ざっくり言ってどういうものなんですか?
(Q4)計算がされたつど、なんらかの値のコピーをするもののようですが、
    そうしたことが必要となる理由はどんなことなんですか?
(Q5)また、その値のコピーによって、さらに計算が起きるようなことはないのですか?
とりあえず、背景となることも含めて教えていただけますか?
 
場合によっては、別のもっと良い対応策が提案される可能性もありますよ。

回答
投稿日時: 21/10/27 06:20:14
投稿者: simple

前発言の補足的なコメントをします。
 
(1)そうした計算は私にとって珍しいものだったので、
   そうした動作をさせる理由を確認したい。

A1が計算されたときに、
・L1 ← K1
・J1 ← I1
を実行するということですが、
 
@これは計算後、瞬時に実行する必要があるのですか?
 その場合、K1セルやI1セルがA1に依存しているからですか?A1セルとの関係は?
 そうであれば、普通は、
 ・K1セルなどでA1セルを参照した式としておき、
 ・L1セル = K1 セルとするなどの式で対応しますよね。
 
Aそれとも、瞬時でなくてもよいので、単に、
  次の作業としてMacro1の作業内容を実行したいということですか?
 
Calculateイベントにそうした作業の連鎖を書いていくというスタイルは
余りとらないのではないかと思います。
むしろ、通常のマクロのなかで、A1を操作(計算)、次にMacro1を実行、
といった指示を順次行っていくスタイルをとるのが、一般的かと思います。
 
Calculateイベントの中に書くべき作業連鎖が、たくさんのセルで発生する
という例に、(質問掲示板等で)今まであまり遭遇したことがなかったので、
全体の作業はどんなことなんだろうか、という疑問が沸いたわけです。
(もし、可能であれば、どんな作業を想定しているのか、
  簡単に概要を説明してもらうとありがたいですね。)
 
(2)要件どおりにするとした場合の方法(案)

・別シートの対応するセルに計算前の値をもっておいて、
・それぞれを比較して、変化があれば、マクロ(Macro1などの)を実行する
という作業をWorksheet_Calculateに書くということではどうですか?
 
なお、懸念点は、
・実行したマクロ(Macro1などの)によって、再計算が起き、
・再度、結果照合が必要、
というイベントの連鎖的なものが起きてしまわないか、という点ですね。
その点はいかがですか?
(これも作業内容に依存した話ですが、内容の確認です。)

回答
投稿日時: 21/10/27 11:38:32
投稿者: sk

引用:
これでA行に対する動きは問題無くできたのですが
このシートには40行のデータが入っています。

引用:
条件としましては、A列の中には計算結果が変わる行と
計算結果が変わらない行が混在するため、計算結果が変わった列のみに
マクロを実行したいと考えています。

クラスモジュールを使用してもよいかどうかによるのではないかと。

回答
投稿日時: 21/10/27 13:51:01
投稿者: hatena
投稿者のウェブサイトに移動

配列に初期値を格納しておいて、それと比較すればいいような気がします。
 
Workbook_Open()で配列の初期化をしたいので、ThisWorkbookモジュールに記述します。
Sheet1が対象シートとします。
 

Option Explicit
Private A(1 To 40) As Variant

Private Sub Workbook_Open()
    Dim i As Long
    For i = 1 To 30
        A(i) = Sheet1.Cells(i, 1).Value
    Next
End Sub

Private Sub Workbook_SheetCalculate(ByVal sh As Object)
    If Not sh Is Sheet1 Then Exit Sub
    With sh
        Dim i As Long
        For i = 1 To 40
            If A(i) <> .Cells(i, 1).Value Then
                A(i) = .Cells(i, 1).Value
                Macro1 sh, i
            End If
        Next
    End With
End Sub

Sub Macro1(sh As Worksheet, rowNum As Long)
    With sh
        .Range("L" & rowNum).Value = .Range("K" & rowNum).Value
        .Range("J" & rowNum).Value = .Range("I" & rowNum).Value
    End With
End Sub

回答
投稿日時: 21/11/01 17:57:28
投稿者: WinArrow
投稿者のウェブサイトに移動

1週間ちかく、質問者さんは、無反応ですが・・・・
前レスで書きましたが、
計算式の結果が変わったら
というイベントは用意されていません。
 
単純に考えると
hatenaさんのレスのようになると思いますが、
シートに入っている数式の中に「揮発性関数」があるとすれば、
無限ループ状態になる可能性があります。
(あくまでも可能性です)
あまり、現実的な対応とは、考えにくいですね・・・勝手な感想です。
 
このようなことを考えた背景、使用環境などの状況
を考えて、仕様の再考をお勧めします。

トピックに返信