Running a Sub from a cell expression in Excel

[David K.]

I am new to VBA. I wrote a simple Sub in VBA (say MySub). When I try
to run it from a cell expression in Excel (=MySub), I get the
following message: "That name is not valid".
Any help will be appreciated.

David

 

[Steve Rindsberg]

I am new to VBA. I wrote a simple Sub in VBA (say MySub). When I try
to run it from a cell expression in Excel (=MySub), I get the
following message: "That name is not valid".
Any help will be appreciated.

I think you need a function rather than a sub (sub doesn't return a value, a
function does)

Example: this in a cell:

=GimmeFive()

Invokes this:

Function GimmeFive() As Integer
GimmeFive = 5
End Function

I'm a PowerPointPusher. There's probably lots more to this I haven't a clue
about, but this might be enough to get you going.

 

[David K.]

I think you need a function rather than a sub (sub doesn't return a value,a
function does)

Example:  this in a cell:

=GimmeFive()

Invokes this:

Function GimmeFive() As Integer
    GimmeFive = 5
End Function

I'm a PowerPointPusher.  There's probably lots more to this I haven'ta clue
about, but this might be enough to get you going.

--
Steve Rindsberg, PPT MVP
PPT FAQ:  www.pptfaq.com
PPTools:  www.pptools.com
================================================

Steve,

Thank you for your help.
Yes, I know that a function returns a value. Yet, Sub is what I need,
since it performs some procedure. for example: Say my Sub displays
some message.
My question still stands; How do I run a Sub from within an Excel
cell.

David

 

[Steve Rindsberg]

Steve,

Thank you for your help.
Yes, I know that a function returns a value. Yet, Sub is what I need,
since it performs some procedure. for example: Say my Sub displays
some message.
My question still stands; How do I run a Sub from within an Excel
cell.

I suspect you can't. I'm probably wrong about this, but I'm assuming Excel wants
the called code to return a value to plug into the cell.

If your cell references a function, the function could call a sub, though, no?

The function (and hence the sub) would be called whenever the sheet recalculates,
I'd guess.

 

[Andy Pope]

As Steve as said you need to use a function.

Your sub does not have to return a value, although, by default, zero will be
displayed in the cell using your function.

Also note a function called from the worksheet can only write information to
the calling cell.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

I think you need a function rather than a sub (sub doesn't return a value,
a
function does)

Example: this in a cell:

=GimmeFive()

Invokes this:

Function GimmeFive() As Integer
GimmeFive = 5
End Function

I'm a PowerPointPusher. There's probably lots more to this I haven't a
clue
about, but this might be enough to get you going.

--
Steve Rindsberg, PPT MVP
PPT FAQ: www.pptfaq.com
PPTools: www.pptools.com
================================================

Steve,

Thank you for your help.
Yes, I know that a function returns a value. Yet, Sub is what I need,
since it performs some procedure. for example: Say my Sub displays
some message.
My question still stands; How do I run a Sub from within an Excel
cell.

David

 

[Mike Croom]

If you want to have cell value calculated then you do indeed need a function.
If you want to be able to call a sub under user control then you need to
insert a control like a pushbutton. So the user decides when it runs (as
opposed to a function which is called very time the cell is recalculated)