Multi-Return Function

Topic

New Reply

I’m using VBA in Access 97.
I know I saw a way to write a function so that it returns two separate values simultaneously, but now I can’t find it. Can anyone tell me how to do this?

Reply | Quote

Replies

You can write the FUNCTION declaration as:

Public Function intMyFunc(int1, int2, int3, int4, int5) As Integer
int1 = int4 + int5
int2 = int4 – int5
int3 = int4 * int5
intMyFunc = int1 + int2 + int3 + int4 + int5
End Function

And you can demonstrate the function by running this macro:

Sub TESTintMyFunc()
Dim intv1 As Integer
Dim intv2 As Integer
Dim intv3 As Integer
MsgBox intMyFunc(intv1, intv2, intv3, 7, 8)
MsgBox intv1
MsgBox intv2
MsgBox intv3
End Sub

Reply | Quote

I’m writing a second reply separately, because it goes off in a different direction.

I am generally against functions that return values in arguments. I can’t ban it outright, but I’d like to.

I hold that it is a generally good programming practice (programming, not just programming-in-VBA) to write functions rather than subroutines, and to write functions that compose two incoming arguments into one outgoing result.

This is not always achievable, but when it is, it makes for more readable code, I maintain.

When you drill right down to bedrock, the foundation of computing from mathematics and logic consist of taking at most two values and forming a third. Data processing must consist of taking two chunks of data and producing one chunk of information, that’s the essential building block of data processing.

I know that this is not the place to debate esoteric merits of programming, but I’m always interested in trying to translate an unwieldly solution into a series of re-usable procedures. I don’t always do it myself (see Code: strBreakFileString in VB/VBA), but I hold that it’s bad to return values in arguments.

And having said all that, I hope that my posted example CAN help you on your way.

Reply | Quote

I think it is worth pointing out that your function does NOT return more than one value; rather, it returns one value, but also sets some variables which can then be used by the calling procedure. This type of workaround opens up a whole host of potential problems, not the least of which is if error checking needs to be used. I must agree that you are much better off returning only one value from a function, and if two values are needed, many headaches can be avoided by using separate functions.

It is also worth mention that VBA does not have any way to return more than one value directly, although in VB 6 (not VBA), you can return an array which can often yield desired results. I have also seen other workarounds such as concatenating strings and breaking them apart; and other methods to get around this. But, as a very good general rule, you should look hard at your code, and determine if it can be broken down into separate procedures.
[indent]

---

I hold that it is a generally good programming practice (programming, not just programming-in-VBA) to write functions rather than subroutines, and to write functions that compose two incoming arguments into one outgoing result.

This is not always achievable, but when it is, it makes for more readable code, I maintain.

---

[/indent]
I also have some thoughts on this issue, but think they will be better served on their own thread, as opposed to bogging this one down.

Reply | Quote

Actually, you can return an array, but you have to return it in a variant. And as for returning values in arguments, that is one of the purposes of ByRef arguments, and it’s the only way to return values to a custom data type. I don’t generally use the technique, but it does have its uses.

Reply | Quote

Charlotte,
You are of course correct, I had completely missed being able to return an array inside a variant.

ByRef can be very useful, in some situations. I think I suffer some semantics issues, since I tend to think of it more as modifying existing values, as opposed to truly returning a value (with some exceptions); and since it lends itself to abuse, I tend not to use it much, except in those situations where it is clearly warranted.

Thanks for pointing out the error in my previous message, I certainly don’t want to mislead anyone with my advice.

Reply | Quote

Well, it does modify a value; but when you passed in an initialized variable for that very purpose, then modifying a value is what you want it to do.

Reply | Quote

Charlotte,

For me, there are two uses (speaking very practically) of returning arguments with “byref” (apart from the example already posted):

1. You are maintaining code which already does this- probably quite liberally

2. You have to achieve a good result quickly which works well, is maintainable, and which doesn’t cost the customer too much.

My theory is that you have to code largely to the style of the site you are coding for. If you come across code which is horrible, you have to decide what to do with it. If you havce small tweaks- leave it. If you have to add small bits- try to code in a way which is consistent with the style and standards of where you are coding. If you have wholesale changes, then it may be better to to rewrite. But even then, don’t go outside the boundaries of the org you are working for.

