Function Vs. Sub

  • Thread starter Thread starter mars
  • Start date Start date
M

mars

Hello,

As a novice, I would like to know if someone can tell me
what the guidelines/best programming practices are on when
one should use a "Function" versus a "Subroutine".

It appears to me that both structures are completely
interchangeable. Is there any speed, memory overhead, or
other performance issues?

Perhaps someone can offer a short discussion, or point me
to web site? Thanks for the help in advance.
 
Mars,

The only difference between a Function procedure and a Sub procedure is that
function procedure can return to its caller a value as the result. For
example,

Result = SomeFunction (X, Y, Z)

A Sub procedure cannot return a result. Beyond that, functions and subs are
interchangeable. Neither has a speed or memory advantage over the other.


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

No differences when it comes to performance as far as I know.

Functions perform "calculations" and return "results". You can use their non-macro power
in Excel cells or call them from Subs or other functions. Subs perform "actions", return
nothing, and call functions when needed.

However, you might want a Sub to go check on something and return a result, or simply
report its own success, for decisions. If so rewrite it to a function, like the function
F1 here:

Sub Test()
If F1 <> 0 Then
Call S2
Else
Call S3
End If
End Sub

Function F1() As Long
Range("C14").Select
On Error Resume Next
F1 = Val(ActiveCell.Formula)
End Function

Sub S2()
MsgBox "Values man"
End Sub

Sub S3()
MsgBox "Nothin"
End Sub
 
Functions cannot change their environment (alter cells other than the
one containing the formula, change cell format, etc)

If you need a value (or an array of values) back, then use a function.
If you want to create charts, modify worksheets, etc., then use a sub.

Jerry
 
Functions cannot change their environment (alter cells other than the

Just for complete accuracy, it should be said that this is true only if the
function is called from a worksheet cell. No procedure, either Function or
Sub, can change the environment if called from a worksheet cell. Functions
called from Sub procedures can certainly change their environment so long as
code execution was not initiated from a worksheet cell.


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

That's true only when called from cells:

Sub Test()
Dim L As Long
Dim M As Long
For L = 1 To 12
Cells(L, 1).Value = L
Next
Cells((Rnd() * 12 + 1), 1).Select
MsgBox "Funcion call:", , _
"Row " & ActiveCell.Row
M = CelVal
MsgBox "Was " & M & _
" -but I lost it to a function"
End Sub

Function CelVal() As Long
ActiveCell.EntireColumn.Font.Bold = _
Not (ActiveCell.Font.Bold)
CelVal = ActiveCell.Value
ActiveCell.EntireRow.Delete
End Function

Best wishes Harald
Followup to newsgroup only please.
 
There are a few scenarios where your procedure must be a sub:

• to allow a user to run the procedure as a macro i.e. Tools, Macros,
Macro: only public subs in standard modules without Option Private
Module will appear in the macro list;
• to assign a procedure to a control (Forms toolbox), shape,
hyperlink, etc;
• events provided by the application (Workbook_Open, Worksheet_Change,
etc) are always subs and can't be changed to functions.

My personal preference is to use functions for all but my top level
procedures, using a Boolean = True return value by default. The
calling procedure can choose to ignore a return value, effectively
treating it as a sub.

Also worth pointing out are the other sub procedure types available to
you: Property Get, and Property Let (Property Set for objects).
 
Back
Top