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?


--
Rob van Gelder - http://www.vangelder.co.nz/excel

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

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

"Rob van Gelder" wrote in message
...
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?


--
Rob van Gelder - http://www.vangelder.co.nz/excel

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


--
Regards
Frank Kabel
Frankfurt, Germany


Rob van Gelder wrote:
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,

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)

"Frank Kabel" wrote in message
...
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


--
Regards
Frank Kabel
Frankfurt, Germany


Rob van Gelder wrote:
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?

Hi Bob
thanks for the clarification!

--
Regards
Frank Kabel
Frankfurt, Germany


Bob Phillips wrote:
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.


"Frank Kabel" wrote in message
...
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


--
Regards
Frank Kabel
Frankfurt, Germany


Rob van Gelder wrote:
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?

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" wrote in message
...
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


--
Regards
Frank Kabel
Frankfurt, Germany


Rob van Gelder wrote:
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?

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)

"Rob van Gelder" wrote in message
...
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?


--
Rob van Gelder - http://www.vangelder.co.nz/excel

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".

--
HTH. Best wishes Harald
Followup to newsgroup only please

"Rob van Gelder" skrev i melding
...
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?


--
Rob van Gelder - http://www.vangelder.co.nz/excel

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

--
Regards,
Tom Ogilvy


"Harald Staff" wrote in message
...
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".

--
HTH. Best wishes Harald
Followup to newsgroup only please

"Rob van Gelder" skrev i melding
...
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?


--
Rob van Gelder - http://www.vangelder.co.nz/excel

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!

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Rob van Gelder" wrote in message
...
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?


--
Rob van Gelder - http://www.vangelder.co.nz/excel