Passing two Arguments

[hycho]

Hi,

I am trying to pass two arguments in a function, but don't know how to exactly do this in VBA coding. For example, I would like to define the function happy() for color and day. Let me know if there is anything wrong with the coding below:

Function happy([color], [day]) as string
If color = "blue" and day = "Friday" then
happy = good
Else: happy = "Unknown"
End If
End Function

I believe the above codes work. I will try tomorrow. In the meantime, feel free to comment on the style and/or show how this can be done using the "case" statements instead of if/then statements.

Thanks.

 

[Alansidman]

look here
http://www.techonthenet.com/access/functions/advanced/if_then.php
and here
http://www.techonthenet.com/access/functions/advanced/case.php

 

[jdraw]

Function happy(color  [COLOR="Blue"]as String[/COLOR], [day] [COLOR="Blue"]as String[/COLOR] ) as string
If color = "blue" and [[COLOR="Blue"][/COLOR]day[COLOR="Blue"]][/COLOR] = "Friday" then
happy = [COLOR="Blue"]"[/COLOR]good[COLOR="Blue"]"[/COLOR]
Else
 happy = "Unknown"
End If
End Function

I think the above is syntactically OK.

A few questions>

Why do you have color and day with in square brackets? Just curious

Day is a reserved word and it should be bracketed.
see this for more Reserved Words in Access
allenbrowne.com/AppIssueBadWord.html

Perhaps using some other names for these parameters would avoid potential reserved words. pColor pDay??

 

[hycho]

Function happy(color  [COLOR=Blue]as String[/COLOR], [day] [COLOR=Blue]as String[/COLOR] ) as string
If color = "blue" and [day[COLOR=Blue]][/COLOR] = "Friday" then
happy = [COLOR=Blue]"[/COLOR]good[COLOR=Blue]"[/COLOR]
Else
 happy = "Unknown"
End If
End Function

I think the above is syntactically OK.

A few questions>

Why do you have color and day with in square brackets? Just curious

Day is a reserved word and it should be bracketed.
see this for more Reserved Words in Access
allenbrowne.com/AppIssueBadWord.html

Perhaps using some other names for these parameters would avoid potential reserved words. pColor pDay??

Thanks for the syntax/style tips. Typed in the brackets as a force of habits (accustom to calling a function and using brackets for variables in queries).

Is there a reason why you have the two arguments pDay and pColor as strings? I tried running the code as below and it worked.

happy(pColor, pDay) as string

Thanks again.

 

[jdraw]

If you do Not identify the data type of the parameter, it is a Variant by default. It is considered a good practice to explicitly Dim all variables and their data type.

My pColor, pDay was just showing how to avoid (usually) reserved words. The p just indicated parameter to me.
They are Dimmed as strings since your example showed strings.

 

[hycho]

If you do Not identify the data type of the parameter, it is a Variant by default. It is considered a good practice to explicitly Dim all variables and their data type.

My pColor, pDay was just showing how to avoid (usually) reserved words. The p just indicated parameter to me.
They are Dimmed as strings since your example showed strings.

This is good advice. Where do learn these advice from? Just from experience or is there a good textbook?

Thanks again.

 

[jdraw]

Much is from experience - that includes working for years in IT, systems development, standards, database, data dictionary etc.
But a lot of good practices can be found by reading some of the threads on various forums.

If you search within a forum for certain key words, you can find many excellent ideas (you can also find some junk - but comments and responses usually point those out).

Google searching will often get you to some good examples/best practices/techniques.

There are many good tutorials that can show good practices while conveying concepts.
http://www.rogersaccesslibrary.com/forum/topic238.html
http://www.databaseanswers.org/data_models/index.htm
http://www.fontstuff.com/access/index.htm
http://www.accessmvp.com/strive4peace/

There are also many good Access/database videos on Youtube
- authors/presenters like 599CD, TrainSignalOffice, Greg Vimont, Mike Zellers..... there are many.
Some observations:
Everyone learns a little differently. Some by reading, some by doing, some by watching..
You can learn something from everyone if you are open to learning.
Not everything you learn is positive, but it may emphasize how NOT to do something.
Learning is a life long journey...

