Code optimisation

[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

 

[Jacob Skaria]

Replace the autobus function with the below. I dont understand why you are
passing the argument region if you are assigning some value within this
function...

Function autobus(billet, region)
region = Sheets("Frais dépl").Range("k8")
If billet = True Then
For intTemp = 21 To 29
If region = Sheets("Frais").Range("B" & intTemp) Then
autobus = Sheets("Frais").Range("E" & intTemp)
Exit For
End If
Next
End If
End Function

If this post helps click Yes

 

[Inkel]

Hi Jacob,

Thank you for taking the time to check and improve my code, it's
really
appreciated.

I didn't understand you question until i tried your code. For now, it
give
me always a zero for answer.
The value assigned to the argument "region" is the content of a cell
where
the employee select in a list the place the worked, and if it is more
than
120km from the office an amount is payed, looking in a table.

The selected place is here :
region = Sheets("Frais dépl").Range("k8")

The the amount paid is chosen in the table :
If billet = True Then
For intTemp = 21 To 29
If region = Sheets("Frais").Range("B" & intTemp) Then
autobus = Sheets("Frais").Range("E" & intTemp)

Do you understand my problem and see a solution ? For the little bit i
know,
your code is good, but maybe something is not in the right order, i
don't
know, that's why i ask.

Thanks

Inkel


"Jacob Skaria" <[email protected]> a écrit dans
le
message de news: (e-mail address removed)...

 

[Jacob Skaria]

Dear Inkel

Functino modified. If billet = True then the function exists or else it will
pick up the right one..... Try and feedback..

Function autobus(billet, region)
region = Sheets("Frais dépl").Range("k8")
If billet = True Then Exit Function

For intTemp = 21 To 29
If region = Sheets("Frais").Range("B" & intTemp) Then
autobus = Sheets("Frais").Range("E" & intTemp)
Exit For
End If
Next

End Function

If this post helps click Yes

 

[Inkel]

Wow, thanks. It's working.

What's the logic behind "If billet = True Then Exit Function" ?
All i understand when i read that is to end the function "autobus". Or
does it mean to end the other function "vehicule" ?

Thank you very much for your help.

Inkel

 

[Jacob Skaria]

Exit function exit from the current function.
If you run the code using F8 (not F5) you will be able to understand it better


If this post helps click Yes