Sub vs Function - who win? (2 Viewers)

[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