View Single Post
 
Old 05-12-2014, 08:53 AM
BobBridges's Avatar
BobBridges BobBridges is offline Windows 7 64bit Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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:
Code:
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.
Attached Files
File Type: xlsm x.xlsm (18.5 KB, 10 views)
Reply With Quote