VBA code for testing efficency

Question

I am currently trying out code for timing efficiency (how fast it runs, basically).

The general consensus is that code with ActiveCell,.Select,Selection and so forth are basicly rejected for being slow and buggy, whereas code that uses variables and not ActiveCell,.Select,Selection are considered quicker and less buggy

The two codes I have made do the same thing, which is to enter a number 40000+1 every consecutive cell (so Cell A1 is 40000 then A2 is 40001 and so forth) and then converts those numbers into dates. One does this with ActiveCell, Selection and Select, while the other does it with varibles,Long and Range. I also did both with and then without Application.ScreenUpdating = False to see how that worked as well.

The code ActiveCell etc. ran at 13494,26567,26489,14040,26598(without Application.ScreenUpdating = False) and 1154,1123,1123,1107,1170 (with Application.ScreenUpdating = False) "Milliseconds"

The code without ActiveCell ran at 905,905,905,671,687 (without Application.ScreenUpdating = False) and ran at 577,609,577,577,562 (with Application.ScreenUpdating = False) "Milliseconds"

The milliseconds is in "Milliseconds" as the Private Declare Function GetTickCount Lib "kernel32.dll" () As Long I have been lead to believe isnt very accurate, I use it because the accurate ones are addins for excel and I unable to download or install anything on this PC, but gvies a decent idea of the speed the code runs at

The code that the efficency tests is the For i = 1 To 1000 loop

Code with ActiveCells:

Private Declare Function GetTickCount Lib "kernel32.dll" () As Long
Sub UsingVaribles()
Dim NumberToday As Long
Dim StartTimer As Long
Dim rngCells As Range
Dim rng As Range
Application.ScreenUpdating = False
[A1].Select
NumberToday = 40000
StartTimer = GetTickCount
k = 1
 For q = 1 To 26
 For i = 1 To 1000
 NumberToday = NumberToday + 1
 Set rngCells = Cells(i, k)
 rngCells.Value = NumberToday
 Next i
 Range(rngCells, Cells(1, k)).NumberFormat = "m/d/yyyy" ' 1 bug out
 k = k + 1
 Next q
MsgBox (GetTickCount - StartTimer & " Milliseconds")
End Sub

Code without ActiveCells:

Sub UsingActivecell()
Application.ScreenUpdating = False
[A1].Select
NumberToday = 40000
Dim StartTimer As Long
StartTimer = GetTickCount
 For q = 1 To 26
 For i = 1 To 1000
 NumberToday = NumberToday + 1
 ActiveCell.Value = NumberToday
 ActiveCell.Offset(1, 0).Select
 Next i
ActiveCell.Offset(-1, 0).Select
Range(Selection, Selection.End(xlUp)).Select ' 3 issues with bug out, due to my incompitence
Selection.NumberFormat = "m/d/yyyy"
ActiveCell.Offset(0, 1).Select
ActiveCell.End(xlUp).Select
Next q
MsgBox (GetTickCount - StartTimer & " Milliseconds")
End Sub

• Kaz: The same advice applies here as for time, encryption and other complicated, fundamental operations: Don't roll your own. There's some very god, very robust benchmarking code out there for VBA, if your focus is on effective benchmarking, I recommend you go seek it out.
• Mr.Burns: @Zak As focus is sort of effective benchmarking as I am wanting to see which code runs quicker, ActiveCell or not ActiveCell VBA based code, as much as I have looked around I havent been able to find any concrete evidense that shows ActiveCell does or doesnt run quicker(other than a large quantity of people saying so)
• Raystafarian: You want to slow your code down? It might be better to put together something real and then change the way you reference, but my bet is the variation is negligible.
• Rick Henderson: It's Excel, so why not attach or link to a chart of the computed times? Also, the speed increase mentioned by @Raystafarian is due to un-declared variables being created as Variant types. So your using extra memory AND it's slowing the code down.

Answer 1

I know this isn't what you're asking, but what's up with your variables? You should always turn on Option Explicit which will catch things like k, q and i not being dimensioned.

When you don't define your variable, VBA defines it as a variant. I'm pretty sure variants are objects:

Performance. A variable you declare with the Object type is flexible enough to contain a reference to any object. However, when you invoke a method or property on such a variable, you always incur late binding (at run time). To force early binding (at compile time) and better performance, declare the variable with a specific class name, or cast it to the specific data type.

So you pay a penalty when you don't dimension your variables.

I imagine defining your variables might shave some milliseconds off over the long run.

---

For variables I'm getting 570ish

For selection I'm getting 950ish

For i=1 to 10000

• using your variables is giving me about 5200
• while using option explicit I'm getting around 5100.

For i=1 to 50000

• option explicit is 24165
• your variables is 24476

Option Explicit has the gainz.

And now, my version (why are you [A1].Selecting?) for i=1 to 50000 comes in at 23182. That right there should show you the .select statement was slowing it down.

Option Explicit
Private Declare Function GetTickCount Lib "kernel32.dll" () As Long
Sub UsingVaribles()
 Application.ScreenUpdating = False
 Dim NumberToday As Long
 NumberToday = 40000
 Dim StartTimer As Long
 StartTimer = GetTickCount
 Dim rngCells As Range
 Dim rng As Range
 Dim k As Long
 k = 1
 Dim i As Long
 Dim q As Long
 For q = 1 To 26
 For i = 1 To 50000
 NumberToday = NumberToday + 1
 Set rngCells = Cells(i, k)
 rngCells = NumberToday
 Next i
 Range(rngCells, Cells(1, k)).NumberFormat = "m/d/yyyy" ' 1 bug out
 k = k + 1
 Next q
 MsgBox (GetTickCount - StartTimer & " Milliseconds")
 Application.ScreenUpdating = True
End Sub

• Mr.Burns: For the k,q and i I never relised that it would reduce my code speed, now that I do I will be Diming them, the [A1].Select was most likely copyied from the UsingActiveCell sub (it was to save time rewriting everything)
• Raystafarian: I added some information about declaring variables to the answer.
• Mr.Burns: Nice update on the answer, I've set this as the answer mainly due to the good information and varied testing on the code ... To anyone lese reading down this far, don't be afraid to post your own answers still, anything you can help to improve this code and anyone elses is good information!
• Rick Henderson: Of course now I'll probably plot the results in R. :)

Answer 2

You could maybe use the VBA.timer function (a double with milliseconds) rather than gettickcount.