エクセルのユーザー定義関数で(VBA)

質問

エクセルのVBAで、セルに「=SheetName」と書き込めば、その関数を書き込んだセルが属するシート名をセルに代入さるようなユーザー定義関数を作りたいと考えています。
で、標準モジュールに
Public Function SheetName(test)
SheetName = ？？？？？
End Function
と、書き込んでふと止まりました。
ユーザー定義関数を書き込んだシートの名前ってどうやって取得すればいいのでしょうか？VBでいうSenderみたいなのってあるんでしょうか？
どなたか？詳しい方教えて頂けないでしょうか？
宜しくお願いいたします。

回答No.4

izmlz · ベストアンサー

「Avtivesheet.Name」で「ユーザー定義関数を書き込んだシートの名前」を表示させるのは、一見うまくいきそうですが、実際にはうまくいきません。以下を試してみてください。

　標準モジュールに↓を入力してから、

Function SheetName() As String
Application.Volatile
SheetName = Application.Caller.Parent.Name
End Function

Function SheetName2() As String
Application.Volatile
SheetName2 = ActiveSheet.Name
End Function

　Sheet1、Sheet2、Sheet3に↓をそれぞれ入力。

=SheetName()
=SheetName2()

　Sheet1、Sheet2、Sheet3に↑をそれぞれ入力した直後は、どちらも正しく「ユーザー定義関数を書き込んだシートの名前」を表示しています。

　でも、その状態から、Sheet1、Sheet2を見てみてください。
　「=SheetName()」、すなわち、Application.Caller.Parent.Nameを使っている方は、正しく「ユーザー定義関数を書き込んだシートの名前」を表示しています。
　一方、「=SheetName2()」、すなわち、ActiveSheet.Nameを使っている方は、Sheet1、Sheet2にもかかわらず、「Sheet3」と表示されてしまいます。

　そのまま、Sheet1がアクティブな状態で、F9キーを押す（再計算させる）と、ActiveSheet.Nameを使っている方も正しくSheet1と表示されます。しかし、そうしてからSheet2、Sheet3を見ると、結果が「Sheet1」になっています。

お礼

ご指導有り難うございます。
私の目的は、この書き込みで解決しました。
本当に有り難うございます。
Avtivesheet.Nameでは、上手くいかないのでふと止まったので、今一度F2を押して、勉強しなきゃと思いました。
izmlzさんは、どのように勉強されてきたのですか？市販のVBA辞典では、勉強に限りがありますよね？

回答No.12

izmlz

＞引数を取る限りは、Volatileメソッドは、あまり必要がないといっています。

　ここ、ちょっと誤解されているようです。引数の変化によってユーザー定義関数の結果が変わるようなものであれば、そうでしょうが、今回のような場合はそうではないと思います。

　「論より証拠」、まずは、↓を実際に試してみてください。

　標準モジュールに↓を入力してから、

Public Function SheetName(test As Range) 'original
SheetName = test.Parent.Name
End Function

Public Function SheetName2(test As Range) 'originalにApplication.Volatileを追加
Application.Volatile
SheetName2 = test.Parent.Name
End Function

　Sheet1に↓をそれぞれ入力。

=SheetName($A$1)
=SheetName2($A$1)

　両者とも「Sheet1」と表示されます。

　続いて、そのシート名をたとえば「Sheet11111」に変更します。

=SheetName($A$1)　→　「Sheet1」のまま。
=SheetName2($A$1)　→　期待された結果である「Sheet11111」になる。

　すなわち、繰り返しになりますが、取っている引数の変化に関数の結果が依存している場合には、Application.Volatileは不要ですが、今回のように「シート名の変更」といったように、そうでない場合には必要になる、というわけです。

　それから、volatileの多用は問題だというのは認識していますが、今回のように、おそらくは各シートに一つずつ程度であれば、そんなに神経質になる必要はないと考えます。

＞何も触らないのに、保存を促されるのでどうしたらよいのか

　これについては、まあ、しょうがないのかなと思います。
　ワークシート関数でシート名を取得する場合に使用するCELL関数も同様ですし。

回答No.11

Wendy02

こんにちは。

少し読ませていただきました。

#9 のizmlzさんの
>どうしても「Application.Volatile」をつけてしまいます。それが私の回答スタイルになります。

