CODEWITHSUNDEEP
vbaexcel
user4974730
user4974730

Reputation:

VBA timer gives 0s for everything

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?

Upvotes: 1

Views: 113

Answers (1)

JamesFaix
JamesFaix

Reputation: 8655

Two things:

  1. 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

  1. 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

Upvotes: 1

Related Questions