Solved: Passing variables to procedures using Application.OnTime method

[malik641]

I can't find this on the web.....how do you do this? Is there a special syntax or something?
[VBA]
Sub test(Str As String)
Debug.Print Str
End Sub


Sub Testme()
Dim xl As Excel.Application
Dim Str As String

Str = "hey"
xl.OnTime Now + TimeValue("00:00:01"), "test(Str)"
End Sub[/VBA]
But I'm getting an error with this.......any suggestions? Is this doable?

[Andy Pope]

Try this, notice the use of single quotes

[vba]Sub Testme()
Dim xl As Excel.Application
Dim Str As String

Set xl = Excel.Application
Str = "hey"
xl.OnTime Now + TimeValue("00:00:01"), "'test " & """" & Str & """'"
End Sub[/vba]

[malik641]

Thank you Andy

I feel like a dummy, the error I was getting was because I didn't set the xl variable...

So the string for the OnTime method is supposed to look like this huh?
[VBA]'test "hey"'[/VBA]

Why does it need the single quotes? Why is it not the other way around? Like:
[VBA]"test 'hey'"[/VBA]

[Cyberdude]

Yeah, Andy, why is that??
If there were no argument involved, then you would write "test".
Sid

[Andy Pope]

An educated guess, as I have not seen a definitive statement, would be that the single quotes act a little like the single quotes used when a sheet name reference contains spaces and keeps the information together.
The double quotes imply the argument is of type String.

[malik641]

But how did you know to even do that? I wouldn't have guessed that, I would have wrote it "test 'hey'"....

[Andy Pope]

It was one of the few questions I posted in the ms newsgroups, which was quickly answered by John Green.

[malik641]

Ah. I guess I'll do some research later then.

Well thanks again Andy Appreciate the quick solution.

[Cyberdude]

Yeah, Andy, excellent "guess". It makes a lot of sense, and I gotta admit I never would have thought of it.

[Arbiter090]

Hi folks, I am trying to implement a similar piece of code and I am just running into a hell of a time with it.

The code I am trying to use to call the procedure Picture_Update is as follows:
Application.OnTime EarliestTime:=TimeValue("16:53:45"), Procedure:="'Scheduler.Picture_Update picCount, arNames()'", Schedule:=True

The sub I want to run looks like this. I keep getting a Macro-not found error when the time comes around. Can anyone tell me what I am doing wrong? I think it has something to do with how I am trying to pass the variables.

Public Sub Picture_Update(picCount, ByRef arNames() As String)

With BOARD_DISPLAY.IMAGES
Picture_Path = ("C:\PICTURES_STREAM\" & arNames(picCount))
.Picture = LoadPicture(Picture_Path)
BOARD_DISPLAY.Repaint
If Cancel Then Exit Sub
picCount = picCount + 1
End With
End Sub