私も、それはそれで良いというか、ほかに方法が見つからない限りは、それで良い思うのですが、ただ、人によって、それが「けしからん」という反応を取る方がいることだけは、知っておいて良いかと思います。そんなことは、本当にたいしたことではないというか、ある変わった人が、それを大ごとにし、執拗に攻撃的な人も、掲示板にはいます。また、古参の方には、おかしな信念を持った方もいらっしゃるようです。私の回答に対して誤解されたままになっている方もいます。

話が戻りますが、時々、何も触らないのに、保存を促されるのでどうしたらよいのか、という質問レベルならよいのですが、Volatile を使うと、一斉に、数式によって一斉に計算し始めるので、問題だという人がいます。それは、ひとつは、ワークシートの設計の問題だとは思うのですが、Volatile 自体は、使うか使わないかは、引数を取っている限りは、引数を入れたときに、再計算が実行されますので、あまり問題がないと思うのです。

この Volatile の問題は、Mougの大村あつし氏が書籍『かんたんプログラミング　Excel VBA応用編』(技術評論社）のユーザー定義関数の章の、コラム内で「再計算」について書いていますが、引数を取る限りは、Volatileメソッドは、あまり必要がないといっています。

それでも、引数なしで、Application.Caller で、シート名を取れば必要になるとは思いますが、このレベルでは、関数で可能なものは、こういっては語弊があるかもしれませんが、方法は、どうでも良いことだと思います。

それよりも、そのプログラミングで、「最後に何を目的にするか」ということです。それによって変わってくることも多いので、ある程度余裕を持ったスタンスで見て行かないと、かなり違ってしまうこともあります。えてして、最初に回答した人が、捨て石になることが多いと思います。中には、最初とはまったく内容の違う質問もありますからね。

回答No.10

izmlz

＞私は特に点数にこだわりはありませんので

などと↑で書いて、舌の根も乾かぬうちに．．．ですが、もし、私が質問者ならば、「今後同様な課題があって検索してたどりついた人」にとって最も有益であるような回答に優先的に点数をつけるべきなんだろうなあ、と思います。解決しちゃうと、終わりのような気がしますが、実際にはその後に検索して見に来る人が結構いらしゃるようです（これは、当該ページの来訪者数がわかる別のサイトで実感しました）。我々当事者は上から下まで見ますが、解決策を検索している人の多くは「結局、どの回答で解決したのか」しか見ないのではないかと想像します。

と、余計なことを書きましたが、回答スタイルが人それぞれなように、評価基準も人それぞれですので、Excel_VBAさんがどのような評価をされようと、それはExcel_VBAさんのご自由です。

回答No.9

izmlz

＞izmlzさんは、どのように勉強されてきたのですか？

ここのようなQ&Aサイトで回答を考えるのは良い勉強になると思います。
私のプロフィールをご覧になると、「そんなこと言っているけど、なんだまだ登録して2か月もたってないじゃないか」と思われるかも知れませんが、昔々は別の名前で出てましたし、ここ以外のサイトでも回答をつけてます。もっとも結構ブランクはありますが（笑

回答する際にはできるだけ"knee-jerk reaction"だけで終わらないように、別の解法はないか考えたり、いろいろな状況下でうまくワークするかどうかなどを検証するようにしてきたことが勉強になったと思います。

たとえば、今回の問題に回答をするとした場合、「その関数を書き込んだセルが属するシート名をセルに代入」する場合、なんとなく「Activesheet.Name」でよさそうな感じがしますが、本当にそれでワークするのかどうかを、たとえば、↑のANo.4のような検証をしてみる、というわけです。

もっとも、「こういうニーズの場合、Activesheet.Nameはワークしない。Application.Caller.Parent.Nameを使うべき。」というのが今の私のknee-jerk reactionになっていました。すなわち、「その関数を書き込んだセルが属するシート名を取得するのであれば、セル参照は不要」だったわけですが、Wendy02さんの回答を見て、入力されているシート以外のシートのセルを参照してシート名を表示させる」こともあると思いいたった次第です。

＞両者に気分を害する環境

いえいえ、私の方はまったくそんなことはありません。
細かいことを言ったので、Wendy02さんが気分を害されたかなと思っていたのですが、ANo.8を見てそうではないということで安心しました。

回答のスタイルは人それぞれです。

また、Wendy02さんの例を出させていただいて恐縮ですが、

＞Public Function SheetName(test As Range)
＞SheetName = test.Parent.Name
＞End Function

Wendy02さんの場合「私の回答のスタイルは、ヒット・アンド・アウェイというか、力をセーブして、質問者さんの様子を見ながらという方法です。」とのことなので、これがWendy02さんの回答スタイルなのだと思います。

私の場合、上記回答をつけるとしたら、このままだとSheet名を変更した場合に対応していない（シートタブをダブルクリックしてシート名を変更してもユーザー定義関数の結果は変更されない）ので、どうしても「Application.Volatile」をつけてしまいます。それが私の回答スタイルになります。

＞共に20点を付けたいのですが、これもルール上差を付けるしかありません。誠に申し訳けないことだと思っています。

その気持ちだけで十分です。私は特に点数にこだわりはありませんので、Wendy02さんにどうぞ。

回答No.8

Wendy02

こんばんは。

たぶん、Excel_VBA様には、私は、No.2485311 のここのカテゴリの最初のご質問の時からです。一応、私の第２幕の終わりが近くなってきています。

ID自体を今のところ、他の関係もあるので抹消はしないつもりですが、こちらの環境の変わりつつあって、当分は、戻らないつもりで、izmlzさんに、私のつたないアドバイスを規約違反を承知で書かせていただきました。別に、気分を害してはいません。私の回答のスタイルは、ヒット・アンド・アウェイというか、力をセーブして、質問者さんの様子を見ながらという方法です。それが良いのかは、人それぞれですが。誰かに、バトンタッチっていうところです。

それと、Office VBA自体も、そろそろ、私としては終わりにしようかなって思っています。今のように、掲示板ばかりに関わっていても、あまり大きな進歩もありません。いずれにしても、充電したいと思っています。

私個人の勉強として、C/C++は、途中で投げ出してしまい(ごたぶんにもれず、ポインタで挫折）、VB.Net も中途半端になってしまったなって思うし、Perl は、ごちゃごちゃして、モノにはなりそうもなかったし、Access やDatabase 関係もちょっと手をつけては休んでしまい、Wscript も、いまや、WMIが主流になっているし、それに、今度は、PowerShellが出てきて、何も知らないままに、どんどん遅れてしまっているような気がします。せめて、C/C++ は、もう少し、勉強したいと思っています。

なんというか、Excel VBAをやっていると、ものすごく泥臭いところで、あれこれやっているような気がしてならないのです。Excel VBAの難しさって、「Excel+Office+VB」の三つが重なり合って、ぐちゃぐちゃになっているわけです。その点で、WordやOutlookやAccess は、もう少しすっきりとしています。特に、ここのカテゴリで出てくる「専門家」さんたちは、その泥臭さをご存知ないようです。しかし、その泥臭いところにスボッとハマったままですと、どうにも、次元の低い所であれこれこだわったりして、うまくないなって思うことがあります。

もちろん、別の仕事の問題もあって、一旦、ここを離れるというか、当分は、続けられそうにもないというのが実際のところです。

回答No.7

Wendy02

こんにちは。
#6 のizmlz さん、ご指摘ありがとうございます。

こんなものでどうでしょうか。私のミスはミスですから、あれこれ、こちらに対して問題自体を書かれるよりも、直接、自分の思うとおりの数式を直して出していただいたほうが、余計なことに巻き込まれずに、スムーズに済むかと思います。もう、ここでは、この書き込みは、規約違反の範囲にはなってしまいます。ルールはルールですから仕方がないです。今、数式の記録を見ると、もう５年も前のメモで、そのまま載せてしまいました。メモのほうも訂正しておきました。

#5
>例：
>=REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename")),"")

=REPLACE(CELL("filename",$A$1),1,FIND("]",CELL("filename",$A$1)),"")

これとは別の話ですが、質問の回答は、ご質問者さんの受け入れ自体の問題です。ここ最近、立て続けに、私が対応してきた質問者さんの反応に、唖然とさせられることが多いので、ある程度、ひとつの解答で完結しているなら、他人の回答の反応を含めて、質問者さんの反応を見てからでも遅くはないかとは思います。せっかく苦心して書かれても、よりレベルの低い回答にポイントが入ることもあれば、ただ、お礼もなく、ポイントも付かずに締めてしまうこともあれば、そのままで締めないままになっていることもあれば、誤解のままで用済みで締めてしまうこともあります。

なお、回答は、ユーザー選択の範囲を残しておいて、コードの説明を入れておいたほうが、良いのかなって思います。Volatileの再計算の問題とか、引数とか、親オブジェクトを取る自体は、初級の範囲は超えないと思いますから、回答者側でどんなに工夫しても、このレベルでは、たかが知れています。

お礼

Wendy02さん、izmlzさん有り難うございます。
お返事が遅くなり申し訳ありません。
高度なレベルで討論されて、とても勉強になりました。
Callerってプロパティは知りませんでしたし、引数をオブジェクトとして扱えるのも知りませんでした。共に20点を付けたいのですが、これもルール上差を付けるしかありません。誠に申し訳けないことだと思っています。ちょっと、出張に行ってお返事するのが遅れたために、両者に気分を害する環境を作ってしまい。また、私自身は、漁夫の利を得た気持ちになっています。(引数のオブジェクトの件)
このサイトでは、Wendy02さんと、KenkenSPさんの２大柱で形成されているのかなぁ？って思っていましたが、3本目の柱も現れ(すみません、私が知らなかっただけかもしれませんが)、より安定したのかと思いました。今後とも宜しくお願い致します。
特に、Wendy02は、、失礼な仕打ちを数多くされてきている見たいですが、神様に思っている人も沢山いるのも事実です。くだらない事も、質問するかもしれませんが、見捨てないでください。

回答No.6

izmlz

私は、「その関数を書き込んだセルが属するシート名をセルに代入」ということだったので、わざわざセルを参照させる必要はないと思いました。

　「その関数を書き込んだセルが属するシート名をセルに代入」だけでなく、「入力されているシート以外のシートのセルを参照してシート名を表示させる」こともある、ということでしたら、

Function SheetName(Optional test As Range) As String
Application.Volatile
If test Is Nothing Then
SheetName = Application.Caller.Parent.Name
Else
SheetName = test.Parent.Name
End If
End Function

で、いかがでしょう。

=sheetname()
=sheetname($A$1)

↑のどちらでも、入力されたシート名を返します。

　また、↓のようにすれば、参照するセルの属するシート名を返します。

=sheetname(Sheet2!$A$1)

　なお、上記で、A1ではなく$A$1とわざわざ絶対参照にしているのは、式をコピーした時などにエラーになるのを防ぐためです。

Wendy02さん

　細かい話で申し訳ないんですが、

＞ =REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename")),"")

1. 二つ目のCELL関数でセル参照がないので、並べて表示の状態で、別のブックがアクティブになっている場合に、正しくシート名が表示されない場合（アクティブになっている別のブックの]までの文字数が、このワークシート関数の入力されているブックの]までの文字数が異なる場合）があります。

