Where to define VBA Functions?

[Poyan]

Where do I define a user-defined VBA functions so that I can call them in the query?



Here is the problem. I am defining a VBA functions called DateDiffW which calculates the number of weekdays (i.e. excludes sat and sun) between 2 dates.

In access 2007, under the "create" tab I clicked "macro" -> "module". Then I defined the function there, starting with:

Function DateDiffW(BegDate, EndDate)

I then saved the module.

In the query, when I call the DateDiffW function I get the error:

"Undefined Function 'DateDiffW' in expression"

What am I doing wrong?

[June7]

Looks like you did everything correctly so don't know why the query can't find the function. This shouldn't make difference but try anyway.
Public Function DateDiffW(BegDate As Date, EndDate As Date) As Integer

Test the function by calling it in the VBA Immediate window.

Post the entire procedure code for analysis.

[Poyan]

Never mind. It was a security setting blocking my macros. Thanks for your help.

[boblarson]

Looks like you did everything correctly so don't know why the query can't find the function. This shouldn't make difference but try anyway.
Public Function DateDiffW(BegDate As Date, EndDate As Date) As Integer

Test the function by calling it in the VBA Immediate window.

Post the entire procedure code for analysis.

The Public part won't matter because it is the default. But I think you nailed it in that the function that the OP posted doesn't have a return type defined.

[June7]

It is certainly good practice to declare datatypes for variables but turns out that wasn't the issue. I should have considered security issue. Must be OP's first code in the project.

[boblarson]

Interesting, I always had thought that a function that didn't have a return type specified would not return anything. But just like an untyped variable, it returns a variant type from what I can tell, because if a value for it is set, it returns that and if you just have a blank shell:

Function GetVal()
End Function

it returns an empty string.

So, another thing learned

[khalid]

Interesting....
Function GetVal()
End Function

it returns an empty string.

So, another thing learned

I thought it returns nothing....