Writing Access functions

[David W. Fenton]

=?Utf-8?B?SmFjayBMZWFjaA==?= <dymondjack at hot mail dot com> wrote
in news:[email protected]: [quote
me, unattributed:]

Just as a side note to OP, there is one significant advantage to
using a Function over a Sub (assuming you understand the Null
variant it may return without a value applied)... When working
elsewhere besides VBA, Public Subs are not callable.

For instance, SQL strings, Conditional Formatting, Menu/Toolbar
commands, Control sources, Macros (thought the only one you should
be using is the Autoexec macro) etc etc. These "Access-side"
utilization of code will only work with functions (or properties,
if you want to go there). You cannot reference Subs this way.

This does not alter my advice one iota. If you're going to call the
code in the situations where you can use only functions then it's a
requirement that it be a function.

This is not an argument for writing nothing but functions (and I
don't mean to imply that I think you were suggesting that).

 

[David W. Fenton]

Actually, I think it returns Empty rather than Null, David.

You're correct on that.

Perhaps that proves your point that it's somewhat confusing.

A function without a declared return type is a badly-written
function.

 

[Jack Leach]

True... there are times when you need to create a function that would
otherwise be a sub. Macros or menu commands for instance, do not accept any
return at all, and therefore the code that they are running "should" be a sub.

This certainly doesn't help make things any less confusing. Now we have
Functions (that return a useful value), Subs, and Functions that would
otherwise be a Sub except for the fact that they need to be a Function to...
well, function properly :|

I wonder why Subs are not able to be referenced in this manner?

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

=?Utf-8?B?SmFjayBMZWFjaA==?= <dymondjack at hot mail dot com> wrote
in news:[email protected]: [quote
me, unattributed:]

Just as a side note to OP, there is one significant advantage to
using a Function over a Sub (assuming you understand the Null
variant it may return without a value applied)... When working
elsewhere besides VBA, Public Subs are not callable.

For instance, SQL strings, Conditional Formatting, Menu/Toolbar
commands, Control sources, Macros (thought the only one you should
be using is the Autoexec macro) etc etc. These "Access-side"
utilization of code will only work with functions (or properties,
if you want to go there). You cannot reference Subs this way.

This does not alter my advice one iota. If you're going to call the
code in the situations where you can use only functions then it's a
requirement that it be a function.

This is not an argument for writing nothing but functions (and I
don't mean to imply that I think you were suggesting that).

 

[Stuart McCall]

True... there are times when you need to create a function that would
otherwise be a sub. Macros or menu commands for instance, do not accept
any
return at all, and therefore the code that they are running "should" be a
sub.

This certainly doesn't help make things any less confusing. Now we have
Functions (that return a useful value), Subs, and Functions that would
otherwise be a Sub except for the fact that they need to be a Function
to...
well, function properly :|

I wonder why Subs are not able to be referenced in this manner?

Because they must be evaluated by the Access expression service, and a call
to a sub isn't an expression, but a call to a function is.

 

[Jack Leach]

Interesting... I didn't realize the expression service handled this much. So
much in the same way that Jet handles all the data transfers in Access, the
ES similarly handles calls to VBA?

Good to know

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Stuart McCall]

Interesting... I didn't realize the expression service handled this much.
So
much in the same way that Jet handles all the data transfers in Access,
the
ES similarly handles calls to VBA?

Good to know

Well it handles calls to VBA functions while running outside of VBA, such as
in queries or control properties. Whether that is 'much the same way Jet
handles data transfers' I have no way of knowing, but my guess is that it
would be a similar mechanism, probably a low-level 'i' interface derived
from iUnknown.

 

[David W. Fenton]

Because they must be evaluated by the Access expression service,
and a call to a sub isn't an expression, but a call to a function
is.

But the return value is irrelevant. I always define them as Boolean
and return True (unless there's some condition where they might
sensibly return False as an alternative -- I know the expression
service won't do anything with it, but it makes more sense to me).

 

[Stuart McCall]

But the return value is irrelevant. I always define them as Boolean
and return True (unless there's some condition where they might
sensibly return False as an alternative -- I know the expression
service won't do anything with it, but it makes more sense to me).

Agreed, although I tend to declare them as Long, in case I want to use the
function in VBA, then I can return an error code or something. Also I can
still return a boolean value if I want to.

 

[Jack Leach]

Well, "much the same" being that Jet is the middleman for Access and Data,
and the ES is the middleman for Access and VBA. I would guess that's about
the end of the similarity though.

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)