Difference between Public 'Sub' and Function'

[muppetgeoff]

Hi All,

Please forgive me if this is a stupid question, I am new to VBA.

I was just wondering if someone could explain simply, the difference between a Public 'Function' and a Public 'Sub'.

Specifically, when the use of each is recommended, and how they differ from each other.

The only difference I can see, is how they handle the passing of results in and out of the process.

Subs seem to hand back a result, as long as the variable referenced is the same as the one that contains the result of the sub.

Functions seem to require you to hand back the result as a variable.

Am I way off on that observation?

The main reason for asking, is that I modified a 'Public Function' written by a user here, changed it to a 'Public Sub' and changed a lot of the methodology. As a 'Function' it didn't seem to work properly, but as a 'Sub' it did.

So I'm curious as to the actual difference.

Many thanks in advance,

Geoff

 

[Mile-O]

Simply put, a Sub performs an operation, a function returns a value.

 

[Mile-O]

Subs seem to hand back a result, as long as the variable referenced is the same as the one that contains the result of the sub.

Functions seem to require you to hand back the result as a variable.

Subs don't hand back any result - they just perform the lines of code between Public Sub and End Sub.

Functions return a value as the name of the function.

 

[muppetgeoff]

Hi Mile-o-Phile

Many thanks for your quick, short-but-sweet reply.

The code that I mentioned was your Limit Characters Typed In A Control function.

Your original version was a Function, and after following a (very simple) code example on support.microsoft.com, I changed it to a Sub.

Is there any benefit in using one over the other? or does it simply depend what you want to do?

I have a bundle of code to complete tonight, and I just want to be sure I code bits best suited as Functions and those best suited to Subs accordingly.

Many thanks again,

Geoff

 

[ChrisO]

I admit I’m being picky here but writing code is being picky.

Subs don't hand back any result - they just perform the lines of code between Public Sub and End Sub.

Depending on the definition of ‘hand back’, this answer could be incorrect.

If a Subroutine is passed arguments then, by default, they are passed by reference (ByRef).

Under this circumstance, any modification of these arguments is passed back to the caller (by default). This can lead to very subtle, unintentional, modifications to the passed arguments, and it can happen by default in VBA. (Commonly called, ‘Pass back by reference’.)

As such, Subroutines pass back changes to arguments, both in user written code and also System written code. System written code that passes ‘Cancel As Integer’ to a Subroutine sends back to the caller (The System) any modification of Cancel.

Far too much could be said about this to fully describe the situation in one posting.

Edit:

A demo of the subtle error.

One of this is from JasonM on another site, one of it is by me…

Option Explicit
Option Compare Text


Sub Test()
    Dim lngX As Long

    lngX = 1234
    
    ModifyX1 lngX
    MsgBox lngX            [color=green]' 2468[/color]
    
    Call ModifyX1(lngX)
    MsgBox lngX            [color=green]' 4936[/color]
    
    ModifyX1 ((lngX))
    MsgBox lngX            [color=green]' 4936[/color]

    MsgBox ModifyX2(lngX)  [color=green]' 0[/color]

    MsgBox ModifyX3(lngX)  [color=green]' 19744[/color]

End Sub


Public Sub ModifyX1(ByRef lngFred As Long)

    lngFred = lngFred * 2

End Sub


Public Function ModifyX2(ByRef lngFred As Long) As Long

    lngFred = lngFred * 2

End Function


Public Function ModifyX3(ByRef lngFred As Long) As Long

    ModifyX3 = lngFred * 2

End Function

The second instance of 4936 and the singular result of 19744 are both subtle errors.


Therefore this question could, or should, become a whole new thread and perhaps something for a FAQ inclusion.

Regards,
Chris.

 

[Pat Hartman]

Normally changing a Function to a Sub would fail. If you were able to do that with a function that someone wrote for you, it could be that the procedure had no reason to be a function to begin with.

A Function serves the purpose of a variable and is used in code as if it were a variable because it returns a single value whose type is determined by the function header :
Public Function YourFunc() As Integer
Public Function YourFunc() As String
Public Function YourFunc() As Date, etc.
If you don't specify the data type, your function will return a variant. In at least one place within the function you will see a line similar to:
YourFunc = some expression - this is what sets the function's return value and the reason that arbitrarily changing a function to a sub will fail. There is no rule that a function must contain a statement that sets its return value. If there is no such statement, the function would always return a value of null. It will still do what ever the code is intended to do but the statement -
SomeVariable = YourFunc() will set SomeVariable to null, assuming that SomeVariable is defined as a variant. If not you'll get a runtime error.

The code contained within a sub or function is just code. It could do almost anything. A function can be used any place a sub can be used but NOT vice versa.

Both subs and functions may take arguments. By default in VBA those arguments are passed by reference. What that means is that the argument is actually an address that points to a place in memory where a value is stored. As ChrisO pointed out, this is dangerous and can lead to accidents if you don't understand what is happening. But if you want your sub or function to return multiple values, it is a useful feature. So the only way a sub can "return" a value at all is by modifying its input parameters.

The other way to pass arguments is by value. In this case, VBA creates a copy of the variable and passes that to the sub or function so that if the sub or function changes its input arguments, the change will not replace the original source variable.