I’ve had horrendous code recently I’ve had to work with. I’ve been extremely tempted to reorganise code- but I’ve (mostly) held back- except where I’ve had to understand, and extensively modify, particular routines which are difficult to understand. If I can simplify it, and can test it properly, then I figure there’s a lot of benefit in making sure my new code works OK. Otherwise I leave well alone.

Sorry, I’m going beyond the question. I’m talking much more generically than passing parameters. BUt I hope my straying off the topic is worth the contribution.

I have a very particular reason for preferring to

Reply | Quote

>Sorry, I’m going beyond the question. I’m talking much more generically than passing parameters. BUt I hope my straying off the topic is worth the
contribution.

[soapbox]

I’m with you 110%. It is my opinion that too much time is spent correcting faulty or poorly-written code, and not enough attention is paid to sound principles.

It’s my awareness of my in-grown standards that gets me to post here. I can’t REALLY believe I’m the best programmer around, and until I know I am, i’d better start writing better code.

Of the programming code I’ve seen over the years (mine included) we are all better off to stand back, or to take a look from a slightly higher level.

Reply | Quote

To All,
Thank you much for all the input. I thought that one of my problems was with understanding the terminology. I see now that I’ll continue to be confused until I get a good handle on the variant term usages within the microsoft applications (can’t they just give the same name to the same thing?) But, its also good to learn from your descriptions that I have probably been doing things pretty close to a “proper” way.
The idea of passing objects to a function, rather than just data values, hadn’t occurred to me. I’ll have to reexamine some of my projects. This could lead to wonderful things.

Chris,
Thanks for the book suggestion. I found it locally, I’ll pick it up later today. Which reminds me, as long as I’m reading one book,

Charlotte,
(I know this is way off the thread, but:) I was checking online database relationship examples. Some of these are more intertwined than a hill-billy family tree. Can you recommend something to straighten this out for me? (I mean a book, I already have a hammer and a shotgun.) I’d really appreciate it. Thanks.

Reply | Quote

You won’t understand complex relationship diagrams until you have internalized the principles of relational design. Then you’ll understand the relationships, but the diagrams will be as intertwined and difficult to read as ever. I can’t think of a really useful reference book off hand, although O’Reilly publishes one called Access Database Design and Programming that has lots of good basics in it.

Reply | Quote

Thank you very much Charlotte. Internalizing the principles is exactly what I need to do. I know I’ll never come close to realizing my own potential, or a database’s without that. Seeing the how and why of other people’s designs helps a lot, but I want to learn faster than I can by just dissecting the few Dbs I get my hands on. Good books make more of a difference to me than most classes I can take. And of course, people like you and the others here at Woody’s to point me in the right direction are always welcome.

Reply | Quote

> point me in the right direction

Heck, Bill! Woody’s Loungers can point y’all in SEVERAL right directions all at once! (vbg!)

Another way of saying that would be to note that we often have different ideas, none of which can really be said to be right or wrong. I don’t expect each bit of feedback here to radically change my course, but the currents exert a tug on me sometimes as I sway poetic in the tidal surge of knowledge …..

Reply | Quote

Thanks Chris, that was just what I needed.
But if this isn’t the place to discuss the merits of various programming techniques, tell me where it is. I’ll go there. I’m new at this and try to do things in a logical manner. But not being aware of all the possibilities means that my logic may be faulty or limiting. (One of the many problems of teaching yourself as you go.)
I’ve been using Functions for generic procedures. As in your strBreakFileString, where I can give it a value (usually 1) and it returns an answer (also usually 1). Just recently my Access 97 behind-the-form code has become unwieldy and I’ve taken to breaking it down into private functions all within the same module. (I get too confused with more than 4 End Ifs at the bottom.) I was thinking of these as subprocedures, as they perform very specific procedures related exclusively to the given form and cannot be made “generic”. This two value return function will be used in a BTF function to help deal with form operations. I thought that it would be better to have less code and have a single function return two values rather than duplicate a lot of the code to make two separate functions. But, once again, I don’t know a lot about the programming. How many lines of code does it take to make something run slower? What are the proper ways to split procedures? What is the definition of the difference between functions and subprocedures anyway?
I should probably go buy a book, huh?

