How can i create a UDF in VBA with an If And statement using both numerical and text values?

Question

I have a table consisting of 3 different categories : MainLand, Island, City ; each one has 2 different possible values where one is assigned to them depending on 'weight'. If 'Weight' <= 2kg then our output, 'Cost', is eg. 1.2 (for Mainland or 1.3 for island etc). If 'weight' >2kg , we need to round it to the nearest integer , then calculate 0.3(for mainland or 0.9 for island etc) times the extra integers above 2kg. The cost then will be the original value for the first 2kg and then add up the decimal of each area times the extra integers . I tried creating my own function to select 3 cells and do the calculation since it seemed too complex for linear functions given from excell.

But I always get an error...

Note all variable values are from user-selected cells and the Location value simply checks which text there is inside the cell to assign the proper numbers for calculation.

Thats what I have so far... Any tips?

Function TotalCost(ByVal tmx As Integer, ByVal weight As Double, _
                                            ByVal Location As Text)
      Dim b As Integer
      Dim c As Integer
      Dim d As Integer

      d = 0
      c = 0
      f = 0
    
      If Location Like "Mainland" And weight <= 2 Then
          TotalCost = 1.2
      ElseIf Location Like "Mainland" And weight > 2 Then

          weight = Round(weight, 0)
          c = weight - 2

          Do While c > 0
              c = c - 1
              d = d + 1
          Loop

          TotalCost = tmx * ((d * 9.55) + 1.2)
        
      ElseIf Location Like "City" And weight <= 2 Then

          TotalCost = 1.1
     
      ElseIf Location Like "City" And weight > 2 Then

           weight = Round(weight, 0)
           c = weight - 2

           Do While c > 0
               c = c - 1
               d = d + 1
           Loop

           TotalCost= tmx * ((d * 0.55) + 1.1)
                    
       ElseIf Location Like "Island" And weight <= 2 Then

           TotalCost = 1.3
     
       ElseIf Location Like "Island" And weight > 2 Then

           weight = Round(weight, 0)
           c = weight - 2
           Do While c > 0
               c = c - 1
               d = d + 1
           Loop
           TotalCost= tmx * ((d * 0.7) + 1.3)
    
       End If

End Function

Answer 1

You can do your calculation with a relatively simple formula but it needs a little preparation, some of which is the same you will need for the UDF as well. Here is the formula. I'll explain it in detail below.

=VALUE(INDEX(Rates,1,MATCH($F$2,$B$1:$D$1))+(INDEX(Rates,2,MATCH($F$2,$B$1:$D$1))*(-INT($G$2/-2)-1)))*INT(MAX($H$2,1))

First, the setup.

1. I placed the table you published in your question into a range A1:D3.
2. I created a named range for the numbers, B1:D3, and called it Rates
3. Then I created a Validation drop-down in F2 which refers to the range B1:D1 ("City", "Mainland" "Islands")
4. I marked cell G2 for entering the Weight
5. And H2 for entering the quantity of parcels.

Next the preparations.

• [G6] =MATCH(F2,B1:D1) returns 1, 2 or 3 depending upon what is selected in F2
• [G7] =(-INT(G2/-2)-1) returns the number of surcharges for extra weight, like 0 for anything up to 2(kg), 1 for 2.1 to 4.0, 2 for 2.1 to 6.0 etc.
• [G5] =INT(MAX(H2,1)) All of these formulas must be tested individually by checking their results while F2:H2 are being changed. (I did that.) That leads to the basic formula.

=INDEX(Rates,1,G6)+(INDEX(Rates,2,G6)*G7)

and, because it's a string,

=VALUE(INDEX(Rates,1,G6)+(INDEX(Rates,2,G6)*G7))

and, to multiply with the number of parcels,

=VALUE(INDEX(Rates,1,G6)+(INDEX(Rates,2,G6)*G7))*G5

Now all that remains to be done is to replace G5, G6 and G7 in the above with the formulas in G5, G6 and G7. Then add $ signs to enable copying of the formula and you get the result first above offered.

Answer 2

Something more like this might be better:

Function TotalCost(ByVal tmx As Integer, ByVal weight As Double, _
                                            ByVal Location As String)
    
    Dim base As Double, mult As Double, tot As Double, wtExtra As Double
    
    Select Case Location
        Case "Mainland"
            base = 1.2
            mult = 9.55
        Case "City"
            base = 1.1
            mult = 0.55
        Case "Island"
            base = 1.3
            mult = 0.7
        Case Else
            TotalCost = "?Location?"
            Exit Function
    End Select
    
    If weight > 2 Then
        'round up to nearest kg and subtract 2
        wtExtra = Application.Ceiling(weight, 1) - 2
        TotalCost = tmx * ((wtExtra * mult) + base)
    Else
        TotalCost = base
    End If
      
End Function