Access 2010 VBA Select Case Statement

Question

I am trying to build a function where based on a string comparison use a specific formula

The problem I have is that I get a Byref arguement type mismatch error

It must be something to do with passing the string for the comparison and also the numbers for the formulas. Could someone please take a look and help?

Public Function MyRateCalc(rateType As String, fixedAmount As Long, minAmount As Long, rateDollar As Long, valPerc As Long, rtValue As Long) As Double

    Select Case rateType

        Case "A1"

            MyRateCalc = fixedAmount * valPerc

        Case "A"

            MyRateCalc = rtValue * rateDollar * valPerc

        Case "B", "C", "D", "H", "L", "N", "R"

            MyRateCalc = IIf(rtValue * rateDollar > minAmount, rtValue * rateDollar * valPerc, minAmount * valPerc)

        Case "M", "U", "MS"

            MyRateCalc = rtValue * rateDollar * valPerc

        Case Else

            MyRateCalc = 0

    End Select

End Function

Answer 1

The function itself is fine.

However the problem will be in the way you are calling the function.

In VBA, function arguments are passed by reference (ByRef) by default. This means that the data types in the caller have to be exactly the same as those in the function.

Two choices:

1) Adjust your function to

Public Function MyRateCalc(ByVal rateType As String, ByVal fixedAmount As Long, ByVal minAmount As Long, ByVal rateDollar As Long, ByVal valPerc As Long, ByVal rtValue As Long) As Double

2) Check the caller variable types carefully; they must match exactly.

I prefer approach (1) as it increases program stability (functions cannot unintentionally modify variables in the calling code); even though it's at the expense of a string copy in your case.