Expression or VBA?

[datAdrenaline]

Hello Doc ...

>> And the truth is, REAL programmers use assembly language <<

No shields needed from me on that!!! ... But can we modify that to "REAL programmers have used assembly language"!!! ... ...


>> The only reason I am not so sanquine about using functions in place of event procedures is precisely because you are creating "hidden" side effects by doing so. With an explicit event procedure, people EXPECT to look in the code to see what it is doing to other things. With a function call, that expectation is less obvious and could lead to confusion <<

I, with the utmost respect, disagree. If you have defined your click event to call a function with something like this:

=fFilterByLetter()

You can click on the elipses button to the right of the text box and when you do, you jump to the VBA code for that Function, which is really no different than having the property as "[Event Procedure]", and clicking on the elipses and being taken to the procedure.

After all a Function is just a procedure that returns a value, that value does not HAVE to be useful. So to me, I would see the function call in the property and just examine what that procedure does.

>> Macro to run some nightly thing on a scheduler and the Macro ends with a QUIT step. Try to do that any other way that doesn't involve leaving the database open on somebody's terminal at night <<

- An AutoExec macro can launch code that ends in DoCmd.Quit
- You can use a Startup form that has a Form_Load event that has code that ends in DoCmd.Quit
- All both a StartUp form and AutoExec macros can branch to appropriate code based on the command like switch of /command, VBA can then use the Command() function to return the text specified in the command switch and thus perform different steps in VBA ... all ending is DoCmd.Quit.

Please note that this is in NO WAY removing the significance of the /x command line switch and the macro action of Quit as they are very useful features ... I personally just don't like Macros, but the argument you make for their use is quite valid.

Also ... macros are gaining in power, as evidenced by there improved power in A2007. I would be surprized if that trend did not continue into the next version of Access. With all the desktop db's out there claiming to have great power with "no code" (for some reason they don't veiw macros/scripts/predefined steps ... as code), the Access development team has really no choice but to bring that type of power to the "Information Workers" across the globe.

.....

So ... along with the subjective "best tool for the job", we have the subjective "best practices", as well as, the tool that we like the best .... I've used my big cresent wrench more than once as an effective hammer!!!!

{disclaimer: with things like "best practices" and "best tool", I do realize that there is the curve where "most worth their salt" consider something a good or bad practice ... so I get that ... }

 

[Banana]

Forgot one more point:

Regarding the behavior of event procedures, if the built-in are same as the custom events, then The_Doc_Man is right:

Public Sub CloseTrans()

Dim Cancel As Integer

If mPend Then
    RaiseEvent BeforeCommit(Cancel)
    
    If Not Cancel Then
        CurrentDb.QueryDefs("trxCommitTrans").Execute
        mStatus.Caption = COTRAN
        mStatus.ForeColor = DECOL
        mPend = False
    Else
        mReturn = True
        ReturnTrans
    End If
End If

Private Sub mtrx_BeforeCommit(Cancel As Integer)

'Do multi-form validation code here!!
Dim rst As DAO.Recordset

Set rst = Me.subServiceProvided.Form.Recordset

If rst.BOF And rst.EOF Then
    MsgBox "You must provide at least one service for the client.", vbInformation + vbOKOnly, "Info required."
    Me.TabCtl1 = 1
    Cancel = True
    Exit Sub
End If

...

End Sub

It's a bit unusual compared to functions in that it doesn't return a value, but it does pass paramaters, which is why I have to declare Cancel variable before I could pass it to the event code then check if it has been set or not.

 

[datAdrenaline]

Hello Bannana ...

>> I wonder if this would help if VBA allowed overloading.... <<

It sorta does ... you can create a procedure with the same name as a vba function and do what you want with it ... for example ...

Public Function DLookup(sExpression As String, sDomain As String, Optional sCriteria As String) As Variant
       
    DLookup = Access.DLookup(sExpression, sDomain, sCriteria)
    
End Function

The above fuction will be called in your forms, code, queries, etc, OVER the access function DLookup() ... if you want the specific Access function in your code, then you must disambiguate the call with Access.DLookup(...)

The same is true is you overload a VBA function, it will be called OVER the VBA function within your app ....

Public Function IsNull(varVariant As Variant) As Boolean
    
    IsNull = VBA.IsNull(varVariant)
    
End Function

You have to disambiguate if you want the VBA function ...

Diambiguation is, well challenging in things like control source expressions or column expressions in a query ... I suppose you can do something like this ...

Public Function IsNull(varVariant As Variant, Optional blUseRealOne As Boolean) As Boolean
    
    If blUseRealOne Then 
        IsNull = VBA.IsNull(varVariant)
    Else
        IsNull = <your custom stuff>
    End If
    
End Function

...

 

[datAdrenaline]

Hello Doc ...

