Function versus Procedure

[lmcc007]

I am a little confused!



On my old forms my command buttons were created using the wizard, which
created macros. Since I am trying to learn VBA, I want to create code for
them. Therefore, I saved the macros and then converted them to VBA. There
is some code on there I do not understand—for instance, “With
CodeContextObject.†Below is an example of one of the codes for saving a
record:



Option Compare Database

Option Explicit



'------------------------------------------------------------

' SaveAddress

'

'------------------------------------------------------------

Function SaveAddress()

On Error GoTo SaveAddress_Err



With CodeContextObject

On Error Resume Next

DoCmd.RunCommand acCmdSaveRecord

If (.MacroError <> 0) Then

Beep

MsgBox .MacroError.Description, vbOKOnly, ""

End If

End With





SaveAddress_Exit:

Exit Function



SaveAddress_Err:

MsgBox Error$

Resume SaveAddress_Exit



End Function



Since I would like to know what is going on I decided to create a code that
I can use over and over. Should I use a Procedure instead of a Function?
This is the code I wrote for my save record command button:



Private Sub cmdSaveAddress_Click()



SaveRecord



End Sub





Also, I created this first in a module called modUtility:



Public Function SaveRecord()

On Error GoTo HandleError

DoCmd.RunCommand (acCmdSaveRecord)

ExitHere:

Exit Function



HandleError:

MsgBox Err.Description

Resume ExitHere

End Function



Am I doing it correctly and on the right path?



Thanks!

 

[Allen Browne]

Okay: big question.

Most times, you will want to do one, specific thing in an event (such as the
click of a button.) To put VBA code behind that specific event, set the
property (on the Event tab of the Properties sheet) to:
[Event Procedure]
Then click the Build button (...) beside the property.
Access will open the code window, and show the module for the particular
form you're in, and it will automatically enter the declaration into that
module, e.g.:
Private Sub Command1_Click()
End Sub
You type your code in between those 2 lines.
That code is only available for that event in that form.
So that's the best way to create something that's a one-off.

If you have something that you need to do in many places, it makes sense to
create it in a stand-alone module instead of the module of a particular
form. In the code window, choose Module on the Insert menu. You get a fresh
module (named Module1 by default.) Typically Access automatically enters
Option Compare Database at the top. Below that you enter your function,
e.g.:
Public Function MyFunc()
MsgBox "You called MyFunc()."
End Function
You can then call this function anywhere in your application.
For example, you could set the On Click property of a command button to:
=MyFunc()
Or, you could leave the property as [Event Procedure], and then call it from
your code like this:
Private Sub Command1_Click()
Call MyFunc()
End Sub

That's probably in line with what you figured out: use an event procedure
normally; use a function in a stand-alone module when you need to reuse it
in different places.

Just for clarity of definition, anything in VBA is called a procedure.
Access has 2 kinds of procedures:
- a Function is a procedure that can return a value;
- a Sub (sub-routine) is procedure that cannot return a value.
You can use a function name in a property (like the =MyFunc() example
above), but you can't use a Sub that way. Therefore I always create
functions (never subs.) But Access always uses Subs for the event
procedures.

HTH

 

[lmcc007]

Thanks a lot! Your explaining helped a lot. Sometime you need to just talk
it out with someone. So, I am understanding that a Function returns a value
or answer a question.

Okay: big question.

Most times, you will want to do one, specific thing in an event (such as the
click of a button.) To put VBA code behind that specific event, set the
property (on the Event tab of the Properties sheet) to:
[Event Procedure]
Then click the Build button (...) beside the property.
Access will open the code window, and show the module for the particular
form you're in, and it will automatically enter the declaration into that
module, e.g.:
Private Sub Command1_Click()
End Sub
You type your code in between those 2 lines.
That code is only available for that event in that form.
So that's the best way to create something that's a one-off.

