Quick question about functions

[crosmill]

I use VBA a fair bit but I've never had the need to use functions, I have however, used them quite alot in ASP so I'm not unacustomed to them.

My question is, is the proper procedure to create them as modules, and them call them from the code?

Is the syntax to call a module mobuleName()?

Cheers

 

[lynsey2]

what i get from them is if you are going to use the function in a few different places it is best to call it from the code but it really depends what you are doing.

 

[Drevlin]

There is no need to call a module. As long as the function is public inside the module you only need to call the function.

Lets say you have two Modules: Module1 and Module2. Inside Module1 you have a sub and inside Module2 you have a function that the sub needs

Module1
---------------------------------------------------------------------------

Private Sub CallingSub()
Dim x As long
Dim y As long
Dim z As long

x = 5
y = 6
z = AddMe(x, y) 'call to function AddMe in Module2 will return 11
Debug.Print z
End Sub

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

Module2
-----------------------------------------------------------------------------
Public Function AddMe(a As Long, b As Long) As Long
AddMe = a + b
End Function

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


This works without a hitch. If, however, you declare Function AddMe to be Private:

Private Function AddMe(a As Long, b as Long) as Long

Then the code won't work because Module1 can't "see" the function held in module2.

I hope this answers your question.

Peace

 

[crosmill]

OK, I've started to discover that Access functions work a little differnetly to what I'm used to doing.

What I'm doing is setting variables based on user options, and then running the script. So what I want to do is

1: test for the selection
2: set variables
3: run the script

So...

If option1 = true then
x = something
y = somethingElse
Call Function Here
End if

If option2 = true then
x = something2
y = somethingElse2
Call Function Here
End if


funtion name()
x = DoAHandStand
y = DoABackFlip
end function

But obviously the whole point of this is that I only write the function once.

Am I thinking the wrong way??

 

[Drevlin]

It sounds like your thinking correctly. I think your major confusion is the difference between subs and functions. Functions are used to accept information (where necessary) and return information. Subs are meant for running through processes.

The module is just a place to organize subs and functions. In your case it sounds like you would be using a Form's Module. This is a module that is "attached" to the form and is where the form looks to when an action has occurred. Say a button click for Button1 on Form1.

Your module would be:

Form_Form1

In Module Form_Form1 you would have the Sub proceedure called everytime Button1 is Clicked (known as a Click event) and if you wanted your code to occur everytime this click event occurs you would place your code inside this sub:


Form_Form1 Module
-------------------------------------------------------------------------------

Option Compare Database

Private Sub Button1_Click()
If option1 = true then
x = something
y = somethingElse
doActions 'this would be the call to your actions Sub
End if

If option2 = true then
x = something2
y = somethingElse2
doActions
End if

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

You can then have the sub that does the actions in the same Module or you can place it in a new New Module. If these actions will only occur from your form then you can leave it in the Form's module. If these actions need to be called from other forms or proceedures not attached to your form then I would suggest putting it in a different module. It's a good idea to name modules to help you find the function or sub your looking for. I personally create a module to hold my subs and a seperate module to hold my functions. You may organize yours however you see fit. Let's say you wanted your action code to be run throughout your database. Then you would create a new module by going to Insert->Module and then placing your code in there.

Module1
----------------------------------------------------------------------------------

Public Sub doActions()
DoAHandStand
DoABackFlip
End Sub

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

Does that make sense?

 

[crosmill]

Nice one, it's starting to make a bit more sense now.

It' seems to be calling the Sub/Function but it's returning 0 values, not sure why.

Any ideas?

Option Compare Database

Public Sub Command126_Click()
Dim timeStart As Date
Dim timeFinish As Date
Dim Day As Integer
Dim Lunch As Integer
Dim LunchStart As Date
Dim LunchFinish As Date
Dim Total As Integer
Dim Rate As Integer
Dim optionRate As Integer
Dim ErrorMsg

timeStart = Me.Start
timeFinish = Me.Finish
LunchStart = Me.LunchStart
LunchFinish = Me.LunchFinish
optionRate = Me.Frame42
'=============================
If optionRate = 1 Then
If Rates_Subform!Rate1 > 0 Then
Rate = Rates_Subform!Rate1
Me.Rate = Rates_Subform!Rate1
Calculate
Else
ErrorMsg = MsgBox("Warning! No vlaue has been entered for Rate of Pay", vbCritical, "Warning")
End If
End If

If optionRate = 2 Then
If Rates_Subform!Rate2 > 0 Then
Rate = Rates_Subform!Rate2
Me.Rate = Rates_Subform!Rate2
Else
ErrorMsg = MsgBox("Warning! No vlaue has been entered for Rate of Pay", vbCritical, "Warning")
End If
End If

If optionRate = 3 Then
If Rates_Subform!Rate3 > 0 Then
Rate = Rates_Subform!Rate3
Me.Rate = Rates_Subform!Rate3
Else
ErrorMsg = MsgBox("Warning! No vlaue has been entered for Rate of Pay", vbCritical, "Warning")
End If
End If

