从电子表格调用另一个工作簿中的 VBA 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26308950/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Call VBA function in another workbook from spreadsheet
提问by Joe Marfice
Here's the function definition:
下面是函数定义：
Public Function StockQuote(strSymbol As String) As Double
It's stored in a module in an already-loaded worksheet ("My Macros.xlsm", in my Startup folder).
它存储在已加载工作表（“My Macros.xlsm”，在我的启动文件夹中）的模块中。
I want to call it from another workbook, as a cell reference:
我想从另一个工作簿中调用它，作为单元格引用：
Workbook1.xlsm cell A1:
Workbook1.xlsm 单元格 A1：
=StockQuote("AAPL")
But all i get are NAME errors.
但我得到的只是 NAME 错误。
回答by anefeletos
=My Macros.xlsm!StockQuote("AAPL")
=My Macros.xlsm!StockQuote("AAPL")
='C:\SomeFolder\My Macros.xlsm'!StockQuote("AAPL")
='C:\SomeFolder\My Macros.xlsm'!StockQuote("AAPL")
Work either.
要么工作。
But in order to work you shoud open the My Macros.xlsm workbook after Workbook1.xlsm, from the excel window of Workbook1.xlsm
但是为了工作，你应该在 Workbook1.xlsm 之后打开 My Macros.xlsm 工作簿，从 Workbook1.xlsm 的 Excel 窗口
回答by Charles Williams
Try saving your XLSM workbook containing the function as an XLAM instead: when the XLAM is open other workbooks should be able to see your UDF.
尝试将包含该函数的 XLSM 工作簿另存为 XLAM：当 XLAM 打开时，其他工作簿应该能够看到您的 UDF。
回答by zoonosis
How about something like this.
这样的事情怎么样。
It should run when the worksheet is activated and put the return value of your function in cell A1.
它应该在工作表被激活时运行，并将函数的返回值放在单元格 A1 中。
Private Sub Worksheet_Activate()
Range("A1") = StockQuote("AAPL")
End Sub
You may need to structure it differently if the current worksheet can't see your function
如果当前工作表看不到您的函数，您可能需要对其进行不同的结构化
