スキルアップ・問題解決なら、モーグにおまかせ！

ユーザー定義関数を自動で再計算させる方法についてご教示いただきたいことがあります。
 
下記のユーザー定義関数は参照したセルに入力された式を計算して指定の桁数で結果を表示する関数ですが、ファイルの起動時や参照先が他のシートで変更作業を行った場合に自動で再計算を行わない場合があります。
 
赤字の「Application.Volatile」で、同一ファイル内の全てのシートの何れセルに対する変更でも、このユーザー定義関数が入っているセルは自動で再計算を行うと認識しているのですが、それは誤った認識でしょうか。
 
***********************************************************************************
Function eval(cl, n%)
    Dim e$, eq$, i%, ro%, co%, c$
 
Application.Volatile
    eval = ""
    e = cl.Text
    If Len(e$) < 1 Then Exit Function
    eq = ""
    For i = 1 To Len(e$)
        c = Mid$(e, i, 1)
        Select Case c
            Case "＋": c = "+"
            Case "−": c = "-"
            Case "×": c = "*"
            Case "÷": c = "/"
            Case "π": c = "pi()"
            Case "√": c = "sqrt"
            Case "Σ": c = "sum"
            Case "（": c = "("
            Case "）": c = ")"
            Case "｛": c = "("
            Case "｝": c = ")"
            Case "{": c = "("
            Case "}": c = ")"
            Case "m": c = ""
            Case "k": c = ""
            Case "g": c = ""
        End Select
        If LenB(StrConv(c, vbFromUnicode)) = 2 Then
            c = ""
        End If
        eq = eq & c
    Next i
    eval = Application.WorksheetFunction.Round(Evaluate(eq$), n%)
End Function
***********************************************************************************
[/b]

Debug.print Timer
などを、挿入して、試してみるとよいでしょう。

引用:
下記のユーザー定義関数は参照したセルに入力された式を計算して指定の桁数で結果を表示する関数ですが、ファイルの起動時や参照先が他のシートで変更作業を行った場合に自動で再計算を行わない場合があります。
 
赤字の「Application.Volatile」で、同一ファイル内の全てのシートの何れセルに対する変更でも、このユーザー定義関数が入っているセルは自動で再計算を行うと認識しているのですが、それは誤った認識でしょうか。

Application.Volatile
は、再計算／非再計算を切り分けることが可能です。
 
テスト
ユーザーモジュール
Function myFunc(RC As Range, Optional V As Boolean = True)
    Application.Volatile V
    myFunc = RC.Value * 100
    Debug.Print Timer & " " & RC.Address
End Function
シート側
A2,A3,A4 に数値を入力
B2,B3,B4 にユーザー定義関数入力
B2:=myFunc(A2,False)
B3:=myFunc(A3)
B4:=myFunc(A4)
 
ユーザー定義関数と無関係な数式を、別のセルに入力
E5:=100+1000
 
↓この時のDebug.printの表示です。
50973.98 $A$4
50973.98 $A$3

追加コメント
Application.Volatile
私は、いままで、引数がないメソッドと思っていました。
調べたら、引数がありました。
規定値は、True です。
 
自動再計算させたくないところは、Falseを指定すると、回避できます。

WinArrow様
Suzu様
ご返信ありがとうございます。
まだ全てのご教示内容を確認出来ておりません。
申し訳ありません。
 
 
＞Debug.print Timer
＞などを、挿入して、試してみるとよいでしょう。
 
使用方法がよく分からずググった情報を元に色々と試しているうちに訳が分からなくなっておりました。
 
このファイルはシートが11個(sheet1〜sheet11)あり、91個のセルにユーザー定義関数「eval」が入力されています。
 
　sheet1　　 0個
　sheet2　　11個
　sheet3　　 0個
　sheet4　　 7個
　sheet5　　38個
　sheet6　　17個
　sheet7　　 7個
　sheet8　　11個
　sheet9　　 0個
　sheet10　　0個
　sheet11　　0個
 
 
現在のところ、下記のように記述しております。
 
***********************************************************************************
Function eval(cl, n%)
   
    ・
    ・
   Debug.Print Timer & " , " & ActiveSheet.Name & " , " & Application.ThisCell.Address
     
End Function
 
***********************************************************************************
 
ファイル起動時のイミデェイトウィンドウの表示以下の通りで82行分の表示がありました。
　※イミデェイトウィンドウの表示限界（199行）を超えているかもしれません。
 
また、表示結果だけみると起動時に表示される「sheet6」しか再計算していないようにも見えます。
 