If you have something that you need to do in many places, it makes sense to
create it in a stand-alone module instead of the module of a particular
form. In the code window, choose Module on the Insert menu. You get a fresh
module (named Module1 by default.) Typically Access automatically enters
Option Compare Database at the top. Below that you enter your function,
e.g.:
Public Function MyFunc()
MsgBox "You called MyFunc()."
End Function
You can then call this function anywhere in your application.
For example, you could set the On Click property of a command button to:
=MyFunc()
Or, you could leave the property as [Event Procedure], and then call it from
your code like this:
Private Sub Command1_Click()
Call MyFunc()
End Sub

That's probably in line with what you figured out: use an event procedure
normally; use a function in a stand-alone module when you need to reuse it
in different places.

Just for clarity of definition, anything in VBA is called a procedure.
Access has 2 kinds of procedures:
- a Function is a procedure that can return a value;
- a Sub (sub-routine) is procedure that cannot return a value.
You can use a function name in a property (like the =MyFunc() example
above), but you can't use a Sub that way. Therefore I always create
functions (never subs.) But Access always uses Subs for the event
procedures.

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am a little confused!



On my old forms my command buttons were created using the wizard, which
created macros. Since I am trying to learn VBA, I want to create code for
them. Therefore, I saved the macros and then converted them to VBA.
There
is some code on there I do not understand—for instance, “With
CodeContextObject.†Below is an example of one of the codes for saving a
record:



Option Compare Database

Option Explicit



'------------------------------------------------------------

' SaveAddress

'

'------------------------------------------------------------

Function SaveAddress()

On Error GoTo SaveAddress_Err



With CodeContextObject

On Error Resume Next

DoCmd.RunCommand acCmdSaveRecord

If (.MacroError <> 0) Then

Beep

MsgBox .MacroError.Description, vbOKOnly, ""

End If

End With





SaveAddress_Exit:

Exit Function



SaveAddress_Err:

MsgBox Error$

Resume SaveAddress_Exit



End Function



Since I would like to know what is going on I decided to create a code
that
I can use over and over. Should I use a Procedure instead of a Function?
This is the code I wrote for my save record command button:



Private Sub cmdSaveAddress_Click()



SaveRecord



End Sub





Also, I created this first in a module called modUtility:



Public Function SaveRecord()

On Error GoTo HandleError

DoCmd.RunCommand (acCmdSaveRecord)

ExitHere:

Exit Function



HandleError:

MsgBox Err.Description

Resume ExitHere

End Function



Am I doing it correctly and on the right path?



Thanks!

 

[David W. Fenton]

use an event procedure
normally; use a function in a stand-alone module when you need to
reuse it in different places.

Just for clarity of definition, anything in VBA is called a
procedure. Access has 2 kinds of procedures:
- a Function is a procedure that can return a value;
- a Sub (sub-routine) is procedure that cannot return a value.
You can use a function name in a property (like the =MyFunc()
example above), but you can't use a Sub that way. Therefore I
always create functions (never subs.)

I think that's a really silly rule. I never create a function unless
I need to return a value.

Also, one can get data back from even a sub by using ByRef
parameters that are passed to the sub, have their values altered in
the sub, and can then be examined after the sub runs. This is
actually my preferred method when I need to return more than one
value at a time. I'll often make the function return a Boolean value
(success or no success) and the calculated values be put in the
ByVal parameters.

I just see no point in needlessly writing functions when they don't
need to return a value.

Obviously, the exception would be if you are going to use the
procedure in a context where Access is limited to functions, such as
in the events of forms/reports/controls, in toolbars and menus, and
in places like the Switchboard manager.

 

[lmcc007]

Thanks, but ByVal and stuff is way over my head. I'm learning!

 

[Douglas J. Steele]

I think that's a really silly rule. I never create a function unless
I need to return a value.

I agree with Allen on this one, David. (well, except for the "always" part.)

There are times when it's convenient to use =MyFunc() as the property for an
event. The alternative is

