How do I call a VBA function from a cell?

[Scott Lyon]

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

 

[Bob Phillips]

Scott,

Don't put the code in the Sheet1 code module, create a general module (menu
Insert>Module) 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)