ExcelのセルからVBAの関数を呼び出すには？

私はVBAの初心者ですが、Excelのセルから呼び出せる関数を書こうとしています。閉じたワークブックを開き、セルの値を調べて返すことができます。

今のところ、次のようなマクロの書き方を知っています。

Sub OpenWorkbook()
    Dim path As String
    path = "C:\Users\UserName\Desktop\TestSample.xlsx"

    Dim currentWb As Workbook
    Set currentWb = ThisWorkbook

    currentWb.Sheets("Sheet1").Range("A1") = OpenWorkbookToPullData(path, "B2")
End Sub

Function OpenWorkbookToPullData(path, cell)

    Dim openWb As Workbook
    Set openWb = Workbooks.Open(path, , True)

    Dim openWs As Worksheet
    Set openWs = openWb.Sheets("Sheet1")

    OpenWorkbookToPullData = openWs.Range(cell)

    openWb.Close (False)

End Function

マクロのOpenWorkbook()は全く問題なく動作しますが、Excelのセルから直接OpenWorkbookToPullData(...)を呼び出そうとすると、うまくいきません。その文は

    Set openWb = Workbooks.Open(path, , True)

は何も返しません。

どなたか、これをExcelのセルから呼び出すことができる、動作するVBA関数にする方法をご存知ですか？

pnuts
編集された質問 24日 2月 2015 в 7:19
プログラミング
vba excel excel-vba
26日 10月 2013 в 12:54
15 ビュー

Here's the answer

**ステップは以下の通りです。

1.Visual Basic Editorを開きます。Excelでは、Windowsの場合はAlt+F11、Macの場合はFn+Option+F11を押します。

2.新しいモジュールを挿入する。メニューからInsert -> Module（スキップしないでください！）。

3.3. Public 関数を作成します。例

    Public Function findArea(ByVal width as Double, _
                             ByVal height as Double) As Double
        ' 面積を返す
        findArea = 幅 * 高さ
    関数の終了

4.次に，他の関数と同じように，この関数を任意のセルで使用します： =findArea(B12,C12).

FloatingRock
編集した答え 4日 5月 2019 в 5:49
解説 (3)

ここで問題となるのは、UDFはExcelの環境を変更することができず、呼び出したセルに値を返すことしかできないということです。

いくつかの選択肢があります。

1.与えられたサンプルでは、実際にはVBAは必要ありません。 次の式が使えます。 ='C:Users\UserName\Desktop\[TestSample.xlsx]Sheet1'!$B$2 となります。

2.かなり面倒な回避策を使います。この回答を見る

3.ExecuteExcel4Macro "または "OLEDB "を使用することができます。

Community
編集した答え 23日 5月 2017 в 12:02
解説 (3)

A Functionは機能しませんし、必要もありません。

Sub OpenWorkbook()
    Dim r1 As Range, r2 As Range, o As Workbook
    Set r1 = ThisWorkbook.Sheets("Sheet1").Range("A1")
    Set o = Workbooks.Open(Filename:="C:\TestFolder\ABC.xlsx")
    Set r2 = ActiveWorkbook.Sheets("Sheet1").Range("B2")
    [r1] = [r2]
    o.Close
End Sub
解説 (2)