I
Inkel
Hi,
I made this and it's one of my first VBA code and i have to say that
i'm a
bit proud of myself because it works !
But i know it lacks experience and optimisation. I know that it could
be
shorter but how. Could someone help me ?
The use of it is calculating the need to pay or not and how much,
transportation for employee with their own car. I joined a picture of
the
sheet to help.
Thanks.
Inkel ;-)
+++++++++++++++++++++++++++++++++++++++++++++++++++
Function vehicule(code, distance)
'Frais de déplacement : Axx = distance, FAxx = montant
'Sheets("Frais").Visible = True
billet = False
A60 = Sheets("Frais").Range("c7")
A91 = Sheets("Frais").Range("c8")
A121 = Sheets("Frais").Range("c9")
FA60 = Sheets("Frais").Range("e6")
FA91 = Sheets("Frais").Range("e7")
FA121 = Sheets("Frais").Range("e8")
B49 = Sheets("Frais").Range("c11")
B73 = Sheets("Frais").Range("c12")
B89 = Sheets("Frais").Range("c13")
B121 = Sheets("Frais").Range("c14")
FB49 = Sheets("Frais").Range("e10")
FB73 = Sheets("Frais").Range("e11")
FB89 = Sheets("Frais").Range("e12")
FB121 = Sheets("Frais").Range("e13")
If code = "" Then 'Vide
vehicule = ""
ElseIf code = "A" Or code = "a" Then 'Montréal, Trois-Rivières,
Québec
& Estrie
If distance < A60 Then
vehicule = FA60
ElseIf distance < A91 Then
vehicule = FA91
ElseIf distance < A121 Then
vehicule = FA121
Else
billet = True
vehicule = autobus(region, billet)
End If
ElseIf code = "B" Or code = "b" Then 'Reste de la province
If distance < B49 Then
vehicule = FB49
ElseIf distance < B73 Then
vehicule = FB73
ElseIf distance < B89 Then
vehicule = FB89
ElseIf distance < B121 Then
vehicule = FB121
Else
billet = True
vehicule = autobus(region, billet)
End If
ElseIf code = "C" Or code = "c" Or code = "P" Or code = "p" Then
'Fournit par la compagnie
vehicule = " -"
Else
vehicule = ""
End If
'Sheets("Frais").Visible = False
End Function
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Function autobus(billet, region)
region = Sheets("Frais dépl").Range("k8")
r1 = Sheets("Frais").Range("B21")
r2 = Sheets("Frais").Range("B22")
r3 = Sheets("Frais").Range("B23")
r4 = Sheets("Frais").Range("B24")
r5 = Sheets("Frais").Range("B25")
r6 = Sheets("Frais").Range("B26")
r7 = Sheets("Frais").Range("B27")
r8 = Sheets("Frais").Range("B28")
r9 = Sheets("Frais").Range("B29")
P1 = Sheets("Frais").Range("e21")
P2 = Sheets("Frais").Range("e22")
P3 = Sheets("Frais").Range("e23")
P4 = Sheets("Frais").Range("e24")
P5 = Sheets("Frais").Range("e25")
P6 = Sheets("Frais").Range("e26")
P7 = Sheets("Frais").Range("e27")
P8 = Sheets("Frais").Range("e28")
P9 = Sheets("Frais").Range("e29")
If billet = True Then
ElseIf region = r1 Then
autobus = P1
ElseIf region = r2 Then
autobus = P2
ElseIf region = r3 Then
autobus = P3
ElseIf region = r4 Then
autobus = P4
ElseIf region = r5 Then
autobus = P5
ElseIf region = r6 Then
autobus = P6
ElseIf region = r7 Then
autobus = P7
ElseIf region = r8 Then
autobus = P8
ElseIf region = r9 Then
autobus = P9
End If
End Function
I made this and it's one of my first VBA code and i have to say that
i'm a
bit proud of myself because it works !
But i know it lacks experience and optimisation. I know that it could
be
shorter but how. Could someone help me ?
The use of it is calculating the need to pay or not and how much,
transportation for employee with their own car. I joined a picture of
the
sheet to help.
Thanks.
Inkel ;-)
+++++++++++++++++++++++++++++++++++++++++++++++++++
Function vehicule(code, distance)
'Frais de déplacement : Axx = distance, FAxx = montant
'Sheets("Frais").Visible = True
billet = False
A60 = Sheets("Frais").Range("c7")
A91 = Sheets("Frais").Range("c8")
A121 = Sheets("Frais").Range("c9")
FA60 = Sheets("Frais").Range("e6")
FA91 = Sheets("Frais").Range("e7")
FA121 = Sheets("Frais").Range("e8")
B49 = Sheets("Frais").Range("c11")
B73 = Sheets("Frais").Range("c12")
B89 = Sheets("Frais").Range("c13")
B121 = Sheets("Frais").Range("c14")
FB49 = Sheets("Frais").Range("e10")
FB73 = Sheets("Frais").Range("e11")
FB89 = Sheets("Frais").Range("e12")
FB121 = Sheets("Frais").Range("e13")
If code = "" Then 'Vide
vehicule = ""
ElseIf code = "A" Or code = "a" Then 'Montréal, Trois-Rivières,
Québec
& Estrie
If distance < A60 Then
vehicule = FA60
ElseIf distance < A91 Then
vehicule = FA91
ElseIf distance < A121 Then
vehicule = FA121
Else
billet = True
vehicule = autobus(region, billet)
End If
ElseIf code = "B" Or code = "b" Then 'Reste de la province
If distance < B49 Then
vehicule = FB49
ElseIf distance < B73 Then
vehicule = FB73
ElseIf distance < B89 Then
vehicule = FB89
ElseIf distance < B121 Then
vehicule = FB121
Else
billet = True
vehicule = autobus(region, billet)
End If
ElseIf code = "C" Or code = "c" Or code = "P" Or code = "p" Then
'Fournit par la compagnie
vehicule = " -"
Else
vehicule = ""
End If
'Sheets("Frais").Visible = False
End Function
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Function autobus(billet, region)
region = Sheets("Frais dépl").Range("k8")
r1 = Sheets("Frais").Range("B21")
r2 = Sheets("Frais").Range("B22")
r3 = Sheets("Frais").Range("B23")
r4 = Sheets("Frais").Range("B24")
r5 = Sheets("Frais").Range("B25")
r6 = Sheets("Frais").Range("B26")
r7 = Sheets("Frais").Range("B27")
r8 = Sheets("Frais").Range("B28")
r9 = Sheets("Frais").Range("B29")
P1 = Sheets("Frais").Range("e21")
P2 = Sheets("Frais").Range("e22")
P3 = Sheets("Frais").Range("e23")
P4 = Sheets("Frais").Range("e24")
P5 = Sheets("Frais").Range("e25")
P6 = Sheets("Frais").Range("e26")
P7 = Sheets("Frais").Range("e27")
P8 = Sheets("Frais").Range("e28")
P9 = Sheets("Frais").Range("e29")
If billet = True Then
ElseIf region = r1 Then
autobus = P1
ElseIf region = r2 Then
autobus = P2
ElseIf region = r3 Then
autobus = P3
ElseIf region = r4 Then
autobus = P4
ElseIf region = r5 Then
autobus = P5
ElseIf region = r6 Then
autobus = P6
ElseIf region = r7 Then
autobus = P7
ElseIf region = r8 Then
autobus = P8
ElseIf region = r9 Then
autobus = P9
End If
End Function