How do I call a VBA function from a cell?
 

This seems like it should be an easy thing to do. But it's escaping me.

Basically, I need to set up a formula for a given cell that will keep that
cell populated with the return-value of the given function.


To over-simplify it, here's the simplest example I can find.

I just created a new workbook, with a worksheet called Sheet1

In the VBA code for Sheet1, I've got the following:

Public Function ReturnString() As String
ReturnString = "Hello"
End Function

I want to be able to put something in the formula for one of the cells that
will call the function. For example, the contents of the cell might be
something like:

=Sheet1.ReturnString()

or

=ReturnString()


Unfortunately, neither seems to work. So how do I call a VBA function from a
cell formula?


Thanks!
-Scott

How do I call a VBA function from a cell?
 

Scott,

Don't put the code in the Sheet1 code module, create a general module (menu
InsertModule) and put it there. In the cell, you just call with

=ReturnString()

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Scott Lyon" <scott.lyonNOSPAM_at__NOSPAMrapistan.comNOSPAM wrote in
message ...
This seems like it should be an easy thing to do. But it's escaping me.

Basically, I need to set up a formula for a given cell that will keep that
cell populated with the return-value of the given function.


To over-simplify it, here's the simplest example I can find.

I just created a new workbook, with a worksheet called Sheet1

In the VBA code for Sheet1, I've got the following:

Public Function ReturnString() As String
ReturnString = "Hello"
End Function

I want to be able to put something in the formula for one of the cells
that
will call the function. For example, the contents of the cell might be
something like:

=Sheet1.ReturnString()

or

=ReturnString()


Unfortunately, neither seems to work. So how do I call a VBA function from
a
cell formula?


Thanks!
-Scott