VBA & Macro Problems

Return a Date from an Access VBA Function to a Subroutine

Huma Ashraf ChHuma Ashraf Ch Nov 22, 2026 868 views

Question details

Return a calculated date from an Access VBA function to the subroutine that calls it.

Return a Date from an Access VBA Function to a Subroutine
Product
Microsoft Access VBA
Device & OS
not provided
Scenario
A subroutine calls a function that adjusts a date and needs to use the result.
Observed behavior
The function does not return the calculated date when the result is not assigned to the function name.
Before you start

Open the Access database and locate the function and calling subroutine in the VBA editor. Identify where the date adjustment finishes so you can assign the final value before the function ends.

Solution 1Recommended

Assign the calculated date to the Access VBA function name

Use this approach when the function calculates a date but the calling subroutine does not receive it.

1
Open the function

In Access, press Alt+F11 to open the VBA editor, then locate DATE_ADJUST in the module that contains it.

2
Declare the return type

Change the function declaration to `Public Function DATE_ADJUST(ByRef INITIAL_DATE As Date) As Date` so VBA treats its return value as a Date.

3
Assign the result

After the existing date-adjustment logic, including its closing `End If` if applicable, add `DATE_ADJUST = INITIAL_DATE` before `End Function`. If the result is stored in a different variable, assign that variable instead.

4
Call and test the function

In the calling subroutine, assign the result with `d = DATE_ADJUST(e)`. For example, declare `d` and `e` as Date, set `e = Date`, and use `Debug.Print d, e` to inspect the values in the Immediate window. Open that window with Ctrl+G in the VBA editor.

Assign the calculated date to the Access VBA function name
ByRef can change the input: Because INITIAL_DATE is passed ByRef, changes to it inside the function can also change the caller's variable. Use ByVal instead if the function should work with a copy.
Free Microsoft Office alternative

Work with Office documents and spreadsheets in WPS Office

WPS Office is a free, lightweight alternative for everyday document and spreadsheet work, with a familiar interface and Microsoft Office format support for easier migration. It does not fix Access VBA functions or replace an Access database.

Work with dates in spreadsheets alongside your Access projects.Open and save common Microsoft Office formats, including DOCX and XLSX.Use familiar document and spreadsheet tools in a lightweight office suite.
microsoft office alternative - wps office

Frequently Asked Questions

Why does my Access VBA function not return the calculated date?

In VBA, a function returns the value assigned to its name. Add an assignment such as `DATE_ADJUST = INITIAL_DATE` after the calculation and before `End Function`.

How do I declare that the function returns a date?

Add `As Date` after the parameter list, as in `Public Function DATE_ADJUST(ByRef INITIAL_DATE As Date) As Date`.

How can I check the returned value?

In the calling subroutine, use `d = DATE_ADJUST(e)` followed by `Debug.Print d`. Press Ctrl+G in the VBA editor to view the output in the Immediate window.

Will changing the date parameter inside the function change the original variable?

It can when the argument is passed ByRef, as in this example. Change the parameter declaration to `ByVal INITIAL_DATE As Date` if you want the function to modify a copy instead.