Public Functions As Worksheet Available Functions

[Steve King]

I've created quite a few VBA functions which are invoked through the macros
menu but would like to be able to build a function which can be invoked in
the cell like a WorksheetFunction object. When I build the function and try
to embed it into a cell with the '=FunctionName()' syntax I get an error. Is
there a way to make my functions available?
--
Steve King

[Chip Pearson]

If you put the function in a regular code module (not a sheet
module and not the ThisWorkbook module), you should be able to
call them directly from worksheet cells. If the function resides
in another workbook, you need to include the workbook name in the
function call.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Steve King" <[email protected]> wrote in
message
news:[email protected]...
> I've created quite a few VBA functions which are invoked
> through the macros
> menu but would like to be able to build a function which can be
> invoked in
> the cell like a WorksheetFunction object. When I build the
> function and try
> to embed it into a cell with the '=FunctionName()' syntax I get
> an error. Is
> there a way to make my functions available?
> --
> Steve King

[Tom Ogilvy]

Make sure you place them in a General module (the type you get from
Insert=>Module)

Make sure they are not private.

Public Function FunctionName()
FunctionName = rnd()
End function

--
Regards,
Tom Ogilvy

"Steve King" <[email protected]> wrote in message
news:[email protected]...
> I've created quite a few VBA functions which are invoked through the
macros
> menu but would like to be able to build a function which can be invoked in
> the cell like a WorksheetFunction object. When I build the function and
try
> to embed it into a cell with the '=FunctionName()' syntax I get an error.
Is
> there a way to make my functions available?
> --
> Steve King

[Steve King]

Thanks Chip!

I suspected it would be an easy answer but sometimes those are the most
difficult to find in the documentation. I had a module for the add-in but
obviously was not addressing it correctly.



"Chip Pearson" wrote:

> If you put the function in a regular code module (not a sheet
> module and not the ThisWorkbook module), you should be able to
> call them directly from worksheet cells. If the function resides
> in another workbook, you need to include the workbook name in the
> function call.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> "Steve King" <[email protected]> wrote in
> message
> news:[email protected]...
> > I've created quite a few VBA functions which are invoked
> > through the macros
> > menu but would like to be able to build a function which can be
> > invoked in
> > the cell like a WorksheetFunction object. When I build the
> > function and try
> > to embed it into a cell with the '=FunctionName()' syntax I get
> > an error. Is
> > there a way to make my functions available?
> > --
> > Steve King
>
>
>