-1

I've tried every suggestion on this site to make my macro faster.

  • Halt calculations
  • Disable the screen updating
  • Disable status bar updating
  • Telling the Excel to ignore events

and so much more. Even in loops, I've tried many methods of making my macro smooth.

And I saw an article that I can use colon to make a multiple lines of codes into single line.

From:

Dim x as Integer
x = 1
If x = 0 Then
    Exit Sub
End If

To:

Dim x as Integer: x = 1: If x = 0 Then Exit Sub

Does making some codes in the same line makes the macro faster?

Mr. Spock
  • 47
  • 1
  • 11
  • 1
    That will not have an effect... the `:` is just a "different" line break. The compiler itself will do exactly the same. – Dirk Reichel Jun 29 '17 at 10:00

3 Answers3

2

No, writing the code on the same line does not make the code run faster.

Code can be slow because of inefficient coding techniques, like looping through all cells in a column, reading each cell, calculating it, and writing the result back to another cell in each step of the loop.

Writing that code in one line is possible, but it won't make the code run faster.

What WILL make the code run faster is reading the data range into an array, then looping over the array, writing the output of any calculation into another array and then finally writing the result array back to the spreadsheet as one write operation.

teylyn
  • 34,374
  • 4
  • 53
  • 73
  • Make sense to me. Learn and focus on the coding technique and do not sacrifice readability of codes. Hoping that everyone who reads this article/question help them a lot. – Mr. Spock Jun 29 '17 at 10:19
2

This is simply a different line break and does not affect execution time. You already noted a couple of tricks you applied:

  • Application.ScreenUpdating = False
  • Application.DisplayAlerts = False
  • Application.Calculation = xlCalculationManual
  • Application.EnableEvents = False

Since you haven't posted full code and "Please optimize my code" is not a question, there's the following general tips to speed up Execution:

  • When referencing the same Object multiple times in a row, use With and End With instead. This is faster than specifying the full path to the object each time, since it prevents certain IO operations to access the right block in memory.
  • Use as little references to Worksheet / Range objects as possible, in general. A lot of times it's faster to use an Array than a Range. See Chip Pearson's article on this.
  • Looping is slow. Using native functions is faster. Make use of Autofilter instead of checking each cell, for example.
  • Do not use Copy and Paste, instead use the Destination parameter of the Copy method
  • Declare your objects as accurately as possible: Use Dim ws As Worksheet instead of Dim ws or Dim ws As Object.
  • Never use .Select and .Activate - See this Question
Community
  • 1
  • 1
Rik Sportel
  • 2,661
  • 1
  • 14
  • 24
1

No, such changes does not boost Your code.

It also depends what Your macro do, but my case was that I needed to use sth similar to vlookup in my code, so at the first version I used ForEach loop and if statment - when I was to match 10 000 records with about 3k values it took about 15 minutes. So I decided to use dictionary (reference does not break acro on other machines) and it really boost my code. Now 20 k records with 6k values takes about 2 minutes.

Mikisz
  • 404
  • 5
  • 20