2. 一つ目のCELL関数のセル参照が相対参照になっていますが、これだと、例えば、あるシートのセルC6に入力してあるこの数式を別のシートのセルA1にコピペした時に、エラーとなります。

回答No.5

Wendy02

こんにちは。

>標準モジュールに

>Public Function SheetName(test)
>SheetName = ？？？？？
>End Function

これなら、

Public Function SheetName(test As Range)
SheetName = test.Parent.Name
End Function

引数を取るように作られているなら、上記のように、Range型の引数<test>から、親オブジェクトを取ればよいのではないでしょうか。もちろん、いろいろ考えれば、再計算関数の問題とか、VBのsender の代わりとして、Application.Caller や、ワークシートのイベントから、そのsender を取る方法とかありますが、ワークシート関数で可能なものですから、あえて、ユーザー定義関数にするとなると、それなりに、その全体の必要性にもよっても変わってくるような気がします。
例：
=REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename")),"")

私は、既存のものに対しては、あまり手を加えてもしょうがないなっていうように思いました。なお、この場合では、Public ステートメントはあってもなくても同じですね。

回答No.3

imogasi

Avtivesheet.Name
Avtivesheetでシートの特定をして
Nameでそのプロパティの値を参照しているだけです。
Avtivesheetと具体名を判らないままシートを特定できるところがミソです。
http://www5b.biglobe.ne.jp/~yone-ken/VBNET/special/sp02_event.html
に解説がある、イベントの関連とは関係ありません。
上記ＷＥＢほかの＞senderがイベント発生源のオブジェクト、・・の箇所を理解してください。「Senderとは　 VB」でＷＥＢ照会して出る記事でも参考に。
ＳＵＢ，Ｆunctionのプロシはイベントとは他分類のプロシです。

回答No.2

izmlz

Function SheetName() As String
Application.Volatile
SheetName = Application.Caller.Parent.Name
End Function

で、セルに「=SheetName」と書き込めば、その関数を書き込んだセルが属するシート名を表示します。

なお、ワークシート関数では、↓です。

=MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,31)

参考URL：

http://www2.odn.ne.jp/excel/waza/sheet.html#SEC5