Sub vs Function - who win?

[amorosik]

Premise: an entire program could be composed exclusively of Functions, with absolutely no Subs
With a Function, you achieve the same result of a Sub, and furthermore, should you ever need to return values, the code is already ready; just insert the type of value to return at the end of the first line of the function
So the question is: assuming we need to write code that doesn't need to return values, what advantage is there in using a Sub instead of a Function?

 

[KitaYama]

Subs and Functions behave differently at times. (compiling wise)

Is this a normal behavior in Access?

After using Access for years, now I'm back to my first years that I don't even understand how Compile behaves. I have a form with a simple code in it's On_Open event : Private Sub Form_Open(Cancel As Integer) SetTestMode Me End Sub And this is the function: Public Function SetTestMode(frm...

www.access-programmers.co.uk

Read #12 #13 #16 & #18

 

[NauticalGent]

An interesting read on Function behavior. It doesnt really "solve" the problem or answer you question, but you may learn something about functions you may or may not have known...

Every Function in VBA Returns a Value https://share.google/docQnUy2ZI7Eo9o4q

 

[MajP]

There is no performance difference, but provides clarity in your code.
Function - I intend to return a value
Sub - I am intending to do something.

KitaYama's, thread shows there can be a few unintended consequences because something is always returned from a function even if you choose not to use it.
I have seen plenty of vba users especially Excel where everything is a function. I find it confusing when I read it.

In some other languages everything is a function or everything is a method but you have a means to tell it to return nothing. In java for example you declare a method "void" when nothing is returned.

 

[MajP]

Somewhat unrelated but explains some quirks in Access. This took me years and years to understand.
For example in an property if you can specify a Function but not a Sub even though you are doing an action and there is not even a way to use a return value if you wanted.

Example in a controls onclick property:

OnClick: =MyFunctionName()

The reason is that Access has two ways to run code.
Expression Engine
VBA

These look the same but are not.
Access actually contains two different “brains” that can run code:

• the Expression Engine
• the VBA Runtime

They look similar on the surface, but they behave very differently. Understanding the split explains a lot of Access’s quirks — including why event properties require functions, why some expressions work in queries but not in VBA, and why some VBA features can’t be used in macros or control sources.
Let’s break them apart clearly.

---

1. The Expression Engine (Access Expressions)​

This is the engine used in:

• Control sources (=Date(), =[FirstName] & " " & [LastName])
• Query calculated fields
• Validation rules
• Default values
• Macro conditions
• Event properties (=MyFunction())

What it is​

A lightweight formula evaluator — similar to Excel’s formula engine.

What it can do​

• Evaluate expressions
• Call functions only
• Work with fields, controls, and literals
• Perform simple logic (IIf, Nz, Len, etc.)

What it cannot do​

• Execute statements (loops, assignments, If…End If, Select Case, etc.)
• Call Subs
• Use objects that require Set
• Handle complex data types (collections, recordsets, classes)
• Run multi-line logic

Why?​

Because the expression engine is designed to compute a value, not perform actions.
It must always evaluate to something — even if that something is ignored.

---

2. VBA (Visual Basic for Applications)​

This is the full programming language behind Access.
Used in:

• Form/report modules
• Standard modules
• Class modules
• Event procedures (Private Sub Form_Load())

What it can do​

Everything the expression engine can’t:

• Execute statements
• Call Subs and Functions
• Use loops, conditions, error handling
• Create and manipulate objects
• Work with recordsets, DAO, ADO
• Use classes, events, and custom types
• Run multi-line logic

Why?​

Because VBA is a full procedural programming environment.

---

3. The Key Difference in One Sentence​

The Expression Engine evaluates formulas; VBA executes code.
That’s the entire distinction.

---

4. Why this matters in real Access development​

Event properties require a Function​

Because the expression engine can only evaluate a function call.

Queries can call functions but not subs​

Same reason — queries use the expression engine.

You can’t put VBA statements in a control source​

Because the expression engine doesn’t understand statements.

Macros and expressions can’t use objects​

Because object variables and Set are VBA-only concepts.

---

Bottom Line​

Feature	Expression Engine	VBA
Purpose	Evaluate formulas	Execute code
Can call	Functions only	Subs & Functions
Supports statements	No	Yes
Multi-line logic	No	Yes
Object manipulation	No	Yes
Used in	Queries, control sources, macros, event properties	Form modules, standard modules

The two systems coexist, but they serve very different roles. Access leans on the expression engine for anything that must be stored as a single-line formula, and on VBA for anything procedural or complex.

---

If you want, I can also show you the cleanest pattern for bridging the two — using a tiny function in the event property that delegates to a Sub in VBA.

 

[NauticalGent]

