Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。

  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
5707 / 13645 ﾂﾘｰ ←次へ　｜　前へ→
▼ 【49202】独自関数作成 ユニフ 07/5/27(日) 16:08 質問[未読]
【49204】Re:独自関数作成 かみちゃん 07/5/27(日) 16:35 回答[未読]
【49206】Re:独自関数作成 yuu1 07/5/27(日) 16:45 回答[未読]
【49213】Re:独自関数作成 ichinose 07/5/27(日) 22:41 発言[未読]
【49216】Re:独自関数作成 yuu1 07/5/27(日) 23:20 回答[未読]
【49217】Re:独自関数作成 ichinose 07/5/27(日) 23:26 発言[未読]
【49218】Re:独自関数作成 yuu1 07/5/27(日) 23:44 回答[未読]
【49219】Re:独自関数作成 ichinose 07/5/28(月) 0:34 発言[未読]
【49221】Re:独自関数作成 ユニフ 07/5/28(月) 2:30 発言[未読]
【49222】試してみました ユニフ 07/5/28(月) 3:30 発言[未読]
【49223】Re:試してみました りん 07/5/28(月) 5:43 回答[未読]
【49228】Re:試してみました ichinose 07/5/28(月) 7:45 発言[未読]
【49239】Re:試してみました yuu1 07/5/28(月) 13:25 回答[未読]
【49240】再度 試してみました ユニフ 07/5/28(月) 15:46 発言[未読]
【49241】Re:再度 試してみました yuu1 07/5/28(月) 16:55 回答[未読]
【49243】うまくいってないです。 ユニフ 07/5/28(月) 18:00 発言[未読]
【49244】Re:うまくいってないです。 yuu1 07/5/28(月) 18:06 回答[未読]
【49245】うまくいきました。 ユニフ 07/5/28(月) 18:25 お礼[未読]
【49251】Re:試してみました ichinose 07/5/29(火) 8:00 発言[未読]
【49266】Re:試してみました ユニフ 07/5/29(火) 23:30 発言[未読]
【49351】xlsheetVeryHidden ユニフ 07/6/1(金) 0:25 お礼[未読]
【49202】独自関数作成
質問  ユニフ  - 07/5/27(日) 16:08 -
引用なし
パスワード
   よろしくお願いします。

VBAで独自関数を作れると聞いたのですが下記のような事はできますでしょうか？

例えばkoiという関数を作りたいとして

セルにkoi（１）とするとシート３のL１２の値を参照する。
koi（２）とするとシート３L１３の値を、koi(3)ならシート３のL14を。
という具合にしたいと思います。シート３は隠しシートしてしています。

普通なら=’Sheet３!L12と書いていけばいいのですが、
事情があって隠しシートを使っている事をわからないようにしたいのです。
【49204】Re:独自関数作成
回答  かみちゃん  - 07/5/27(日) 16:35 -
引用なし
パスワード
   こんにちは。かみちゃん　です。

>例えばkoiという関数を作りたいとして
>
>セルにkoi（１）とするとシート３のL１２の値を参照する。
>koi（２）とするとシート３L１３の値を、koi(3)ならシート３のL14を。
>という具合にしたいと思います。シート３は隠しシートしてしています。

以下のようなコードを標準モジュールに記述して、

Function koi(lngRow As Long)
　koi = Sheets("Sheet3").Range("L" & lngRow + 11).Value
End Function

結果を表示したいセルに
=koi(1)
などと入力します。
【49206】Re:独自関数作成
回答  yuu1  - 07/5/27(日) 16:45 -
引用なし
パスワード
   >VBAで独自関数を作れると聞いたのですが下記のような事はできますでしょうか？

ユーザー関数は任意に作れますが、
引数にはセルを指定するのが基本です。その引数が変更されたとき自動計算されます。


>セルにkoi（１）とするとシート３のL１２の値を参照する。

このような関数ですと、シート３のL１２の値が変更されても自動計算されません。

>普通なら=’Sheet３!L12と書いていけばいいのですが、
>事情があって隠しシートを使っている事をわからないようにしたいのです。

そうであれば、
セルの書式設定-保護-表示しない、にチェックして
数式が表示されないようにするのが普通では。
【49213】Re:独自関数作成
発言  ichinose  - 07/5/27(日) 22:41 -
引用なし
パスワード
   こんばんは。

>>セルにkoi（１）とするとシート３のL１２の値を参照する。
>
>このような関数ですと、シート３のL１２の値が変更されても自動計算されません。
>
>>普通なら=’Sheet３!L12と書いていけばいいのですが、
>>事情があって隠しシートを使っている事をわからないようにしたいのです。
>
>そうであれば、
>セルの書式設定-保護-表示しない、にチェックして
>数式が表示されないようにするのが普通では。

