Excel VBA Function with Multiple Conditions

[cob2020]

I have a product template in Excel, which I have reduced for this question. My business unit uses it to onboard products in a database. I would like some help in creating a vba function that I can assign a button to trigger. This is a screenshot of the template:

Basically, I want the VBA code to assign the Pkg_CD to the product based on the following conditions, which are rows filled by users:

A - Has Haz_ID AND Weight is < 66
B - Has Haz_ID AND Weight is > 66 OR
any of the Length, Width, Height is > 108 OR
Weight is > 150
C - No Haz_ID and <150
If there is not enough information then blank Pkg_CD

I am not versed in VBA and have no idea where to start or what would be the best way in doing this... so thx in advance if help is provided, or if I'm pointed to another post where something similar already was asked and answered.

 

[rmwitt]

i like functions.
try:
Function Package(ByRef Haz_id As String, ByRef weight As Integer, ByRef length As Integer, ByRef width As Integer, ByRef height As Integer) As String
Dim result As String

If Haz_id <> "" And weight < 66 Then
result = "A"
ElseIf (Haz_id <> "" And weight >= 66) Or length > 108 Or width > 108 Or height > 108 Or weight > 150 Then
result = "B"

ElseIf Haz_id = "" And weight < 150 Then
result = "C"
Else
result = ""
End If

Package = result

End Function

works for the most part; however, all integers blanks result in zero, so haz_id is not empty and weight < 66 will result in A, when weight is zero. this may not be something that you want.

 

[cob2020]

Thx rmwitt... you have given me something to work on... I should be able to tweak it until I get the desire results.

 

[cob2020]

Here is the final block of vba code... it handles blanks and 0s... I'm passing the code to be used with an array and then triggered from an addin button. Thx so much rmwitt.

Public Function Package(ByVal Haz_ID As String, _
    Optional ByVal weight As Double = -1, _
    Optional ByVal length As Double = -1, _
    Optional ByVal width As Double = -1, _
    Optional ByVal height As Double = -1) As String

    Dim result As String

                    If Haz_ID <> "" And weight < 66 And weight > 0 Then
                    result = "A"
                    ElseIf (Haz_ID <> "" And weight >= 66) Or length > 108 Or width > 108 Or height > 108 Or weight >= 150 Then
                    result = "B"
                    ElseIf Haz_ID = "" And weight < 150 Then
                    result = "C"
                    Else
                    result = ""
                    End If

    Package = result
End Function