1) setting the property to [Event Procedure]
2) put a single line of code into the sub that's generated to call the
common sub.

for each control.

I've got forms with 300 controls on them where each control calls the same
code (think of it as a seating plan, where each control represents a
particular seat, and I want specific code to run for each control when
particular events, such as GotFocus, LostFocus or DblClick, occur.). Using
subs instead of functions would be a LOT more work to set up (even though I
generated the form through code)

 

[David W. Fenton]

ByVal and stuff is way over my head.

The way it works is this:

Say you have a subroutine:

Public Sub SetBackGroundColors(ByRef lngTextBoxBackColor As Long,
_
ByRef lngLabelForeColor As Long)
lngTextBoxBackColor = 16777215
lngLabelForeColor = 0
End Sub

In a form's OnOpen event, you might do this, then:

Dim lngFieldBackground As Long
Dim lngLabelBackground As Long
Dim ctl As Control

Call SetBackGroundColors(lngFieldBackground, lngLabelBackground)
For Each ctl in Me.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox
ctl.BackColor = lngFieldBackground
ctl.Controls(0).ForeColor = lngLabelBackground
End Select
Next ctl
Set ctl = Nothing

What happens when you define the parameters of a sub/function as
ByRef (which is the default) is that a pointer to the original value
passed to the sub/function is passed, and if the original value is
stored in a variable, operations on that parameter within the
sub/function are actually operations on the original variable passed
by reference.

When you pass ByVal (by value), you're passing a literal, and
operations on the parameter variable within the sub/function are
local to the sub/function.

As I said, this is most useful when you want what you'd normally
declare as a function to return multiple values. The above code
would require two functions to return those two values, but my
solution requires only one, and you keep everything all in one
place.

Now, obviously, if you're looking at 3 or 4 or 5 or more values, it
starts to become more useful to use a more complex data structure to
get the values. Candidates for that would be a standalone class
module, a function that returns an array, a user-defined type
structure, or a custom collection, or a sub that requires an array,
user-defined type or custom collection as a parameter.

All of these mean more complex code where you're looking up the
values (the standalone class module is the easiest to deal with, in
fact), but when you need to lookup multiple values, you're going to
have substantial code, anyway.

 

[David W. Fenton]

I agree with Allen on this one, David. (well, except for the
"always" part.)

There are times when it's convenient to use =MyFunc() as the
property for an event.

When I'm going to do that, I'll create it as a function. When it's
clearly not going to be used for an event, there's no point, in my
opinion, in making it a function.

Also, I find this kind of code

Public Function MyFunction(varParameter As Variant)
[code the does something]
End Function

....to be highly objectionable.

1. it doesn't declare a return type, so defaults to Variant.

2. there is no assignment of the return value, so it returns Null in
all cases.

I believe that it should be an ironclad rule that:

1. all functions should have a declared return type.

2. all functions should set the value of what is returned.

In my opinion, functions used as events, as you describe, should
return a Boolean, even though the return value is completely ignored
by the form's event handler.

The alternative is

1) setting the property to [Event Procedure]
2) put a single line of code into the sub that's generated to call
the common sub.

for each control.

I've got forms with 300 controls on them where each control calls
the same code (think of it as a seating plan, where each control
represents a particular seat, and I want specific code to run for
each control when particular events, such as GotFocus, LostFocus
or DblClick, occur.). Using subs instead of functions would be a
LOT more work to set up (even though I generated the form through
code)

So, fine. If you have a sub that you're going to call on 300
controls, declare it as a function -- I am not arguing against that
at all.

I'm arguing against declaring all user-defined subroutines as
functions, whether or not there is even the remotest possibility
that it will be used as a function someday. How hard is it to change
a sub to a function? All you do is change Public Sub to Public
Function -- no further changes in code are needed.

I just think it's deceptive to define as a function a piece of code
that doesn't return a value just because you want to avoid typing
the word "function" in place of "sub" at the point that 1 of those
subroutines should get used as an event argument in a
form/report/control.

