Как вызвать функцию VBA из ячеек Excel?

Я новичок в 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?

Ответы

Ответ 1

Вот ответ

Шаги, чтобы следовать:

  1. Откройте редактор Visual Basic. В Excel нажмите Alt + F11, если в Windows, Fn + Option + F11, если на Mac.

  2. Вставьте новый модуль. Из меню: Вставить → Модуль (не пропустите это!).

  3. Создать Public функцию. Пример:

    Public Function findArea(ByVal width as Double, _
                         ByVal height as Double) As Double
    ' Return the area
    findArea = width * height
End Function

  4. Затем используйте его в любой ячейке, как и в любой другой функции: =findArea(B12,C12).

Ответ 2

Проблема, с которой вы столкнулись, заключается в том, что UDF не может изменить среду Excel, они могут возвращать значение только вызывающей ячейке.

Существует несколько альтернатив

  • Для выбранного образца вам действительно не нужен VBA. Эта формула будет работать   ='C:\Users\UserName\Desktop\[TestSample.xlsx]Sheet1'!$B$2

  • Используйте довольно грязную работу: См. этот ответ

  • Вы можете использовать ExecuteExcel4Macro или OLEDB

Ответ 3

Функция не работает и не нужна:

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