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

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. — Kaz
– Kaz, Commented Jan 29, 2016 at 15:38
@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) — Mr.Burns
– Mr.Burns, Commented Jan 29, 2016 at 16:13
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. — Raystafarian
– Raystafarian, Commented Jan 29, 2016 at 16:23
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. — Rick Henderson
– Rick Henderson, Commented May 20, 2016 at 18:11

Raystafarian · Accepted Answer · 2016-02-04 18:31:00Z

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

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) — Mr.Burns
– Mr.Burns, Commented Feb 4, 2016 at 17:11
I added some information about declaring variables to the answer. — Raystafarian
– Raystafarian, Commented Feb 4, 2016 at 18:33
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! — Mr.Burns
– Mr.Burns, Commented Feb 5, 2016 at 9:39

Toby Speight · Accepted Answer · 2022-04-02 12:45:24Z

1

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

edited Apr 2, 2022 at 12:45

Toby Speight

88.7k14 gold badges104 silver badges327 bronze badges

answered Apr 2, 2022 at 3:41

vbAdder

2011 silver badge4 bronze badges

Add a comment |

Stack Exchange Network

VBA code for testing efficency

2 Answers 2

You must log in to answer this question.

Hot Network Questions

VBA code for testing efficency

2 Answers 2

You must log in to answer this question.

Related

Hot Network Questions