Need Help With Using/(finding) Timer in Excel !

[washcloth]

okay here's the plan:

I need to write a macro for excel that will display a number ("B") real-time alongside a movie (it would be preferable for the movie to also play on the form using shockwave or something could someone please instruct me as to how to do that?) Here's a hypothetical first few lines of the data in Excel (A is the time, and B is the number that needs to be displayed):

A           B     
1   12.00.01     55    
2   12.05.01     58
3   12.07.02     46
4   12.09.05     23
5   12.11.04     67
... (and so on for about umm several thousand lines)

What I need to do is use the timer to display the value of "B", after the correct interval of time. For example, the first value ("B1") appears immediatley, and then five seconds later I need for the value of "B2" to be displayed.

The interval between new numbers needs to be something like this (where x equals the row because I'm using a loop):

interval = (A(x+1))-Ax

Right now (having never written a macro/program in VBA before) I am having trouble finding the "timer" control. There are hundreds of "Additional Controls" and I don't want to sort through them. I really have no idea what I am doing with VBA so thanks for the help in advance. Keep in mind that I have only taken Programming 1 last year, I am 15, and my VB skills are so incredibly limited, so if possible, please explain everything fully and don't assume that I know anything

[RobDog888]

Welcome to the Forums.

Thats because its not a control in Excel. Its just a function called Timer: Returns a Single representing the number of seconds elapsed since midnight.

Here is an example from the help file on how to use it.

Dim PauseTime, Start, Finish, TotalTime
If (MsgBox("Press Yes to pause for 5 seconds", 4)) = vbYes Then
    PauseTime = 5    ' Set duration.
    Start = Timer    ' Set start time.
    Do While Timer < Start + PauseTime
        DoEvents    ' Yield to other processes.
    Loop
    Finish = Timer    ' Set end time.
    TotalTime = Finish - Start    ' Calculate total time.
    MsgBox "Paused for " & TotalTime & " seconds"
Else
    End
End If

[zaza]

And, to add to RD's post, you can't put it in either (like you might do with other VB controls). At least, as far as I am aware. You have to do the "Get start point and check each interval" method as shown above by RD.

zaza

[washcloth]

So it should look something like this?

Sub TEST1()
 
Dim PauseTime, Start, Finish, TotalTime
Dim x As Integer
 
For x = 1 To 10
    Display.Caption = ("Bx") 'Display the number from cell Bx
    PauseTime = ("Ax")  ' Set duration (from cell Ax)
    Start = Timer       ' Set start time.
        
        Do While Timer < Start + PauseTime
            DoEvents 'Pause
        Loop
 
x = x + 1
Next x
End Sub

I get errors for like every other line and I have no idea how to debug them... thanks for helping btw

[RobDog888]

What are the errors?

Do you know that your declaring all your variables as a Variant? Thats what your doing on this line when you dont specify the type.

Dim PauseTime, Start, Finish, TotalTime
'Should be
Dim PauseTime Long, Start As Long '(, Finish, TotalTime) Not used

[washcloth]

Sub TEST1()
 
Dim PauseTime As Long, Start As Long
 
Dim x As Integer
 
For x = 1 To 10
    Display.Caption = ("Bx") 'Display the number [U][B]<-- run time error '424': object required[/B][/U]
    PauseTime = ("Ax")  'Set duration [U][B]<-- run time error '13': type mismatch[/B][/U]
    Start = Timer       ' Set start time.
        
        Do While Timer < Start + PauseTime
            DoEvents
        Loop
 
x = x + 1
Next x
End Sub

[RobDog888]

Ok, where is your "Display" object? What is the Ax supossed to mean? Usually a time value is a long or single value type.

[washcloth]

The "Display" label is on a user form, and the "Ax" is supposed to represent a cell ie A1, A2 etc and the same for "Bx". I know that it's wrong... I just don't know quite how to get the program to recognize the value of a cell

[RobDog888]

Ok, where is "Sub TEST1()"? Is it behind the userform code or in a module or ThisWorkbook?

You can access a sheets cell by referenceing the workbook/sheet depending on where your trying to reference it from.

PauseTime = Workbooks("Book1").Sheets("Sheet1").Cells(1, 1) 'A1

[washcloth]

the sub is in a module, so should it look like this?

Sub TEST1()
 
Dim PauseTime As Long, Start As Long
 
Dim x As Integer
 
For x = 1 To 10
    PauseTime = Workbooks("Book1").Sheets("Sheet1").Cells(1, "x + 1") - Workbooks("Book1").Sheets("Sheet1").Cells(1, "x")
    Display.Caption =  Workbooks("Book1").Sheets("Sheet1").Cells(2, "x")
    Start = Timer       ' Set start time.
        Do While Timer < Start + PauseTime
            DoEvents
        Loop
x = x + 1
Next x
End Sub

[RobDog888]

Almost, "x + 1" and "x" should not have double quotes around them as we want the variable value to add to 1 and not be a complete string representation.

If your workbook is called "Book1" and your sheet is named "Sheet1" then yes.

To reference your Display label from a module - UserForm1.Display.Caption = "Something.

[washcloth]

Ok so I did everything that you said... (& my wrkbk is "Book1" and sheet is "Sheet1" and form is "UserForm1") and here is the current code:

Sub TEST1()
Dim PauseTime As Long, Start As Long
Dim x As Integer
 
For x = 1 To 10
    PauseTime = Workbooks("Book1").Sheets("Sheet1").Cells(1, x + 1) - Workbooks("Book1").Sheets("Sheet1").Cells(1, x)
    UserForm1.Display.Caption = Workbooks("Book1").Sheets("Sheet1").Cells(2, x)
    Start = Timer       ' Set start time.
        Do While Timer < Start + PauseTime
            DoEvents
        Loop
x = x + 1
Next x
End Sub

Now I can run it without error messages, but the for just sits there and the number stays as "0", which is the default number on "Display"... pretty much nothing happens.

[RobDog888]

Place a breakpoint on "TEST()", run your code and in your immediate window type these and post what value you get.
"?Workbooks("Book1").Sheets("Sheet1").Cells(1, x + 1)"

"?Workbooks("Book1").Sheets("Sheet1").Cells(2, x)"

"?PauseTime?

[washcloth]

oh noes I'm so confused... a breakpoint is that thing that happens when you click in the left margin next to a line and a circle appears and the line of code turns burgundy correct? I did that to the line Sub TEST1(), and ran it, but the line just turned yellow with a line pointing to it. Now what might this immediate window be?

[RobDog888]

Breakpoint is correct and the yellow highlighted line is the current line of execution that the program is going to execute. You can step through your code line by line by pressing F8.

The immediate window can be viewed by pressing Ctl+G.

[washcloth]

uuhhhmmm ok so I just did what I first did [make a breakpoint on sub TEST1() ] and then I went to the immediate window and typed in"?Workbooks("Book1").Sheets("Sheet1").Cells(1, x + 1)"
and pressed enter and a window popped up that said "Compile Error: Expected: Line number or label or statement of end of statement" Should the yellow line be on the line of code that I am refering to? Thanks for helping me through this

[RobDog888]

Dont use the double quotes as I used them just to show what to type.
Press F8 once to advance the yellow line into the procedure to initialize the variables.

[washcloth]

?Workbooks("Book1").Sheets("Sheet1").Cells(1, x + 1)
0.1 (<-- that is correct at least)

?Workbooks("Book1").Sheets("Sheet1").Cells(2, x)
application defined or object defined error

?PauseTime?
expected: expression

[RobDog888]

So row 1, column 2 has 0.1 in it and row 2 column 1 has what in it?

[washcloth]

row 2, clm 1 has "0.51" in it, but heh I just figured out that the number I want displayed is in row 1 clm 2 and not row2 clm1 whoopsies so I guess that I got my clms and rows mixed up sorry

[RobDog888]

At least its straightened out now. So PauseTime should have a correct value in it now?

[washcloth]

Actually no I don't know why... here is the revised code with parts I just changed in bold (I don't think there are any glaring errors?):

