[Home]] ](/danielep71/VBA-PERFORMANCE/wiki/[[Tips-and-Tricks)

---

1. Statement

Reducing the number of lines improves the performance (although it reduces readability).

2. Description

There are some VBA statements that may be written in a single executable line of code instead of multiple lines.

Example:

We can declare variables on the same line, separating each one with a comma:

Sub Declare_Variables()
    Dim intFirstNumber As Integer, IntSecondNumber As Integer   
End Sub

Or we can use colon (:) to write multiple statements in a single line:

Sub Use_Colon_ForMultipleLine()
    Dim intFirstNumber As Integer, IntSecondNumber As Integer
    intFirstNumber = 5: intSecondNumber = 10
End Sub

3. Performance Test

Sub Test_ReduceNumberofLines()
'------------------------------------------------------------------------------
'Declare
    Dim i               As Long                     'Loop counter
    Dim MaxIter         As Long                     'Max # of iterations
    Dim iMethod         As Integer                  'Performance method
    Dim cPM             As cPerformanceMonitor      'Performance tracker
'------------------------------------------------------------------------------
'Initialize
    MaxIter = 1
    iMethod = 5
    Set cPM = New cPerformanceMonitor
'------------------------------------------------------------------------------
'Test1
    cPM.StartTimer (iMethod)
    For i = 1 To MaxIter
        Dim iFirstNumber As Integer
        Dim iSecondNumber As Integer
        iFirstNumber = 5
        iSecondNumber = 10
    Next i
    Debug.Print cPM.ElapsedTime(iMethod) & " - multi line"
'Test2
    cPM.StartTimer (iMethod)
    For i = 1 To MaxIter
        Dim iThirdNumber As Integer, iFourthNumber As Integer
        iThirdNumber = 5: iFourthNumber = 10
    Next i
    Debug.Print cPM.ElapsedTime(iMethod) & " - single line"
'------------------------------------------------------------------------------
'Exit
    Set cPM = Nothing
End Sub

4. Results

The results are the following:

• Looping 100,000,000 times:
  • 00:00:00 - 643 ms - 206 µs - 900 ns - multi line
  • 00:00:00 - 699 ms - 806 µs - 000 ns - single line
• Looping 1,000,000,000 times
  • 00:00:07 - 588 ms - 789 µs - 100 ns - multi line
  • 00:00:08 - 790 ms - 854 µs - 100 ns - single line
• Looping 1 time
  • 00:00:00 - 000 ms - 003 µs - 200 ns - multi line
  • 00:00:00 - 000 ms - 005 µs - 400 ns - single line

5. Conclusions

Impact: false

According to my test there isn’t any performance increase.

Using multi line seems to perform even better. Furthermore, it increases code readability.

---

[Home]] ](/danielep71/VBA-PERFORMANCE/wiki/[[Tips-and-Tricks)