You can "execute" a sub by simply typing its name:
YourSub
As a long time COBOL programmer, I find this disconcerting so I always use the more formal:
Call YourSub
You can also use the Call syntax to execute a function:
Call YourFunc
although this is not commonly done.
The normal syntax for a function call is:
someVariable = YourFunc() - because you want to take the value returned by the function and put it somewhere or use it in some other expression.

 

[ChrisO]

G’day all.

Just to throw a little more code at this subject.

Functions only return one (1) value. This is True.

But when that one (1) returned value is a pointer then the Function may return almost anything.

No point in re-writing all the text here, there should be enough ‘green stuff’ to explain it in the attached A97 demo.

Hope that helps and doesn’t confuse.

Regards,
Chris.

 

[muppetgeoff]

Thank you so very much for your explanation Pat! The fog has lifted as they say

I may not have explained my understanding of the difference too well (I don't really speak the lingo ) but it was along the lines of how you described.

And thanks Chris for the code samples, I'll be looking them over (after my PC has finished batch formatting 1500 Icons for me!)

The long and short of it I guess is if I only need a single value returned (such as a True or False for a routine that checks a users password) then a Function would be the way to go. (In my 'lay-mans' terms, hand the Function the username and password, and let it work out if they are valid, and just let me know the outcome)

However, if I need to know more than one result from the procedure called (trying to think of example....erm... Query the database to find the most recent entry for a record matching criteria X, AND how many matches there were in total) I could do this with a Public Sub, handing the Sub the criteria X, and then declaring and setting variables within the Public sub, that I can evaluate when it returns to the calling sub.

That make any sense?

As I said, I am new to access and VB so please forgive the obviousness of these questions. I just like to know the 'nature of the beast'

One last related question:

I know public variables are constant. But are variables declared in a Public sub and/or Public Function are only active for the duration of that Procedure (and the procedure that called it).Or are Function variables only active for the duration of the function itself, and variables in a Public Sub are available to the calling procedure, clearing upon exit of that calling procedure.

:edit

Would I be right in seeing Public Functions as 'Seperate to the calling procedure' - i.e. They get called and may be handed some criteria to evaluate. They always return something to the calling procedure, but whatever they do with that criteria in run-time is kept within the function (variables used is an example)

Public Subs are more like 'extensions to the code of the calling procedure'. Whatever they do can affect the calling procedure directly (again, access to variables used in runtime is an example)

Blimey I hope that makes sense

Many thanks for everyones help.

Geoff

 

[Mile-O]

I know public variables are constant. But are variables declared in a Public sub and/or Public Function are only active for the duration of that Procedure (and the procedure that called it).Or are Function variables only active for the duration of the function itself, and variables in a Public Sub are available to the calling procedure, clearing upon exit of that calling procedure.

A variable can exist on one of three levels:

1. Global
2. Modular
3. Procedural

• Global: The variable has been declared as Public in a the Declarations section of a standalone module. They exist for the duration of the database.
• Modular: The variable has been declared in the Declarations section of any module. These are generally Private. They exist for the duration of the module.
• Procedural: The variable has been declared within a sub, function, or property. It exists for the duration of the procedure.

If, in a procedure, you want to retain the value of a variable then you can forget the Dim (or Private) statement and use the Static statement instead.

i.e.

Static x As Integer

When the procedure ends, the value of x is retained.

 

[muppetgeoff]

Now that WAS a rapid response, thanks Mile-O

So if I uderstand you, with an example scenario of Private Sub A calling Public Sub (or Function) B.

Upon completion, Public B returns the process back to Public Sub A.

When Called, Public B may have been handed values that it assigned to variables that it declares (in its openargs) and during its run-time, it may Dim and Set more variables for whatever reason there may be. Now all of those variables are 'lost' when Sub B completes, unless it declares them as Static. And a Static Variable will be 'available' until Sub A completes.

Is that right?

My misunderstanding is probably that I thought a Public Subs variables stayed set until the calling private sub completed, regardless of a 'static' statement. Functions would however need the Static function to preserve them.

Kind regards,

Geoff

P.S. In a few months I'll probably look back on the triviality of these questions with shame

 

[Pat Hartman]

Functions only return one (1) value. This is True.

But when that one (1) returned value is a pointer then the Function may return almost anything.

- functions can also return arrays but both the pointer and the array answers seemed to be over kill and more likely confusing to the poster.

 

[ChrisO]

Pat, again we agree and at the same time seem to disagree (I think.)

The words in your last posting are absolutely correct as far as I can see.
(over kill and more likely confusing to the poster)

But I think there is also another way to look at this problem.

Given a specific question from a singular poster, do we (You and I and everyone else) attempt to answer that solitary specific question and nothing else?

To me the answer is obviously…no.

I think our aim is to try and answer the specific question but also push the limits of education.

I do not believe we can assume that the poster is totally ignorant in programming in general. The questions may appear ignorant in VBA but we all were at one stage.

The poster may understand Structures in ‘C’ but not know how to implement them in VBA.

The poster may understand pointers in ‘C’ and not know what Me is.

Given the nature of this site, is it not appropriate to push the limit a little?

Regards,
Chris.