VBA timer gives 0s for everything

Question

I have a timer which calculates the differences in execution times between data types performing the same calculation.

Here is the macro:

Public Declare Function GetTickCount Lib "kernel32.dll" () As Long
Sub Function1_Var_RandNumCounter()

Dim Var_RandNum_X As Variant, Var_RandNum_Y As Variant, Count As Variant

For Count = 1 To Count = 1000000000
    Var_RandNum_X = Rnd(Now) ' Get rnd vals based on Now, built-in VBA property
    Var_RandNum_Y = Rnd(Now)
Next Count

Select Case IsNull("A2")
    Case True
        Cells.Clear
        Set target_sheet = ActiveSheet
        target_sheet.Range("A2").Value = -t
    Case False
        Set target_sheet = ActiveSheet
        target_sheet.Range("A2").Value = -t
End Select

'MsgBox GetTickCount - t, , "Milliseconds"
Call Function1_Dec_RandNumCounter

End Sub
Sub Function1_Dec_RandNumCounter()

Dim Count, Var_RandNum_X, dec_RandNum_X, Var_RandNum_Y, dec_RandNum_Y

dec_RandNum_X = CDec(Var_RandNum_X)
dec_RandNum_Y = CDec(Var_RandNum_Y) ' convert these vals to decimals

For Count = 1 To Count = 1000000000
    dec_RandNum_X = Rnd(Now) ' Get rnd vals based on Now, built-in VBA property
    dec_RandNum_Y = Rnd(Now)
Next Count

Select Case IsNull("B2")
    Case True
        Cells.Clear
        Set target_sheet = ActiveSheet
        target_sheet.Range("B2").Value = -t
    Case False
        Set target_sheet = ActiveSheet
        target_sheet.Range("B2").Value = -t
    End Select

'MsgBox GetTickCount - t, , "Milliseconds"
Call Function1_Int_RandNumCounter

End Sub
Sub Function1_Int_RandNumCounter()

Dim Count, Int_RandNum_X, Int_RandNum_Y

For Count = 1 To Count = 1000000000
    Int_RandNum_X = Rnd(Now)
    Int_RandNum_Y = Rnd(Now)
Next Count

Select Case IsNull("C2")
    Case True
        Cells.Clear
        Set target_sheet = ActiveSheet
        target_sheet.Range("C2").Value = -t
    Case False
        Set target_sheet = ActiveSheet
        target_sheet.Range("C2").Value = -t
End Select

'MsgBox GetTickCount - t, , "Milliseconds"

Call Function1_Double_RandNumCounter

End Sub
Sub Function1_Double_RandNumCounter()

Dim Count, Dbl_RandNum_X, Dbl_RandNum_Y

For Count = 1 To Count = 1000000000
    Dbl_RandNum_X = Rnd(Now)
    Int_RandNum_Y = Rnd(Now)
Next Count

Select Case IsNull("D2")
    Case True
        Cells.Clear
        Set target_sheet = ActiveSheet
        target_sheet.Range("D2").Value = -t
    Case False
        Set target_sheet = ActiveSheet
        target_sheet.Range("D2").Value = -t
End Select

'MsgBox GetTickCount - t, , "Milliseconds"

End Sub
Sub Function2_BarGraph()
'Put all of these vals in a 2D bar graph
End Sub

When I run this I am given 0s values for everything. Even when I increase the decimal place count the time is 0.00000s. What should I do about this?

You're getting 0s for your GetTickCount - t? It doesn't look like you declare t anywhere if you're talking about your commented out messageboxes. If you're talking about your Rnd(now) values, I am getting random decimals myself. techonthenet.com/excel/formulas/rnd.php — user1274820
– user1274820, Commented Jul 14, 2015 at 15:43

JamesFaix · Accepted Answer · 2015-07-14 15:43:28Z

1

Two things:

Variables in VB are not typed (are Variant) unless you declare them as a specific type with an AS clause.
```
Dim Count AS Long, Int_RandNum_X AS Integer, Int_RandNum_Y AS Integer
```

AS must be used for each variable, the statement below will only declare Int_RandNum_Y as an integer, leaing Int_RandNum_X a variant:

    Dim Count AS Long, Int_RandNum_X, Int_RandNum_Y AS Integer

You never assign a value to 't'. It is declared in your MsgBox call. You should declare
```
Dim t as Double 
```

early in the routine, and then assign the current time to it with

   t = Timer

Then later you can get the elapsed time in seconds with

   t = Timer - t

answered Jul 14, 2015 at 15:43

JamesFaix

8,80510 gold badges43 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA timer gives 0s for everything

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related