54987.2 , sheet6 , $Q$14
54987.2 , sheet6 , $Q$18
54987.2 , sheet6 , $H$24
54987.2 , sheet6 , $F$38
54987.2 , sheet6 , $H$21
54987.2 , sheet6 , $Q$20
54987.2 , sheet6 , $Q$12
54987.2 , sheet6 , $Q$15
54987.2 , sheet6 , $Q$9
54987.2 , sheet6 , $F$43
54987.2 , sheet6 , $R$16
54987.21 , sheet6 , $Q$17
54987.21 , sheet6 , $Q$21
54987.21 , sheet6 , $Q$5
54987.21 , sheet6 , $F$45
54987.21 , sheet6 , $Q$12
54987.21 , sheet6 , $Q$13
54987.21 , sheet6 , $Q$6
54987.21 , sheet6 , $Q$13
54987.21 , sheet6 , $F$40
54987.21 , sheet6 , $F$89
54987.21 , sheet6 , $H$22
54987.21 , sheet6 , $F$29
54987.21 , sheet6 , $F$96
54987.21 , sheet6 , $F$98
54987.22 , sheet6 , $F$94
54987.22 , sheet6 , $F$56
54987.22 , sheet6 , $F$118
54987.22 , sheet6 , $F$150
54987.22 , sheet6 , $F$129
54987.22 , sheet6 , $F$95
54987.22 , sheet6 , $F$62
54987.22 , sheet6 , $F$55
54987.22 , sheet6 , $F$96
54987.22 , sheet6 , $F$57
54987.22 , sheet6 , $F$120
54987.23 , sheet6 , $F$121
54987.23 , sheet6 , $F$151
54987.23 , sheet6 , $F$57
54987.23 , sheet6 , $F$88
54987.23 , sheet6 , $F$86
54987.23 , sheet6 , $F$128
54987.23 , sheet6 , $F$56
54987.23 , sheet6 , $F$87
54987.23 , sheet6 , $F$119
54987.23 , sheet6 , $F$5
54987.23 , sheet6 , $F$9
54987.23 , sheet6 , $H$18
54987.23 , sheet6 , $F$55
54987.23 , sheet6 , $F$62
54987.23 , sheet6 , $F$24
54987.23 , sheet6 , $F$11
54987.24 , sheet6 , $F$10
54987.24 , sheet6 , $F$23
54987.24 , sheet6 , $F$4
54987.24 , sheet6 , $H$14
54987.24 , sheet6 , $H$13
54987.24 , sheet6 , $F$22
54987.24 , sheet6 , $H$17
54987.24 , sheet6 , $F$6
54987.24 , sheet6 , $F$17
54987.24 , sheet6 , $F$15
54987.24 , sheet6 , $F$18
54987.24 , sheet6 , $F$24
54987.25 , sheet6 , $F$14
54987.25 , sheet6 , $F$22
54987.26 , sheet6 , $F$20
54987.26 , sheet6 , $F$188
54987.26 , sheet6 , $F$180
54987.26 , sheet6 , $F$194
54987.26 , sheet6 , $F$174
54987.27 , sheet6 , $F$181
54987.27 , sheet6 , $F$191
54987.27 , sheet6 , $F$190
54987.27 , sheet6 , $F$173
54987.27 , sheet6 , $F$182
54987.27 , sheet6 , $F$186
54987.27 , sheet6 , $F$192
54987.27 , sheet6 , $F$185
54987.27 , sheet6 , $F$177
54987.27 , sheet6 , $F$193
54987.27 , sheet6 , $F$187
 
 
「sheet6」に「eval」が入力されているセルはF列の17セルになります。
　　F4
　　F5
　　F6
　　F9
　　F10
　　F11
　　F14
　　F15
　　F17
　　F18
　　F20
　　F22
　　F24
　　F38
　　F40
　　F43
　　F45
 

Suzu様
 
全てのシートが「自動」になっています。
再計算されていないセルは、「#VALUE!」と表示されています。
 
同じシート内でも起動時に再計算されているセルと「#VALUE!」と表示されているセルがあります。
Sheet2の場合
　　　　　　　　　　　　　　　入力式
　　　　　　　　　　　D　　　　　　　　　　　　　　　　　　　　Q
　5　　=FIXED(Sheet3!O25,1)&"+"&FIXED(Sheet5!F98,1)　　　　 =eval(D5,1)
 
　6　　=FIXED(Sheet3!L25,1)　　　　　　　　　　　　　　　 　=eval(D6,1)
 