Any way, good luck with your projects--- and keep an open mind for learning.

 

[hycho]

Thanks jdraw. Very good and thorough advice. I just looked at the first link regarding normalization, and I am like... this is useful since I have only taken one programming/C++ course in my life. Lots to learn.

Thanks again.

 

[NauticalGent]

JDraw, I stumbled across this while I was searching for a similar answer. Question on this: how do you make the second, or any argument for that matter, optional?

 

[pbaldy]

Looks like JDraw is offline. See if this helps:

http://www.cpearson.com/excel/optionalargumentstoprocedures.aspx

 

[NauticalGent]

Thanks Paul,

As fate would have it, is was reading this when you posted. In your opinion, which of the two methods is bettee?

 

[jdraw]

Jeez I take a half hour break and miss all the action.

Here is a link to a function I posted on a different forum showing 4 parameters of which 2 are optional

But Paul has already responded.

 

[NauticalGent]

Thanks JDraw, it's always good to have options! Orange??

 

[Galaxiom]

Another best practice often overlooked, even by experienced developers who should know better, is the declaration of the way the arguments are passed.

Using the example above:

Function happy(ByVal pColor As String, ByVal pDay] As String ) As String

Without that declaration, the parameter is passed as a reference, which is the default in VBA. Any change made to the argument inside the sub or function is reflected back to the code that passed the parameter.

Passing ByRef is a useful technique to return more than one value from a function. Without the declaration, the intent of the original programmer is not clear. Declaring ByVal clearly indicates that the argument is not being used to pass values back.

Note that objects and arrays are always passed ByRef regardless of any declaration so be aware if any changes are made to them in a procedure.

 

[NauticalGent]

Thanks Galaxiom, useful to know. I read about ByVal and ByRef in the link Paul provided and although Chip Pearson did his best to make it easy for thick people like me to understand, it was a little over my head.

The function I am developing simply returns a True or False and then the Sub that called it uses that info to decide which form controls are visible and/or enabled to that user. I looked for some code examples to save me some time and frustration, and I wanted to see if I could figure it out on my own.

I thought I did rather well, but I need to make it dynamic so that if I needed more than one argument to pass, the code will still work.

Really appreciate the input from everybody.

 

[The_Doc_Man]

Gent... just in case you aren't being modest about understanding arguments, there is a very simple difference between "ByVal" and "ByRef" (and of course, "simple" in the same paragraph as ANY description of something inside MS Access is an oxymoron.)

Not every variable can be passed to a function or subroutine ByVal. Complex object structures are too expensive to pass by value because they are not single values. Not to say that a "ByVal" structure can't be done, but in most cases, if you are passing a structure, you do it by reference.

If you are making a read-only function in the sense that you do not want to allow the actual arguments to change, all arguments must include ByVal - which makes a COPY of the value and makes that copy available to you. But since it is a copy, the original variable is not modified by the operations of the sub or function.

If you are working with a really complex structure (such as an application object), you will work "ByRef" - which is a shortcut way of saying "address reference." When you pass an argument by reference, that isn't a COPY of the variable - it is the real and very original thing.

The short answer is: ByVal copies the value. ByRef copies the address. That means that if you call a function with a ByRef argument, it is theoretically possible for the function to do more than just return a function value. Functions and subs that include a ByRef have the opportunity to create a side-effect. That is, a function can return more than just the "face value" of the function by returning something through a ByRef argument. A subroutine can return a value at all.

 

[NauticalGent]

Thanks Doc. For the record there was no modesty in my reply. Very easy to read something on paper, but until I actually plug it in and play with it, I don't get a real understanding of what something is actually doing.

Like my Grandfather used to say to us "You can tell some fools to get out of the rain, others gotta get wet first!"

I always seemed to be one of the wet ones!

 

[NauticalGent]

...(and of course, "simple" in the same paragraph as ANY description of something inside MS Access is an oxymoron.)

Say that again and the truth shall set you free!