>> ..... using functions in place of event procedures is precisely because you are creating "hidden" side effects by doing so. <<

What "hidden" side effects ... after a re-read I don't think I follow that statement.

 

[Banana]

Oh, wow. That's total news to me.

I was pounded in the head that we shouldn't use same names for anything ever or God will descend from the high Heavens and blow his mighty trumpet, unleashing Holy Wrath and setting you ablaze in the descent into Reign of Terror!

That said, I wonder how reliable this is. (I ask only because this is my first time I'm informed of this fact, and I'm suspicious it may be because other MVPs before me already had toyed with it and found it useless or ...???)


Edit: Also a quick test shows that it's not a true overloading in the sense that I cannot overload my own functions: e.g. this is illegal:

Public Function IsTrivial(sString as String) As Boolean

If sString="" Then
   IsTrivial=True
End If

End Function

Public Function IsTrivial(iInteger As Integer) As Boolean

If iInteger=0 Then
   IsTrivial=True
End If

End Function

 

[datAdrenaline]

I have found it useful and reliable with things like MsgBox() to implement my own message box. I have also found it useful with functionality that is not present from version to version, but that can be handled pretty much with conditional compilation also ...

I have also used it to overload DLookup() as I added a 4th argument ... a variant that was intended to accept a DAO.Database, ADODB.Connection, or a connection string (ODBC or OLEDDB) in order to speed up processing time with passthru's or existing db or cnn objects. If the 4th argument was specified, I rolled my own DLookup() using SQL statements, if the 4th argument was not specified I used the Access inherent function.

With my use of the "overloading" I had never experienced ill effects.

 

[Banana]

Thanks for sharing- it's good to know it's been good, though I'm now wondering how come I've not heard about it until just now.

Better late than never, I suppose. This gives me few ideas where I can go with it...

 

[datAdrenaline]

To me ... where this has been "tricky" is when someone has "helped" with development and they do not know that I have overloaded!!! ... they happily go along using my overloaded functions, then I here something like ...

"Stupid Access Help ... it doesn't even list the 4th argument in the help for Dlookup()!!! ... I wish MS would pay more attention to details!"

You see ... the Access help still pops up with the cursor on a overloaded function call, but if you do a right click on a overloaded function call and choose "Definition" you will jump to the overloaded function definition and code, on VBA/Access functions, you will be shot to the object browser.

Also ... another "urban myth" is that you can not have a module and procedure share the same name. Well ... you can, but, you just have to fully qualify the call. This good for obscurity techniques. So you can have a "PUBLIC" procedure that can NOT be called in property expressions, nor is it very visible outside of the application ... or inside for that matter ... but you can still use it, if you fully qualify the call.

For example:

In module "GetUser"

Public Function GetUser() As String
 .... Do stuff and return value ....
End Function

You can only call it with something like ....

MsgBox GetUser.GetUser

So ... since that function requires full qualification, you can't use it in queries, control sources and such, but in VBA, its good to go.... if you know its there.
....

Interesting eh?

 

[Banana]

To me ... where this has been "tricky" is when someone has "helped" with development and they do not know that I have overloaded!!! ... they happily go along using my overloaded functions, then I here something like ...

"Stupid Access Help ... it doesn't even list the 4th argument in the help for Dlookup()!!! ... I wish MS would pay more attention to details!"

Well, to be perfectly fair, this is true of any languages where overloading are supported. It may make thing convenient for the developer but as soon it is inherited or collaborated among developers, does it start to suck giant steel horse balls. I don't know if there's any IDE that provide a visual indicator that an overloaded function is being used, though I know that in VS.NET, they may display a popup listing all overloaded function, but doesn't exactly differentiate from what is built-in. If it had highlighed, say purple, it would be less of a problem, but it is not so.

Also ... another "urban myth" is that you can not have a module and procedure share the same name. Well ... you can, but, you just have to fully qualify the call. This good for obscurity techniques. So you can have a "PUBLIC" procedure that can NOT be called in property expressions, nor is it very visible outside of the application ... or inside for that matter ... but you can still use it, if you fully qualify the call.

For example:

In module "GetUser"

Public Function GetUser() As String
 .... Do stuff and return value ....
End Function

You can only call it with something like ....

MsgBox GetUser.GetUser

So ... since that function requires full qualification, you can't use it in queries, control sources and such, but in VBA, its good to go.... if you know its there.
....

Interesting eh?

Very!!

Interestingly, it could confuse other people into thinking VBA is fully OOP, as I noticed that in some source code, you construct an instance of a class by its name like this: Class.class. I personally don't like that, but that's how they do it, I guess.

 

[Pat Hartman]

