VBA Private Sub Worksheet_Calculate()

質問

「ユーザー定義関数」で計算されるセルがセルB2にある場合、シートモジュールの
Private Sub Worksheet_Calculate()で、他の再計算は無視し、そのユーザー定義関数が計算されたことを判断するためにはどのような記述をすればいいのでしょうか？

回答No.2

taocat · ベストアンサー

こんにちは。

大分梃子摺っていらっしゃいますね。
今回のコードでシート名の変更を捕らえたければ
Calculateイベントではなく
ユーザー定義関数そのものでやったらどうですか。

間違いなくする為に、新しいブックで以下のコードをテストしてください。

簡単の為に、"sheet1"だけの変更に対応。

---------------------------------------------
（標準モジュール）

Public OldName As String　'パブリック変数

Function Sheetname(ByVal Target As Range) As String

　Application.Volatile
　Sheetname = Target.Parent.Name

　If OldName = "" Then
　　　OldName = Sheetname
　End If

　If OldName <> Sheetname Then
　　MsgBox OldName & "が " & Sheetname & " に変更されました"
　　OldName = Sheetname
　　End If

End Function
---------------------------------------------

コードを書いたあと、Sheet1のA1に、=sheetname(A1) を入れておく

ようは、以前のシート名を保持しておき
ユーザー定義関数が再計算されるとき比較するだけです。

もちろん、Calculateイベントで同様のことをしてもＯＫですよね。

何回も言うようですが、このサンプルは、ユーザー定義関数の入っている、シート１つだけに対応です。

全てのシートに対応したい場合は、ちょっと考えてみてください。
以上です。

お礼

ありがとうございました。

全シート対応はわかりませんが、今回はこれで目的を達成できました。
助かりました。

回答No.1

KenKen_SP

アイディアですが、ユーザー定義関数の末尾に

MsgBox "再計算されました"

などと、ここではMsgBoxで示しましたが、このようにユーザー定義関数の末尾で何らかの処理を行うのはどうでしょう？

あるいは、

作業セル（例えば、A1セル）を用意し、ユーザー定義関数による計算が行われれば、関数内の最終処理で作業セル（A1セル）に何らかのデータを書込む。
そうすれば、Worksheet_Changeなどのイベントでユーザー定義関数のみが再計算されたことをとらえられるかと思います。

EXCELで試してませんので、不確かなアイディアですが。
はずしていたら、すみません。

補足

さっそくありがとうございます。

現在、標準モジュールに

Function Sheetname(ByVal Target As Range) As String
Application.Volatile
Sheetname = Target.Parent.Name
End Function

とし、Sheet1のA1に　=Sheetname(A1)といれています。
現在のシート名（Sheet1）が正しく表示されています。

Sheet1のシートモジュールに

Private Sub Worksheet_Calculate()
MsgBox "シート名が変更されました。"
End Sub

といれています。

これでシート名が変わったときは再計算されてMsgBoxが表示されますが、それ以外の再計算でもすべて出てくるため困っています。
関数内の最終処理で作業セルに何らかのデータを書込むとは、どういう記述をするのでしょうか？