Expression or VBA?

[datAdrenaline]

Hello Doco ...

I don't think that is what is being said ... I *think* ... the intent is something like this ... if there is an expression you can create with an Access function (or functions) then use them appropriately ... but it would not be a good use of resources just write code for the sake of writing code ....

BAD PRACTICE EXAMPLE ...

Control Source: =MyRoundFunction([SomeControl],2)

Public Function MyRoundFunction(dblNumber As Double, lngDecimals) As Variant
    MyRoundFunction = Round(dblNumber, lngDecimals)
End Function

The above would be senseless (at least on the surface) simply because you can use the built-in Round() function in your control source ...

So expanding a bit, you can create many expressions to use in Control Sources or column expressions in queries that DON'T neccessitate the creation of VBA code to return your specific desired result. The "breaking point" of when to jump to VBA is typically a developer preference.

 

[Banana]

Another point I think Pat was trying to make RE: making a event procedure even if it's a one-liner to call her own UDF was because I suspect that if you inherit a database and you're wanting to know how things works, you should be able to read everything in one document. This can be done if everything is a event procedure, because you only need to go to the module to find what so and so does.

OTOH, calling a function instead of a event procedure means that it won't be visible in the module, and you'd have to click on the control itself and scanning the events in the properties window to see if any function is being called.

So this is a problem of documentation & discovery- I can envision myself going "WTF? Where did it get *that* value?" if there was such control that had functions called instead of event procedures, and this is even more insidious if it was *another* control that modified yet another control!

It probably would be less of a problem if there were a sheet that showed all possible events for all controls & the form that can be read in one place, but this isn't available, so VBE is the next logical place to keep everything in one place to be read.

 

[Pat Hartman]

Banana interpreted my intent correctly. Discoverablilty is the ONLY reason. And he's correct, I would never expect a function used on the expression of one control to do ANYTHING except return a value that is displayed in the control that calls it. Unfortunately, coupling and cohesion are not concepts that non-programmers understand and even programmers are not necessarily taught their importance.

 

[Banana]

Golly, two new concepts for a price of one! Thanks, Pat; it made for an interesting read.

Now, I have to admit something. There is one case where I often put in functions: controls' default value. I think the same can be accomplished using OnCurrent event, but at expense of making it a bit longer to read, especially for a form where there are several fields that requires a value before it can be saved.

An added benefit is if I C & P a control, the function for default value is taken along. Not true if I had written it in form's OnCurrent event. Not to mention that it kinds of feel wrong to have a control's behavior altered by a form's event (though I assume that this is still the case even if I used nothing but functions/constants for default values.) As I have several instance where dates needs to be entered but cannot extend beyond our fiscal year, it made most logical sense to define a public function in a standard module to return the last day of current fiscal year and use that function in the default value.

One more point. When a function/expression/whatever is used in control source of any given control, at least it will expose itself in design view. This is not the case if a function is used in one of controls' events, or its default value property.

 

[ChrisO]

Banana.

Me >> ‘This is starting to be another talk fest…’

You >> ‘I hope that's not a bad thing? ’

Me >> Yes it can be…
Very little, if anything at all, said here has not been said before.
We basically regurgitate the same old things, time and time again.
We basically chant the mantra.

So much has been written on the WWW, so much that it is indeed difficult to weed through all the hits we get.

But is it something that has become too hard to search?
Do our questions here become an expression of laziness?

----------

Too hard to search or too hard to understand?

For example…
Overloading is not well known at all, least of all in VBA guise.
I can write an example (MsgBox) yet the value of that post was not understood.
Brent gives other examples in post #23.

But overloading, in VBA, will not be understood due mainly to laziness.

----------

Overloading in VBA boils down to ‘scope of reference’.

‘Scope of reference’ is a flowery term used by nerds like me to try to explain something they know very little about in reality but with which they have to live.

We can view ‘scope of reference’ as the ‘perimeter’ in which a compiler tries to resolve a reference. (I know, more nerd language… but please read on.)

The compiler, wanting to resolve a reference as efficiently as it can, should look as close to home as is possible. This should mean that the first resolution it seeks, that satisfies its criteria completely, will be accepted.

Once the first unambiguous (completely resolved) resolution is made then processing stops and that value is used.

We can look at this as an exploding reference seek (Something which expands with time…but usually very short time.)

In other words, when your code is compiled the compiler will try to resolve unambiguous references to the nearest point it can while expanding through its radius from the centre of your code.

----------

But it remains a ‘talk fest’ simply because nothing that is said, without hard, provable and verifiable evidence, is little more.

Regards,
Chris.

 

[Banana]

Banana.

Me >> ‘This is starting to be another talk fest…’

