如何从 Excel 单元格调用 VBA 函数？

How to Call VBA Function from Excel Cells?

提问by user780069

I am a VBA newbie, and I am trying to write a function that I can call from Excel cells, that can open a workbook that's closed, look up a cell value, and return it.

我是 VBA 新手，我正在尝试编写一个可以从 Excel 单元格调用的函数，该函数可以打开关闭的工作簿，查找单元格值并返回它。

So far I know how to write a macro like this:

到目前为止，我知道如何编写这样的宏：

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

The macro OpenWorkbook() runs perfectly fine, but when I am trying to call OpenWorkbookToPullData(...) directly from an Excel cell, it doesn't work. The statement:

宏 OpenWorkbook() 运行得非常好，但是当我尝试直接从 Excel 单元格调用 OpenWorkbookToPullData(...) 时，它不起作用。该声明：

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

returns Nothing.

什么都不返回。

Does anyone know how to turn it into a working VBA function that can be called from Excel cell?

有谁知道如何将其转换为可以从 Excel 单元格调用的有效 VBA 函数？

回答by FloatingRock

Here's the answer

这是答案

Steps to follow:

要遵循的步骤：

1. Open the Visual Basic Editor. In Excel, hit Alt+F11if on Windows, Fn+Option+F11if on a Mac.

2. Insert a new module. From the menu: Insert -> Module(Don't skip this!).

3. Create a Publicfunction. Example:

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

4. Then use it in any cell like you would any other function: =findArea(B12,C12).

1. 打开 Visual Basic 编辑器。在 Excel 中，如果在 Windows 上点击Alt+ F11，如果在 Mac 上点击Fn+ Option+ F11。

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).

回答by chris neilsen

The issue you have encountered is that UDFs cannot modify the Excel environment, they can only return a value to the calling cell.

您遇到的问题是UDFs 无法修改 Excel 环境，它们只能向调用单元格返回一个值。

There are several alternatives

有几种选择

1. For the sample given you don't actually need VBA. This formula will work
   ='C:\Users\UserName\Desktop\[TestSample.xlsx]Sheet1'!$B$2

2. Use a rather messy work around: See this answer

3. You can use ExecuteExcel4Macroor OLEDB

1. 对于给定的示例，您实际上并不需要 VBA。这个公式会起作用
   ='C:\Users\UserName\Desktop\[TestSample.xlsx]Sheet1'!$B$2

2. 使用一个相当混乱的解决方法：见这个答案

3. 您可以使用ExecuteExcel4Macro或OLEDB

回答by Gary's Student

A Function will not work, nor is it necessary:

函数将不起作用，也没有必要：

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