ｅｘｃｅｌでセルの値が変わったら通知したい

計算式の入ったセルで、結果の値が変わったときに変わったことを知らせる為に画面に通知したいのですが、
考えたのはユーザファンクション（ufnc）を作成し、
該当セルに「=ufnc(式)」を入れておき、ufncを
function ufnc(p as integer) as integer
msgbox "値が変わりました"
ufnc = p
end function
としてみたのですが、単に値が変わったとき以外にも
何かしらシートをいじくると"値が変わりました"と表示されます。値が変わったときだけ表示できないでしょうか？

No.4 ベストアンサー 回答者： Musaffah 回答日時： 2005/03/26 10:59

ちょいと面倒くさいですが、これでどうでしょう？

長くなりました。ごめんなさい。

やはり変更前の値を保持する必要がある以上、変数を用意するのは避けられないでしょう。
そこで、クラスモジュールと動的配列定義を使ってやってみました。
計算式のセルをクラス化（以降、値クラスと呼びます）させ、値クラスを配列で保持し、計算式が増えるたびに動的に再定義するクラス（以降、計算式配列クラスと呼びます）を用意します。

本当だったら細かく説明したいところですが、時間がかかりそうなので勘弁してください。（まぁ、見ればわかると思います。）
簡単に言うと、自身のセル（Rangeオブジェクト）をキーとして、動的配列に格納している前回値と今回値を比較するという内容です。

まず、クラスモジュールを２つ(cCalArr,cVal)を追加してください。
で、それらのクラスをこんな感じでコーディングします。

====== cCalArクラス ======
Private ar() As cVal
Private num As Integer

'初期化
Private Sub Class_Initialize()
Dim ar(1) As cVal
num = 0
End Sub

'チェック
Public Function check(r As Range, val As Variant) As Variant
Dim i As Integer

If num > 0 Then
For i = LBound(ar) To UBound(ar) '計算式数分
If ar(i).chkRange(r) = True Then '同一セル発見？
If ar(i).chkVal(val) = False Then '値が違う？
MsgBox "値が変わりました"
End If
check = ar(i).Value
Exit Function
End If
Next i
End If

'ここに来る=新規登録
Call newEntry(r, val)
check = val

End Function

'登録
Private Sub newEntry(r As Range, val As Variant)
num = num + 1
ReDim Preserve ar(1 To num) As cVal

Set ar(num) = New cVal
ar(num).Range = r
ar(num).Value = val
End Sub


Private Sub Class_Terminate()
Dim i As Integer

For i = LBound(ar) To UBound(ar)
Set ar(i) = Nothing
Next
End Sub

====== cValクラス =====
Private r As Range
Private val As Variant

Private Sub Class_Initialize()
val = 0
Set r = Nothing
End Sub

Public Property Let Range(rng As Range)
Set r = rng
End Property

Public Property Let Value(v As Variant)
val = v
End Property

Public Property Get Value() As Variant
Value = val
End Property

Public Function chkRange(rng As Range) As Boolean
chkRange = False
If r Is Nothing Then Exit Function
If StrComp(r.Parent.Name, rng.Parent.Name) = 0 Then
If (r.Row = rng.Row) And (r.Column = rng.Column) Then
chkRange = True
End If
End If
End Function

Public Function chkVal(v As Variant) As Boolean
cmp = True
If r Is Nothing Then Exit Function
If val <> v Then
val = v
cmp = False
End If
End Function

Private Sub Class_Terminate()
Set r = Nothing
End Sub

そして、uFnc()関数をこんな感じで作ります。
====== 標準モジュール ======
Private cAr As cCalArr

Public Function ufnc(r As Range, p As Variant) As Variant
If cAr Is Nothing Then
Set cAr = New cCalArr
End If

ufnc = cAr.check(r, p)

End Function

これで関数の作成は完了です。
次にこの関数(ufnc())の使い方です。
A1～A10までの合計をA11に挿入する計算式を例にします。
A11セルには、"=ufnc(A11, SUM(A1:A10))"
と記述します。
「コピペ」で持っていっても、自身のセル（第一引数）は相対記述なので、「コピペ」後も自身を示すようになります。楽でしょ？

これでお望みの結果が出ると思いますが、いかがでしょうか？とんだ勘違いをしていたらごめんなさい。

この回答への補足

有難うございます。

おぼろげに分かりましたが、
途中で行を追加したりすると既存のセルのアドレスが変わってしまい覚えた分とマッチしなくなるような気が．．．

