0

I want to create a function which give me what contribution an employee has to make to a pension plan in function of its salary (with given down and upper limit) and in function of its age (doesn't contribute before 25 and after 64).

With the following code, I try to achieve that. I may have spend too much time on front of my screen but i don't see the problem. The debugger runs fine but I keep getting a "#Value!" alert. It may be the If-statment within another If. If someone may see where the problem could be, it would be very helpful 

Option Explicit
Constant M = 1175

Function coti_min_LPP(x As Integer, salaire_AVS As Double)

Dim salaire_coord As Double

'The basis for the calculation uses the salary in different layers
If salaire_AVS < 18 * M Then 
salaire_coord = 0
ElseIf salaire_AVS > 18 * M And salaire_AVS < 24 * M Then
salaire_coord = 3 * M
ElseIf salaire_AVS > 24 * M Then
salaire_coord = salaire_AVS - 21 * M
End If

'Under a certain limit, the contribution is a percentage of the basis 
'according to the age
If salaire_AVS < 72 * M Then
If x < 25 Then
coti_min_LPP = 0
ElseIf x > 24 And x < 35 Then
coti_min_LPP = salaire_coord * 0.07
ElseIf x > 34 And x < 45 Then
coti_min_LPP = salaire_coord * 0.1
ElseIf x > 44 And x < 55 Then
coti_min_LPP = salaire_coord * 0.15
ElseIf x > 54 And x < 65 Then
coti_min_LPP = salaire_coord * 0.18
ElseIf x > 64 Then
coti_min_LPP = 0
End If

Else
'Above the limit, it is the percentage of a fixed amount
If x < 25 Then
coti_min_LPP = 0
ElseIf x > 24 And x < 35 Then
coti_min_LPP = 51 * M * 0.07
ElseIf x > 34 And x < 45 Then
coti_min_LPP = 51 * M * 0.1
ElseIf x > 44 And x < 55 Then
coti_min_LPP = 51 * M * 0.15
ElseIf x > 54 And x < 65 Then
coti_min_LPP = 51 * M * 0.19
ElseIf x > 64 Then
coti_min_LPP = 0
End If

End If

End Function
Community
  • 1
  • 1
endlessend2525
  • 11
  • 1
  • 2
  • 7

1 Answers1

2

There are two immediate things that result in an issue:

  • Instead of Constant M = 1175 you should use Private Const M As Long = 1175. Why Long? Please look at the line If salaire_AVS < 72 * M Then if you add watch and go through your code in the debbuger you will find out that, if M is declared as an integer, after this line (according to watch window) M has value "out of context". This is basically due to the fact that you have achieved integer overflow. Please consider following code:

Example 1:

Sub test2()

Dim a As Integer
Dim b As Double

a = 1175
b = 15 

Debug.Print b < 72 * a

End Sub

After above changes your function works for me (tested =coti_min_LPP(27,19*1175))

Hope that helps. Btw. Please notice that having so many nested ifs with hardcoded values is troublesome to maintain, control and usually is not considered as good coding practice.

TomJohn
  • 747
  • 6
  • 19
  • 1
    And consider long over integer – QHarr Feb 10 '18 at 13:39
  • Thanks for your answer. It still doesn't work for my change the Constant to Const and deleting the Option Explicit. I've learnt in college that Option Explicit was always the best practice choice, not really understanding why. – endlessend2525 Feb 10 '18 at 13:39
  • @QHarr Thanks for your input. So you would, for any "numeric" variables, always use Long ? Does it have sth to do with computation time ? – endlessend2525 Feb 10 '18 at 13:41
  • I write =coti_min_LPP(27; 19*1175) (Using the "Swiss" way of entering variables, if it may be the source of the problem) – endlessend2525 Feb 10 '18 at 13:44
  • I have edited my answer. Please try to change your const declaration. – TomJohn Feb 10 '18 at 13:45
  • It is I believe more efficient and avoid the risk of overflow. https://www.pcreview.co.uk/threads/vba-code-optimization-why-using-long-instead-of-integer.3606971/ and https://stackoverflow.com/questions/26717148/integer-vs-long-confusion – QHarr Feb 10 '18 at 13:47
  • This explains better than I can https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long/26409520#26409520 – QHarr Feb 10 '18 at 13:52
  • @QHarr +1 - edited my answer changing Double to Long :) – TomJohn Feb 10 '18 at 14:13
  • @TomJohnRiddle It is very very helpful. Thanks for your help and time. I feel a bit more VBA-able than before ! :) – endlessend2525 Feb 11 '18 at 16:35
  • Glad that it works for you. Please remember to mark answer as accepted if it solves your problem. – TomJohn Feb 11 '18 at 16:47