CODEWITHSUNDEEP
excelvbareference
JoeM
JoeM

Reputation: 135

Call VBA function in another workbook from spreadsheet

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

I want to call it from another workbook, as a cell reference:

Workbook1.xlsm cell A1:

=StockQuote("AAPL")

But all i get are NAME errors.

Upvotes: 1

Views: 10045

Answers (3)

anefeletos
anefeletos

Reputation: 702

=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

Upvotes: 6

Charles Williams
Charles Williams

Reputation: 23505

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.

Upvotes: 2

zoonosis
zoonosis

Reputation: 799

How about something like this.

It should run when the worksheet is activated and put the return value of your function in cell 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

Upvotes: 0

Related Questions