function formula returning error when inserted in sheet

Hi Guys

I do Need your help regarding this problem

i had created a UDF (public) to read 2 values and it returns some calculated value

Public Function C(p1 As String, p2 As String) As Integer
Dim i, j As Integer
i = Val(p2)
Select Case p1
Case "A":
j = i * 2
Case "B":
j = i * 3
Case "C":
j = i * 4
Default:
j = 0
End Select
C = j
End Function

whn i call this by a macro it works fine

But whn i use it As a Formula in a particular cell like =Functionname(arg1,arg2)

in this case "=C(A2,B2)"
it returns an error

i'm attaching a file with it for ease


thanks in advance

Rename to something other than C ... ie CVAL or some such.

Also note the following:

a) Integer type is whole numbers limited to range -32768 - 32767 ... use Long if you expect to exceed that or Double if you expect to require Decimal places.
(Integers in VBA are coerced under the hood to Longs anyway so there's no memory gain in using Integer)

b) VBA by default is Case Sensitive so be sure you're Select Case is using appropriate Case in criteria etc... if you want to be Case Insensitive either
i) coerce passed values to Upper Case (ie UCase(p1)) and set criteria to be Upper Case also
or
ii) use Option Compare Text at head of module to make the Module case insensitive.

c) Remove the

Please Login or Register  to view this content.

the above serves no real purpose given when initialised the variable j will default to 0... also it is in the wrong position - ie j will be 0 where Case is "C"... you would really put this above the Select Case line if it were needed (ie if default to be other than 0)

d) If you wish i to be Integer type then use:

Please Login or Register  to view this content.

and not

Please Login or Register  to view this content.

given the above in VBA (unlike .NET) is akin to saying:

Please Login or Register  to view this content.