　7　　=FIXED(Q5,1)&"+"&FIXED(Q6,1)　　　　　　　　　　 　 =eval(D7,1)
 
　8　　=FIXED(Sheet5!F29,1)&"+"&FIXED(Sheet5!F62,1) =eval(D8,1)
        &"+"&FIXED(Sheet5!F95,1)&"+"&FIXED(Sheet5!F128,1)
 
　　　　　　　　　　　　　ファイル起動時の表示
　　　　　　　　　　　D　　　　　　　　　　　　　　　　　　　　Q
　5　　　　　　　「6.0+6.8」 と表示　　　　　　　　　　　「#VALUE!」と表示
    
　6　　　　　　　「2.0」　　と表示　　　　　　　　　　　 「2.0」と表示
　
　7　　　　　　　「#VALUE!」と表示　　　　　　　　　　　 「#VALUE!」と表示
　
　8 「16.1+3.1+63.1+11.8」　と表示　　　　　「#VALUE!」と表示
     
 
　　　　　　　　　　　　リボンの再計算ボタンをクリック後の表示
 
　　　　　　　　　　　D　　　　　　　　　　　　　　　　　　　　Q
　5　　　　　　　「6.0+6.8」 と表示　　　　　　　　　　　「12.8」と表示
    
　6　　　　　　　「2.0」　　と表示　　　　　　　　　　　 「2.0」と表示
　
　7　　　　　　　「12.8+2.0」と表示　　　　　　　　　　　「#VALUE!」と表示
　
　8 「16.1+3.1+63.1+11.8」　と表示　　　　　「94.1」と表示
     
 
 
　　　　　　　　　　　　再度リボンの再計算ボタンをクリック後の表示
 
　　　　　　　　　　　D　　　　　　　　　　　　　　　　　　　　Q
　5　　　　　　　「6.0+6.8」 と表示　　　　　　　　　　　「12.8」と表示
    
　6　　　　　　　「2.0」　　と表示　　　　　　　　　　　 「2.0」と表示
　
　7　　　　　　　「12.8+2.0」と表示　　　　　　　　　　　「14.8」と表示
　
　8 「16.1+3.1+63.1+11.8」　と表示　　　　　「94.1」と表示
 
 

>Function eval(cl, n%)
の「cl」は、セル(Rabgeオブジェクトと考えてよいですよね？
 
「cl」がRangeオブジェクトならば
Function eval(cl As Range, n%)
のように記述しましょう。
   
 
そして
>Debug.Print Timer & " , " & ActiveSheet.Name & " , " & Application.ThisCell.Address
は、
Debug.Print Timer & " , " & cl.Prennt.Name & " , " & cl.Address
 
に変更すると正確な情報が得られるでしょう。

23/02/22 15:59:32 の投稿の内容に沿う様テストを行いました。
 
※マクロが実行できるパス にファイルがあり、起動時に eval が 認識できる前提です。
　セキュリティーに引っかかり、マクロが実行できない環境ではありません。
 
 
どうして Q5 が #VALUE! なのか 当方には判りませんが
Q7 の #VALUE! は再現できました。
 
これは、D7 が、eval の結果を元にした数式になっており、
再計算の段階では、D7 が 取得できておらず、#VALUE!エラーになったと思われます。
 
当方では
ThisWorkbookモジュールに
 
Private Sub Workbook_Open()
  Application.Calculate
End Sub
 
としたら、ブックを開いた際の #VALUE! を回避できました。
 
----------------------------------------------------------------------------
別のワークシートの更新後に戻ってみるとまた #VALUE! エラーになる様であれば
そのシートモジュールに
 
Private Sub Worksheet_Activate()
  Application.Calculate
End Sub
 
とするか
 
Thisworkbookモジュールに
 
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  Sh.Calculate
End Sub
としてみてください。
 
別アプリをアクティブにし、当該シートに戻る場合を考慮するなら
WindowActivate 辺りのイベントを使う事になるでしょうか。
 
その辺りは試してみてください。
 
 
ただ・・
当方は、数式そのモノを eval 内で 置換する事はしませんね。。
数式は 埋め込みの様なので、
 
Sheet名 セル位置 代入先　数式
Sheet2 D5 Q2　=FIXED(Sheet3!O25,1)&"+"&FIXED(Sheet5!F98,1)
　：
 
の様なシートを用意。
 
その数式をVBA で 順次 実行。
必要時に再実行を行い、セルの値を書き換えるでしょうね。
参考まで。