PDA

View Full Version : Solved: Passing variables to procedures using Application.OnTime method



malik641
09-05-2006, 10:04 PM
I can't find this on the web.....how do you do this? Is there a special syntax or something?

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
But I'm getting an error with this.......any suggestions? Is this doable?

Andy Pope
09-06-2006, 01:23 AM
Try this, notice the use of single quotes

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

malik641
09-06-2006, 06:50 PM
Thank you Andy :)

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

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

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

Cyberdude
09-06-2006, 07:25 PM
Yeah, Andy, why is that??
If there were no argument involved, then you would write "test".
Sid

Andy Pope
09-07-2006, 12:49 AM
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
09-07-2006, 05:04 AM
But how did you know to even do that? I wouldn't have guessed that, I would have wrote it "test 'hey'"....

Andy Pope
09-07-2006, 05:21 AM
It was one of the few questions I posted in the ms newsgroups, which was quickly answered by John Green.

malik641
09-07-2006, 08:20 AM
Ah. I guess I'll do some research later then.

Well thanks again Andy :thumb Appreciate the quick solution.

Cyberdude
09-07-2006, 10:19 AM
Yeah, Andy, excellent "guess". It makes a lot of sense, and I gotta admit I never would have thought of it. :bow:

Arbiter090
07-21-2015, 03:01 PM
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