It's particularly bad coding if you don't declare a return type nor
set the return value. This is the kind of thing that can badly
confuse a different programmer coming to the code sometime later, as
it implies "this is a piece of code that returns a value" but it
never does (except the default Null). You're writing code that
implies something about its use and then *not* using it that way.

I consider kind of practice to be a premature optimization. That is,
you're writing your code for an eventuality that will occur only
very seldom. Now, of course, if you write sloppy code where your
functions don't doesn't declare a return type and doesn't set a
return value, then it's easy.

But that's BAD CODE.

To get the benefit of Allen's recommendation, you have to write BAD
CODE.

And I just won't do that, nor will I stand by in silence while
someone recommends doing it.

 

[Allen Browne]

David, we are in the realm of stylistic choices, so I hope you can respect
that others may make choices for reasons other than the ones that make sense
to you.

To me it makes no sense to start it out as a Sub when we don't know for sure
that it will never be used in an event property, and we don't know for sure
that it will have no return value. There's also an inconsistency in the way
VBA calls subs and functions, so if everything I create is a function, I
don't have to remember whether it was a sub or a function in order to call
it. To me, it makes the code simpler to create and to maintain if I know all
my proc's are functions. There are just fewer things that can go wrong (e.g.
in calling, or in using it in an event property.)

I do tend to assign a return value anyway, even if it's no more than a
success/failure flag, or a count of the records affected/matched.

(I think you'll find that a function that does not declare or return any
type yields Empty rather than Null, through I understand this is not
something you ever do.)

Call me sloppy and a bad coder if you wish; to me, declaring everything as a
function is not the same as the issue of whether it has a return value, it's
just more consistent coding and less problematic in the way it's called. One
could even suggest suggest that having both is unnecessary complexity, and
Subs should be dumped (a kind of Occam's razor.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

There are times when it's convenient to use =MyFunc() as the
property for an event.

When I'm going to do that, I'll create it as a function. When it's
clearly not going to be used for an event, there's no point, in my
opinion, in making it a function.

Also, I find this kind of code

Public Function MyFunction(varParameter As Variant)
[code the does something]
End Function

...to be highly objectionable.

1. it doesn't declare a return type, so defaults to Variant.

2. there is no assignment of the return value, so it returns Null in
all cases.

I believe that it should be an ironclad rule that:

1. all functions should have a declared return type.

2. all functions should set the value of what is returned.

In my opinion, functions used as events, as you describe, should
return a Boolean, even though the return value is completely ignored
by the form's event handler.

The alternative is

1) setting the property to [Event Procedure]
2) put a single line of code into the sub that's generated to call
the common sub.

for each control.

I've got forms with 300 controls on them where each control calls
the same code (think of it as a seating plan, where each control
represents a particular seat, and I want specific code to run for
each control when particular events, such as GotFocus, LostFocus
or DblClick, occur.). Using subs instead of functions would be a
LOT more work to set up (even though I generated the form through
code)

So, fine. If you have a sub that you're going to call on 300
controls, declare it as a function -- I am not arguing against that
at all.

I'm arguing against declaring all user-defined subroutines as
functions, whether or not there is even the remotest possibility
that it will be used as a function someday. How hard is it to change
a sub to a function? All you do is change Public Sub to Public
Function -- no further changes in code are needed.

I just think it's deceptive to define as a function a piece of code
that doesn't return a value just because you want to avoid typing
the word "function" in place of "sub" at the point that 1 of those
subroutines should get used as an event argument in a
form/report/control.

It's particularly bad coding if you don't declare a return type nor
set the return value. This is the kind of thing that can badly
confuse a different programmer coming to the code sometime later, as
it implies "this is a piece of code that returns a value" but it
never does (except the default Null). You're writing code that
implies something about its use and then *not* using it that way.