これは、もっともな話ですが、可能性ということとなると・・・。

オプションで計算方法が「自動」に設定されていれば、

標準モジュールに
'================================================================
Function ok(myindex As Long, Optional myaddress As String = "sheet3!l3") As Variant
　　Application.Volatile
　　ok = Application.Range("offset(" & myaddress & "," & myindex - 1 & ",0,1,1)").Value
End Function

これで適当なセルに

=ok(1)


とすると、Sheet3（仮に隠しシートのシート名だとすると）のL3の内容を返します。
更にL3の更新が「=ok(1)」と指定したセルに反映されます。
【49216】Re:独自関数作成
回答  yuu1  - 07/5/27(日) 23:20 -
引用なし
パスワード
   >Function ok(myindex As Long, Optional myaddress As String = "sheet3!l3") As Variant
>　　Application.Volatile
>　　ok = Application.Range("offset(" & myaddress & "," & myindex - 1 & ",0,1,1)").Value
>End Function

引数のあるユーザー関数にVolatileメソッドを用いるのは最悪の設計だとおもいますよ。
Application.Volatileの意味はお分かりですか？
【49217】Re:独自関数作成
発言  ichinose  - 07/5/27(日) 23:26 -
引用なし
パスワード
   >引数のあるユーザー関数にVolatileメソッドを用いるのは最悪の設計だとおもいますよ。
>Application.Volatileの意味はお分かりですか？
HELPを見て調べたつもりでが、
数式の数にもよると思いますが、いかがですか？
【49218】Re:独自関数作成
回答  yuu1  - 07/5/27(日) 23:44 -
引用なし
パスワード
   >HELPを見て調べたつもりでが、
>数式の数にもよると思いますが、いかがですか？

Helpの使用例として以下のようなものがあると思いますが、
Function My_Func()
　　Application.Volatile
　　'
　　'　　関数の実際の動作を記述します。
　　'
End Function

これは引数の無いユーザー関数の場合、
しかたがないのでワークシートのいずれかのセルで計算(変更)を行うたびに、
この関数を再計算するというものです。

引数のある関数でわざわざVolatileをつかうのはいかがなものでしょうか。
どこかのセルが計算(変更)されるたびに、再計算が行われ動作の重いブックになってしまいます。

使用箇所が少なければ、たいした実被害はないかもしれませんが、
Volatileメソッドを使わなくてすむならそれに越したことはないと思います。
【49219】Re:独自関数作成
発言  ichinose  - 07/5/28(月) 0:34 -
引用なし
パスワード
   >>HELPを見て調べたつもりでが、
>>数式の数にもよると思いますが、いかがですか？

とお尋ねしましたので寝ずに待っていました。


>>これは、もっともな話ですが、可能性ということとなると・・・。

ということで記述しました。

この隠しシートのデータを引用するバックボーンが見えていません。
予め、シートの保護で設定できる内容（数式）なら良いですが、
ユーザーに設定させたいのかもしれない。
（その際に隠しシートのシート名をユーザーに知らせたくない等）
よって、可能性としてこんな方法もということで記述しました。

数式の数が少なければ、という条件は書き忘れましたが・・・。

もっともそれならば、シートイベントを使って、
「1」と入力されたなら、Sheet3のL3の内容を設定するという仕様の方が
良かったのかもしれません。
（これも返信投稿の流れによっては、可能性として記述するつもりでした）


>引数のある関数でわざわざVolatileをつかうのはいかがなものでしょうか。
そうですね！！おっしゃっている意味は理解しているつもりですが、

今回の事情は再計算を誘発する引数がないのでVolatileを
使ったまでです。
引数がない関数では仕方なくせよ認めるならば、↑も同じ事情だと思いますが・・。

でも、Volatileを使う使わないの基準の再確認ができました。
ありがとうございます。

もう寝るので何かありましたら、返信は明日以降にまります。
【49221】Re:独自関数作成
発言  ユニフ  - 07/5/28(月) 2:30 -
引用なし
パスワード
   皆様、色々レスありがとうございます。
夜勤で皆様のレスを拝見するのが遅くなってしまいまして申し訳ないです。
掲示していただいたコードはこれから試してみます。

