Application.OnTime -- Unable to Pass Macro with Numeric Parameter

[Butaambala]

I am having trouble with Application.OnTime when specifying a Macro
with Numeric (Byte) parameter. Here is the code:

macro I'm calling:
Public Sub mcrExtractIntraData(bteIntraday As Byte)

Failed attempts:
1)
NextIntradayTime = Now + TimeValue("00:01:00")
strProc = "mcrExtractIntraData" & " 1"
Application.OnTime NextIntradayTime, strProc

2)
NextIntradayTime = Now + TimeValue("00:01:00")
Application.OnTime NextIntradayTime, "mcrExtractIntraData " & 1

3)
NextIntradayTime = Now + TimeValue("00:01:00")
Application.OnTime NextIntradayTime, "'mcrExtractIntraData 1'"

Any help greatly appreciated.

 

[Tom Ogilvy]

don't pass a byte. Change your function to expect an integer or a long.

 

[Butaambala]

thanks, Tom. Ok, so now the macro I'm calling is:
Public Sub mcrExtractIntraData(intIntraday As Integer)

All three attempts still fail. Here are the results:
1) The macro "C:\Test.xls'!mcrExtractIntraData 0' cannot be found.
2) SAME
3) Nothing happens at all (procedure not called)

 

[Tom Ogilvy]

There has been some discussion that xl2002 and later did not support this
construct (it is undocumented which has always meant use at your own risk
for it may go unsupported). I don't remember the details - but as I said, I
heard it is no longer supported and I couldn't get it to work in xl2003.

The syntax that works in earlier versions is:

Application.OnTime Now() + TimeValue("00:00:05"), _
"'checkOntime.xls!mcrExtractIntraData 1'"
End Sub

to the best of my recollection.

 

[Tushar Mehta]

The foll. works OK with 2003:

Public Sub mcrExtractIntraData(bteIntraday As Byte)
MsgBox bteIntraday
End Sub

Sub testIt2()
Dim NextIntradayTime As Date
NextIntradayTime = Now + TimeValue("00:00:03")
Application.OnTime NextIntradayTime, "'mcrExtractIntraData 1'"
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Tushar Mehta]

Hi Tom,

Maybe others had too, but I for sure did point out it was broken
w/2002. That was in the context of an argument of a sub assigned to
the OnAction property of a button.

However, it started working with some support release of 2002.

As far as I can recall, MS acknowledged neither the problem nor the
fix. ;-)

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Tom Ogilvy]

Tushar,

It worked for me as well - good find. Must be the "!" that is causing the
problem or it just doesn't like to include the workbook name.

 

[Tushar Mehta]

Hi Tom,

It appears one has to get the single and double quotes just right. I
tested with the called routine in the same module, in another module,
and in two different modules in a different workbook. Here's a
summary.

But before that, a note about the use of this undocumented feature
(trick?). At least the first version shown below (i.e., without
specifying the workbook or module) works with:

* the OnAction property of a forms(?)/commandbar(?) control,
* XL's OnKey method (coincidentally, I tested it a few days ago), and
* XL's OnTime method.

In any case, back to the tests with OnTime...

I had a function similar to the one below in each of the four modules
(module1 and module2 in the workbook scheduling the calls, and module1
and module2 in another workbook).

Public Sub mcrExtractIntraData(bteIntraday As Byte)
MsgBox "Same module " & bteIntraday
End Sub

Each subroutine differed only by the string literal part. That made it
possible to identify which function was actually called.

We already know that testIt1 works.

Sub testIt1()
Dim NextIntradayTime As Date
NextIntradayTime = Now + TimeValue("00:00:03")
Application.OnTime NextIntradayTime, "'mcrExtractIntraData 1'"
End Sub

The next was to call the subroutine in another module. The correct
syntax is shown in testIt2.

Sub testIt2()
Dim NextIntradayTime As Date
NextIntradayTime = Now + TimeValue("00:00:03")
Application.OnTime NextIntradayTime, _
"'module2.mcrExtractIntraData 1'"
End Sub

The next was to call the subroutine in another workbook. The correct
syntax turned out to be:

Sub testIt3()
Dim NextIntradayTime As Date
NextIntradayTime = Now + TimeValue("00:00:03")
Application.OnTime NextIntradayTime, _
"'g:\temp\book3.xls'!'module1.mcrExtractIntraData 1'"
Application.OnTime NextIntradayTime, _
"'g:\temp\book3.xls'!'module2.mcrExtractIntraData 1'"
End Sub

I discovered the workbook has to be saved. If it is not and the
workbook name is specified as jsut 'book3', XL/VBA tries book3.xls and
book3.htm and, obviously, fails in both cases.

As a final test, I scheduled a call to a subroutine that expected a
string argument. It worked with the syntax below:

Sub testIt4()
Dim NextIntradayTime As Date
NextIntradayTime = Now + TimeValue("00:00:03")
Application.OnTime NextIntradayTime, _
"'g:\temp\book3.xls'!'SubWithStringParam ""1""'"
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions