ExcelVBA イベントプロシージャの起動

VBA初心者です。
Office365のExcelで、ハイパーリンクのアドレスをチェックし、その評価をセルの値とするユーザー定義関数(linkchecker)を作成しました。
これを特定のセル(O3)をダブルクリックすることで起動(再計算)するイベントを作成し、起動が確認できたのですが、
ハイパーリンクのアドレスを一度でも編集すると、以降イベントが起動できなくなってしまいます。
以下に状況を詳しく記載しますので、ご教授いただけると大変助かります。

【Excelブックの環境】
・Office365
・行方向に案件を追加、列方向に入力条件を指定してある一覧表(テーブル)です。
・ハイパーリンク(N列)には社内ネットワーク中のフォルダを指定
・linkcheckerはO列に、行ごとにチェックしてその結果を表示
・イベントを設定したO3セルはテーブルの見出し
・同設定のシートが複数ある

【VBA作成状況】
●ユーザー定義関数(linkchecker)
・ハイパーリンクのアドレスを再編集するまでは、イベント呼び出し・N列,O列セルのダブルクリック、
　新規行、ハイパーリンクの追加　など、すべての条件で動作確認済み。
　アドレスを再編集すると、イベント呼び出しのみ起動しない(ブックの再起動でリセットされない)
・1つのシートでアドレスを再編集すると、他シートも起動しなくなる

以下、コード
Function linkchecker(Target As Range) As String
Dim count As Integer
count = Target.Hyperlinks.count
If count = 0 Then
linkchecker = "未設定"
Else
Dim address As String, kekka As String
address = Target.Hyperlinks(1).address
kekka = Dir(address, vbDirectory)
Select Case kekka
Case ""
linkchecker = "×"
Case Else
linkchecker = "○"
End Select
End If
End Function

●イベントプロシージャ(各シート)
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Range("O3")) Is Nothing Then Exit Sub
Else
Call シートの再計算
Cancel = True
End If
End Sub

●マクロ
・イベントプロシージャの中に再計算を組み込むと起動が気まぐれになったので分けました(原因不明)
・再計算範囲をRangeで指定すると思う通りの挙動にならなかったのでアクティブシートで指定

以下、コード
Sub シートの再計算()
ActiveSheet.Calculate
End Sub

No.1 ベストアンサー 回答者： Qchan1962 回答日時： 2020/07/14 00:36

こんばんは、

当てずっぽですが、表示文字とアドレスを混同していませんか？

セルに表示されているのは、表示文字
リンクアドレスを変更しても表示文字を変更しなければ、Function linkchecker(Target As Range) As Stringは
実行されないかと思います。
逆にリンクアドレスを変更しなくても、表示文字を変更すれば実行されると思います。
つまり、表示文字を変更しなければ再計算を行ってもFunctionは、実行されない事になるのではないでしょうか。

Private Sub Worksheet_BeforeDoubleClickでリンクの変更を捕まえて表示したいのであれば、
明示的にFunctionを呼ぶのではいかがでしょう？もちろんユーザー定義関数の機能は残したままで。

違っていたらごめんなさい。

この回答へのお礼

ご回答いただきありがとうございます。

仰るとおり、セルの値とハイパーリンクの設定値を混同してました。
アドバイスいただいた方法は私のスキルでは上手く使いこなせなかったので、
ユーザー定義関数を揮発性関数とすることで解決できました。

ご協力感謝いたします。ありがとうございました。

お礼日時：2020/07/14 10:57