@MajP, what AI is that, ChataGPT?

You(it) just answerd a question I have always had but didnt quite know how to ask...

 

[MajP]

That is Copilot. It is kind of mind boggling how clear and concise it is.
So somewhat related question I had is why you can use TempVars in a query and not a VBA variable. It is also related to above.

Access looks inconsistent here at first glance, but once you understand which engine is doing the work, the behavior makes perfect sense.
Let’s break it down cleanly.

---

Why TempVars work in queries but VBA variables don’t​

Access queries are evaluated by the Expression Engine, not the VBA runtime.
That single fact explains everything.
The Expression Engine can only “see” things that are:

• Stored in Access’s global expression namespace
• Exposed through Access’s object model
• Designed to be expression‑safe

TempVars meet all three criteria.
VBA variables meet none of them.

---

1. TempVars are part of Access’s global expression namespace​

TempVars live in a global collection that Access exposes to:

• Queries
• Macros
• Control sources
• Validation rules
• Event property expressions

The Expression Engine knows how to resolve:
TempVars!MyValue
because TempVars are explicitly registered with the engine.
This makes them “expression-visible.”

---

2. VBA variables are private to the VBA runtime​

A VBA variable—whether declared in:

• a procedure
• a module
• a class
• a form/report module

exists only inside the VBA runtime, not in the expression engine.
The Expression Engine has no mechanism to:

• inspect the call stack
• read module-level variables
• read static variables
• read local variables
• read object variables
• read anything stored in the VBA runtime

So when a query tries to evaluate:
MyVbaVariable
the expression engine simply has no idea what that is.

---

3. TempVars were created specifically to bridge the gap​

TempVars were introduced in Access 2007 for exactly this reason:

• Developers needed a way to pass values from VBA → queries/macros/forms
• Without relying on hidden controls or global functions

TempVars are:

• Global
• Persisted
• Expression-safe
• Thread-safe within Access’s single-threaded UI model
• Accessible from both VBA and the Expression Engine

They are the official “shared memory” between the two worlds.

---

4. Why Access doesn’t expose VBA variables to queries​

There are deep architectural reasons:

a. Expression Engine must be deterministic​

Queries may be:

• cached
• optimized
• executed outside the form context
• run by the ACE engine (JET successor) without the UI loaded

VBA variables are not stable enough for that.

b. VBA variables can disappear​

They vanish when:

• a form closes
• a module resets
• an error occurs
• the project recompiles
• Access auto-compacts memory

The query engine cannot depend on such volatile state.

c. Security and sandboxing​

Expression evaluation must be safe.
VBA variables could expose objects, file handles, or unsafe references.

---

Bottom Line​

Feature	TempVars	VBA Variables
Visible to Expression Engine	Yes	No
Visible to VBA	Yes	Yes
Persist across queries	Yes	No
Designed for cross-engine communication	Yes	No
Can be used in queries	Yes	No

Access can evaluate TempVars in a query because TempVars are part of the expression engine’s global namespace.
Access cannot evaluate VBA variables because they exist only inside the VBA runtime.

---

If you want, I can show you the cleanest patterns for passing values from VBA to queries—TempVars, hidden controls, global functions, or parameterized DAO queries—and when each one is the best choice.

 

[NauticalGent]

Premise: an entire program could be composed exclusively of Functions, with absolutely no Subs
With a Function, you achieve the same result of a Sub, and furthermore, should you ever need to return values, the code is already ready; just insert the type of value to return at the end of the first line of the function
So the question is: assuming we need to write code that doesn't need to return values, what advantage is there in using a Sub instead of a Function?

In this article, @NoLongerSet explicitly answers your question:

Why Use Subs at All?

Almost every Sub could be a Function...but that doesn't mean it's a good idea.

nolongerset.com

 

[The_Doc_Man]

There is an old principle I sometimes trot out that is a matter of good programming practices. "Purity of purpose."

The purpose of a function is to process and return something. It implies a value. (Which is why a function can have a function as an argument.)

The purpose of a subroutine is to do something to/with an object or entity, usually to make changes (including deleting it). The call is not meant to return something, but to do something.

Using functions to do the work of subroutines violates a theoretical purity of purpose.

 

[AHeyne]

The command-query separation programming principle comes into play here.

Command–query separation - Wikipedia

en.wikipedia.org

 

[GPGeorge]

Bottom line: The set of "Things that you can do, but really shouldn't" includes using Functions instead of Subs where appropriate.

 

[sonic8]

It is kind of mind boggling how clear and concise it is.

