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

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 2

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