Why does Excel VBA adds 16ms at random to exection

Question

I have some Excel VBA code which runs several iterations. Each iteration basically calculates an array, until a target is reached, usually around 8 or 6 tries The problem is that I need to run this several hundred times and the system is too slow.

First thing I timed how much it takes to reach the target: usually around 16ms. Then I timed each loop, and I get that usually they time at 0ms (so pretty fast) except one that times at 16ms. I then repeated this several times (CTRL+ALT+F9) without any changes, and the loop that takes 16ms varies (sometimes the 2nd, sometimes the 5th, etc...)

btw, sometimes it's 15ms...

So I understand that this is a multitasking system, but is there any way that I can make this run more consistently?

for reference this is the code that does the timing:

      While (Abs(Y - Me.Model.Tjref) > 0.5) And (Count < 100)
        Count = Count + 1
t(Count) = GetTickCount
        If Y > x Then ' left condition
            X1 = x
            Y1 = Y
        Else                ' right condition
            X2 = x
            Y2 = Y
        End If
        x = (X1 + X2) / 2
        Me.Model.Tjref = x
        Y = Me.Model.Tjmax
 t(Count) = GetTickCount - t(Count)
 t1 = t1 + t(Count)
    Wend
 MsgBox t(1) & " " & t(2) & " " & t(3) & " " & t(4) & " " & t(5) & " " & t(6) & " " & t(7) & " " & t(8) & " " & t(9) & " " & t(10) & " tot: " & t1

however the actual calculation is quite long and divided in several classes...

I am not concerned about 16ms, but when I run this many times over these 16ms become 10s of seconds to recalculate a sheet... Thanks for your help!!!!!!

Answer 1

Its not that your code is taking extra time, the resolution of GetTickCount is in the order of 10 to 16 ms. You will only get time values in multiples of this value.

See MSDN article here

Answer 2

Make the Excel.exe a Realtime Priority and test again (this will affect system response when the code is running):