Using Application.OnTime

[Lavina]

Hello guys,

I'm trying to setup a timeout for a macro I'm creating, it reaches out to a file, if that file is open it times out for 10 sec and then tries again.
My initial approach was to make a do loop for x seconds, but that was an awful idea because it prevents other actions from happening.

My second approach landed me on: Application.OnTime.

After reading the documentation: https://docs.microsoft.com/en-us/office/vba/api/excel.application.ontime
I was surprised that: "Procedure[FONT=&quot] must take no arguments and cannot be declared in a custom class or form."
Why is it so? What is the normal work around?

Calling a sub that calls a sub with arguments just [/FONT]feels incompetent.

 

[shg]

You can pass scalar arguments, it's just a little fussy:

Sub main()
  Range("A1").Value = "Bob"
  Application.OnTime EarliestTime:=Now(), Procedure:=CallString("mySub", "Test", "A1")
End Sub

Sub mySub(arg1 As String, arg2 As String)
  MsgBox arg1 & " " & Range(arg2).Value
End Sub

Function CallString(sProc As String, ParamArray av() As Variant) As String
  ' shg 2014

  Dim i             As Long

  If UBound(av) = -1 Then
    CallString = sProc
  Else
    CallString = "'" & sProc & " "
    For i = 0 To UBound(av)
      If VarType(av(i)) = vbString Then av(i) = """" & av(i) & """"
    Next i
    CallString = CallString & Join(av, ", ") & "'"
  End If
End Function