How Do you Put Optional Parameters in UDF the Correct Way?

[VBE313]

In the following code, I can not seem to calculate my UDF correctly, am I putting the optional things incorrectly?

Function weldMinutes(numOfPIA, simpleOrComplex, antiSplatterYorN, fixtureYorN, numOfTack, weldType1, GMAWorGTAW1, weldLength1, numOfWeldStarts1, Optional weldType2 As Long, Optional GMAWorGTAW2 As String, Optional weldLength2 As Long, Optional numOfWeldStarts2 As Long, Optional weldType3 As Long, Optional GMAWorGTAW3 As String, Optional weldLength3 As Long, Optional numOfWeldStarts3 As Long)
 Dim procEff As Double, secsPerInch1 As Double, secsPerInch2 As Double, secsPerInch3 As Double, secsPerStartStop As Double, partHandlingTime As Double, antiSplatterApplication As Double, antiSplatterHandling As Double, antiSplatterSpray As Double, pureWeldMins As Double, startStopMins As Double, handlingTackMins As Double
    If IsMissing(weldType2) Then
        weldType2 = 0
    Else
        weldType2 = weldType2
    End If
    If IsMissing(GMAWorGTAW2) Then
        GMAWorGTAW2 = ""
    Else
        GMAWorGTAW2 = GMAWorGTAW2
    End If
    If IsMissing(weldLength2) Then
        weldLength2 = 0
    Else
        weldLength2 = weldLength2
    End If
    If IsMissing(numOfWeldStarts2) Then
        numOfWeldStarts2 = 0
    Else
        numOfWeldStarts2 = numOfWeldStarts2
    End If
    If IsMissing(weldType3) Then
        weldType3 = 0
    Else
        weldType3 = weldType3
    End If
    If IsMissing(GMAWorGTAW3) Then
        GMAWorGTAW3 = ""
    Else
        GMAWorGTAW3 = GMAWorGTAW3
    End If
    If IsMissing(weldLength3) Then
        weldLength3 = 0
    Else
        weldLength3 = weldLength3
    End If
    If IsMissing(numOfWeldStarts3) Then
        numOfWeldStarts3 = 0
    Else
        numOfWeldStarts3 = numOfWeldStarts3
    End If
    secsPerStartStop = 3
    procEff = 0.2975
    antiSplatterApplication = 0.4167
    antiSplatterHandling = 10
    If numOfTack = 0 Then
        numOfTack = 1.5
    End If
    If weldType1 = 0 And GMAWorGTAW1 = "" Then
        secsPerInch1 = 0
    Else
        secsPerInch1 = WorksheetFunction.VLookup(GMAWorGTAW1 & weldType1, Sheets("Standard").Range("Z4:AD20"), 5, False)
    End If
    If weldType2 = 0 And GMAWorGTAW2 = "" Then
        secsPerInch2 = 0
    Else
        secsPerInch2 = WorksheetFunction.VLookup(GMAWorGTAW2 & weldType2, Sheets("Standard").Range("Z4:AD20"), 5, False)
    End If
    If weldType3 = 0 And GMAWorGTAW3 = "" Then
        secsPerInch3 = 0
    Else
        secsPerInch3 = WorksheetFunction.VLookup(GMAWorGTAW3 & weldType3, Sheets("Standard").Range("Z4:AD20"), 5, False)
    End If
    weldMinutes = (secsPerInch1 + secsPerInch2 + secsPerInch3)
End Function

 

[Fluff]

I've no idea what you are doing, but you can declare the optional arguments like

Optional weldType2 As Long = 0, Optional GMAWorGTAW2 As String = "", Optional weldLength2 As Long = 0,

which means you don't need all this

    If IsMissing(weldType2) Then
        weldType2 = 0
    Else
        weldType2 = weldType2
    End If
    If IsMissing(GMAWorGTAW2) Then
        GMAWorGTAW2 = ""
    Else
        GMAWorGTAW2 = GMAWorGTAW2
    End If
    If IsMissing(weldLength2) Then
        weldLength2 = 0
    Else
        weldLength2 = weldLength2
    End If

Also why are you specifying arguments that you don't even use?

 

[VBE313]

Hi @Fluff ,
thanks for the help! I have my code like this now. It is still giving me #VALUE!

Function weldMinutes(numOfPIA, simpleOrComplex, antiSplatterYorN, fixtureYorN, numOfTack, weldType1, GMAWorGTAW1, weldLength1, numOfWeldStarts1, Optional weldType2 As Long = 0, Optional GMAWorGTAW2 As String = "", Optional weldLength2 As Long = 0, Optional numOfWeldStarts2 As Long = 0, Optional weldType3 As Long = 0, Optional GMAWorGTAW3 As String = "", Optional weldLength3 As Long = 0, Optional numOfWeldStarts3 As Long = 0)
 Dim procEff As Double, secsPerInch1 As Double, secsPerInch2 As Double, secsPerInch3 As Double, secsPerStartStop As Double, partHandlingTime As Double, antiSplatterApplication As Double, antiSplatterHandling As Double, antiSplatterSpray As Double, pureWeldMins As Double, startStopMins As Double, handlingTackMins As Double
    secsPerStartStop = 3
    procEff = 0.2975
    antiSplatterApplication = 0.4167
    antiSplatterHandling = 10
    If numOfTack = 0 Then
        numOfTack = 1.5
    End If
    If weldType1 = 0 And GMAWorGTAW1 = "" Then
        secsPerInch1 = 0
    Else
        secsPerInch1 = WorksheetFunction.VLookup(GMAWorGTAW1 & weldType1, Sheets("Standard").Range("Z4:AD20"), 5, False)
    End If
    If weldType2 = 0 And GMAWorGTAW2 = "" Then
        secsPerInch2 = 0
    Else
        secsPerInch2 = WorksheetFunction.VLookup(GMAWorGTAW2 & weldType2, Sheets("Standard").Range("Z4:AD20"), 5, False)
    End If
    If weldType3 = 0 And GMAWorGTAW3 = "" Then
        secsPerInch3 = 0
    Else
        secsPerInch3 = WorksheetFunction.VLookup(GMAWorGTAW3 & weldType3, Sheets("Standard").Range("Z4:AD20"), 5, False)
    End If
    weldMinutes = (secsPerInch1 + secsPerInch2 + secsPerInch3)
End Function

 

[Fluff]

Please remember that we cannot see your screen, your worksheet, nor can we read your mind.
It would help if you could post a small sample of your sheet (using the XL2BB add-in, not an image) and describe what you are trying to do.

 

[VBE313]

Hi @Fluff

I figured it out, I needed to change all my "Optional as Long" to "Optional as Double" and it worked.

Thanks again!

 

[Fluff]

Glad you sorted it out & thanks for the feedback