Module variables don't work

D

DJJ

Can anybody tell me why this code does not work as a Public Function when
using declared variables but will work when explicitly referencing the
actual controls on the form (I would rather use the variables as this
function is called in several sub procedures)?

Private Sub txtLength_AfterUpdate()

str1 = Nz(Me.txtMetalType.Value, "")
dbl2 = Nz(Me.txtPlatWeight, 0)
dbl3 = Nz(Me.txtGoldWeight, 0)
dbl4 = Nz(Me.txtOtherWeight, 0)
dbl5 = Nz(Me.txtLength, 0)
dbl6 = Nz(Me.txtCastWeight, 0)

Call MetalWeight(str1, dbl2, dbl3, dbl4, dbl5, dbl6)

End Sub

Public Function MetalWeight(strMetal As String) ',dblPlatWt As Double, _
'dblGoldWt As Double, dblOtherWt As Double, dblLength As Double, dblCastWt
As Double)


Select Case strMetal

Case "PL"
dblPlatWt = FormatNumber(dblLength * dblCastWt, 2)


Case "B8", "G4", "G8", "R4", "R8", "W4", "W8", "W8L", _
"Y1", "Y2", "Y20", "Y22", "Y24", "Y4", "Y8", "Y9"
dblGoldWt = FormatNumber(dblLength * dblCastWt, 2)


Case "SBD", "SBD8", "SBL", "SBS", "SKB", "SY8", "Y4S", "Y8S"
dblOtherWt = FormatNumber(dblLength * dblCastWt, 2)

End Select

End Function


Thx
DJ
 
K

Klatuu

There are several things wrong here.

You are calling the function with multiple arguments:

Call MetalWeight(str1, dbl2, dbl3, dbl4, dbl5, dbl6)

But the function is only expecting one argument:

Public Function MetalWeight(strMetal As String) ',dblPlatWt As Double, _
'dblGoldWt As Double, dblOtherWt As Double, dblLength As Double, dblCastWt
As Double)

Is should be:

Public Function MetalWeight(strMetal As String,dblPlatWt As Double, _
'dblGoldWt As Double, dblOtherWt As Double, dblLength As Double, dblCastWt
As Double)

The function is not cast as any data type, so it will return a Variant.

Also, no where in the function MetalWeight do you return a value.

After the End Select, you need something like

MetalWeight = dblOtherWt

Or, in the Case statements, you could just use:

= FormatNumber(dblLength * dblCastWt, 2)

But since you are returning the exact same formula in every case, why even
bother with the function or the Select Case at all?

But, your varialbe naming is suspusious. A variable name prefixed with dbl
is commonly used with a Double data type; however, you are casting dblOtherWt
as a String variable, which I would expect to be named strOtherWt. The
prefix has noting to do with the data type, it is only useful when reading
the code to know what you are dealing with.

Although not required, you don't have Option Explicit in your module. That
means you could easily misspell an undeclared variable with the wrong name
and you wouldn't get what you expect.

Since you don't declare your varialbes, they are all Variants. Variants
have a lot of overhead and will degrade performance.

A function doesn't need to be declared Public unless it will be called from
outside the module it is in. It is always better to declare a Public
Function in a standard module. If the function will be called from multiple
forms, reports, or queries, it should be Public and in a standard module.

You should declare and type your varialbe names.
 
D

DJJ

I don't understand - Like I said this code works fine when I substitute the
variables for the actual fields that contain the data. The variables are
declared I just did not include that code. There is a typo in the beginning
function statement which you corrected. Its not that formula that changes
but the choice of fields where the value ends up that changes that's why
there is a case statement. I can get ride of the module it is not
important.

This following code DOES work but I would rather use variables instead of
specifying the field names. I would like to know how to do that. It is
very important for the rest of the project. Thanks.

Public Function MetalWeight(strMetal As String)

Select Case strMetal

Case "PL"
Forms!sfrmWireTubing!txtPlatWeight.Value = _
FormatNumber(Forms!sfrmWireTubing!txtLength * _
Forms!sfrmWireTubing!txtCastWeight, 2)


Case "B8", "G4", "G8", "R4", "R8", "W4", "W8", "W8L", _
"Y1", "Y2", "Y20", "Y22", "Y24", "Y4", "Y8", "Y9"
Forms!sfrmWireTubing!txtGoldWeight.Value = _
FormatNumber(Forms!sfrmWireTubing!txtLength * _
Forms!sfrmWireTubing!txtCastWeight, 2)

Case "SBD", "SBD8", "SBL", "SBS", "SKB", "SY8", "Y4S", "Y8S"
Forms!sfrmWireTubing!txtOtherWeight.Value = _
FormatNumber(Forms!sfrmWireTubing!txtLength * _
Forms!sfrmWireTubing!txtCastWeight, 2)


Private Sub txtLength_AfterUpdate()

dim str1 as String

str1 = Nz(Me.txtMetalType.Value, "")

MetalWeight (str1)

End Sub
 
K

Klatuu

Okay, got it. The problem is that a function can return only one value.
Since you are wanting to populate a specific field based on you select
statement, you will have do it like you are doing it.
 
Top