A VBA Function can be created either within a single form or report or in a stand-alone module, which can then be called from anywhere in the database. The key difference between a Sub() and a Function() is that functions return a value.
Syntax
[Public | Private ] Function name([arg_List])
code_block
[name=expression]
End Function
Key
Public Extend the scope of this function to all procedures in the project.
Private Restrict the scope of this function to procedures within the same module.
name The name of the function.
arg_List Argument variabless passed to the function, comma separated.
By default, each local variable=argument (ByRef)
To have each local variable=value of the argument prefix the
argument with 'ByValue'.
code_block Program code
expression The value to return.
Example
Dim intResult as Integer
intResult = DemoFunc(5,10)
msgbox intResult
Function DemoFunc(x,y)
DemoFunc = x + y
End Function
Most functions can be used in both VBA and in a query by adding the function (and it's parameters) to the 'field' line in the query design view.
For example using the builtin Format() and DateDiff() functions:
If one or more table columns are used as function parameters, then the function will return a different value for each row of the result set. So the query above will return different results for every row in the table that has a different Purchase_Date.
When a function is used that has no parameters such as Now() then it will return the same value for every row returned by the query. Some VBA functions will be considered 'unsafe expressions' when used within an SQL query.
You can prefix the expression with a meaningful name or allow Access to assign a name automatically Expr1: , Expr2: ...
“Measuring programming progress by lines of code is like measuring aircraft building progress by weight” ~ Bill Gates
Related:
Format - Format a Number/Date/Time.
Now - Return the current date and time.
Sum (SQL) - Add up the values in a query result set.