Sub TEST1()
Dim PauseTime As Long, Start As Long
Dim x As Integer
 
For x = 1 To 10
    PauseTime = Workbooks("Book1").Sheets("Sheet1").Cells[B](x + 1, 1)[/B] - Workbooks("Book1").Sheets("Sheet1").Cells[B](x, 1)[/B]
    UserForm1.Display.Caption = Workbooks("Book1").Sheets("Sheet1").Cells[B](x, 2)[/B]
    Start = Timer       ' Set start time.
        Do While Timer < Start + PauseTime
            DoEvents
        Loop
x = x + 1
Next x
End Sub

?Workbooks("Book1").Sheets("Sheet1").Cells(x + 1, 1)
returns "0.1" ,however I just realized that it should be "0.51", because (x + 1 , 1) = A2 the first time through the loop, right? I guess that it isn't correct then :\

?Workbooks("Book1").Sheets("Sheet1").Cells(x , 2)
application-defined or object-defined error :S

?Workbooks("Book1").Sheets("Sheet1").Cells(x, 1)
application-defined or object-defined error

?PauseTime
returns "0"

no esta bien

[RobDog888]

Place a breakpoint on the PauseTime line of code. Then do the "?" printing of your cell values.

A2 for your first value, (2, 1) = row 2 column 1 or A2.

