How do I best use the function statement in similar instances? 3

[mrtauntaun]

Hi. I have a long line of code which needs to be run very often. I would like to create a function (if I properly understand how a function statement works, if not please enlighten to perform an equation and return a value. I am taking five different fields, adding them together, and get that value. So:

Forms!generator!Total = Forms!generator!FieldA1-Forms!generator!FieldA2 and so forth until FieldA5

Now, I have Fields A-Y, so this needs to be done multiple times.

On got focus of FieldC, persay, then Forms!generator!FieldC1 etcetcetc

I would like to use a function and write one line of code which I can call to do this for me. Perhaps something like:

Temp=FieldA

Private Function()

Forms!generator!Total=Forms!generator![& Temp &]1-Forms!generator![& Temp &]2 etcetc

Can this work? If so, how? Did I make enough sense here?

Thank you!!!!!

 

[SQLBI]

The following passes 5 values from a form to a function. The values are added together and passed back to a control on the form.

It might not be 100% what you're looking for but the general idea is as per your question.

Public Sub GetValue()

Dim v As Double
Dim w As Double
Dim x As Double
Dim y As Double
Dim z As Double

v = Me.Control1
w = Me.Control2
x = Me.Control3
y = Me.Control4
z = Me.Control5

Me.Control6 = AddValue(v, w, x, y, z)

End Sub


Public Function AddValue(v As Double, w As Double, x As Double, y As Double, z As Double) As Double

v + w + x + y + z

End Function

Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk

 

[Norris68]

You could do it using the following function.

There are some limitations, as I wanted to make it versatile, so that you can pass in any number of parameters. For this I had to use an array. However, to generate an array using the Array function to pass into the function, you can only assign the result to a variant and cannot coerce or convert it into a strongly typed array. Anyway, it works.

Public Function AddArray(Numbers As Variant) As Long
Dim A As Long
Dim Total As Long
If IsArray(Numbers) Then
For A = LBound(Numbers) To UBound(Numbers)
If IsNumeric(Numbers(A)) Then
Total = Total + Numbers(A)
End If
Next A
End If
AddArray = Total
End Function

Then you can call it e.g.
Forms!generator!Total = AddArray(Array(Forms!generator!FieldA1,Forms!generator!FieldA2,Forms!generator!FieldA3))

Here we are creating an array using the Array function e.g. Array(1,2,3,4). Then we pass it into the AddArray function which returns the result e.g. 10.

 

[mrtauntaun]

Thanx guys. Leighmore, can I change where is says Control to something like "& variable control &1", where I can set what control is and have it set before I call the function? This does save me a lot of work, but if I could change the variable control, it would save me more

 

[SQLBI]

I'm not sure if you can use a variable to refer to a controls name, but you can cycle throught the controls on a form using "For Each...", but i'm not 100% sure of how you'd use it in this example.

Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk

 

[NeilRP]

You can use a variable for the control's name. Use the construct
strControlName = "Control1"
x=Me.Controls(strControlName).Value

 

[SQLBI]

Thanks for that Neil, very useful!

Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk