Excel : 関数内からセルを更新したい

質問

実行環境は Excel 2000 です。

新規ブックに、
標準モジュールを追加し、

Function Test()
　　Cells(2, 2) = "OK"
　　Test = 2
End Function

という関数を追加します。

Sheet1 の、
セル「A1」に「10」を入れます。
セル「A2」に「=Test() + A1」

セル「A1」を変更すると、
関数「Test()」が呼び出されます。

このとき、コードのように、B2 のセルに「OK」を
表示させたいのですが、
「アプリケーション定義またはオブジェクト定義のエラーです。(1004)」
のエラーが発生してできません。

マクロやボタンなどのイベントプロシージャでは可能なのですが、上記のようにセルに組み込んだ関数が、
更新により実行された場合はできないのでしょうか？

（Cells の指定を Workbooks(1).Worksheets(1).Cells(2, 2) としてもダメでした）
（「書式」→「セル」→「保護」→「ロック」はチェックされていません）

すみませんが、よろしくお願い致します。

回答No.1

FlossenEngel · ベストアンサー

できないはずですが、次のようにすればできるそうです。(dummyは適当な文字列)(ウチのExcel2000ではこれでできましたが...)

Sheet1.Hyperlinks.Add(Sheet1.Cells(2, 2), "dummy", , , "OK").Delete

(もちろん推奨はしません)

参考URL：

http://tllsp.hp.infoseek.co.jp/howto/kowaza/kowaza.htm

お礼

ありがとうございました。

試してみたところできました。
リンクを張って、それを消すのですね。
ただし、重いですね。
数十箇所のレベルで、重さを体感します。

やはり、関数内から変えること自体を考えないといけないようですね。

回答No.2

imogasi

エクセルVBAのFunctionプロセジュアの原則に反することをいたいと思っていると言う気がします。
エクセルのFunctionプロセジュアの中では、関数式を入れるセルに値を設定する他のこと、例えば他のセルに値を設定したり（本件）書式を設定したり、セル（行・列）を抹消したり、挿入追加したりは出来ないようである。値を返すことだけに特化して、それらはSubプロセジュアによって行いなさいという役割分担だと思う。本来原初的にはメモリはグローバルに値を設定取得出来るものであるが、CPUやOSや言語の仕組みでそれらをさせないようにすることが行われる。メリットが大きいからである。さてその原因とは（１）循環計算など収拾がつかなくなることを避ける（２）Subプロセジュアと役割分担（３）そう決めただけのこと（４）その他のどれか判らないが（２）が大きいと思う。
（１）イベントプロセジュアとして分ければ似たことは出きる。下記では特定の関数で変ったのでなくても反応するので不完全ですが。
Private Sub Worksheet_Change(ByVal Target As Range)
Cells(2, 2) = "OK"
End Sub
（２）Functionプロセジュアの中でSubプロセジュアで置換えてもだめでした。
Function test1(a)
'Cells(2, 2) = "ok"
'test2
test1 = 2
End Function
Sub test2()
Cells(2, 2) = "ok"
End Sub
（３）変数を新たに使って値を設定し加工は可能
Function test1(a)
'Cells(2, 2) = "ok"
'test2
w = 2
test1 = w
End Function
VBAのFunctionプロセジュアは、VBのFunctionプロセジュアとは違いがあるようです。セル全体に亘ってその関数が使われているかのチェックと再計算が行われるなどが特殊性です。

お礼

ありがとうございました。

私も循環の問題でできないようにしているのかな、と思いました。
でも、セルに数式を入れるときに、循環参照のエラーは出ますよね？あのようなエラーは発生してくれれば、、、

また、試してはないのですが、
フラグを立てて、Worksheet_Change イベントによる処理もできないことはないような気がしました。