"But, Pat, why are you VB adverse?" - It's not that I am so VBA adverse, it is that people who come to Access from a programming background immediately jump in and try to solve problems with VBA that should have been solved with an update query or property setting. I write plenty of VBA. It is simply not where I start and of the three options, VBA requires the most work to code and debug so it is the least desirable.

Another reason why I instictively shy away from using expressions except for the simplest cases is that they are neither here nor there. That makes them extremely difficult for another programmer to discover. If I create a function that will be used in multiple places, I never use it as an expression. I always create a VBA procedure even if the only line in the procedure is the call to the function. This exposes it to discovery.

Someone mentioned overloading and my head almost burst. I believe that that is the most dangerous functionality ever thought up by a bit head on crack. It has shades of the infamous COBOL - Alter goto - for those of you who go back that far. It was a bad idea 40 years ago when all programmers were professionals and it is a worse idea now.

 

[ChrisO]

G’day Banana.

If we assume that there is a difference between an ‘Expression’ and a User Defined Function (UDF) call then it seems to me to break down into two distinct situations.

----------------------------

The first is similar to the control source which will use (display) the return value.
An ‘Expression’ under these circumstances is likely to be SQL based, even if it happens to be a Function like the IIF() function.
When used for something like a control source it behaves differently to a UDF in that an error is not raised on evaluation of its arguments as an ‘Expression’.

If we were to use the IIF() function, as part of a called UDF, then evaluation of its arguments will raise an error.

So, using this example, there is a difference between using the IIF() function directly as an expression in a control source or indirectly in a UDF in VBA.
The former will not raise some errors and the latter will not only raise the error but will also give us the potential to at least report it with some error number and description.
I would think that any Function will behave the same; in VBA there is a possibility of raising an error and reporting it…as an ‘Expression’??????

Kind of makes the ‘Expression’ sound like the use of a Macro…does it not?

----------------------------

The second is when a UDF is used as the ‘Event Procedure’ for an event.

The ‘Event Procedure’ may be a Subroutine as constructed directly by the wizard and called with

[Event Procedure]

or a UDF called directly from the Even Property as in

=SomethingOrOther()

or the event may be switched off completely at runtime with a zero length string (ZLS)

In the case that the ‘Event Procedure’ is user defined it must be a function, even though the return value is not used, else the ‘Event Procedure’ will be called more than once on each event.

The property of the event handler is simply a string (the ‘Event Procedure’ string) that can be modified at runtime, even in an MDE.

The real power of using a user defined ‘Event Procedure’, as the event handler, is that it can be common to many controls and passed the name of the control which instigated the action. (This does not rely on the ‘screen active control’ as that can be a little dubious at times.)

Don’t know if that helps or what?

Regards,
Chris.

 

[ChrisO]

Pat…

>>“I believe that that is the most dangerous functionality ever thought up by a bit head on crack.”<<

Nah, not by a long shot…
I once worked for a company that used the AMD 2901 4 bit slice paralleled up accordingly.
It used external microcode in ROM, below the machine code.
The microcode was defined in house.
One instruction was called ‘Execute Modified Instruction’ and, like other machine code instructions, called in house microcode.

Fundamentally what it did was to execute any binary bit pattern as an instruction even if that bit pattern was data.

I personally would not demean that generation of programmers as ‘a bit head on crack’.
Maybe we could call that technique, ‘something we would have preferred not to do if there was a better alternative at the time’.

But I still think that ‘overloading’, for what it’s worth, could still be a valid technique in VBA.
All that it requires is just like any solution in computing…be the master of the language and not its subservient.

There is a small overload demo at UA that might help others to understand.

Regards,
Chris.

 

[Pat Hartman]

Horrible as it is, there is justification for something like that in microcode where you are severely limited by the amount of space in which your code must execute. There is absolutely no justification for it with modern computers where we just add more and more memory and disk space every year and there was no justification for it in COBOL either. It existed in COBOL because similar functionality existed in machine code upon which all other languages are built.

 

[Pat Hartman]

This is bringing back HORRIBLE memories of a reporting database I fixed for a client last year. The "developer" and I use the term loosely, had a step in several of his macros (yes, everything was macros) that renamed reports because he had no clue how to use arguments. So, I opened the application, found the report that needed changing and ran it. It ran fine. I sent it back and the client called to say it didn't work so he sent the db back. So I ran it from the menu and sure enough it didn't work. So, I opened the report and the changes weren't there so I closed the database and reopened my own copy and sure enough it didn't work. Puzzled, I opened the report I had changed and to my horror discovered that my changes were no longer there. That programmer better hope I never run into him in a dark alley!

 

[ChrisO]

So are we, or were we then, ‘a bit head on crack’?

 

[Banana]

Another reason why I instictively shy away from using expressions except for the simplest cases is that they are neither here nor there. That makes them extremely difficult for another programmer to discover. If I create a function that will be used in multiple places, I never use it as an expression. I always create a VBA procedure even if the only line in the procedure is the call to the function. This exposes it to discovery.