I find it rather mind boggling how a clear structure, a few icons, and (over)confident writing style tricks people to stand in awe and not question the actual sensibility and veracity of the statements made.
I counted 10+ statements in the AI output in this thread that are either factually incorrect or at least strongly misleading. - And no, don't ask me to elaborate; I'm not wasting my time to correct AI junk. Just as much: It doesn't even use the correct name of the thing it is writing about.

 

[MarkK]

So the question is: assuming we need to write code that doesn't need to return values, what advantage is there in using a Sub instead of a Function?

Good code expresses intent.
In expressing your intent, whether you want to...
• do something, or
• get something
...is a critical distinction.

 

[Edgar_]

For a MS Access developer who just wants to get things done:
Use sub for routines that don't need to return a value
Use functions for things that need to return a value and things that will be used from MS Access' GUI expression fields and queries.

So, that's my POV, OP.

assuming we need to write code that doesn't need to return values, what advantage is there in using a Sub instead of a Function?

I don't see any practical advantage other than writing sub myfunction + enter is faster than function myfunction() as whatever + enter. Then again, VBA is picky and won't be updated ever, so it's best if you follow the general convention in the long run.

 

[Albert D. Kallal]

Actually, you can create a public VBA function, and thus have that return the global VBA variable, or anything else.

As a result, such expressions are in fact NOT fixed, and can change, and you CAN pass values to that function from SQL.

eg:

select id, FirstName, LastName, GetFullName([FirstName], [LastName]) as FullName from tblCustomers

global public function thus:

public function getFullName(FN as varient, LN as varient) as string

    GetFullName = nz(FN,"") & " " & nz(LN,"")

end function.

So, you can "expose" and "use" VBA variables to JET SQL if you use a function........

R
Albert.

 

[KitaYama]

I don't see any practical advantage other than writing sub myfunction + enter is faster than function myfunction() as whatever + enter.

Not trying to promote using function instead of sub, but your point is not an advantage.
function myfunction + Enter is the same as sub myfunction + Enter
If one's not trying to receive a returned value, there's no need to type the trailing () as whatever.

 

[Edgar_]

Not trying to promote using function instead of sub, but your point is not an advantage.
function myfunction + Enter is the same as sub myfunction + Enter
If one's not trying to receive a returned value, there's no need to type the trailing () as whatever.

hey, sub is shorter than function
i win

 

[KitaYama]

hey, sub is shorter than function

I imagined I will be told so

 

[MajP]

In early vba it was common to see people do the opposite and not use functions but only sub routines. There was heavy use of In/Out parameters. I use to see it quite often and was always confused. I believe this was leftover habits from early limitations in VB and similar use in windows API. Or maybe familiarity with other languages at the time.

You would see construct like this where you pass in F and L by ref.

Sub ParseName(ByVal fullName As String, ByRef firstName As String, ByRef lastName As String)
    Dim parts() As String
    parts = Split(fullName, " ")
    firstName = parts(0)
    lastName = parts(UBound(parts))
End Sub

Sub Example()
    Dim f As String
    Dim l As String
    Call ParseName("John Smith", f, l)
    MsgBox "first Name: " & f & " Last Name: " & l
End Sub

 

[The_Doc_Man]

According to the VBA Language Ref. (v20140424), section 5.3.1 Procedure Declarations, the main difference between a SUB and a FUNCTION is a compile-time action. The presence of a function call in any module creates a procedure-scope local unnamed variable (referred to as the "function result" variable) of the same type that was declared as the function's type. That is, it acts like an implicit DIM statement for the function result variable in the procedure where the call occurs. Like the variables implicitly created when you don't use "Option Explicit", this variable type defaults to Variant, so you CAN leave off the type of a function. The run-time semantics are that when you invoke the function, there is an implied LET statement of {function result variable} = {result returned by function} i.e. a value assignment occurs from the procedure scope of the function to the procedure scope of its caller - even if that implied LET is in the middle of another statement. In the rest of the statement, the function result variable acts as being local to the calling procedure.

What does this mean in practical terms? You COULD call a function intending for it to have "side effects" either on items passed ByRef or items declared Public in another module body. In fact, some networking software uses the return value of the function call as a status code even though the real changes occurred in the ByRef object (usually, a UDT). You COULD call a subroutine with ByRef In/Out parameters to pass back values. The syntax CALL function-name(args) is like declaring a variable but never actually using it.

The real answer to the question is: Why do you want to use the function or sub in a non-standard way? If you feel you have a good reason, do it. It won't hurt the code.

I have only one other relevant comment to make: Be sure that you document ALL DECISIONS that invoke non-standard usages such as this one, because if this product is going to be used by the public, that means it will eventually need maintenance. And some night a year or two down the line, after about the 5th cup of coffee, you will look bleary-eyed at your code asking yourself "What the HELL was I thinking when I did this?" With good notes, you would have an answer.