文字列から数式に変換する標準モジュールが不安定

質問

文字列を数式に変換する標準モジュール「EVALUATE」の更新が不安定です
エクセルシート内の文字列を数式に変換して、計算結果を返すために下記標準モジュールを登録して試すのですが
うまく行ったりいかなかったり、標準モジュールが安定して機能しない原因などが分かりません。

●現在の設定とやりたいこと
(1)Ａ１⇒=myEvalAry(B1)、B1⇒C1+D1、C1⇒2、D1⇒5 として、A1にC1+D1計算結果の7を表示させたい
(2)一つのシートの中に、myEvalAry標準モジュールを数百使っている
(3)一つのセルの中で、=myEvalAry(B1)+myEvalAry(B2)のように標準モジュールを複数使っているセルもある

●現在の状況
上記状態で、何かのタイミングで標準モジュールの計算結果が一気に全て正しく反映されることもあれば、
急に反映されなくなることもある。１００のうち１０だけ反映されることもある。
というような不安定な状態です。

しかも数量が問題かと思って、多量に登録していたmyEvalAryのセルを１つだけにして動きを確認しようとしたらまた反映
されなかったりで、全然理由が分かりません。

どこか標準モジュール内に、考慮すべき構文が漏れたりしてるのでしょうか？？？？
正常稼働しない理由が分かると大変ありがたいです。win7、win8、excel2003、excel2013のいずれの環境でも同様です。

///////////////////////////////////////////////////////

Function myEvalAry(ParamArray ItemR()) As Variant

Dim re As Variant
Dim strTmp As String
Dim varR As Variant
Dim i As Variant, j As Variant

strTmp = ""
varR = ItemR()

For Each i In varR
If IsArray(i) Then
'引数が配列の場合
For Each j In i
If IsNumeric(j) Then
re = CStr(j)
Else
re = j
End If
strTmp = strTmp & re
Next
Else
'引数が配列以外
If IsNumeric(i) Then
re = CStr(i)
Else
re = i
End If
strTmp = strTmp & re
End If
Next
myEvalAry = Application.Evaluate(strTmp)
End Function

回答No.1

cj_mover · ベストアンサー

こんにちは。

ちょっとコンサバ過ぎるかも知れませんが、現在の私流で書いてみました。
もし、数字（1とか2とか）であれば、そのまま連結してはいけない場合もあろうかと。
とりあえず、評価する対象文字列＝strBufの先頭であればそのまま数字、
それ以外は"+"演算子に続けて数字という風に、勝手に少しお節介にしてみました。
（+2という文字列なら++2、-3という文字列なら+-3、を連結しますが戻り値は変わりません。）
ポイントは、最後の一行で、これだけでかなりの誤作動を防げる筈です。
現状が不安定ということですから、なるべく堅実なものをという意図で書いていますから、
処理は比較的遅くなりがちです。
叩き台にするなり、パーツだけ流用するなり、後はそちらで工夫してみてください。
それと、ＵＤＦに限らず、ParamArrayの多用は負荷が大きいようですから、
せいぜい数百、と考えておいた方がストレスがないとは思います。
（関数名、替えてあります。）

' ' ///////////////////////////////////////////////////////

Function myEvalAryC(ParamArray ItemR()) As Variant

　　Dim a As Range　　'　　Area
　　Dim c As Range　　'　　Cell
　　Dim strTemp As String　　'　　Cellの表示文字列
　　Dim strBuf As String　　'　　バッファ文字列
　　Dim Param As Variant　　'　　ParamArrayの要素

　　strBuf = ""

　　For Each Param In ItemR()
　　　　If TypeName(Param) = "Range" Then
　　　　　　' ' 引数がRangeの場合
　　　　　　' ' 各領域（Area）をループ
　　　　　　For Each a In Param.Areas
　　　　　　　　' ' 各領域（Area）内のCellをループ
　　　　　　　　For Each c In a
　　　　　　　　　　' ' Cellの表示文字列
　　　　　　　　　　strTemp = c.Text
　　　　　　　　　　' ' Cellの表示文字列が数字として読めるものであれば、
　　　　　　　　　　If IsNumeric(strTemp) Then
　　　　　　　　　　　　' ' バッファ文字列が空ならそのまま数字を
　　　　　　　　　　　　' ' バッファ文字列が空でなければ"+"演算子と数字を
　　　　　　　　　　　　If strBuf <> "" Then strBuf = strBuf & "+"
　　　　　　　　　　End If
　　　　　　　　　　' ' バッファ文字列に流し込み
　　　　　　　　　　strBuf = strBuf & strTemp
　　　　　　　　Next
　　　　　　Next
　　　　Else
　　　　　　' ' 引数がRange以外の場合
　　　　　　If IsNumeric(Param) Then
　　　　　　　　If strBuf <> "" Then strBuf = strBuf & "+"
　　　　　　End If
　　　　　　strBuf = strBuf & Param
　　　　End If
　　Next

　　' ' Applicationではなくて呼び出し元のシートに評価させることが誤作動を防ぎます。
　　myEvalAryC = Application.Caller.Worksheet.Evaluate(strBuf)

End Function

お礼

残念ながら結果的には正常稼働しませんでしたが、ご指摘にあった多用しているのもありますので別の方法を検討したいと主追います。

しかし、アドバイス頂いた内容は大変参考になりました。
ありがとうございました。