If optionRate = 4 Then
If Rates_Subform!RateOther > 0 Then
Rate = Rates_Subform!RateOther
Me.Rate = Rates_Subform!RateOther
Else
ErrorMsg = MsgBox("Warning! No vlaue has been entered for Rate of Pay", vbCritical, "Warning")
End If
End If
'===============================


End Sub
'****************************************************
Public Sub Calculate()
Dim timeStart As Date
Dim timeFinish As Date
Dim Day As Integer
Dim Lunch As Integer
Dim LunchStart As Date
Dim LunchFinish As Date
Dim Total As Integer
Dim Rate As Integer
Dim optionRate As Integer
Dim ErrorMsg

Day = DateDiff("n", timeStart, timeFinish)
Lunch = DateDiff("n", LunchStart, LunchFinish)
Total = (Day - Lunch) / 60
TotalHours = Total
Me.SubTotal = Total * Rate

Me.Description = "Pay=£" & Total * Rate & " day=" & Day & " Lunch=" & Lunch
End Sub

 

[Drevlin]

Ok. The problem with your code is that the Calculate sub doesn't have know what values should be in: timeStart, timeFinish, LunchStart, LunchFinish. Because you declared them inside of your click proceedure it holds this information to itself as private and doesn't share it with the Calculate sub.

There are three ways to fix this.

1. Declare the variables as Public, so that no matter which proceedure calls them the information is always the same. (I personally don't recommend)

2. Re-declare the variables in every proceedure. (probably counter productive to having the proceedure seperate in the first place)

3. Make the Calculate proceedure capable of receiving the variables from whatever proceedure calls it.

Let's go with option three.

Like functions, subs can have variables passed to them (they just can't pass variables back).

So to just fix your code lets try this:

Public Sub Command126_Click()
Dim timeStart As Date
Dim timeFinish As Date
Dim Day As Integer
Dim Lunch As Integer
Dim LunchStart As Date
Dim LunchFinish As Date
Dim Total As Integer
Dim Rate As Integer
Dim optionRate As Integer
Dim ErrorMsg

timeStart = Me.Start
timeFinish = Me.Finish
LunchStart = Me.LunchStart
LunchFinish = Me.LunchFinish
optionRate = Me.Frame42
'=============================
If optionRate = 1 Then
If Rates_Subform!Rate1 > 0 Then
Rate = Rates_Subform!Rate1
Me.Rate = Rates_Subform!Rate1
'pass the four variables to the sub.
Calculate timeStart, timeFinish, LunchStart, LunchFinish
Else
ErrorMsg = MsgBox("Warning! No vlaue has been entered for Rate of Pay", vbCritical, "Warning")
End If
End If

If optionRate = 2 Then
If Rates_Subform!Rate2 > 0 Then
Rate = Rates_Subform!Rate2
Me.Rate = Rates_Subform!Rate2
Else
ErrorMsg = MsgBox("Warning! No vlaue has been entered for Rate of Pay", vbCritical, "Warning")
End If
End If

If optionRate = 3 Then
If Rates_Subform!Rate3 > 0 Then
Rate = Rates_Subform!Rate3
Me.Rate = Rates_Subform!Rate3
Else
ErrorMsg = MsgBox("Warning! No vlaue has been entered for Rate of Pay", vbCritical, "Warning")
End If
End If

If optionRate = 4 Then
If Rates_Subform!RateOther > 0 Then
Rate = Rates_Subform!RateOther
Me.Rate = Rates_Subform!RateOther
Else
ErrorMsg = MsgBox("Warning! No vlaue has been entered for Rate of Pay", vbCritical, "Warning")
End If
End If
'===============================


End Sub
'*************************************************
***
Public Sub Calculate(timeStart As Date, timeFinish as Date, _
LunchStart As Date, LunchFinish as Date)
Dim Day As Integer
Dim Lunch As Integer
Dim Total As Integer
Dim Rate As Integer
Dim optionRate As Integer
Dim ErrorMsg

Day = DateDiff("n", timeStart, timeFinish)
Lunch = DateDiff("n", LunchStart, LunchFinish)
Total = (Day - Lunch) / 60
TotalHours = Total
Me.SubTotal = Total * Rate

Me.Description = "Pay=£" & Total * Rate & " day=" & Day & " Lunch=" & Lunch
End Sub


This is just a quick look and fix. Hopefully it works.

 

[crosmill]

Thanks Drevlin.

I couldn't get it to carry the values with the Sub call, it kept telling me it was expecting a Sub or Function call (at which point I screamed at my computer "It IS a Sub call!!!") It works without the attched variables though.
Alas, no matter, I declared the variables as public, and after a bit of tinkering ironed out the creases. I've taken note why it's not a good idea to declare them this way though, and I'll have to be very carefull, it could get very messy.

Thanks for your help

Peace brother.

 

[Drevlin]

See, now it's going to bother me to no end wondering what didn't work. *has tested the code himself and had it work without a problem*

Ah well. I'm glad I was able to help.