CODEWITHSUNDEEP
vbaexcel
BIBD
BIBD

Reputation: 15414

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

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.

Upvotes: 3

Views: 3107

Answers (4)

Stephen
Stephen

Reputation: 4216

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.

Upvotes: 5

anorm
anorm

Reputation: 2263

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

Upvotes: 3

JeffK
JeffK

Reputation: 3039

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.

Upvotes: 1

chilltemp
chilltemp

Reputation: 8960

include the file name like this

=PERSONAL.XLS!foo(10)

Upvotes: -1

Related Questions