You >> ‘I hope that's not a bad thing? ’

Me >> Yes it can be…
Very little, if anything at all, said here has not been said before.
We basically regurgitate the same old things, time and time again.
We basically chant the mantra.

So much has been written on the WWW, so much that it is indeed difficult to weed through all the hits we get.

But is it something that has become too hard to search?
Do our questions here become an expression of laziness?

Fair point.

However, my intent for starting this thread was primarily to get a idea of what other people do and why they do this way. Best practices, IMO, can change and what was true few years ago may not be so relevant. Requests for techniques should be referred to manuals, tutorials and so forth because they don't change. OTOH, what techniques are actually being used, may do change, especially as people discover better way to implement existing techniques.

Overloading is not well known at all, least of all in VBA guise.
I can write an example (MsgBox) yet the value of that post was not understood.
Brent gives other examples in post #23.

But overloading, in VBA, will not be understood due mainly to laziness.

Or maybe just because it's not applicable. There's a saying that only 10% of any given code is used 90% of the time, and I am very positive that overloading, among other several things falls in that disused 90%. But I will agree that it would be a disservice to myself (and anyone else) as a programmer to fail to understand!

That said, I thought of one thing I would love to see in action: Iif(). Suppose I overloaded it so it would short circuit, rather than evaluating both parts; that would provide better performance. The question is then, will the Query Builder use my custom Iif()? I'll have to give it a whirl when I have a free time and am in front of Access.

BTW, have you had a look at the sample I provided regarding custom events' behaviors in handling parameters?

 

[datAdrenaline]

Hello Banana!

>> The question is then, will the Query Builder use my custom Iif()? <<

It will use JET expression services IIf(), NOT your own!! ... You see IIf() is part of (or is a native function in) the JET expression service, so JET will NOT call upon VBA to perform the evaluation of IIf(). By having IIf() as part of the JET Expression service, it almost becomes a JET SQL native function, so your VBA overload will not be called.

It may also be important to note that the JET expression service's IIf() will not evaluate both argruments (or it just suppresses the error) as evidenced by putting something like this is an expression column of a Query objects SQL statement.

Expr1: IIf(True, 1, 1/0)

You will find that there is no errors generated by that expression. This is what Chris was eluding to (I beleive) in post #31, because the same effect is seen if you use the same expression above as a Control Source for a text box, simply because the JET Expression Service is called upon prior to VBA and thus your overloaded IIf() will not be called upon. However, if in VBA, your overloaded IIf() will take precedence OVER the VBA IIf().

Also ... in your attempt to prevent both arguments from evaluating, just how might you do that? ... pass both arguments as strings and use the Eval()? ... With typical coding conventions I would think your overloaded IIf() would look something like this:

Public Function IIf(blBoolean As Boolean, varVariantTrue As Variant, varVariantFalse As Variant) As Variant
    
    IIf = VBA.IIf(blBoolean, varVariantTrue, varVariantFalse)
    
End Function

... Which would, by nature of VBA, require both arguments to be evaluated prior to/during the call.

....

So ... with all that, I do not beleive you will see any improvement in performance by overloading IIf() ...

Also, note that the JET Expression Service has other functions that are "Native" to it and thus do not require VBA ... I don't know all those functions, but with a little study like overloading and seeing if a query calls the overloaded function or not, would probably do a good job of identifying such JET Ex. Serv. native functions.

 

[Banana]

A bit of backtracking as I just realized something else when reading Brent's analysis on Jet's IiF() and ignoring overloading:

Am I only one who get frustrated with how documentations for anything usually can tell you what they can do, but not so much for what they *can't* do? I then end up wondering if it can be done. I try to assume that someone else has already thought of it before I did and look for the answers, but it's not always successful either because the idea may be in a different vein or different keyphrases were used.



Back to regularly scheduled programming-

Brent, thanks for sharing. Too bad that QBE won't accept overloaded functions, which would be much more neat solution to drawbacks inherent in built-in functions without using UDFs. This way, whenever the database is moved, it just default to built-in function without any error and merely runs slower should the overloading function be missing. I wouldn't dream of using UDF to replace the native functions because if they were to go missing or break due to versioning (?? - not sure how compiler will handle such cases), database just won't work anymore.

 

[datAdrenaline]

Hello Banana ...

>> Too bad that QBE won't accept overloaded functions, <<<

On the contrary ... the QBE WILL accept overloaded functions ... just not the ones that are native to the JET Expression Service ... For example ... when I overloaded IsNull() and DLookup() a QueryDef will use the Overloaded UDF. This is true because those two functions are NOT native to the JET Expression Service, so the JET Expression Service hooks into VBA to evaluate the call. But for IIf() ... its a different story because the JET Expression Server has its OWN IIf() and thus does not need VBA's help.

 

