Я новичок в 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() работает отлично, но когда я пытаюсь вызвать OpenWorkbookToPullData(...) непосредственно из ячейки Excel, это не работает. Утверждение:
Set openWb = Workbooks.Open(path, , True)
возвращает Ничего.
Кто-нибудь знает, как превратить это в рабочую функцию VBA, которую можно вызвать из ячейки Excel?
Следующие шаги:
Откройте редактор Visual Basic. В Excel нажмите Alt+F11, если на Windows, Fn+Option+F11, если на Mac.
Вставьте новый модуль. В меню: Insert -> Module (Не пропускайте этот пункт!).
Создайте функцию Public
. Пример:
Public Function findArea(ByVal width as Double, _
ByVal height as Double) As Double
' Возвращает площадь
findArea = width * height
Конец функции
Затем используйте ее в любой ячейке, как любую другую функцию: =findArea(B12,C12)
.
Проблема, с которой вы столкнулись, заключается в том, что UDF
не могут изменять среду Excel, они могут только возвращать значение в вызывающую ячейку.
Существует несколько альтернатив
Для приведенного примера вам не нужен VBA. Эта формула будет работать
='C:\Users\UserName\Desktop\[TestSample.xlsx]Sheet1'!$B$2
Используйте довольно сложное решение: См. этот ответ
Вы можете использовать ExecuteExcel4Macro
или OLEDB
.
Функция не работает, да и не нужна:
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