背景についてもっと説明しておくべきだったでしょうか。
ファイルは２年ぐらいちょこちょこいじって作ってきた最低４シート
連動させるタイプです。関数は色々積み重ねてきて全体で数万文字から数十万文字
ぐらいあると思います。関数を消されるミスで何度も何度も困りはてたのですが、
シート保護と隠しシート、ブックの保護で解決したのですが、職場全体で正式に
採用される運びなので、パスワードは様式の管理上上層部に公開することになりそうです。

ところが関数を壊すのは上層部の人に多いので、今回関数をほとんど隠しシートに移して
ブックの保護で何とか破壊は防ごうと思ってますが、関数の積み重ねは今後も続くと思うので
修正の際保護をかけ忘れるかもしれません。そうした場合でも隠しシートの存在
がわからなければファイル破壊は防げそうだと考えたわけです。

今は=’Sheet３!L12のように関数を置いてあり、

Sheet３ L12に置いてある関数はVLOOKUP で文字列（氏名や曜日）が抽出されるようになっています。
【49222】試してみました
発言  ユニフ  - 07/5/28(月) 3:30 -
引用なし
パスワード
   Function ok(myindex As Long, Optional myaddress As String = "sheet3!l3") As Variant
　　Application.Volatile
　　ok = Application.Range("offset(" & myaddress & "," & myindex - 1 & ",0,1,1)").Value
End Function

を試してみました。

=ok(1)は順調です。例で言うとsheet3!l3の内容が出てきます。
が=ok(2)は0が返りました。すいません私の例示が間違っていました。
この場合=OK(2)にするとsheet3!M3、=OK(3)にするとsheet3!N3、の値を引っ張って欲しいです。自分でoffsetをいじってみましたが、駄目でした。

その他動作は順調です。
【49223】Re:試してみました
回答  りん E-MAIL  - 07/5/28(月) 5:43 -
引用なし
パスワード
   ユニフ さん、おはようございます。

>=ok(1)は順調です。例で言うとsheet3!l3の内容が出てきます。
>が=ok(2)は0が返りました。すいません私の例示が間違っていました。
>この場合=OK(2)にするとsheet3!M3、=OK(3)にするとsheet3!N3、の値を引っ張って欲しいです。

こういうことですか？
Function ok(myindex As Long, Optional myaddress As String = "sheet3!L3") As Variant
　　Application.Volatile
　　'
　　ok = Application.Range(myaddress).Offset(0, myindex - 1).Value
End Function
【49228】Re:試してみました
発言  ichinose  - 07/5/28(月) 7:45 -
引用なし
パスワード
   おはようございます。

>>=ok(1)は順調です。例で言うとsheet3!l3の内容が出てきます。
>>が=ok(2)は0が返りました。すいません私の例示が間違っていました。
>>この場合=OK(2)にするとsheet3!M3、=OK(3)にするとsheet3!N3、の値を引っ張って欲しいです。
>

>Function ok(myindex As Long, Optional myaddress As String = "sheet3!L3") As Variant
>　　Application.Volatile
>　　'
>　　ok = Application.Range(myaddress).Offset(0, myindex - 1).Value
>End Function

りんさん、フォローありがとうございます。
にしても、早起きですねえ。私も普段は、同じくらい（いや、もっと早いかも）に
起きるのですが、昨日は、球技大会もあったし、寝るの遅かったし・・・。

ユニフさん、私の方法でも修正できますが、りんさんのコードで確認してください。

それとすいぶん関数や数式が多いとの事、数式の設定が沢山であれば、
Volatileを使用した例は問題があります。

これは、私とyuu1さんのやり取りでお分かりだかと思います。

まずは、問題のシートで十分に確認してみてください。
その上で、運用を決断してください。
【49239】Re:試してみました
回答  yuu1  - 07/5/28(月) 13:25 -
引用なし
パスワード
   こんにちは。

>=ok(1)は順調です。例で言うとsheet3!l3の内容が出てきます。
>が=ok(2)は0が返りました。すいません私の例示が間違っていました。
>この場合=OK(2)にするとsheet3!M3、=OK(3)にするとsheet3!N3、の値を引っ張って欲しいです。自分でoffsetをいじってみましたが、駄目でした。

隠しシートを使っている事をわからないようにして
且つ、Volatileメソッドを用いない方法を考えてみました。

名前を使います。
一例として、Sheet3の L3:N3 に lmn と名前を付けます。
名前を見られたくなければ非表示にします。

マクロでは以下。
　ActiveWorkbook.Names.Add(Name:="lmn", RefersTo:="=Sheet3!$L$3:$N$3").Visible = False

ユーザー関数は以下。
Function koi(xx As Long, rg As Range)
　koi = rg(xx)
End Function