An excellent point. It seems to be more common for any programmers to want to write more codes in less space. Fine, but don't ask me to read it. I find myself gravitating toward language such as Visual Basic and Python because they use plain english words and thus improve the readability of code.

A language that is also self-documenting is a bon, IMO.

Someone mentioned overloading and my head almost burst. I believe that that is the most dangerous functionality ever thought up by a bit head on crack. It has shades of the infamous COBOL - Alter goto - for those of you who go back that far. It was a bad idea 40 years ago when all programmers were professionals and it is a worse idea now.

Well, to be fair, when I first heard of it, I was adamantly against it. Why would in the hell would we want to name two different functions with same name?!? Someone then pointed out a case why this is a good thing when used wisely.

Suppose we needed a function that converts a parameter to a string. Let's unimaginatively name it ToString().

Let us further suppose we wish to be explicit with the parameter being passed to the function. We can then make those functions:

intToString()
dblToString()
boolToString()

We'd be explicit, but also introducing fragility in the code. Suppose we needed a different data type? Now we have to go all over the place to change it to the right one. Overloading allows us to just pass a ToString and let compiler figure out what parameter it is and convert it.

This is better than a variant data type because this is resolved at compile time, not at run time, which gives a performance boost and none of that silly running around with the typecasting that variants are prone to.

So, I am now a bit more sympathetic to overloading, though I will totally agree that they will require 10x the documentation or else it's just confusion galore.

 

[Banana]

G’day Banana.

Chris, as always, thanks for putting in your $.02 (not adjusted for inflation.)

An ‘Expression’ under these circumstances is likely to be SQL based, even if it happens to be a Function like the IIF() function.
When used for something like a control source it behaves differently to a UDF in that an error is not raised on evaluation of its arguments as an ‘Expression’.

Well, it is certainly a good point. But I think it bears noting that in lieu of a full-blown error where we must supply error handling, it will return something like "#Name?". The bugbear is that there is less control because suppose you had a form where an error in evaluating a expression is expected now & then, you're at mercy of "#Name?", "#Deleted" and so forth unless you opt to overload those and supply your own error handling so it comes out with a nicer-looking result such as "N/A" or "No such record."

If we were to use the IIF() function, as part of a called UDF, then evaluation of its arguments will raise an error.

So, using this example, there is a difference between using the IIF() function directly as an expression in a control source or indirectly in a UDF in VBA.
The former will not raise some errors and the latter will not only raise the error but will also give us the potential to at least report it with some error number and description.

Thanks for sharing that observations.

In the case that the ‘Event Procedure’ is user defined it must be a function, even though the return value is not used, else the ‘Event Procedure’ will be called more than once on each event.

Hmm, not sure what make you assert that. I already posted an example of my custom event and how I passed Cancel as a parameter to the BeforeCommit event then checking to see if Cancel had been altered after the RaiseEvent. This isn't quite like a function where you cannot get back any parameters you pass to a function, getting only one return value, and if I'm not mistaken, VBA doesn't quite support INOUT, IN, OUT behaviors as seen in other languages (though, one could imitate this by using ByRef instead of ByVal)

 

[ChrisO]

Banana.

This is starting to be another talk fest…

>>I already posted an example of my custom event and how I passed Cancel as a parameter to the BeforeCommit event then checking to see if Cancel had been altered after the RaiseEvent.<<

Where did you post it…what was called…what event called it…how was it called?

Exact code please, preferably in a downloadable demo.

Regards,
Chris.

 

[doco]

Another reason why I instictively shy away from using expressions except for the simplest cases is that they are neither here nor there. That makes them extremely difficult for another programmer to discover. If I create a function that will be used in multiple places, I never use it as an expression. I always create a VBA procedure even if the only line in the procedure is the call to the function. This exposes it to discovery.

I am trying to understand here. So, what you are saying is if I have a UDF you would not suggest just placing =MyUDF() in the unbound control but, in some triggered event either a form event or a control event you would place calls to that function[ality]?

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
'    for example the control would be a TextBox
'    or place a call to functions that would populate
'    all unbound controls
    MyTextBox_1.Value = MyUDF_1
    ...
    MyTextBox_n.Value = MyUDF_n
 
End Sub

 

[Banana]

This is starting to be another talk fest…

I hope that's not a bad thing?

Where did you post it…what was called…what event called it…how was it called?

Exact code please, preferably in a downloadable demo.

Regards,
Chris.

I posted an example earlier in this thread: post #22

As for demo, I provided a sample in another thread a while ago.

I believe it will run but not work if you open it and use the Access tables, rather the MySQL backend. You still will see how the custom event work anyway.

HTH.