Speed up my code!

[blelli]

Dears,

Is it possible to speed up the following code?
It's my first time using VBA!

Public Sub Fleet()

Dim Linha, ColIn, ColOut, ColUti, ColData, Contador, Model, LiFore, LinOut, LinUti, Region, Deliv, Total, Entregas, AnoDataOut, LinMS As Integer
Dim LinEECAMS, LinEECFMS, Regiao, LinEEC As Integer
Dim Uti, Randomico, EOSCMS, Inter, Std, Enh, EECMs As Double
Dim ScreenUpdating As Boolean
Dim Regions(), Models(), ColMes(), ColMS(), ColEECAMS(), ColEECFMS(), DMS() As Variant
Dim Meses As Range


ScreenUpdating = False


Regions() = Array("NAC", "EMEA", "CSA", "ASIA", "CHINA")
Models() = Array("0500", "0505", "0145", "0145", "0145", "0190")
ColMes() = Array(15, 33, 87, 87, 87, 105)
ColMS() = Array(14, 32, 86, 86, 86, 104)
ColEECAMS() = Array(17, 35, 89, 89, 89, 107)
ColEECFMS() = Array(13, 31, 49, 67, 85, 103)
DMS() = Array(0, 1, 2, 3, 4)


Linha = 6: ColIn = 13: ColOut = 3: ColData = 12: LinUti = 69: ColUti = 15:


For Model = 0 To 5
LiFore = (Sheets("Fleet").Cells(Linha, ColIn).End(xlDown).Row) - 5
LinOut = (Sheets("Fleet").Cells(Linha - 2, ColOut).End(xlDown).Row + 1)

LinMS = 69: LinEECAMS = 77: LinEECFMS = 86: Contador = 1

Set Meses = Range(Cells(31, ColMes(Model)), Cells(42, ColMes(Model)))

For Region = 0 To 4
Uti = Sheets("Fleet").Cells(LinUti, ColUti).Value
Inter = Sheets("Fleet").Cells(LinEECAMS, ColEECAMS(Model) - 2).Value
Std = Sheets("Fleet").Cells(LinEECAMS, ColEECAMS(Model) - 3).Value
Enh = Sheets("Fleet").Cells(LinEECAMS, ColEECAMS(Model) - 1).Value

For Deliv = 1 To LiFore
Total = Sheets("Fleet").Cells(Linha, ColIn)

For Entregas = 1 To Total
Sheets("Fleet").Cells(LinOut, ColOut).Value = "F " & Models(Model) & "-" & Contador
AnoDataOut = Sheets("Fleet").Cells(Linha, ColData).Value
Randomico = Rnd() + 0.1

Sheets("Fleet").Cells(LinOut, ColOut + 1).Value = DateSerial(AnoDataOut, Application.Match(Randomico, Meses, 1), Int(29 * Rnd) + 1)
Sheets("Fleet").Cells(LinOut, ColOut + 2).Value = Regions(Region)
EOSCMS = Sheets("Fleet").Cells(LinMS, ColMS(Model)).Value

Select Case Randomico <= EOSCMS
Case True: Sheets("Fleet").Cells(LinOut, ColOut + 3).Value = "EOSC"
Case False: Sheets("Fleet").Cells(LinOut, ColOut + 3).Value = "EASC"
End Select

Sheets("Fleet").Cells(LinOut, ColOut + 4).Value = Uti

EECMs = 1 - Sheets("Fleet").Cells(LinEECAMS, ColEECAMS(Model)).Value
Select Case EECMs > Sheets("Fleet").Cells(LinEECFMS, ColEECFMS(Model) + DMS(Regiao)).Value
Case True: Sheets("Fleet").Cells(LinOut, ColOut + 5).Value = vbNullString
Case False:
Select Case Randomico
Case 0 To Inter: Sheets("Fleet").Cells(LinOut, ColOut + 5).Value = "EEC INT"
Sheets("Fleet").Cells(LinOut, ColOut + 6).Value = Sheets("Fleet").Cells(LinOut, ColOut + 1).Value
Sheets("Fleet").Cells(LinOut, ColOut + 7).Value = DateAdd("yyyy", 5, Sheets("Fleet").Cells(LinOut, ColOut + 1).Value)

Case 0 To Std: Sheets("Fleet").Cells(LinOut, ColOut + 5).Value = "EEC STD"
Sheets("Fleet").Cells(LinOut, ColOut + 6).Value = Sheets("Fleet").Cells(LinOut, ColOut + 1).Value
Sheets("Fleet").Cells(LinOut, ColOut + 7).Value = DateAdd("yyyy", 5, Sheets("Fleet").Cells(LinOut, ColOut + 1).Value)

Case 0 To Enh: Sheets("Fleet").Cells(LinOut, ColOut + 5).Value = "EEC ENH"
Sheets("Fleet").Cells(LinOut, ColOut + 6).Value = Sheets("Fleet").Cells(LinOut, ColOut + 1).Value
Sheets("Fleet").Cells(LinOut, ColOut + 7).Value = DateAdd("yyyy", 5, Sheets("Fleet").Cells(LinOut, ColOut + 1).Value)
End Select
End Select

LinOut = LinOut + 1: Contador = Contador + 1

Next Entregas

Linha = Linha + 1: LinEECFMS = LinEECFMS + 1

Next Deliv

ColIn = ColIn + 1: Linha = 6: LinMS = LinMS + 1: LinUti = LinUti + 1: LinEEC = LinEEC + 1
LinEECAMS = LinEECAMS + 1: LinEECFMS = 86

Next Region

ColIn = ColIn + 13: ColOut = ColOut + 18: ColData = ColData + 18: ColUti = ColUti + 18
LinUti = 69: Linha = 6: LinEECAMS = 77
Next Model

ScreenUpdating = True

End Sub

 

[Jon Peltier]

I don't know if it will speed things up, but you can improve your programming practice.

1. The following only declares LinEEC as integer, and the rest as variants. Variants make the code slower, but the speed difference is probably unnoticeable by any user.
Dim LinEECAMS, LinEECFMS, Regiao, LinEEC As Integer

You need to use this:
Dim LinEECAMS As Integer, LinEECFMS As Integer, Regiao As Integer, LinEEC As Integer

2. Use Long instead of Integer, Double instead of Single. It would seem Integer would be better since Integer uses less memory. But VB converts internally to Long, does the math, then converts back. same with Single and Double. The speed difference is probably unnoticeable by any user.

3. If your Select Case only has cases True and False, Select Case may be more readable (but probably not if you're joining lines with colons), but in general, If-Then is faster. The speed difference is probably unnoticeable by any user.

3.a. In terms of readability, joining lines with colons is bad practice. I almost never do this.

4. Entering information step by step is slower than a whole range at a time. I can't tell offhand how you would do this with your information, but it's worth a look. This speed difference is most likely noticeable by most users.