クラスというのは変数と違ってシートと一緒に保存できるものなのでしょうか？出来るとしたら別シートに書き込むようなことをしなくて良いので助かりますね。

補足日時：2005/03/27 09:07

No.8 回答者： Musaffah 回答日時： 2005/03/27 17:02

たびたびすいません。

No.7について、急いでたので誤った情報を教えてしまいました。申し訳ありません。

ufnc(A,B)で、第二引数(B)の計算式は、計算元となる値に変化がない場合は再計算しないみたいですね。
よって、ファイルを開いたときに、閉じる前に計算した値と変化がないため計算しませんでした。
よって、開いた後に変更しても変化を見つけることができませんでした。ほんとごめんなさい。

ということで、以下の作戦を練りました。（別のファイルに計算式の値を入れるという作戦は使わない方法です。）
(A)起動時のみ乱数を発生させる。
(B)ufnc()関数の引数を１つ（その乱数を引数として渡す）増やす。
(C)乱数を使って、起動時にすべてのufnc()関数を無理やり動かすようにする。
(D)発生させた乱数を消す。
これにより、起動時に１回だけすべてのufnc()関数が処理することになりますので、ファイルを閉じるときに、別ファイルに保存していた場合と同じような結果になります。

A1～A10までの合計をA11に挿入する計算式を例にして説明します。
（乱数を発生させる場所を「"Sheet2"ワークシートの"A1"セル」とします。）
※ワークシートの名前は任意にしても大丈夫です。
　そのときは、の"Sheet2"をそのワークシート名に
　書き換えてください。

（１）No.4で書いたソースリストで、cValクラスのchkRange()メソッドを見直しました。
（ufnc()モジュールを使っていたセルを削除したら、変な動きをしなくしました。）
No.7で変更したchkVal()メソッドも追記します。
↓に差し替えてください。
====== cValクラス ======
Public Function chkRange(rng As Range) As Boolean
On Error GoTo err
chkRange = False
If r Is Nothing Then Exit Function
If StrComp(r.Parent.Name, rng.Parent.Name) = 0 Then
If (r.Row = rng.Row) And (r.Column = rng.Column) Then
chkRange = True
End If
End If
Exit Function

err:
Set r = Nothing
End Function

Public Function chkVal(v As Variant) As Boolean
chkVal = True
If r Is Nothing Then Exit Function
If val <> v Then
val = v
chkVal = False
End If
End Function
==========

（２）Auto_Open()モジュールを新規に追加してください。（ファイルを開いたときに自動的に実行される関数です。）
====== Auto_Open() モジュール ======
Private Sub Auto_Open()
Worksheets("Sheet2").Range("A1") = "=RAND()"
Worksheets("Sheet2").Range("A1").ClearContents
End Sub

（３）ufnc()モジュールを↓に差し替えてください。
（計算式がエラーとなった場合に処理しないようにしました。）
====== ufnc() モジュール ======
Public Function ufnc(rnd As Single, r As Range, p As Variant) As Variant
If Not IsNumeric(p) Then Return '数値でない場合は処理しない

If cAr Is Nothing Then
Set cAr = New cCalArr
End If

ufnc = cAr.check(r, p)

End Function

（４）使っていない"Sheet2"ワークシートを用意します。
（５）A11セルには、"=ufnc(Sheet2!$A$1, A11, SUM(A1:A10))"
と記述します。
（６）"Sheet2"ワークシートを非表示にします。（「意地悪」されるのを防ぐためです。）

これで、起動時に若干時間を必要とします(全ufnc()モジュールがコールされますので)が、ちゃんと動くと思います。また、行追加／削除があってもちゃんと対応するようになります。
しかも、計算値保存用のファイルは不要です。

これで大丈夫だと思いますが・・・・。

この回答へのお礼

質問してから1年がたちもう忘れておられることと思います。私は当時はexcelマクロを自分の事務処理の効率化に始めたばかりで、教えていただいたコードがまったく理解できなかったのですが、いつかこれが判るようになりたいと思って休みの日に少しづつ勉強を続けてきました。1年がたってようやく理解できデバッグもしてみましたので報告させて戴きます。
(1)rand関数を入れて直後にクリアー(この時もufncが起動される)しているのでrand関数ではなくとも例えば定数1でも構わない。クリアーを止めてrand関数のみにした場合は同一の値となる確率がのこるので止められない。
(2)値が変わったことを検知したときは新たな値と置き換えておかないといけない。
大きくはこのくらいでした。
あと、cValArrクラスはインスタンスが1個なのでわざわざクラスにしなくとも標準モジュールでもＯＫだと思いました。どちらが慣れてるかだとは思いますが。
もひとつ、Rangeの行列値と値を覚えこませる配列もcValクラスの配列をわざわざ作らなくとも普通の配列2個（as Rangeとas integer）に覚えこませるだけで済むと思いました。
ともあれ、お陰さまでクラスというものの理解とコーディングが出来るまでになりましたのでお礼とご報告をさせていただきました。
有難うございました。

