我是一个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编辑器。在Excel中,点击Alt+F11如果在Windows上,Fn+Option+F11 如果是Mac。
2.插入一个新的模块。从菜单上看:插入-> 模块(不要跳过这一步!)。
3.创建一个 "公共 "函数。例子:
公共函数findArea(ByVal width as Double, _
ByVal height as Double) As Double
' 返回面积
findArea = width * height
结束函数
4.然后像其他函数一样在任何单元格中使用它:=findArea(B12,C12)
。
你遇到的问题是,"UDF "不能修改Excel环境,它们只能向调用单元格返回一个值。
有几种替代方法
1.对于给出的例子,你实际上不需要VBA。 这个公式可以工作
='C:\Users\UserName\Desktop\[TestSample.xlsx]Sheet1'!$B$2
。
2.使用一个相当混乱的解决方法:见此答案
3.你可以使用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