[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『Function()をEVALUATE関数で呼び出すと2回実行される』（半平太）
掲題の通りなんですが、すごく不思議。
下記テストを実行すると
不思議() → 連番()が２回実行されます 普通() → 連番()が１回実行されます
<不思議() 実行後> 行 _A_ _B_ _C_ _D_ 1 2 4 6 8
<普通() 実行後> 行 _A_ _B_ _C_ _D_ 1 1 2 3 4
これは何故なのか？ と言っても分かる人は居ないと思いますので、 それっぽいメカニズムが考えつけたら、それをお聞かせいただきたい。
Sub 不思議()
With Sheets(1)
.Range("A1:D1").ClearContents '事前クリア
.Evaluate "連番(A1)"
End With
End Sub
Sub 普通()
With Sheets(1)
.Range("A1:D1").ClearContents '事前クリア
連番 .Range("A1")
End With
End Sub
Function 連番(rABCD As Range)
Dim i As Long
For i = 1 To 4
rABCD.Cells(1, i) = rABCD.Cells(1, i) + i
Next i
End Function
ちなみに、上記コードは本当に連番を作りたいのではなく、 ２回実行されることを実証する為に書いたものです。
＜ 使用 Excel：Excel2010、使用 OS：Windows10 ＞
evaluateでfunctionを呼び出すと２回実行する でググると Evaluate を使うと2回実行されてしまう（Excel VBA） https://www.zu-min.com/archives/527 というページがあった。 （ねむねむ） 2021/04/13(火) 15:35
回避方法としてはApplication.EvaluateではなくSheetのEvaluateを使い(半平太さんのではこれはクリア) >.Evaluate "連番(A1)" を .Evaluate "0+連番(A1)" とするということらしい。 （ねむねむ） 2021/04/13(火) 15:39
ｗｗｗ ＾＾； ほんと こわい、お話ですね。。。 肝に銘じておきませう。ありがとうございます。 ( ..)φメモメモ m(__)m （隠居じーさん） 2021/04/13(火) 15:43
ねむねむ さん
早速、ありがとうございます。
バグ認識が相場ですか。 解消法が「0+」は簡便でいいですね。
（半平太） 2021/04/13(火) 16:23
こんにちは。 この現象ははじめて知りました。面白いですね。。。。
Application.Callerを調べると、 Functionに渡すセルのアドレスに関わらず、 A1とアクティブセルからのそれぞれ呼び出されていることになってるんですね。
.Evaluate "0+連番(A1)" とすると、A1からの呼び出しがなくなります。 なんででしょうね。バグに理由を求めても仕方ないですが。
.Evaluate "+連番(A1)" とか .Evaluate "-連番(A1)" とかでも大丈夫なように見えますが、どうなんでしょう
Sub 不思議()
With Sheets(1)
.Range("A1:D1").ClearContents '事前クリア
.Evaluate "=連番(C1)"
End With
End Sub
Function 連番(rABCD As Range)
Dim i As Long
Debug.Print TypeName(Application.Caller)
If TypeName(Application.Caller) = "Range" Then Debug.Print Application.Caller.Address(external:=True)
For i = 1 To 4
rABCD.Cells(1, i) = rABCD.Cells(1, i) + i
Next i
End Function
（´・ω・`） 2021/04/13(火) 17:14
（´・ω・`）さん
ありがとうございます。
＞ Functionに渡すセルのアドレスに関わらず、 ＞ A1とアクティブセルからのそれぞれ呼び出されていることになってるんですね。
そこまで究明できていると頭の整理になります。
そうなるとテストコードに「A1セル」を使うのはまずかったです。 また、初期化の一環として、Sheet2のActiveteも入れた方が良かった。
まぁ、こんな展開になると思ってなかったですからねぇ。
＞ .Evaluate "+連番(A1)" とかでも大丈夫なように見えますが、どうなんでしょう
やってみるとその通りです。「0」が省ける。
マイナスを使うと「なんじゃこのコードは」とか言われそう。 多分、プラスの場合より風当りが強くなるでしょうね。意味は同じでも。
（半平太） 2021/04/13(火) 18:04
貴重な情報ありがとうございます。参考になります。
引用されたサイトのコピーを、閲覧者の利便のために貼付ます。
Evaluate Method limitations:
(以下、省略）
半平太さんから邦訳をいただきました。ありがとうございます。
英文は消去いたします。もし、原文を読みたいかたは、
http://www.decisionmodels.com/calcsecretsh.htm
を参照ください。
# 上記5行追加。4/14 0:07
（γ） 2021/04/13(火) 20:50
英語だと喉に詰まりそうなので、日本語の流動食も置いておきます。 ※Gooble翻訳ってありがたいですね(独断で一部手修正あり）
Evaluateメソッドの制限： ・文字列は256文字未満
・A1形式の数式文字列は、A1/R1C1どちらの参照モード(Application.ReferenceStyle）でも
使用可能です。
一方、R1C1形式の数式文字列は、R1C1参照モードでのみ有効である。
・文字列数式の中に相対参照があっても、それは絶対参照として扱われます。
ただし、文字列数式内に「相対参照を含む定義名」がある場合は、
当該定義名はA1セルから相対的に評価されます。
・日付はUSA形式（月-日-年）である必要があります。
・Evaluate関数は、文字列式を配列数式として扱います。
・文字列式が、閉じたブックへの外部参照またはXLM関数を含んでいる場合、 Evaluate関数はエラー値を返します
・文字列式がUDFへの参照である場合（Evaluate（"= MyUdf()"））
二度評価されているようですが、Application.Evaluateの形にせず、
Worksheet.Evaluateの形(Activesheet.Evaluate（"= 0 + MyUdf()"））にすれば
一度だけの評価に留まります。
・文字列式にUDFと名前定義両方への参照が含まれている場合、
UDFへの参照が先で、名前への参照が後に行われるとエラー2029が発生します。
・「fred」が範囲につけた名前で、xyz（）がUDFの場合
次のステートメントはエラー2029を返します
Application.Evaluate（ "= xyz（b1）+ fred"）
・しかし次のステートメントは正しい値を返します
Aapplication.Evaluate（ "= fred + xyz（b1）"）
・Microsoft KB記事823604はこの問題を特定していますが、
それが如何なる状況下で引き起こされるかキッチリと解明できておりません。
以上の制限の多くは（パフォーマンスを犠牲にはするが）以下の手法で回避できます。 ※数式をワークシート上のセルに入力し、その結果値をVBA変数に取り込む。
（半平太） 2021/04/13(火) 23:14
事後報告です。
＞・文字列数式の中に相対参照があっても、それは絶対参照として扱われます。 ＞ ただし、文字列数式内に「相対参照を含む定義名」がある場合は、 ＞ 当該定義名はA1セルから相対的に評価されます。
その但し書は本当なのかなぁと思って
（´・ω・`）さんのこの着目点を手掛かりに後記のテストをしたら、嘘(？）だった。 ↓ ＞ Functionに渡すセルのアドレスに関わらず、 ＞ A1とアクティブセルからのそれぞれ呼び出されていることになってるんですね。
まず、Evaluateを使わないで連番をCallしたら、アクティブセルとは関係なく、 A1セルの相対位置にあるセル(B2）が処理された。
正しそうてすが、これはEvaluateを使ってない話なので、ただ「そーなんだ」と言う話でしかない。
次にEvaluateで文字列「連番(右下)」をCallしたら、既知の通り2回処理されたが、 B2(A1の右下）とC6(アクティブセルの右下）が評価された。 この時点で「そうとも言えないなぁ」と思われた。
最後に2回処理回避策とされた文字列「0+連番」にしてCallしたら、 実行は1回に留まったが、C6(アクティブセルの右下）の方が評価されてしまった。
解説(A1相対評価)と対策(0+）がミスマッチである。
<普通Call 結果図> <EvaluateCall 結果図> <EvaluateCall(0+）結果図>
行 _B_ _C_ _D_ _E_ 行 _B_ _C_ _D_ _E_ _F_ 行 _B_ _C_ _D_ _E_ _F_
1 1 1
2 11 12 13 14 2 11 12 13 14 2
3 3 3
4 4
5 5
6 21 22 23 24 6 11 12 13 14
Private cnt '実行回をウオッチ
Sub 事前準備()
'名前定義
On Error Resume Next
ThisWorkbook.Names("右下").Delete
On Error GoTo 0
ThisWorkbook.Names.Add Name:="右下", RefersToR1C1:="=!R[1]C[1]"
'結果表示場所をクリア
With Sheets(1)
.Activate
.Range("A1:Z100").ClearContents '結果表示エリアを事前クリア
.Range("B2").Activate 'Sheet1のアクティブセルを固定
End With
cnt = 0
Application.Goto Sheets(2).Range("B8") '無影響な場所に一旦移動
End Sub
Sub EvaluateCall()
事前準備
With Sheets(1)
.Select
.Range("B5").Activate
.Evaluate "連番(右下)"
' .Evaluate "0+連番(右下)"
End With
End Sub
Sub 普通Call()
事前準備
With Sheets(1)
.Select
.Range("B5").Activate
連番 .Range("右下")
End With
End Sub
Function 連番(r相対 As Range)
Dim i As Long
cnt = cnt + 10
For i = 1 To 4
r相対.Cells(1, i) = r相対.Cells(1, i) + i + cnt
Next i
End Function
（半平太） 2021/04/17(土) 12:12
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.