PDA

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

mana
12-19-2017, 04:54 AM
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

SamT
12-19-2017, 06:50 AM
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.

snb
12-19-2017, 07:39 AM
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