禁断の技、ユーザー定義関数で他のセルにデータを入れる方法

関数のコードの中で他のセルにデータを入れることはできません。セルにデータを入れるSubプロシージャを作り、関数からそのSubプロシージャを呼び出しも禁止されてます。

これは、再計算の繰り返しにならないようにするためだそうです。

でも対応策はあります。

イベントを使います。関数が計算して戻り値を更新するとWorksheet_Changeイベントが発生します。このSubプロシージャを使ってセルにデータを入れることは可能です。

Sheet1のコード、イベントのコードです。

コピーできるようにコードを貼り付けておきます。

Private Sub Worksheet_Change(ByVal Target As Range)
    If 1 = gaaa Then
        gaaa = 0
        Excel.Application.EnableEvents = False '次行でWorksheet_Changeイベントが何度も発生しないようにする
        grInput.Value = Now '変化したことが分かるように時間を入れる
        Excel.Application.EnableEvents = True '解除
    End If
End Sub

以下は関数aaaとグローバル変数gaaaとgrInputを定義してます。標準モジュールを追加して書きます。

Public gaaa, grInput

Function aaa(r As Range, rInput As Range) 'rの範囲が変化したらこの関数は実行します
    ' 現在のセルが対象範囲内かを確認
    If Not Intersect(ActiveCell, r) Is Nothing Then
        ' 対象範囲内のセルの場合の処理
        gaaa = 1
        Set grInput = rInput
    End If
End Function

G6に=aaa(B1:F5,I1)を入れます。B1からF5の範囲にデータを入れるとI1の時間が変わります。I1の書式は秒まで見えるようにしておきましょう。変化がわかりませんので。

何かの加減でChangeイベントプロシージャが実行しない場合は、イベントが無効になっている可能性があります。以下のコードをイミディエイトウィンドウに貼り付けて実行してください。貼り付けた位置にカーソルを移動してエンターで実行します。