writing a vba more than and less than code-CodePudding

I am trying to build a code for a VBA user form.

I am trying to state that if a postal area "BT" and a weight less than or equal to 30 then the cost will be £15.15, there would then be an additional cost for each unit over 30 of 0.65.

If the postal code is anything other than "BT" with a weight less than or equal to 35 then the cost will be £4.73, for each additional unit over 35 it will be 0.25

I have the following but it is not calculating correctly.

Private Sub Weighttb_Change()

    If PostCodecb = "BT" Then
        UKMtb = 15.51   (Weighttb - 30) * 0.65
    ElseIf Weighttb <= 35 And PostCodecb <> "BT" Then
        UKMtb = 4.73   (Weighttb - 35) * 0.25
    Else
        UKMtb = "4.73"
    End If
End Sub

Any help is appreciated

Lisa

CodePudding user response：

I believe you just need to separate the calculation for each postal code.

Also, the second part of the calculation, (Weighttb - 30) * 0.65 will introduce errors if a value below the base is passed, e.g. 20 instead of 30, since it will calculate -10 * 0.65.

See an example below with some tests. Not sure what the units are, so I assume they are whole numbers (integer).

Private Function CalculateCost(ByVal postCode As String, ByVal weight As Integer) As Double

    Select Case postCode
        Case "BT":
            CalculateCost = 15.51   IIf(weight > 30, (weight - 30) * 0.65, 0)
        
        Case Else:
            CalculateCost = 4.73   IIf(weight > 35, (weight - 35) * 0.25, 0)
    End Select
End Function

A few tests to verify the outcome:

Sub T()

    'BT
    Debug.Print "Expected: " & 15.51, "Got: " & CalculateCost("BT", 30)
    Debug.Print "Expected: " & 15.51   5 * 0.65, "Got: " & CalculateCost("BT", 35)
    Debug.Print "Expected: " & 15.51, "Got: " & CalculateCost("BT", 20) 'below base
    
    'Other
    Debug.Print "Expected: " & 4.73, "Got: " & CalculateCost("NBT", 35)
    Debug.Print "Expected: " & 4.73   5 * 0.25, "Got: " & CalculateCost("NBT", 40)
    Debug.Print "Expected: " & 4.73, "Got: " & CalculateCost("NBT", 25) 'below base

End Sub

The outcome:

'Expected: 15.51    Got: 15.51
'Expected: 18.76    Got: 18.76
'Expected: 15.51    Got: 15.51
'Expected: 4.73     Got: 4.73
'Expected: 5.98     Got: 5.98
'Expected: 4.73     Got: 4.73

To call it in your sub:

Private Sub Weighttb_Change()

    UKMtb = CalculateCost(PostCodecb, Weighttb)

End Sub