私は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関数にする方法をご存知ですか?
**ステップは以下の通りです。
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)
.
ここで問題となるのは、UDF
はExcelの環境を変更することができず、呼び出したセルに値を返すことしかできないということです。
いくつかの選択肢があります。
1.与えられたサンプルでは、実際にはVBAは必要ありません。 次の式が使えます。
='C:Users\UserName\Desktop\[TestSample.xlsx]Sheet1'!$B$2
となります。
2.かなり面倒な回避策を使います。この回答を見る
3.ExecuteExcel4Macro "または "OLEDB "を使用することができます。
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