ユーザー定義関数で辞書のような使用法

質問

Excel2000ユーザー定義関数で、
参照された文字列を、任意のフォルダ内にあるエクセルファイルから、
Vlookup関数のような形で引っ張ってくることは出来るでしょうか？
どうしても、ファイルパスを指定出来ません。

ｅx)

Function kanjiname(セル)
Application.Volatile
Dim myRange As Range
Set myRange = Workbooks("C:\辞書ファイル.xls").Worksheets("Sheet1").Range("C1:C9")
kanjiname = Application.VLookup(セル, myRange, 5, False)

という感じにしたいのです。
開いていないファイルをＯｐｅｎしても無理ですし、
どうすればいいのでしょうか。

End Function

回答No.3

KenKen_SP · ベストアンサー

=VLOOKUP(RC[-1],'C:\[辞書ファイル.xls]Sheet1'!C1:C9,5,FALSE)

#1の方もコメントされてますが、この計算式は正しい値を返しません。
というツッコミは横においておきます。

＞アドインの中にそのようなデータベースを蓄積させる方法は無いものでしょうか。。

この方法は、#1でも「さらっ」と書いてますが、ほぼ同じことを延べた手法ですよ。

VLOOKUP関数でデータを検索しているブックを便宜上A.xlsとしましょう。

1.　A.xls に新規シートを追加し、シート名を DataSheet とします
2.　C:\辞書ファイル.xls を開きます
3.　C:\辞書ファイル.xls の Sheet1 の C1:C9 をコピーします
4.　A.xls の 1で追加したシート DataSheet の C1セルを選択
　　[形式を指定して貼付け]から[リンク貼付け]をクリックします
　　--＞C:\辞書ファイル.xls の内容が表示されます。

これで、計算式は、

=VLOOKUP(RC[-1],DataSheet!C1:C9,...

と同一ブックの参照で計算式が書けますね。VLOOK関数内で他ファイルを指定するのは（これができたかどうか確かめてませんが）、関数が再計算のたび閉じたブックのデータを取得しに行くわけですから、時間がかかって当然です。

そこで、作業シートにリンクを張ってデータを取り込む。これは、ブックを開いたときに「更新しますか？」と聞かれて、その後は参照しません。つまり、始めに一気にデータを取り込こんでおくわけです。

作業シートに C:\辞書ファイル.xls のデータをリンクさせておいて、VLOOKUP関数で参照するのは 作業シート。です。

とまぁ、、ここまで言ってなんですが、、

C:\辞書ファイル.xls のシートを A.xls にコピーするのはダメなんですか？

回答No.2

KenKen_SP

＞開いていないエクセルファイルから...

ブックを開かず開かずデータを取得する方法はバイナリ解析、またはADOやDAOでデータベース接続するしかありません。これは、難易度が高いです。

ブックを開いていないように見せかけてデータを取得する方法は、Application.ScreenUpdating で画面更新を停止して、ブックを開き、データ取得後、ブックを閉じてしまえばOKです。または、EXCEL4.0マクロを使用します。

ここで、今回ご希望の処理について考えて見ます。

結論から言えば、ユーザー定義関数で処理するのはお勧めできません。

上記の方法でデータさえ拾えれば、ご希望通りの関数は作成可能は可能ですが、関数が再計算される度に、“ブックのOPEN/CLOSE”や“データ問合せ”を繰り返すわけですから、アっという間にフリーズするかも知れないです。さらに、Application.Volatile でやるなら、なおさらです。

今回は、作業シートに作り、辞書ファイル.xlsからのリンクを張って、そこをVLOOKUPで検索すれば良いかと思います。また、作業シートは非表示にしておけば良いと思います。

最後に一応、“ブックを開いていないように見せかけて”データを取得する関数のサンプルコードをアップしておきます。

'【WorkBookを見かけ上開かず指定シート+指定セルのデータを配列で返す】
Function GetData( _
　　strBookPath As String, _
　　strSheetNam As String, _
　　strCellAddr As String)

　　Dim Wb As Workbook
　　Dim Sh As Worksheet
　　Dim Buf

　　On Error GoTo ErrorHandler
　　Application.ScreenUpdating = False
　　Set Wb = Workbooks.Open(strBookPath, , True)
　　Set Sh = Wb.Sheets(strSheetNam)
　　GetData = Sh.Range(strCellAddr).Value

ExitHandler:
　　Wb.Close
　　Set Sh = Nothing
　　Set Wb = Nothing
　　Exit Function
ErrorHandler:
　　GetData = False
　　Resume ExitHandler
End Function

【使い方】

配列変数=GetData(ブックのフルパス,シート名,セルアドレス)

Sub Sample()

　　Dim myData As Variant

　　myData = GetData("C:\TEST.xls", "Sheet1", "A1:E4")
　　If IsArray(myData) = False Then
　　　　MsgBox "データ取得に失敗", vbCritical
　　　　Exit Sub
　　Else
　　　　ActiveSheet.Range("A1") _
　　　　.Resize(UBound(myData), UBound(myData, 2)) = myData
　　End If

End Sub

お礼

なるほど。
いろいろ調べたのですが、あまり的を得たtextが無かったもので、、、そういう時は、できないという事ですね。
現在は、IMEでnameを変換すると、「=VLOOKUP(RC[-1],'C:\[辞書ファイル.xls]Sheet1'!C1:C9,5,FALSE)」
と変換されるように設定し、使用しているんですが、時間がかかって…
結局同じように時間がかかるということなんですね。
アドインの中にそのようなデータベースを蓄積させる方法は無いものでしょうか。。

回答No.1

maruru01

こんにちは。maruru01です。

根本的な解決にはならないかも知れないし、単なる記載ミスかも知れませんが、1点だけ。
myRangeの範囲が「C1:C9」ということは、VLookup関数の第3引数に「5」は指定出来ないのでは？
「C1:G9」のように最低5列は必要かと思いますが。