[washcloth]

Place a breakpoint on the PauseTime line of code. Then do the "?" printing of your cell values.

?Workbooks("Book1").Sheets("Sheet1").Cells(x, 1)
0.1 (correct)
?Workbooks("Book1").Sheets("Sheet1").Cells(x + 1 , 1)
0.51 (correct)
?Workbooks("Book1").Sheets("Sheet1").Cells(x, 2)
1 (correct)
?PauseTime
0 (I dont think thats correct)

[RobDog888]

Ok, since your working with decimal values you need to change your variable from a long to a single.

Dim PauseTime As Long
'Should be
Dim PauseTime As Single

0.51 - 0.1 = 0.41

[washcloth]

I just changed PauseTime to single and tried to run the program and nothing happened besides the form showing up, but I placed a breakpoint on Pause Time etc and now I get

?PauseTime
0.41 (correct)

I can't find anything else that might be the problem... btw this code should be under "Module1" right? Or should it be under "UserForm1"?

[RobDog888]

Step through your code checking the values from this line of code.

Do While Timer < Start + PauseTime

[washcloth]

They are all correct

[RobDog888]

But as you step through the loop does the values ever change or evaluate to let it go inside the loop? or does it just bypass the loop altogether?

[washcloth]

yea I have to press f8 reall fast before that 0.41 of a second is up, but all the values are right on all the lines:

Do While Timer < Start + PauseTime
DoEvents
Loop

[RobDog888]

Well according to your code its only a pause loop so nothing is going to happen unless you increase the pause to some values that wil be noticeable.

[washcloth]

but wont it pause for the right interval? ie 0.41 secs? am I wrong?

[RobDog888]

Its probably too fast since its less then 1/2 a second.

[dglienna]

Isn't the timer in milliseconds?

[washcloth]

Its probably too fast since its less then 1/2 a second.

I just tried changed the first ten increments to:
1 sec , 3, 5, 7, 9, 11, 13, 15, 17, and 19secs after beginning
and it still didn't work so I know that is probably not the problem
is that what you meant?

[washcloth]

Isn't the timer in milliseconds?

what do I do then??

[RobDog888]

I'm not sure about that as "Timer: Returns a Single representing the number of seconds elapsed since midnight."

[washcloth]

oh.... so does that mean that I can't work with fractions of a second?? because if so then that's not good at all

[RobDog888]

This is part of why I asked if your loop was working and what the values were.

Do While Timer < Start + PauseTime

If your pause time is fractional then it will always exit the loop and never go into it.

Why do you need such short durations?

[dglienna]

86,400 seconds in a day. It returns 2 decimal places, though. Prolly wouldn't help to try to gauge .41 seconds. I doubt it would register accurately. You could see how many times it was looping, though.