Sorry to ramble on so. Just a case of the empty tin rattling loudest, I guess.

Reply | Quote

> programming techniques, tell me where it is. I’ll go

Let’s stay here until we get kicked out, eh?

> (One of the many problems of teaching yourself as

Which is why I like a peer-to-peer conference like this. i develop code in isolation, and can only benefit from feedback/criticism.

> your strBreakFileString, where I can give it a value

Actually you give it two values – a string of characters and an integer number, and it should return a string of characters. But I think we know what we mean ….

> breaking it down into private functions all within the

I’ve pretty well abandoned PRIVATE functions in Word97/VBA. I wrote that way at first, then discovered that the functions were more useful than I’d thought, moved them into another module, and the original module couldn’t see them! Now I use PUBLIC all the time, unless I have a VERY good reason for making them private.

> too confused with more than 4 End Ifs at the bottom.)

I read years ago that the human mind can cope with only 7 ideas at any one time. If four of those are assigned to nested IFs, one to food, one to shelter and one to rest, it doesn’t leave room for anything else (grin!), so I try to limit mine to 3 levels. I’m not always successful, but when I go above 3, I know I’m skating on sin advice.

> cannot be made “generic”.

Why not? I’d like to suggest that if you had a second form, similar but not identical in function and nature, then these functions might apply to that form too.

I will be posting a set of functions called Fun-For-All, symbolised as Fun4X which do strange and esoteric things to any GUI form whatsoever, dynamically, on-the-fly, in real time.

> to have less code and have a single function return two > values rather than duplicate a lot of the code

Always open to debate, and it’s worth listening to both sides of any argument, but remember one thing: I’m *always* right (grin!)

> How many lines of code does it take to make something run slower?

This can be measured, but then you must also measure how much longer it takes to re-write similar code to that embedded in the first larger procedure; and test that code again. And read it and so on. Writing and Maintaining code is usually the biggest cost. Of course, the very lowest-level routine could be worth optimizing, but it usually payes to wait and see how often that code is used before we can determine the value of speeding it up.

And especially with GUI forms, which suggests response times of seconds while the user wonders what to do next.

>difference between functions and subprocedures anyway?

VBA has SUBroutines and FUNCTIONS. These are known as procedures. A procedure is either a SUB or a FUNCTIOn (in VBA). A SUB with no parameters appears as a Macro to the user. Functions are the best sort of all, they return values.

> I should probably go buy a book, huh?

If you can dig up a copy of Gerald Weinberg’s “The Psychology of Computer programming” Lib Congress 72-165813 ISBN 0-442-29264-3 you’ll probably enjoy it.

> empty tin rattling loudest, I guess.

I resemble that remark!

Reply | Quote

If you’re programming in Access, you’re going to get confused by some of this discussion because Access VBA is exactly the opposite of the other Office apps.

In Access VBA, you can only call Functions from the interface, although you can call Subs from within code routines–the reverse from Word, Excel, etc. Form modules are also special cases, since they’re actually class modules rather than standard modules.

Class modules follow a slightly different set of rules from standard modules because everything in a class module either a property or method of the form. Any module level variables you declare in a class module are actually properties of the class, and it isn’t a good idea to make them public unless you absolutely have to. Form module routines (methods) are private by default, again because that is in keeping with good class design.

With classes, you often use private module level variables to pass values between various methods in the class, so you could also take that approach within your form module. It’s kosher in a class module, but questionable elsewhere.

And, by the way, it isn’t hard to build standard, generic routines to handle tasks for forms, you just have to pass the form as an object to the routine or at least pass a control as an object. With that, the routine can figure out which form you’re using and do things like find a record in the form’s recordset, or save a record, or print, or whatever.

Reply | Quote

>it isn’t hard to build standard, generic routines to handle tasks for forms, you just have to pass the form as an object to the routine

… and we’re going to see an example of this when i post my Fun4X code.

Reply | Quote