セルに、
=koi(1,lmn)
=koi(2,lmn)
=koi(3,lmn)
と記述します。
【49240】再度 試してみました
発言  ユニフ  - 07/5/28(月) 15:46 -
引用なし
パスワード
   皆様こんにちは ichinoseさん、りんさん、yuu1さん、ご指導ありがとうございます。

皆様のコード試してみましたが、皆様の説明だとyuu1さんのコードならリスクが
すくなさそうな事と、セルに独自関数を入れたときに普通の関数っぽく装いやすいので
使わせていただきました。結果順調で満足しています。
以下の部分がよくわからないのですが

>名前を使います。
>一例として、Sheet3の L3:N3 に lmn と名前を付けます。
>名前を見られたくなければ非表示にします。

これは、挿入→名前→定義のリストに出てこなくなるのでしょうか？

>マクロでは以下。
>　ActiveWorkbook.Names.Add(Name:="lmn", RefersTo:="=Sheet3!$L$3:$N$3").Visible = False

このコードは標準モジュールに

Sub 名前隠し()
ActiveWorkbook.Names.Add(Name:="lmn", RefersTo:="=Sheet3!$L$3:$N$3").Visible = False

End Sub

のように書けば良いのでしょうか？
【49241】Re:再度 試してみました
回答  yuu1  - 07/5/28(月) 16:55 -
引用なし
パスワード
   こんにちは。

>これは、挿入→名前→定義のリストに出てこなくなるのでしょうか？
>
>>マクロでは以下。
>>　ActiveWorkbook.Names.Add(Name:="lmn", RefersTo:="=Sheet3!$L$3:$N$3").Visible = False

はい。挿入→名前→定義のリストに出てきません。

>このコードは標準モジュールに
>
>Sub 名前隠し()
>ActiveWorkbook.Names.Add(Name:="lmn", RefersTo:="=Sheet3!$L$3:$N$3").Visible = False
>
>End Sub
>
>のように書けば良いのでしょうか？

標準モジュールとは限らず、それなりに書けばどのモジュールでもいいと思います。
このコードは名前"lmn"を作成して非表示にしています。
どうも、ThisWorkbookに対する処理みたいなので、
そうであれば、 ActiveWorkbook→ThisWorkbookに差し替えたほうがいいですね。

すでに名前が作成してあるのであれば
ThisWorkbook.Names("lmn").Visible = False
と、なります。
【49243】うまくいってないです。
発言  ユニフ  - 07/5/28(月) 18:00 -
引用なし
パスワード
   始めにThisWorkbookに

Sub 名前隠し()

ThisWorkbook.Names("lmn").Visible = False
End Sub

と入れましたが効果ありませんでした。次に一度名前の定義を消して標準モジュールに

Sub 名前隠し()

ActiveWorkbook.Names.Add(Name:="lmn", RefersTo:="=Sheet3!$L$3:$N$3").Visible = False

>End Sub

としましたが、名前定義の所から効果が出ずに独自関数がエラーになってしまいます。

私の使い方が悪いのだと思います。Subとして使うならこのマクロ自体を一回
走らせないといけないわけですよねぇ。
Workbook_Open()の中に

ActiveWorkbook.Names.Add(Name:="lmn", RefersTo:="=Sheet3!$L$3:$N$3").Visible = False

を書いて保存して開けば良いのでしょうか。これ以上いじると自分で
壊してしまいそうなのでご助言お願いします。
【49244】Re:うまくいってないです。
回答  yuu1  - 07/5/28(月) 18:06 -
引用なし
パスワード
   こんにちは

>Workbook_Open()の中に
>
>ActiveWorkbook.Names.Add(Name:="lmn", RefersTo:="=Sheet3!$L$3:$N$3").Visible = False
>
>を書いて保存して開けば良いのでしょうか。

Workbook_Openに書く必要はありません。
標準モジュールで1回実行して保存すれば、あとは必要ありません。
名前はブックに保存されますから。
【49245】うまくいきました。
お礼  ユニフ  - 07/5/28(月) 18:25 -
引用なし
パスワード
   レスしてくださった皆様ありがとうございました。
ファイルは無事新機能つきでうまく動いております。

おかげで頭痛の種が解消して、コードの勉強もちょびっとできたと思います。
忙しい中、ご指導いただきありがとうございました。

VBA質問箱には、いつも助けていただいているので
開設者の管理人さんにも改めてお礼を言わせていただきます。
ありがとうございました。
【49251】Re:試してみました
発言  ichinose  - 07/5/29(火) 8:00 -
引用なし
パスワード
   おはようございます。