[Banana]

Aha, I didn't catch the subtle point made earlier by both of you and Chris. Thanks for splitting the hair.

I have to admit, though, it seems a bit odd that IsNull() isn't a native JET expression... One would thunk it'd be given that nulls is a integral part of database theory...

 

[ChrisO]

G’day Banana.

>>BTW, have you had a look at the sample I provided regarding custom events' behaviors in handling parameters?<<

Not really because I’ve been busy and also I was holding out for a small demo database.
If I construct a small test case based on your code I might make a mistake and possibly arrive at the wrong conclusion based on my build error and not your code.

So I was rather hoping you could build a little test database with a table, query, form, some controls and your code that demonstrates the method. If it is seen to work fine then you could also submit it for inclusion into the code archive so others may also see it.

If you can knock up a nice little demo (as if you were posting it in public, which you are) then I’m sure some of us will be only too happy to jump on it and have a good look.

----------

Brent covered the overload problem well but I would like to stress another point.

In the VBA documentation it does state that both arguments are always evaluated for the VBA IIF() function, and that is correct. But it may lead people to think that it only applies to the VBA IIF() function, and that would be incorrect.

All arguments passed to all VBA procedures, both Functions and Subroutines, are evaluated before the call. This means that any error raised is not raised in the called procedure but at the time of evaluation. Therefore, no protection can be applied in the called procedure simply because the error is raised outside of the procedure.

The IIF() function, I believe, got a bad reputation because it is a one-liner and people stuff far too much processing within the arguments. We can see many occasions where, within queries, multi-level IIF() functions are used. I’ve even seen the level (8?) exceeded. What an horrendous piece of garbage that sort of thing is to even look at let alone debug.

So the IIF() function gets a bad name, not because it’s bad, but rather from the way people use it. But then again, that would apply to most methods available to us.

----------

Regards,
Chris.

 

[datAdrenaline]

Hello Chris ..

>> All arguments passed to all VBA procedures, both Functions and Subroutines, are evaluated before the call. This means that any error raised is not raised in the called procedure but at the time of evaluation. Therefore, no protection can be applied in the called procedure simply because the error is raised outside of the procedure. <<

An EXCELLENT point to bring up! ... big thumb up!! ....

 

[datAdrenaline]

Hello Banana ...

>> I have to admit, though, it seems a bit odd that IsNull() isn't a native JET expression... One would thunk it'd be given that nulls is a integral part of database theory...<<

Nulls are a HUGE part of JET, but there is no need for IsNull() as a native function in JET because there is the JET SQL native of Is Null and Is Not Null, then I don't think even the JET expression service is called upon ... seems its straight db engine with that!

Knowing all the above is part of the reason I coerce Nulls in queries like this ...

IIf([SomeTextField] Is Null, "<No Input>", [SomeTextField])
.. Or ..
IIf([SomeNumericField] Is Null, 0, [SomeNumericField])

Which is more efficient than using Access's Nz() ... granted not much faster, but still faster!

An additional note about some of this stuff ... the following is valid in a query ...

IIf([SomeField] Is Null, "<N/A>", [SomeField])

But the SAME expression in VBA will fail {object required error is raised} ... simply because VBA is not db engine driven .. that is why we need/use IsNull() in VBA ....

IIf(IsNull(varSomeVariant), "<N/A>", varSomeVariant)

{note this info is kind of an expansion of what Chris was taking about when he said ...
>> An ‘Expression’ under these circumstances is likely to be SQL based << }

I hope this is making sense!!! ...

 

[Banana]

G’day Banana.

>>BTW, have you had a look at the sample I provided regarding custom events' behaviors in handling parameters?<<

Not really because I’ve been busy and also I was holding out for a small demo database.
If I construct a small test case based on your code I might make a mistake and possibly arrive at the wrong conclusion based on my build error and not your code.

I did post a database, but I think you were hoping for a simpler database that gets to the point directly, no? I'll see if I can further strip to demo just this.

In the VBA documentation it does state that both arguments are always evaluated for the VBA IIF() function, and that is correct. But it may lead people to think that it only applies to the VBA IIF() function, and that would be incorrect.

