View Full Version : Speedup VBA
ElPetit11
12-19-2017, 03:53 AM
Hello Everyone,
I have a question about optimizing my code. I have read that for speeding vba you should (next to other things like Turn off Automatic Calculations, Screen Updating)
- avoid using Select statement
- use With statement
- using minimum dots/periods(.)
So I am wondering what is better option for my codes, when they look like this in general (see below) because...
Option 1 = I use Select (just once for selecting target sheet) and I have less dots than in Option2
Option 2 = I dont use Select, I use With, however I have more dots than in Option1
Is there an answer with option is better in general, or do I have to test it in every particular case?
Thank for any reply.
Option 1
Sheets("A").Select
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 to LastRow
Range("A" & i) = Worksheetfunction.Example 1
Range("B" & i) = Worksheetfunction.Example 2
Range("C" & i) = Worksheetfunction.Example 3
.
.
.
Next
Option 2
With Sheets("A").
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 to LastRow
.Range("A" & i) = Worksheetfunction.Example 1
.Range("B" & i) = Worksheetfunction.Example 2
.Range("C" & i) = Worksheetfunction.Example 3
.
.
.
Next
End With
With Sheets("A").
Set r = .Range("A2", .Cells(Rows.Count, 1).End(xlUp))
End With
r.Formula = "=Example 1"
r.Offset(, 1).Formula = "=Example 2"
r.Offset(, 2).Formula = "=Example 3"
With r.Resize(, 3)
.Value = .Value
End With
Is there an answer with option is better in general, or do I have to test it in every particular case?
I don't worry about speed, other than the basics, until the process takes more then a second to complete. Then, I try to do all the work in memory with arrays, and only access the sheet at the end to place the results on it.
Sometimes, it's just going to take time to complete the process. Although, sometimes, one has mere milliseconds to complete it. See the "Bet Angel" thread in the Other Applications Forum. It is all about speed, speed, speed.
Always use Arrays
@mana do not use objectvariables
Sub M_snb()
sn=Sheets("A").cells(1).currentregion.resize(,3)
For j=1 to ubound(sn)
sn(j,1)= "example1"
sn(j,2)= "example 2"
sn(j,3)= "example 3"
Next
Sheets("A").cells(1).currentregion.resize(,3)=sn
End Sub
Further reading: http://www.snb-vba.eu/VBA_Arrays_en.html
Paul_Hossler
12-19-2017, 08:30 AM
Throwing my opinions into the pot with the other opinions
1. http://programmer.97things.oreilly.com/wiki/index.php/Write_Code_for_Humans_not_Machines
Programmers spend more their time reading someone else's code than reading or writing their own. This is why it is important that whoever writes the code pays particular attention not only to what it does but also to how it does it. For a compiler, it makes no difference if a variable is called p or pageCounter, but of course it makes a big difference for the programmer who has to figure out what kind of information that variable contains. That is why it is easier to write code for compilers than for people.
<snip>
By making your code easily readable by other programmers you are making their job simpler. And this is no bad thing when you consider that the next programmer to read the code could be you.
2. https://shreevatsa.wordpress.com/2008/05/16/premature-optimization-is-the-root-of-all-evil/
There is a famous saying that “Premature optimization is the root of all evil”. Sometimes it quoted in a longer form: “We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil.”
The quote, in its full form, is from Knuth’s paper Structured Programming with go to Statements (http://pplab.snu.ac.kr/courses/adv_pl05/papers/p261-knuth.pdf), ACM Computing Surveys, Vol 6, No. 4, Dec. 1974 (see p.268), in which he says
There is no doubt that the grail of efficiency leads to abuse. Programmers waste enormous amounts of time thinking about, or worrying about, the speed of noncritical parts of their programs, and these attempts at efficiency actually have a strong negative impact when debugging and maintenance are considered. We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil.
Yet we should not pass up our opportunities in that critical 3%. A good programmer will not be lulled into complacency by such reasoning, he will be wise to look carefully at the critical code; but only after that code has been identified
3. Arrays can be useful, but are not the answer to everything, for everything, for anytime, for every situation -- If I'm doing a LOT of computation and I'm only interested in values, then arrays MIGHT be a way to go. If I'm going through a WS and filling / formatting cells depending on some criteria, then I'd loop the rows and columns, and forego an array
4. Excel is loaded with objects, so I don't see any reason not to use object variables if it enhances to readability of the code. Using range.SpecialCells (xlConstants, xlNumbers) uses an intrinsic Excel capability, so why not use it instead a FORTRAN-like nested For/Next loop looking for numbers
5. Using With/End With offers a slight performance improvement (theoretically) since object de-referencing (aka the dot) is avoided, but I use it to make the code more readable
So ...
1. So for a routine macro, if something like this takes 10 ms or 2 seconds, the question is ... does it really make a difference. Neither is enough time to go get a cup of coffee. When I got back to fix/add in 6 months, I want to be able to understand what I was thinking
With Sheets("A").
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To LastRow
.Cells (I,1).value = Worksheetfunction.Example 1
.Cells (I,2).value = Worksheetfunction.Example 2
.Cells (I,3).value = Worksheetfunction.Example 3
.
.
.
Next
End With
2. A large part of the time, if something is "too slow" improving the algorithm gets a bigger bang for the buck, that just changing the technique
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.