I consider kind of practice to be a premature optimization. That is,
you're writing your code for an eventuality that will occur only
very seldom. Now, of course, if you write sloppy code where your
functions don't doesn't declare a return type and doesn't set a
return value, then it's easy.

But that's BAD CODE.

To get the benefit of Allen's recommendation, you have to write BAD
CODE.

And I just won't do that, nor will I stand by in silence while
someone recommends doing it.

 

[David W. Fenton]

David, we are in the realm of stylistic choices, so I hope you can
respect that others may make choices for reasons other than the
ones that make sense to you.

I don't see it that way. I see writing functions without explicit
return types and explicit assignment of the returned values as
equivalent to omitting OPTION EXPLICIT in your modules -- it's bad
programming practice.

To me it makes no sense to start it out as a Sub when we don't
know for sure that it will never be used in an event property, and
we don't know for sure that it will have no return value.

This is *crazy*. It's so incredibly easy to change a sub to a
function that it makes no sense to me prematurely optimize for a
situation that may never occur (i.e., by creating a function instead
of a sub). If you're not writing bad code, i.e., you're properly
declaring a return type and assigning a return value, then it takes
much more time to write it as a function rather than as a sub.

It's only by skipping the necessary step of writing good code that
there is any advantage to your recommendation, and I'm frankly
shocked that a programmer of your level of accomplishment and
obvious wisdom would be recommending such a shortcut approach to
coding.

There's also an inconsistency in the way
VBA calls subs and functions,

I call them all with Call ProcedureName() so I don't have to
remember the differences with parens.

so if everything I create is a function, I
don't have to remember whether it was a sub or a function in order
to call it. To me, it makes the code simpler to create and to
maintain if I know all my proc's are functions. There are just
fewer things that can go wrong (e.g. in calling, or in using it in
an event property.)

I very, very strongly disagree. You're recommending writing bad code
just to save a few keystrokes.

I find the comment about how to call them completely unpersuasive as
there is already a way to call them consistently, regardless of
type.

I do tend to assign a return value anyway, even if it's no more
than a success/failure flag, or a count of the records
affected/matched.

Once you're doing that, you're writing extra code that you may not
need. Again, that's premature optimization, and also implies things
about what it does. If I delve into someone else's code and
encounter a function whose return value is never used, I'll be
scratching my head wondering what I'm missing. Is it used in
queries? Is it used in expressions? I'm going to have to use a
find/replace tool to figure out what the deal is, whereas if it's
used as a sub and declared as a sub, I don't have to research
anything.

(I think you'll find that a function that does not declare or
return any type yields Empty rather than Null, through I
understand this is not something you ever do.)

Even worse! Null is at least a usable value, whereas Empty is pretty
useless.

Call me sloppy and a bad coder if you wish; to me, declaring
everything as a function is not the same as the issue of whether
it has a return value,

If you're declaring the return type and setting a return value,
you've just made all your subroutines implemented as functions two
lines of code longer (more or less) than the equivalent sub. And
you've written code that implies it will be used in a particular
way, even if you never use it that way.

it's
just more consistent coding and less problematic in the way it's
called. One could even suggest suggest that having both is
unnecessary complexity, and Subs should be dumped (a kind of
Occam's razor.)

I think anything that moves VBA in the direction of the untyped
scripting languages would be a bad thing. It's a *good* thing that
we have subs and functions and obfuscating the differences makes for
code that does not self-document. If you have to insert a comment to
the effect that your function's return value is not used anywhere,
then you're making your code worse.

I'm flabbergasted over this discussion.

The recommendation violates every canon of good coding I can think
of.

 

[Fred]

If an expert is doing it that way and it works, it's probably not "crazy" or
"bad", it's probably just different.

 

[David W. Fenton]

If an expert is doing it that way and it works, it's probably not
"crazy" or "bad", it's probably just different.

I certainly recognize Allen as an expert. I still consider his
advice bad advice, though. Just because someone is an expert doesn't
mean that everything he says is correct.