Code optimisation
 

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

Code optimisation
 

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
---------------
Jacob Skaria

Code optimisation
 

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" a écrit dans
le
message de news: ...

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
---------------
Jacob Skaria

Code optimisation
 

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
---------------
Jacob Skaria

Code optimisation
 

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" a écrit dans le
message de news: ...
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
---------------
Jacob Skaria

Code optimisation
 

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
---------------
Jacob Skaria


"Inkel" wrote:

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" a écrit dans le
message de news: ...
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
---------------
Jacob Skaria