calling a procedure with parameters

[Lino]

I am using the following function and at the same time would like call another procedure with parameters

Application.OnTime (Now + TimeValue("00:00:02"), "Do This"

works just fine

If I try to pass parameters it doesn't

Application.OnTime (Now + TimeValue("00:00:02"), "DoThis(color, day, week)"

any suggestions?

Thanks,

Lino

 

[Scott Huish]

Not sure if this will help, but first off, is your sub statement set up to accept parameters? Secondly, I imagine that your use of VBA/Excel Function and Property names for variable names is probably causing problems. When I tried to use your variable names, I got all kinds of errors, changing them to a, b, and c fixed it. I wasn't sure what type of parameter you were trying to send with color.

Example:

Sub doThis(ByRef a As Long, b As Integer, c As Long)
MsgBox "Color=" & a & " Day=" & b & " Year=" & c
End Sub

Sub calldo()
Dim a As Long
Dim b As Integer
Dim c As Long
a = 255
b = 17
c = 2004
Call doThis(a, b, c)
End Sub

 

[Juan Pablo González]

Look at this post by Chip Pearson:

http://tinyurl.com/36jyc

 

[Lino]

this is what i have and it is not working

The parameters are all variables that i assigned values to in the current procedure


Application.OnTime Now + TimeValue("00:00:02"), _
" 'CalculateThis " & DollarAmount & "," & Net & "," & SRow & "," & FinalRow & "," & Tax & " ' "

the vbe doesn't complain but when the code runs an error message comes up and says that it can't find the macro 'CalculateThis.....'

Right below this procedure is another procedure called

Sub CalculateThis(DollarAmount, Net, SRow, FinalRow, Tax)

so I don't know why it can't find it?

Any suggestions?

Lino

 

[Juan Pablo González]

These three worked for me in Excel 2000

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> CalculateThis(DollarAmount, Net, SRow, FinalRow, Tax)
    MsgBox DollarAmount
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>


<SPAN style="color:#00007F">Sub</SPAN> Test()
    Application.OnTime Now + TimeSerial(0, 0, 2), _
        "'CalculateThis 1,2,3,4,5 '"
    Application.OnTime Now + TimeSerial(0, 0, 4), _
        "'CalculateThis " & 1 & "," & 2 & "," & 3 & "," & 4 & "," & 5 & " '"
        
    <SPAN style="color:#00007F">Dim</SPAN> DollarAmount <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, Net <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, SRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, FinalRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, Tax <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    DollarAmount = 1
    Net = 2
    SRow = 3
    FinalRow = 4
    Tax = 5
    
    Application.OnTime Now + TimeSerial(0, 0, 6), _
        "'CalculateThis " & DollarAmount & "," & Net & "," & SRow & "," & FinalRow & "," & Tax & " '"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Lino]

I am using TimeValue...could that be the problem?

 

[Juan Pablo González]

Did it work with TimeSerial() ? I like TimeSerial because i like being able to enter hour, minute and seconds separately, but both should work...

 

[DRJ]

I tried Juan's code with time value and it works fine.

Option Explicit

Sub CalculateThis(DollarAmount, Net, SRow, FinalRow, Tax)
    MsgBox DollarAmount
End Sub


Sub Test()
    Application.OnTime Now + TimeValue("00:00:02"), _
        "'CalculateThis 1,2,3,4,5 '"
    Application.OnTime Now + TimeValue("00:00:04"), _
        "'CalculateThis " & 1 & "," & 2 & "," & 3 & "," & 4 & "," & 5 & " '"
        
    Dim DollarAmount As Long, Net As Long, SRow As Long, FinalRow As Long, Tax As Long
    DollarAmount = 1
    Net = 2
    SRow = 3
    FinalRow = 4
    Tax = 5
    
    Application.OnTime Now + TimeValue("00:00:06"), _
        "'CalculateThis " & DollarAmount & "," & Net & "," & SRow & "," & FinalRow & "," & Tax & " '"
End Sub