お礼日時：2006/03/06 06:29

No.7 回答者： Musaffah 回答日時： 2005/03/27 12:34

No.4のものです。

まず、No.4で記述した関数に間違いがあったので訂正します。ごめんなさい。（前の関数では、恐らく片っ端から”変わりました”というはずです。）
cValクラスのchkVal()関数を↓に差し替えてください。
==========
Public Function chkVal(v As Variant) As Boolean
chkVal = True
If r Is Nothing Then Exit Function
If val <> v Then
val = v
chkVal = False
End If
End Function
==========

さて・・・。

> 途中で行を追加したりすると既存のセルのアドレスが変わってしまい覚えた分とマッチしなくなるような気が．．．
cValオブジェクトには、Rangeオブジェクト自身を記憶させます。よって、行または列の追加／削除を行って計算式セルの位置がずれても、ずれた位置の情報をそのまま記憶しています。
No.4の例で言うと、ufnc()の第一引数では、"A11"という文字列を渡しているようにみえますが、文字列ではなく、A11のセル自身を渡しています。それによって、そのセルが移動しても相対的に記憶します。ご安心ください。

> クラスというのは変数と違ってシートと一緒に保存できるものなのでしょうか？出来るとしたら別シートに書き込むようなことをしなくて良いので助かりますね。
変数と同じですよ。違うファイルではなく、そのファイルに保存されます。

まず、クラスとオブジェクトの違いを、鯛焼きを例に簡単に説明しますね。
クラスは鯛焼きを作る鋳型（鯛焼きを作るときに使うプレート？みたいなやつ）と思ってください。オブジェクトは、そのプレートを作って出来上がった鯛焼きです。
要は、「こんなものを作りたい」と思って作る鋳型をクラス。その鋳型によって作られた「もの（実態）」をオブジェクトといいます。

また、cValオブジェクトは、シート名称とセル情報（行、列）で同一かチェックしていますので、違うシートにコピーしても使えます。その場合は、cCalArオブジェクトが新しいcValオブジェクトを生成して記憶するようになります。（うまく伝えられないなぁ～・・・口下手（？）ですいません。）

また、変数に格納した情報はファイル終了後に保持できないという心配をされていますね。
ところが、計算式を入れているセルは計算結果を保持しているのではありません。あくまで計算式を記録し、見た目上は計算結果を表示しているだけです。

このマクロを簡単に説明します。
ファイルを開いたときに、そのファイルで使っているufnc()関数が１回すべて実行されます。（厳密に言うと、ufnc()関数の第２引数の計算式が初回計算結果を実行するため、自動的に起動されます。）
その結果、cCalArオブジェクトに、ucnf()関数が書かれているセルの数だけcValオブジェクトを生成し、第一引数で指定したセル（Rangeオブジェクト)と、第二引数でその後に（計算対象となる）値が変化すると、該当する計算式（ufnc()の第２引数）が再計算を行うため、それと同時に前回値（ファイル起動時にcValArクラス内のcValクラス）に記憶させている値）とチェックします。
ufnc()関数を追加したり、コピーしたりするたびに、cCalArオブジェクトが新しくcValオブジェクトを生成・記憶させます。
よって、結果的に「ファイルを閉じるときに値を記憶させ、ファイルを開いたときもその値を覚えている」という動きになります。
もし、ufnc()関数のセルを削除してしまった場合は・・・・エラーがでるかも？
それでも、ファイルを一旦閉じて再度開くと大丈夫です。（もちょっと真面目に考えれば対策できますが、取り急ぎなので・・・。）

ちょいと急いでいるので、いまいちまとまりに欠けた文章になってしまいましたが。こんなんでわかりました？？

No.6 回答者： BLUEPIXY 回答日時： 2005/03/26 21:25

＃５でイベントを利用して別シートに値を保存するようなやり方というのを言っていましたが、