I made a mention earlier that I found myself using VBA more only because they were better documented (or maybe it's accurate to say more accessible) compared to JET's expressions. If I need a refresh or have a question if so and so method is appropriate for my problem, it's just a search away, whereas in Expression Builder, you get a generic help on how to use the builder, but never for what a given function does, and in Access environment, the help are well, unhelpful.

So I'd like to check- could I have had missed the documentations for JET's expression service somewhere? A quick google gives me Microsfot's Programmer's guide to JET but I suspect this is more oriented for people writing in C++ or VB and using JET rather than for Access developer, and thus does not discuss much about the native functions (or at least it's not available for public viewing; only first four chapters are shown).

All arguments passed to all VBA procedures, both Functions and Subroutines, are evaluated before the call. This means that any error raised is not raised in the called procedure but at the time of evaluation. Therefore, no protection can be applied in the called procedure simply because the error is raised outside of the procedure.

So basically, overloading for sake of error handling is futile, then. No wonder it didn't catch on.

So the IIF() function gets a bad name, not because it’s bad, but rather from the way people use it. But then again, that would apply to most methods available to us.

Interestingly enough, I did read an essay where the author argued that GOTOs weren't inherently bad; it was just the implementation and laziness of programmers that got us into the spaghetti mess. Sure runs counter to the mainstream thought of computer science.


As always, thanks for contributing!

 

[The_Doc_Man]

Getting a bit deep, here, gang...

OK, as to overloading. I'm sorry to say that MS did that to us a long time ago with the DAO / ADO dichotomy. Where you really had to worry about selecting which of the several possible overlapping methods you meant. AND it was sometimes a non-trivial decision since the data structures for the two types of recordsets weren't always quite the same in detailed interpretation. I avoided it because of the serious headaches. (Don't ask for specifics, it has been too long.) However, "disambiguation" - a fancy term for selecting scope - has been around since AC97 - what, 11 years?

My comment about using a function as the directly-referenced element of an event procedure pointer is this...

If you are going to call a public function from an event procedure, by necessity it must be either global within the class module (which means it isn't really public) or within a general module (which means it IS public.) OK, global vs. local for the newer versions of VBA, right? Don't obfuscate.

Anyway, if you have a public / global routine in the module, it will use either its own internal declarations (that must be private / local to the routine) or it will use public / global declarations. If it is using public declarations, you had better not leave behind any values that need instantiation because the global stuff doesn't instantiate at the same time that the local stuff does. And therein lies a potential side-effect. Not only do you have issues with disambiguation elsewhere in the thread, but in this point, you worry about instantiation of the intermediate values.

When you use a "real" event procedure, that is by its very nature always private/local and unless you take drastic steps, all event routine variables instantiate (on the call stack as part of the call context area) when the event routine is called. This instantiation difference (related to the concept of "binding time") can at least sometimes lead to interesting side-effects if you aren't paying closer attention to the potentially shared variables in the routine.

So I guess my gripe about using functions in event slots isn't that you automatically have ambiguous instantiation, but that it is way too easy to have it. Whereas with an event routine, it is nearly automatic that you will NOT have ambiguous instantiation of declared variables. It simply means that by using that function, I'm adding work for myself when I don't have to.

 

[ChrisO]

To quote General McAuliffe…"Nuts!"

 

[ChrisO]

G’day Banana.

>>Interestingly enough, I did read an essay where the author argued that GOTOs weren't inherently bad; it was just the implementation and laziness of programmers that got us into the spaghetti mess. Sure runs counter to the mainstream thought of computer science.<<

What mainstream thought would ever say that within the environment we work?

We could ask if they are sure and they would have to say yes, else they are proved wrong.
We could ask if we should use error handling in procedures and they would have to say yes, else they are could be proved wrong.
We could ask them how to GoTo an error handler without using a GoTo and they would say ‘you are being excessively syntactical’.
We could ask them what they mean by ‘excessively syntactical’ given that computers are, by nature, purely syntactic.
At this point there is the big ‘dummy spit’/’tantrum’ on their part.

To me the real hammer comes down on such people when we learn that they don’t really have a clue how widespread VBA is. They seem to perceive it to be only in their backyard and have no concept of its real usage by other non-MS products.

Its name is ‘Visual Basic for Applications’ and that means not just applications written by Microsoft but all application vendors that choose, and pay, to employ it.

Regards,
Chris.

 

[The_Doc_Man]

As I recall, Nicklaus Wirth proved something about how a GOTO statement makes it impossible to "prove" that a program is correct.

My only complaint was that if the problem wasn't so hot, either, who GAVE a rat's patoot that the program couldn't be proven correct?

But then, I'm know to sometimes claim to be a pragmatist.

Good, since Ada and a couple of other languages are into pragmas. But I digress...

ChrisO - was that "nuts" directed my way? Sheesh, I wasn't asking for a surrender and wasn't shelling Bastogne at the time...

 

[ChrisO]

>>ChrisO - was that "nuts" directed my way? Sheesh, I wasn't asking for a surrender and wasn't shelling Bastogne at the time...<<