- ベストアンサー
Excel VBA ユーザ関数 他ブックセル範囲の引渡し
Excel VBA の ユーザ関数に、そのユーザ関数を使用するブックとは別のブックのセル範囲を引数で渡したいのですが、可能でしょうか? =test_function('C:\Documents and Settings\hiropon\My Documents\[Book2.xls]Sheet1'!$A$1)みたいな感じで ・・・・ 関数の定義側で オブジェクト変数を用意すればよいのかと思いましたが、だめでした。 Function test_function(myvar As Object) As Vriant .・・・・・・・ End Function myval.worksheet.name でワークシート名 Sheet1 は 取得できますが パス名とブック名はどうしたらよいのでしょうか? 根本的に違う方法がある? どなたか方法をご教授願います。
- k_hiropn
- お礼率100% (4/4)
- その他MS Office製品
- 回答数4
- ありがとう数9
- みんなの回答 (4)
- 専門家の回答
質問者が選んだベストアンサー
大変興味深い試みですね。 アプローチに応じて,様々な設計が考えられると思います。 言い換えると一発ドンで答えに辿り着く性質の内容ではありませんので,何をしたいのかもう少しずつ詰めながら仕様とプログラムを形作っていくご質問ではないかと思います。 >別のブックのセル範囲を引数で渡したい まず出発点として,「まだ開かれていないブック(の配下のシート,セル)」はオブジェクトとして(言い換えるとインスタンスとして)存在がありません。なので,これをオブジェクト変数に格納しようにも,そもそも入れるモノがありません。 一方「既に横に開いているブック(のセル)」であれば,そこに現物(オブジェクト)としての他ブックとシートやセルは存在していますから,引数として渡す事も何ら支障有りません。 プロトタイプ: Public Function myTest1(byVal Target as Excel.Range) As Variant myTest1 = Target.Value End Function 使い方: Book2.xlsが横に開いている前提で, =myTest1([Book2.xls]Sheet1!$A$1) これで目的の他ブックのセルの値は勿論,そのセルのParentとしてのシート,更にそのParentとしての参照元ブックまで,プログラム内で取得できます。 このプロトタイプでは,関数側でTargetをExcel.Rangeと定義づけてあります。なのでこのままBook2を閉じると数式は自動で =myTest1('C:\test\[Book2.xls]Sheet1'!$A$1) に変化し,またリンクの編集を確認してみるとブックには正しくBook2.xlsへのリンク情報が登録されていることも確認できます。
その他の回答 (3)
- keithin
- ベストアンサー率66% (5278/7940)
さて,少し改良してみようかなと思ってみた所,思いがけず次のようになりました。 Public Function myTest2(ByVal Target As Variant) As Variant myTest2 = Target End Function 使い方: =myTest2([book3.xls]Sheet1!A1) =myTest2('C:\test\[Book3.xls]Sheet1'!$A$1) 色々試してみてください。
お礼
いろいろ試していただきありがとうございます .Value をつけないでも値を取得できるのですね。 私もいくつか試して見ます。 今回は解決しましたので、終了とさせていただきます。 ありがとうございました
- keithin
- ベストアンサー率66% (5278/7940)
「Excel.Range」は,深い意味は全くありません。通常は …(byVal Target As Range) As … などのようにします。 (エクセルの)Rangeオブジェクトとして変数Targetを宣言しますという構文ですね。
お礼
いじってみて理解できました。 Function test_function(myvar As Range) As Vriant .・・・・・・・ End Function Range型のobject変数 myvar で引き渡した範囲内(Rangeオブジェクト)で、位置を指定してセルを参照したい場合(これがやりたかったのですが)、 たとえば myvar.Cells(y, x).Value とすることで 他ブックへの参照であっても myvarの左上を Cells(1,1)として値を取得できるわけですね。 ほかには myvar.Worksheet.Cells(y, x).Value にて 他ブックへの参照であっても 参照したシートの"A1"を Cells(1,1)として値を取得とか。 ---------------------------------------------- いままでは適切でない面倒な方法をしていました。 SheetName = myvar.Worksheet.name Worksheet(SheetName).Cells(x, y) こんな感じで、いったんワークシートの名前を取得してから、その名前を使い、改めてワークシートオブジェクトを指定していました。 同じブックの場合はこれでいいのですが、 違うブックの場合、どうやってブック名を取得したものかで悩んでいたわけです。 (この方法でやるのであれば mimeu様のご回答が参考になるかなと・・・) 実際のところ、この方法は、ブック、シート、レンジのオブジェクト情報が入っており、そのままオブジェクトとして使える、せっかくのオブジェクト変数 myvar を適切に活用していない、不適切な方法であったようです。 解決できました。ありがとうございます。
- mimeu
- ベストアンサー率49% (39/79)
可能です。こんな感じになります。 Sub Test_Main() Debug.Print Test_Func("C:\Documents and Settings\MiMeu\デスクトップ\OkWave\0103090848.xls", "Sheet1", "A1") End Sub Private Function Test_Func(ブック名 As String, シート名 As String, 範囲名 As String) Dim ブック As Workbook Set ブック = Workbooks.Add(ブック名) Test_Func = ブック.Worksheets(シート名).Range(範囲名).Value ブック.Saved = True ブック.Close End Function
お礼
mimeu様 ありがとうございます ブック名, シート名などを指定する方法ですね。 初めはこれに近い方法で考えていて、方法が分らなくご質問したので、今後の参考にいたします。 最終的にはkeithin様の方法が簡単に今回の目的に合うので 今回はそちらを採用させていただきます。 ありがとうございました。
関連するQ&A
- Excel VBA別ブックのシートをコピーするには
Excel2010のVBAで別ブックのシートをコピーしてくる方法 Excelファイル(C:\test\BOOK2.xls)のシート名が TESTというシートを自分のExcelファイル(C:\doc\BOOK1.xls)に コピーするにはどのように記述すればよいのでしょうか。 ・コピー先:自分のExcelファイル(C:\doc\BOOK1.xls) VBAのコードがあるファイルです ・コピー元:C:\test\BOOK2.xlsのTESTシート なお、TESTシートを持つ同じ名前(BOOK2.xls)のファイルが 別フォルダにもあります Workbooks( )の引数にファイル名(BOOK2.xls)は指定できるのですが、 フルパス名(C:\test\BOOK2.xls)で指定できないので困っています。
- ベストアンサー
- Visual Basic
- エクセルVBA 数式の中に、変数で定義したシート名を入力するには
いつもお世話になります。 =SUMPRODUCT(('[BOOK1.xls]SHEET1!$D$20:$D$1000=$E$4)*('[BOOK1.xls]SHEET1!$D$20:$D$1000!$O$20:$O$235>=$B9)*('[[BOOK1.・・・ という長い数式を、VBAに書き込みたいのですが、関数が長すぎるせいか、書き込めません。 そこで、 Dim SH1 As Worksheet SET SH1 = Workbooks("BOOK1.xls").Worksheets("SHEET1") として、[BOOK1.xls]SHEET1!をSH1に省略したいのですが、どのように数式に組み込めば良いでしょうか。
- ベストアンサー
- オフィス系ソフト
- excelユーザー関数で
同じブック内の全てのシートの、引数で指定したセルの文字列をconcatenateするユーザー関数を作成しました。が引数でセルを指定する時に""をつけなければならず少々不便を感じています。どのように修正すれば""をつけなくてよいようになるでしょうか? VBA初心者です。よろしくお願いします。 Function allbookscont(ContCell As String) As String Dim strRet As String For a=2 to Worksheets.Count strRet = strRet & Worksheets(a).Range(ContCell).Value Next a allbookscont = strRet End Function
- 締切済み
- その他MS Office製品
- エクセルのユーザー定義関数で(VBA)
エクセルのVBAで、セルに「=SheetName」と書き込めば、その関数を書き込んだセルが属するシート名をセルに代入さるようなユーザー定義関数を作りたいと考えています。 で、標準モジュールに Public Function SheetName(test) SheetName = ????? End Function と、書き込んでふと止まりました。 ユーザー定義関数を書き込んだシートの名前ってどうやって取得すればいいのでしょうか?VBでいうSenderみたいなのってあるんでしょうか? どなたか?詳しい方教えて頂けないでしょうか? 宜しくお願いいたします。
- ベストアンサー
- オフィス系ソフト
- エクセル VBA? 関数?
book1(データ入力用)とbook2(送信用)で作業します。 必ずbook1→book2の順番で開きます。 book1には1ヵ月の日数分のsheetがあり(約30sheet)、作業中のsheetのA1の日付を、book2を開いたときにbook2のB4に自動的に表示される様にしたいです。 関数でも可能であればOKですが、できればVBAの文章をおしえてください。Private Sub Worksheet_Activate()で可能でしょうか??? よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- エクセルVBAでブックを開きたいのですが、
エクセルVBAでブックを開きたいのですが、 アクティブシートを別のブックに移動させたいのですが、 まず、目的のブックを開く → 移動させたいシート名を右クリック → 移動またはコピー を選択 → 移動先ブック を選択 というふうにしようと思っていますが、 Workbooks.Open Filename:= _ "C:\Documents and Settings\YUMIKO\My Documents\YYYY.xls" のようにファイル名を入れると開くことができました。 が、 Workbooks.Open Filename:= _ "C:\Documents and Settings\YUUKORON\My Documents\Range("F1").Value.xls" のように、アクティブシートの セルF1にファイル名を入れて、開きたいのですが、どうしても開くことができません。いろいろ調べて試しましたが、無理でした。 基本的にマクロで記録で作っている初心者です。 回答よろしくお願いします。
- ベストアンサー
- その他(ビジネス・キャリア)
- Excel - セルの値を外部シート名として認識させる
お世話になります。 例えばデスクトップに置いてあるブックのセルを、別のブックのセルに引っ張るとき。 ='C:\Documents and Settings\UserName\デスクトップ\[Book1.xls]Sheet1'!A1 という風になりますが。 例えばBook2のセルA1に【Book3】、A2に【Sheet5】とあってこれらが可変であるとき、セルA3に ='C:\Documents and Settings\UserName\デスクトップ\[Book3.xls]Sheet5'!A1 の値を引っ張ろうと思ったら、それは可能でしょうか。 【Book3】、【Sheet5】はそれぞれユーザが任意に入力すると、テーブルの値が変わる、という風にしたいわけですが。
- ベストアンサー
- オフィス系ソフト
- 【VBA】ユーザ定義関数が動かない
【VBA】ユーザ定義関数が動かない 下記のようなプログラムをVBEの標準モジュールに記述して、 ワークシートの適当なセルに「=tc("aa","bb")」と入力してEnterを押した所 セルの表示が「=tc("aa","bb")」となってしまい狙った効果が得られません。 本当は、「aabb」と表示させたいのですが、なぜうまくいかないのでしょうか。 ご教授願います。 ◆関数の仕様 引数1と引数2に入力された文字列を連結して返り値として返す。 ◆コード Function tc(str1 As Range, str2 As Range) As String Dim Str As String Str = str1.Value & str2.Valuett tt = Str End Function
- ベストアンサー
- オフィス系ソフト
- VBAの関数で引数渡しでエラー
関数呼び出しでObject型を渡したいのですが「引数の型が一致していません」との エラーになってしまいます。 以下のプログラムをどのように修正すればよいのでしょうか? Sub Test() Dim obj As Object Set obj = CreateObject("Scripting.Dictionary") : Sample(obj) End Sub Function Sample(obj As Object) End Function
- ベストアンサー
- その他(プログラミング・開発)
- EXCEL VBA ユーザー定義関数について。
EXCEL VBA ユーザー定義関数について。 例) FUNCTION Test(a as integer, b as integer,c as integer) as integer とした場合、に添付図のような =Test(数値1, 数値2, 数値3) のような説明は表示できないのでしょうか? ご存知の方よろしくお願いします。
- ベストアンサー
- その他MS Office製品
お礼
keithin様 早速試してみました。 まさにこれです!! でも仕組みがわかってないので、ちょっと気持ち悪いです。 Excel.Rangeは 初めて見ましたもので.. HELP で見たら Excel.Range は CreateObject 関数に関係があると見たのですが、それで正しいでしょうか? 考え方の方向性がわかれば、後は自分で調べられると思いますので。 ちなみに、やろうとしたことは Excelの組み込み関数はRangeをほかのブックからの参照で普通に渡せるので、ユーザ関数でも同じことをしたかったわけです。 同一フォーマットの複数のブックのデータを、集計用のブックに計算を施しながらまとめるのですが、そのとき自分で定義した関数に直接Rangeを参照させて計算させたほうが、作業の効率もいいし、間違いも減るので... やり始めたものの実際わからなくて、気づいたら2時間はまってました・・・・ どうもありがとうございました。