Standard Code Modules

[3239]

I am a beginner when it comes to creating functions in code modules. When ever I create function in a new module and use Call to invoke the function , I always get an error message. I know that I am doing something wrong but I don't know what. Could anyone let me know what steps to take from beginning to end when it comes to creating modules and using them through out the access application.

 

[SkipVought]

Hi,

If you are creating a function, it is returning a VALUE, and consequently is used like this...

x = MyFunction(parm1)

If you are creating a subroutine, then you can CALL it list this...

Call MySub parm1
'or simply
MySub parm1

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[xlbo]

nothing to do with the module - more likely to do with parameters and Public / Private statements

If the function starts

Private Function.......

then it can only be called from a procedure that exists in the same module

If the function has parameters eg

Function Test_Func(Str as string) as Variant

then you must pass the parameter to the function like

Call Test_Func(myStr)

Other than that, I'd need more info to give any advice

Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question

 

[3239]

I have been trying to create a function that will calculate the [Number of vacation days earned] - [the number used].


I also need a little advise. What is the best way to learn how to program in Access and do you know of any resources that will be helpful in learning how to program in Access.

Thanks guys for your help.

 

[fumei]

I think you need to grasp programming in general, as a start.

For example, while stated in the above posts, I do not think it was totally apparent (even though Master Skipji did mention Value) the fundamental difference bwteen Functions and Subs.

Functions can use many pieces of logic, and even call other functions or procedures in that processing, but the bottom line is that functions only return ONE value. That is, after whatever goes on behind the scenes within a function, the end result is a single value assigned to the function name.

Function AllAboutMe(byRef blahblah as Long, byVal MoreBlah as String) as Boolean
...lines of code
...more lines of code
,,,even more
AllAboutMe = False
End Function

It only returns a single value.

xlbo - according to Help

"If you use either Call syntax to call any intrinsic or user-defined function, the function's return value is discarded."

I have not been using Call for functions. Should I be????? I can understand possible circumstances where a discarded return may be in the works, but yourpost seems to suggest that Call is normally used. Could you please clarify?

Subs, on the other hand are sequential instructions. They can return multiple results to various things along the way of those instructions.

The nest way to learn is to do it. record macros. Look at them in the Visual Basic Editor. learn how to move around easily in the editor. Explore Help a LOT. Explore here a LOT. Copy code and try to run it yourself. Make LOTS of comments, even if it evern second line. Try and comment each step so that you can walk through each step. All this stuff is built one step at a time.

WEhen you got enough like minded steps, then you chunkl them togetehr into a procedure (or a function) and then call that chunk. But it starts with the little bits inside those chunks.

Good luck! You can do it, it just takes time, a perhaps a little patience.

Gerry

 

[SkipVought]

A real simple example

Function TheDifferenceIs(a as integer, b as integer) as integer
  TheDifferenceIs = a - b
End Function

and here's how I'd use it

Sub MyMain()
  Dim a As Integer, b As Integer
  a = 4
  b = 1
  MsgBox "The Difference between " & a & " and " & b & " is " & TheDifferenceIs(a, b)
End Sub

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[xlbo]

Gerry - apologies I was not more specific. I generally use CALL when using Functions with parameters as it forces me to remember to pass the parameters. As far as I know, if you use Call, it forces the use of arguments within parenthesese (multiple of parenthesis anyone ?? ) so that I actually remember to pass them.

If my function has no parenthesese, I would not use Call.

In this instance I thought that one of the issues might've been a function that needed an argument passing to it and adding Call to the function should highlight that as it will throw up a specific and traceable error

In terms of Functions vs Subs, bear in mind also that functions can be used as "disguised" subs - they do not HAVE to return a value. I have utilised this in some projects to stop subs appearing in the macro list in excel. Whilst this can be achieved using the PRIVATE keyword, this brings issues of it's own when passing variables between standard and sheet modules which the function does not - private or public, it won't be in the list....

Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question

 

[fumei]

Thanks Geoff, that helps me a lot. It confirms what I have been doing as well. I also use Call to force myself to remember the arguments properly. And yes, Call requires arguments with parentheses. I do not use Call if there are no arguments.

oh, and the plural of parenthesis is in fact parentheses.

Gerry