Public Sub vs. Public Function

Question

I have been reading up on Public Function vs. Public Sub, since I am self taught.

I, knowing little, put everything I want to share in a Module as a Public Function. For example, if its as simple as 

Public Function OpenCal()

On error goto Err_Process

Dim MyForm as string

MyForm = "Cal"

docmd.openform MyForm

Exit_Process:

    Exit Function

Err_Process:

    MsgBox Err.Description, , "Error " & Err.Number

    Resume Exit_Process

End Function

I didn't make it a sub in a form, because I need to use this code from several different forms or toolbars.  I assume putting it in a module as a Public Function would be a good way to "normalize" my code, or keep me from typing the same code in multiple forms.  The OnClick command of all the related command buttons that access this code is =OpenCal().

Then I read up recently. It seems it only has to be a FUNCTION if it is returning a value being used somewhere else.  These simple procedures are not doing that...so I tried replacing all these types of procedures with PUBLIC SUB instead of PUBLIC FUNCTION. 

Alas, when clicking the related command buttons, I get "function not found" error.

What am I not understanding?

Answer 1

Thank you...Since some of that is greek to me...Does that mean, since I have changed Public Function OpenCal() to Public Sub OpenCal() I have to:

1.  delete the OnClick command of the command_OpenTheCalendarForm that is currently =OpenCal()
2. Create an [event procedure] for the command_OpenTheCalendarForm that reads Call OpenCal()

?

Functions can be evaluated by the expression service, so they can be called directly from an event property, as in your example, where you had the OnClick property of a command button set to:

    =OpenCal()

That doesn't work with a Sub.  I find that quite convenient, so if I have a procedure that I want to be able to call from mutiple control events, I will often make it a Function even if it doesn't need to return a value.  There is no harm in that.  Because it's a function, technically it returns a Variant value of Null or Empty (I don't recall which) if you don't assign any return value, but you can always ignore the return value of a function anyway.

Answer 2

You read correctly, the main difference between a Function and a Sub is that the Function returns a value. But that leads to the second difference, how you call the function.

If you called the function using a assignment, i.e.

x=Functionname()

If you change the function to a sub, this will no longer work, You also have to change the line of code to:

CALL subname()

You can call a function using the CALL command if it doesn't return a value, but you can't assign the results of a Sub to a variable since there is no returned value.

Answer 3

Thank you...Since some of that is greek to me...Does that mean, since I have changed Public Function OpenCal() to Public Sub OpenCal() I have to:

1.  delete the OnClick command of the command_OpenTheCalendarForm that is currently =OpenCal()
2. Create an [event procedure] for the command_OpenTheCalendarForm that reads Call OpenCal()

?

Answer 4

Just change the command to

Call OpenCal()

But why bother? Based on what's in OpenCal() it would be easier to just put

DoCmd.OpenForm "Cal"

I really don't see the value of a sub here.

The value of using a function or sub is when you make it more generic. Pass a variable to the sub/function so it can be used in different scenarios.

Answer 5

Just change the command to

Call OpenCal()

But why bother? Based on what's in OpenCal() it would be easier to just put

DoCmd.OpenForm "Cal"

I really don't see the value of a sub here.

The value of using a function or sub is when you make it more generic. Pass a variable to the sub/function so it can be used in different scenarios.

I was just giving the simplest of scenarios...I was really looking for a conceptual answer. Dirk gave me exactly what I was looking for, in that it does no harm to call these type of things functions even if they are returning no value.  This way I can continue to use =FunctionName() in the OnClick property of any command button that needs to use the procedure in question.  Thank you both very much.