VBA code for testing efficency

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 r

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

StackExchange Code Review Q#118259, answer score: 3