【Excel VBA】他のブックのセルの値を取得するユーザー定義関数の検討結果

ExcelのINDIRECT関数は、同じブック内にある複数のシートの値を抽出する場合には有効ですが、他のブックの値を抽出する場合は用途が限定されます。

具体的には、他のブックを参照している場合（これを外部参照と言うようです）、そのブックを事前に開いておく必要があります。参照先のブックを開いていないと、エラー値 #REF! が返される仕様。

support.microsoft.com

複数のブックのセルから数個ずつ、セルの値を抽出したいような場合はPowerQueryも設定が手間となるので、Excelの数式として利用できるユーザー定義関数で対応できないかを検討した備忘録。

なお、使用のExcelは、Microsoft® Excel® for Microsoft 365 MSOです。

結果、処理速度が非常に遅いユーザー定義関数しかできなかったので、Excelの数式としてではなく、VBAで処理するのがよさそう。

処理が遅い要因は、ユーザー定義関数では、数式の一つずつで「ファイルを開く、読み取り、閉じる」を繰り返すような方法しか不明だった。ユーザー定義関数でなければ高速化の余地はある。また、良い方法が探せなかっただけで、他に良い方法があるかも知れません。

内容：

実現したいこと
- 参考にした情報
調べた内容
参考

実現したいこと

Excelの数式にユーザー定義関数を追加し、①ファイル名（フルパス）、②シート名、③セル番地を指定すれば、当該のセルの値を抽出できるようにしたい。

要は、ファイルを開いていなくても外部参照できるINDIRECT関数のような関数。

参考にした情報

以下のサイトを参考にさせていただきました。

daitaideit.com

調べた内容

Workbooks.Openを使った方法

実現はできるが、一つの値取得に数秒かかるので、用途に適さなかった。

Function 他のブックの値取得WorkbooksOpen(フルパス As String, シート名 As String, セル番地 As String) As Variant
    Dim result As Variant
    Dim xlApp As Object
    Dim xlBook As Object
    Dim xlSheet As Object

    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Open(フルパス)
    Set xlSheet = xlBook.Sheets(シート名)

    result = xlSheet.Range(セル番地).Value
    
    xlBook.Close
    xlApp.Quit
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing

    他のブックの値取得 = result
End Function

ExecuteExcel4Macroを使った方法

実現はできるが、Workbooks.Openを使った方法と同様に、一つの値取得に数秒かかるので、用途に適さなかった。

また、詳細未確認だが、Excelの数式として利用できるユーザー定義関数とするには、CreateObject("Excel.Application")が必要で、Excel起動の時間を要する。

（Subからの呼び出しならCreateObject("Excel.Application")が不要だった）

Function 他のブックの値取得Excel4Macro(フルパス As String, シート名 As String, セル番地 As String) As Variant
    Dim xlApp As Object
    Set xlApp = CreateObject("Excel.Application")

    Dim result As Variant
    Dim 参照文字列 As String
    Dim ファイル位置 As Long
    Dim RCセル番地 As String

    ファイル位置 = InStrRev(フルパス, "\")
    ファイル名 = Mid(フルパス, ファイル位置 + 1)
    RCセル番地 = "R" & Range(セル番地).Row & "C" & Range(セル番地).Column

    参照文字列 = "'" & Left(フルパス, ファイル位置) & "[" & ファイル名 & "]" & シート名 & "'!" & RCセル番地
    result = xlApp.ExecuteExcel4Macro(参照文字列)
    
    xlApp.Quit
    Set xlApp = Nothing

    他のブックの値取得Excel4Macro = result
End Function

なお、ExecuteExcel4Macroの詳細は以下です。

learn.microsoft.com