udf value not getting refreshed , have to click on each - pls help

[Deepak Kishwar]

í have some value stored in worksheet as below

Name Sum Assuured Age Prem to be paid
Jacobs 300000 35 2879
Steve 400000 24 3686
Amit 400000 46 5954
Deepak 600000 78 15596
Ajay 300000 90 10347
Samuel 400000 55 5954
Tom 300000 65 5422
Russell 400000 72 10023

i have written a udf function as below :


Option Explicit
'Main Function
Function premium(ByVal MyNumber)
Dim Sumass As Long
Dim asage As Long
Dim prem As Long

Sumass = Cells(ActiveCell.Row, 2)
asage = MyNumber
Select Case Sumass
Case 300000
If asage < 36 Then prem = 2879
If asage > 35 And asage < 46 Then prem = 3188
If asage > 45 And asage < 56 Then prem = 4698
If asage > 55 And asage < 66 Then prem = 5422
If asage > 65 And asage < 71 Then prem = 7033
If asage > 70 And asage < 76 Then prem = 7865
If asage > 75 Then prem = 10347

Case 400000
If asage < 36 Then prem = 3686
If asage > 35 And asage < 46 Then prem = 4103
If asage > 45 And asage < 56 Then prem = 5954
If asage > 55 And asage < 66 Then prem = 6950
If asage > 65 And asage < 71 Then prem = 8989
If asage > 70 And asage < 76 Then prem = 10023
If asage > 75 Then prem = 13139


Case 600000
If asage < 36 Then prem = 4285
If asage > 35 And asage < 46 Then prem = 4823
If asage > 45 And asage < 56 Then prem = 7160
If asage > 55 And asage < 66 Then prem = 8393
If asage > 65 And asage < 71 Then prem = 10955
If asage > 70 And asage < 76 Then prem = 11970
If asage > 75 Then prem = 15596

End Select
premium = prem
End Function

in column 4 i have put the value = premium(c3) and dragged to the last row
but it is giving odd result but if i click on each cell and press enter it gives the correct result

i think i'm missing something in the udf, please help

[BobBridges]

I'll tell you what I see, Deepak. When I open your workbook, the first thing Excel does is tell me that editing is disabled. (That's because I downloaded it from the internet; MS Office is protecting me, you see.) I click the button to enable editing, and it then warns me about embedded macros. I select the option to enable the macro, and I see column 4 is all #VALUE. But when I paste R2C4 over R2C4:R18C4, it fills in the values correctly. So far, so good. I don't think that's an error; it was just temporarily confused because the worksheet didn't have macros enabled at first. At least, that's what I surmise.

There is something wrong, though, or sort of wrong. If you change a value in C3, the premium in C4 is updated correctly; but if you change a value in C2, C4 remains unchanged. This is because Excel can see that RC3 is an argument to the call to premium(), so when RC3 changes then RC4 needs to be recalculated. But Excel cannot tell by looking at the arguments to =premium that RC3 is also part of the equation.

You should fix that, and there are two ways. One is to rewrite your UDF so that both RC3 and RC4 are arguments to Premium, like this:

Function Premium(Payout, Age)
  Select Case Payout
    Case 300000: Select Case Age
      Case Is < 36: prem = 2879
      Case Is < 46: prem = 3188
      Case Is < 56: prem = 4698
      Case Is < 66: prem = 5422
      Case Is < 71: prem = 7033
      Case Is < 76: prem = 7865
      Case Else:    prem = 10347
      End Select
    Case 400000: Select Case Age
      Case Is < 36: prem = 3686
      Case Is < 46: prem = 4103
      Case Is < 56: prem = 5954
      Case Is < 66: prem = 6950
      Case Is < 71: prem = 8989
      Case Is < 76: prem = 10023
      Case Else: prem = 13139
      End Select
    Case 600000: Select Case Age
      Case Is < 36: prem = 4285
      Case Is < 46: prem = 4823
      Case Is < 56: prem = 7160
      Case Is < 66: prem = 8393
      Case Is < 71: prem = 10955
      Case Is < 76: prem = 11970
      Case Else: prem = 15596
      End Select
    End Select
  Premium = prem
  End Function

(I don't care whether you use my Case structure or stick to your If statements; I'm just demonstrating that you can use two arguments in the program.) Your call in C4 would then say "=Premium(RC2,RC3)", and the value displayed there would change whenever you changed either C2 or C3.

But the way I'd do it is to set up a table in another sheet and use lookup formulae to find the proper premium on that table. I like programming in VBA, but this looks to me like a case for a table, instead. Take a look at the sample I've uploaded. There are lots of ways to do the lookup—and by the way if you ask macropod he'll probably come up with a better way than the one in my example, he's a whiz at that sort of thing—but I think that for this sort of application, this is better than writing a UDF.

[Deepak Kishwar]

Thanx Bob

I have already modified my VBA code after posting the problem with passing two variables to the function and that worked well

but the example provided by you seems to be a interesting one and would defnitely try that out.


Thanx a lot for your time and response