none
passing arguments to macros through buttons and during application.ontime call RRS feed

  • Question

  • Hello,

    I am pretty much a new user in Excel VBA so I would like to ask a basic programming question.

    Is it possbile to pass an input arguement during a call to a macro through a button?

    Or when it is called by certain event driven procedures like application.ontime

    thanks a lot

    Tuesday, February 7, 2012 3:43 PM

Answers

  • Is it possbile to pass an input arguement during a call to a macro through a button?

    Sort of. If the idea is for the macro to do something different depending on which button's onaction macro called it, give the button a unique name and in the macro -

    sButtonCaller = Application.Caller
    Select Case sButtonCaller
       etc

    To pass arguments with an OnTime macro here's a demo I posted in the old Excel group, gosh five years ago!

    https://groups.google.com/group/microsoft.public.excel.programming/msg/921645e373a522ff?

    Sub test()
    Dim nVar1 As Long, nVar2 As Long
    Dim sVar1 As String, sVar2 As String
    Dim sMacro As String
    
    ' these four Ontimes will run in order from last to first
    
    '''' number values
    sMacro = "    'MacroNum ""123"" , ""789"" ' "
    
    Application.OnTime Now, sMacro
    
    '''' number variables
    nVar1 = 1111: nVar2 = 2222
    sMacro = "    'MacroNum " & nVar1 & " , " & nVar2 & " ' "
    
    Application.OnTime Now, sMacro
    
    '''' string values
    sMacro = "    'MacroStr ""Prompt value1"", ""Title value2"" ' "
    
    Application.OnTime Now, sMacro
    
    '''' string variables
    sVar1 = "Prompt var1": sVar2 = "Title var2"
    sMacro = " 'MacroStr " & Chr(34) & sVar1 & Chr(34) & _
                                 ", " & Chr(34) & sVar2 & Chr(34) & " ' "
    
    Application.OnTime Now, sMacro
    
    End Sub
    
    Sub MacroStr(s1 As String, s2 As String)
         MsgBox s1, , s2
    End Sub
    
    Sub MacroNum(n1 As Long, n2 As Long)
         MsgBox n1, , n2
    End Sub

    Peter Thornton

    • Marked as answer by sen saven Wednesday, February 8, 2012 8:41 AM
    Tuesday, February 7, 2012 7:17 PM
    Moderator

All replies

  • Yes, but you must build-in a mechanism to supply the arguement.  If the button calls Macro1, have Macro1 call Macro2:

    Sub Macro1()
    Dim a As String
    a = "hello"
    Call Macro2(a)
    End Sub

    Sub Macro2(m As String)
    MsgBox m
    End Sub


    gsnu201202

    Tuesday, February 7, 2012 4:56 PM
    Moderator
  • thanks a lot,

    any hint about the ontime problem?

    for example in the following code:

    //////

    Application.OnTime Now + TimeValue("00:00:30"), "Sheet1.RefreshData" 

    ////

    is it possble to pass an argument to RefreshData?

    Tuesday, February 7, 2012 6:08 PM
  • Is it possbile to pass an input arguement during a call to a macro through a button?

    Sort of. If the idea is for the macro to do something different depending on which button's onaction macro called it, give the button a unique name and in the macro -

    sButtonCaller = Application.Caller
    Select Case sButtonCaller
       etc

    To pass arguments with an OnTime macro here's a demo I posted in the old Excel group, gosh five years ago!

    https://groups.google.com/group/microsoft.public.excel.programming/msg/921645e373a522ff?

    Sub test()
    Dim nVar1 As Long, nVar2 As Long
    Dim sVar1 As String, sVar2 As String
    Dim sMacro As String
    
    ' these four Ontimes will run in order from last to first
    
    '''' number values
    sMacro = "    'MacroNum ""123"" , ""789"" ' "
    
    Application.OnTime Now, sMacro
    
    '''' number variables
    nVar1 = 1111: nVar2 = 2222
    sMacro = "    'MacroNum " & nVar1 & " , " & nVar2 & " ' "
    
    Application.OnTime Now, sMacro
    
    '''' string values
    sMacro = "    'MacroStr ""Prompt value1"", ""Title value2"" ' "
    
    Application.OnTime Now, sMacro
    
    '''' string variables
    sVar1 = "Prompt var1": sVar2 = "Title var2"
    sMacro = " 'MacroStr " & Chr(34) & sVar1 & Chr(34) & _
                                 ", " & Chr(34) & sVar2 & Chr(34) & " ' "
    
    Application.OnTime Now, sMacro
    
    End Sub
    
    Sub MacroStr(s1 As String, s2 As String)
         MsgBox s1, , s2
    End Sub
    
    Sub MacroNum(n1 As Long, n2 As Long)
         MsgBox n1, , n2
    End Sub

    Peter Thornton

    • Marked as answer by sen saven Wednesday, February 8, 2012 8:41 AM
    Tuesday, February 7, 2012 7:17 PM
    Moderator