>隠しシートを使っている事をわからないようにして
>且つ、Volatileメソッドを用いない方法を考えてみました。
>
>名前を使います。
>一例として、Sheet3の L3:N3 に lmn と名前を付けます。
>名前を見られたくなければ非表示にします。
>
>マクロでは以下。
>　ActiveWorkbook.Names.Add(Name:="lmn", RefersTo:="=Sheet3!$L$3:$N$3").Visible = False
>
>ユーザー関数は以下。
>Function koi(xx As Long, rg As Range)
>　koi = rg(xx)
>End Function
>
>セルに、
>=koi(1,lmn)
>=koi(2,lmn)
>=koi(3,lmn)
>と記述します。
これだとVolatile要りませんね！！
メモメモ、ありがとうございます。よく覚えておきます。

これも可能性として、
上記例でlmnの参照内容は、
基本的には見えません（VBA、Excel4Makuro等の使用を除く）。

が、

「挿入」---「名前」---「定義」でlmnと指定すれば、削除も参照の変更も可能です。

「名前の定義なんて、触らないから大丈夫！！」ということでしたら、
大きなお世話ですが、何らかのフェイルセーフを用意しておいたほうが良いかも
しれませんよ！！

例
　Workbook_Open()イベントにて再定義する等など


それと
>ブックの保護で何とか破壊は防ごうと思ってますが、関数の積み重ねは今後も続くと思うので
>修正の際保護をかけ忘れるかもしれません。そうした場合でも隠しシートの存在
>がわからなければファイル破壊は防げそうだと考えたわけです。

という箇所を再度読み返しました。

これ、そもそもはこの隠しシートの内容が破壊されないための処置ですよね！！
ブックの保護をし忘れた場合、「書式」---「シート」---「再表示」で
対象シートが触れてしまうことを懸念されているのでしょうか？

だとしたら、

ＶＢＥにて（Excelからでも可能ですが説明の都合上）、

プロジェクトエクスプローラーにて、

・非表示を行う対象シートを選択
・選択した状態でＦ４を押します。プロパティウインドーが表示されます。
・このプロパティの表内のVisibleを2のxlsheetVeryHiddenに設定します。

これでExcelからは、ブックの保護の有無に関わらず、再表示が出来ません。

つまり、隠しシート名の公開が問題にならないと思うのですが、
私の意味の取り違いえでしょうか？
【49266】Re:試してみました
発言  ユニフ  - 07/5/29(火) 23:30 -
引用なし
パスワード
   ichinose さん、こんばんは


>「挿入」---「名前」---「定義」でlmnと指定すれば、削除も参照の変更も可能です。
>「名前の定義なんて、触らないから大丈夫！！」ということでしたら、
>大きなお世話ですが、何らかのフェイルセーフを用意しておいたほうが良いかも
>しれませんよ！！

う〜む、よくわからないので試してみます。
>ブックの保護をし忘れた場合、「書式」---「シート」---「再表示」で
>対象シートが触れてしまうことを懸念されているのでしょうか？

おっしゃるとおりです。

>ＶＢＥにて（Excelからでも可能ですが説明の都合上）、
>プロジェクトエクスプローラーにて、
>・非表示を行う対象シートを選択
>・選択した状態でＦ４を押します。プロパティウインドーが表示されます。
>・このプロパティの表内のVisibleを2のxlsheetVeryHiddenに設定します。
>これでExcelからは、ブックの保護の有無に関わらず、再表示が出来ません。
>つまり、隠しシート名の公開が問題にならないと思うのですが、
>私の意味の取り違いえでしょうか？

この辺をいじったことがなく、どのように動くかわからないので明日試してみます。
つまりはVBAが保護されていれば不可視にできるということだと思うので
かなり期待します。

ご意見ありがとうございます。
【49351】xlsheetVeryHidden
お礼  ユニフ  - 07/6/1(金) 0:25 -
引用なし
パスワード
   ichinose さん、こんにちは

>>・このプロパティの表内のVisibleを2のxlsheetVeryHiddenに設定します。
>>これでExcelからは、ブックの保護の有無に関わらず、再表示が出来ません。
>>つまり、隠しシート名の公開が問題にならないと思うのですが、
>>私の意味の取り違いえでしょうか？

これも意に沿ったいい機能でした。そういえば以前本で読んだような気がしますが
すっかり忘れておりました。いずれ独自のシートを入れたい人たちが出てきて
ブックのパス公開をせまられてもこれならOKです。
ありがとうございました。
  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
5707 / 13645 ﾂﾘｰ ←次へ　｜　前へ→
ページ：  ┃  記事番号：
2610219
(SS)C-BOARD v3.8 is Free