Sub vs Function

[Rob van Gelder]

Something that I've always been curious about, but never found a real reason
behind.

Why do we use a Sub instead of using a Function?

Is there any reason other than a Sub returns nothing?

These both do exactly the same thing:

Function Test()
MsgBox "test"
End Function

Sub Test()
MsgBox "test"
End Sub

At the moment my opinion is that Sub is redundant and may as well never be
used.

Thoughts? Comments?

 

[Frank Kabel]

Hi Rob
one difference between sub and functions:
Function can not change the Excel environment. That is they can't
change formats, other cells, etc. Only subs can do this.

Also have a look at http://www.cpearson.com/excel/differen.htm

 

[Guest]

Sub has no return value whereas Function can return object or variable. You must decide to use Sub or Function according to what you want to do
Valid déclaration of function :
Function Test() AS boolea
'test a value, check something or do a calculatio
Test = True 'return your test or calculatio
End Function

 

[Charles Williams]

I think its a question of style.

personally I like to use functions when I want to return a single value,
otherwise subs.
I find that easier to understand when I look at code I wrote a few years
ago.

regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[Bob Phillips]

Rob,

Great question (which is usually an intro into a highly opinionated
discourse<vbg>).

As has been pointed out, Function (can) return a value, but it doesn't have
to. So in your example a call to Test works in the same manner, regardless
of whether it is a Sub or a Function.

Paul Lomax, in VB & VBA In A Nutshell (O'Reilly ISBN: 1-56592-358-8)
suggests that you should never use Subs for a custom procedure, always use
Functions, precisely because Functions can return a value. At a minimum this
could be a Boolean indicating successful or unsuccessful completion of that
procedure. I find myself drawn to this argument, as I am sure that you are
like me, and omit error handling in many cases (this code will never error
so why bother - FLW). By using a function you can return the procedure
status to the caller very simply. Clearly, it gets more difficult if you
have a function that needs to return some other value.

Paul has also tested the performance of a call to a sub or a function, and
has found no discernible difference. Of course, there is often an extra
statement in a function, namely that of setting the return value.

As I say, I am drawn to Paul's argument on a theoretical basis, but I do not
rigorously adhere to it, but that is laziness or sloppiness on my part, not
for any other good, pre-determined reason.

In summary, I concur with Paul's reasoning that using Function calls makes
good sense, but apply in my own style (typical programming I guess) and for
specific purpose rather than as a blanket procedure.

--

HTH

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

 

[Harald Staff]

Hi Rob

A sub is simply a function returning Void, nothing, as you know. So there
are no differences from a programming view.

In Excel, however, you'll find Subs in the Tools > Macro > Start menu, and
you also can assign them to buttons and objects. Not so with funtions, you
can't run them outside the VBE unless they are called from a Sub.

Functions can be used directly in spreadsheet cells -where they, as Frank
points out, loose their "macro power".

 

[Bob Phillips]

Frank,

This is confusing worksheet functions with VB/VBA functions. Functions can
change the attributes of the container application (such as worksheet cell
attributes) if they are not called from a worksheet function, just like a
sub.

--

HTH

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

 

[Chip Pearson]

Function can not change the Excel environment.

Just for clarity, it should be said that a function (or sub)
called from a worksheet cell cannot change the Excel environment.
Functions, per se, can change the Excel environment. It is only
when they are called directly or indirectly from a worksheet cell
that the prohibition on changes comes in to play.


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

 

[Frank Kabel]

Hi Bob
thanks for the clarification!

 

[Tom Ogilvy]

There is another "advantage"; you can view all your function names in the
function wizard if you don't make them private.

 

[Rob van Gelder]

Thanks to everyone for your reply.

Just to sum up, it appears there is no performance/ functional difference
VBA-wise, but there are advantages defining a Sub or Function if it's to be
integrated into Excel.

Very useful to know that.

Cheers!