実際に試してみると、
シートチェンジイベントでは、変更のあったセルしか捕まえられないので
=ufnc(B2+B16)
の様な式でＢ２が変化したことによるこの式の値が変化したことが捉えられないということに気がつきました。
これを、調べるには、シートカリキュレイトイベントでufncを含んでいるセルを（セルの変化があるたびに）すべて探して調べないといけなくなります。
とても簡単な方法とは言えないので＃５のアドバイスは取り下げます。
＃４の方法で、ブックを閉じる時に裏シートに変数値を保存し、ブックを開く時に、再び、裏シートから変数に値を取り込むということはできるかと思いますが…

この回答への補足

考えてみましたが、
私の最初考えた単純な関数でも、通常に該当セルの計算結果が変わったときはちゃんと該当セルのみ通知してくれますから、
他のケース（シートが全体的に再計算されるとき）と区別できればmsgboxの表示と非表示をコントロールすることでなんとかなるような気がしてるのですが．．．
なにぶんVBAは先週入門編を買ったばかりで自分ではどうにも．．．

補足日時：2005/03/27 09:20

No.5 回答者： BLUEPIXY 回答日時： 2005/03/26 20:27

>変数のＶの中身は一旦ｅｘｃｅｌを終了させると消えてなくなってしまうと思うのでちょっと考えてる事とは違います。

なるほど、
私も、
＃３で指摘したような欠点については、
＃４のようにするのかなと思っていましたが、
言われてみればその通りで、変数だと、終了した後保持することはできません。
そうすると、別シートを裏シートみたいな形で前回の値を保存するというようにすれば（簡単で）いいかと思いますが、別シートが手動で変更されることがないように保証しなければなりませんし、シートで保持する値が倍になってしまいます。

No.3 回答者： BLUEPIXY 回答日時： 2005/03/25 16:24

やり方は別に（＃１に比べて）悪くはないが（良いとも言えないけど）

＃２は、明らかに間違っているので指摘しておきます。
＞Worksheets(1).Range("A1").Value = ChkValue
逆
＞
Ａ１の値が変化したら（chkValueが更新されないので）
２回目以降"ずっと"値が変わりましたと表示される
＞
セルを限定して調べたら、関数にしている意味がなくなる

ついでにいっておくと
＃１もあんまり良い方法とは言えないです・
＃１は、ufnc(式)の式の結果自体が変化したことを捉えるので
A1=ufnc(2)
A2=ufnc(2)
のような場合、式が変化したとは検知しない
そういう意味では、＃１も＃２も同じ欠点がある。

この回答への補足

おっしゃるとおりです。

何回も操作するので、その都度検知出来ないと困るのです。

また検知したいセルの追加の都度、追加セル用の記憶変数を用意するためにコーディングをいじくるのも避けたいです。

ただ、式の変化ではなく結果の値の変化を捉えたいのです。

現在は、msgbox文をコメントにしておき、調べたいときだけ有効にして使って最後にまたコメントにして保存しています。

簡単な事のようでいて結構難しい事なのですね．．．

補足日時：2005/03/26 07:09

No.2 回答者： Wendy02 回答日時： 2005/03/25 14:46

Calculate が働いて、MsgBox が出るのですよね。

だから、Calculateイベントを使えばよいのでは？
単純かしらね。ユーザーファンクションでも、一旦、以下のように値を確保してあげればよいけれども。

'<ThisWorkbook に、>
Public ChkValue As Variant　'パブリック変数を置き
Private Sub Workbook_Open()
　Worksheets(1).Range("A1").Value = ChkValue
　'1度、Public変数に、値を送ってやります。
End Sub

'<シートモジュール>
Private Sub Worksheet_Calculate()
　'Calculateイベントでチェックさせます。
If Range("A1").Value <> ChkValue Then
MsgBox "値が変りました"
End If
End Sub

で、チェックすればよいと思います。

この回答への補足

変化を捉えたいセルが縦に飛び飛び（その間は何も入っていないセル）に200箇所位あるので（たまに途中に行追加などもする）、値を覚えるための変数を使うのは今のところ避けたいとは思っていますが。

補足日時：2005/03/26 07:01

No.1 回答者： BLUEPIXY 回答日時： 2005/03/25 14:18

Function ufnc(p As Integer) As Integer

Static v As Integer
If v <> p Then
MsgBox "値が変わりました"
v = p
End If
ufnc = p
End Function
みたいな感じでどうでしょう

この回答への補足

有難うございます。

変数のＶの中身は一旦ｅｘｃｅｌを終了させると消えてなくなってしまうと思うのでちょっと考えてる事とは違います。

また複数のセルの変化をつかみたいので変数を使うのはどんなものかとも感じています。

補足日時：2005/03/26 06:48