如何从Excel单元格调用VBA函数？ [英] How to Call VBA Function from Excel Cells?

问题描述

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

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

  Sub OpenWorkbook（）
 Dim path As String 
 path =C：\Users\UserName\Desktop\TestSample.xlsx
 
 Dim currentWb As Workbook 
设置currentWb = ThisWorkbook 
 
 
 currentWb.Sheets（Sheet1）。Range（A1）= OpenWorkbookToPullData（路径，B2）
 End Sub 
 
 
函数OpenWorkbookToPullData（路径，单元格）
 
 Dim openWb As Workbook 
设置openWb = Workbooks.Open（path，，True）
 
 Dim openWs As工作表
设置openWs = openWb.Sheets（Sheet1）
 
 OpenWorkbookToPullData = openWs.Range（cell）
 
 openWb.Close（False）
 
结束函数
  

宏OpenWorkbook（）运行perf非常好，但是当我试图从Excel单元格直接调用OpenWorkbookToPullData（...）时，它不起作用。声明：

 设置openWb = Workbooks.Open（path，，True）
  

返回没有。有没有人知道如何把它变成一个可以从Excel单元格调用的工作的VBA函数？

解决方案

这是答案

要执行的步骤：

1. 打开Visual Basic编辑器。在Excel中，如果在Windows上选择 + F11 >如果在Mac上。




2. 插入一个新模块。从菜单中选择插入 - >模块。




3. 创建一个 public 函数。示例：

    公共功能findArea（ByVal width as Double，_ 
    ByVal height as Double）As Double 
   '返回区域
    findArea = width * height 
   结束函数
     




4. 然后在任何单元格中使用它，就像任何其他函数一样： = findArea（B12，C12）。






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.

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:

    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?

解决方案

Here's the answer

Steps to follow:

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

2. Insert a new module. From the menu: Insert -> Module.

3. Create a Public function. 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).

这篇关于如何从Excel单元格调用VBA函数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！