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 は 取得できますが
パス名とブック名はどうしたらよいのでしょうか？

根本的に違う方法がある？

どなたか方法をご教授願います。

回答No.1

keithin · ベストアンサー

大変興味深い試みですね。
アプローチに応じて，様々な設計が考えられると思います。
言い換えると一発ドンで答えに辿り着く性質の内容ではありませんので，何をしたいのかもう少しずつ詰めながら仕様とプログラムを形作っていくご質問ではないかと思います。

＞別のブックのセル範囲を引数で渡したい

まず出発点として，「まだ開かれていないブック(の配下のシート，セル)」はオブジェクトとして(言い換えるとインスタンスとして)存在がありません。なので，これをオブジェクト変数に格納しようにも，そもそも入れるモノがありません。

一方「既に横に開いているブック(のセル)」であれば，そこに現物(オブジェクト)としての他ブックとシートやセルは存在していますから，引数として渡す事も何ら支障有りません。

プロトタイプ：
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へのリンク情報が登録されていることも確認できます。

お礼

keithin様
早速試してみました。　まさにこれです!!

でも仕組みがわかってないので、ちょっと気持ち悪いです。
Excel．Rangeは 初めて見ましたもので．．

HELP で見たら Excel．Range は　CreateObject 関数に関係があると見たのですが、それで正しいでしょうか?
考え方の方向性がわかれば、後は自分で調べられると思いますので。

ちなみに、やろうとしたことは
Excelの組み込み関数はRangeをほかのブックからの参照で普通に渡せるので、ユーザ関数でも同じことをしたかったわけです。

同一フォーマットの複数のブックのデータを、集計用のブックに計算を施しながらまとめるのですが、そのとき自分で定義した関数に直接Rangeを参照させて計算させたほうが、作業の効率もいいし、間違いも減るので．．．

やり始めたものの実際わからなくて、気づいたら２時間はまってました・・・・
どうもありがとうございました。

回答No.4

keithin

さて，少し改良してみようかなと思ってみた所，思いがけず次のようになりました。

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 をつけないでも値を取得できるのですね。

私もいくつか試して見ます。

今回は解決しましたので、終了とさせていただきます。
ありがとうございました

回答No.3

keithin

「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 を適切に活用していない、不適切な方法であったようです。

解決できました。ありがとうございます。

回答No.2

mimeu

可能です。こんな感じになります。

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様の方法が簡単に今回の目的に合うので
今回はそちらを採用させていただきます。

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