Called or Directly excecuted routine

Hi Forum Friends
I want to thank you all for the solutions/support I received in completing my (for me) difficult workbook.
I still have a tricky problem. In the example below, can Multibeep "know" when it is being called from Beep versus being excuted directly? And if so can I direct a different sequence?

Sub Beep_21()    
    Call MultiBeep(5)
End Sub

'----------------------------------------------
Sub MultiBeep(numbeeps)
    For counter = 1 To numbeeps
        Beep
    Next counter
    If called by Beep then
        do A
    Else
        do B
    End if
End Sub

Of course in valid code.
Many thanks
Al

You could add an (optional) variable to enter when you call multibeep. I'd go with boolean, then branch if var = true ...
else...
Make sense?

I'm still trying figure out how to directly activate a macro that has required initiation variables. I can't get it to activate, only when it is called from the other routine that is feeding in the variable.

These don't work at all:

Option Explicit

Sub MultiBeep(numbeeps)
        MsgBox "Hi there"
End Sub

Sub MultiBeep(numbeeps As Long, Optional Remote As Boolean)
    For counter = 1 To numbeeps
        Beep
    Next counter
    If Remote Then
        MsgBox "called remotely"
    Else
        MsgBox "called directly"
    End If
End Sub

Here is what I have (based off yours JB). Seems to work fine.

Public Sub multibeep(numbeeps As Long, Optional remote As Boolean)

    For i = 1 To numbeeps
        MsgBox i
    Next i
    
    If remote = True Then
        MsgBox "Called Remotely"
    Else
        MsgBox "Called Directly"
    End If
    
End Sub


Public Sub testdirect()

multibeep 2

End Sub

Public Sub testremote()

multibeep 2, True

End Sub

Glad you got what you needed, but as I stated originally and also is still true for Dave's method based off of mine, I cannot get the "MultiBeep" macro to initiate directly. I can only get it to run when another macro activates it while feeding in variables.

Dave, how do you get it to activate without a second macro? I thought that was the whole point?

The macro "testdirect" doesn't actually resolve the original request since it, too, is actually a remote activation.

Seems an interesting thread - by directly do you mean from XL - ie via F8 ?

If you do then you could use Module level variables (or Public if nec.), eg:

Dim numbeeps As Long
Public Sub multibeep()
    Dim i As Byte
    For i = 1 To IIf(numbeeps, numbeeps, 1)
        MsgBox i
    Next i
    If numbeeps Then
        MsgBox "Called Remotely"
    Else
        MsgBox "Called Directly"
    End If
    numbeeps = 0
End Sub

Public Sub testdirect()
numbeeps = 2
multibeep
End Sub

You could thus call multi beeps from XL via F8 or "indirectly" from another sub routine - assumption being for the latter that you would alter the local variable accordingly.

Is that what you meant ?

Sorry if I've misinterpreted

(you could add Input dialog to trap requirements where beep = 0 if variable no. of beeps required)

You are correct, I just suggested a work-around. You are correct though, it doesn't answer the request as specified. I did not read the initial request carefully enough.

I think the work-around would be simpler, but you could create a global boolean variable that you could toggle to true before calling the macro from a different procedure. Like:

Global Called As Boolean


Public Sub multibeep(numbeeps As Long)
    For i = 1 To numbeeps
        MsgBox i
    Next i
    
    If Called = True Then
        MsgBox "Called Remotely"
    Else
        MsgBox "Called Directly"
    End If
    
End Sub



Public Sub testremote()

Called = True
multibeep 2

End Sub

This however still wouldn't work if you want numbeeps to be an argument. You could also make that a global variable, but then you are getting very complicated, and I think the work-around is much simpler.

Global Called As Boolean
Global numbeeps As Long

Public Sub multibeep()

    If numbeeps = 0 Then
        numbeeps = Int(InputBox("How many beeps?", "?")) 'Or other source such as worksheet cell.
    End If
    
    For i = 1 To numbeeps
        MsgBox i
    Next i
    
    If Called = True Then
        MsgBox "Called Remotely"
    Else
        MsgBox "Called Directly"
    End If
    
End Sub



Public Sub testremote()

numbeeps = 2
Called = True
multibeep
numbeeps = 0
Called = False

End Sub

Does that seem reasonable?

Originally Posted by davegugg

You are correct, I just suggested a work-around. You are correct though, it doesn't answer the request as specified. I did not read the initial request carefully enough.

I think the work-around would be simpler, but you could create a global boolean variable that you could toggle to true before calling the macro from a different procedure. Like:

Global Called As Boolean


Public Sub multibeep(numbeeps As Long)
    For i = 1 To numbeeps
        MsgBox i
    Next i
    
    If Called = True Then
        MsgBox "Called Remotely"
    Else
        MsgBox "Called Directly"
    End If
    
End Sub



Public Sub testremote()

Called = True
multibeep 2

End Sub

This however still wouldn't work if you want numbeeps to be an argument. You could also make that a global variable, but then you are getting very complicated, and I think the work-around is much simpler.

Global Called As Boolean
Global numbeeps As Long

Public Sub multibeep()

    If numbeeps = 0 Then
        numbeeps = Int(InputBox("How many beeps?", "?")) 'Or other source such as worksheet cell.
    End If
    
    For i = 1 To numbeeps
        MsgBox i
    Next i
    
    If Called = True Then
        MsgBox "Called Remotely"
    Else
        MsgBox "Called Directly"
    End If
    
End Sub



Public Sub testremote()

numbeeps = 2
Called = True
multibeep
numbeeps = 0
Called = False

End Sub

Does that seem reasonable?

Yes, the InPutBox thing is exactly what I came up with originally, but was stumped by trying to get the original macro to activate directly somehow and never finished that. Ah well, onward and upward.

Using the Macro Dialog box, there is no way to pass a arguments to a Macro.
Therefore, if a Macro has arguments it will not appear in the dialog box's list.
Even if its arguments are optional.

If a Macro has only optional arguments, it can be called from the Macro Dialog box by typing the name of the maco into the textbox (even though that name does not appear on the list.)

Instead of introducing new arguments, you could make numBeeps an optional argument.
If numBeeps equals its default value, then multiBeeps was called from the Macro dialog box (or was called by another macro that passed no argument).
Otherwise, multBeep was called by some other macro, which did pass an argument.

Sub MultiBeep(Optional numBeeps As Long)
    Dim counter As Long
    If numBeeps = 0 Then
        Rem called from Macro Dialog box
        numBeeps = 1
        'do B
    Else
        Rem argument passed when called by non-dialog box
        'do A
    End If
    For counter = 1 To numBeeps
        Beep
    Next counter
End Sub

Since your numBeeps is type Variant, an IsMissing test could do the same thing.

Sub MultiBeep(Optional numBeeps)
    Dim counter As Long
    If IsMissing(numBeeps) Then
        Rem called from Macro Dialog box
        numBeeps = 1
        'do B
    Else
        Rem argument passed when called by non-dialog box
        'do A
    End If
    For counter = 1 To numBeeps
        Beep
    Next counter
End Sub

Originally Posted by mikerickson

If a Macro has only optional arguments, it can be called from the Macro Dialog box by typing the name of the maco into the textbox (even though that name does not appear on the list.)

I did not know this - thanks.