How can I return the results of a function to a cell in Excel?

Question

Suppose I have a function attached to one of my Excel sheets:

Public Function foo(bar As Integer) as integer
    foo = 42
End Function

How can I get the results of foo returned to a cell on my sheet? I've tried "=foo(10)", but all it gives me is "#NAME?"

I've also tried =[filename]!foo(10) and [sheetname]!foo(10) with no change.

Answer 1

include the file name like this

=PERSONAL.XLS!foo(10)

Answer 2

Put the function in a new, separate module (Insert->Module), then use =foo(10) within a cell formula to invoke it.

Answer 3

Try following the directions here to make sure you're doing everything correctly, specifically about where to put it. ( Insert->Module )

I can confirm that opening up the VBA editor, using Insert->Module, and the following code:

Function TimesTwo(Value As Integer)
    TimesTwo = Value * 2
End Function

and on a sheet putting "=TimesTwo(100)" into a cell gives me 200.

Answer 4

Where did you put the "foo" function? I don't know why, but whenever I've seen this, the solution is to record a dimple macro, and let Excel create a new module for that macro's code. Then, put your "foo" function in that module. Your code works when I follow this procedure, but if I put it in the code module attached to "ThisWorkbook," I get the #NAME result you report.