Double excution from custom control (Excel VBA)

[Zauberkind]

Greetings,
I’ve been playing around with adding controls to the menu bar to call subs in an xla.
The idea is to add a menu to the menu bar and call the sub. One string parameter is passed.

The menu setup:

Code: Select all

    Set objNewMenu = objMenuBar.Controls.Add(Type:=msoControlPopup, Temporary:=True)
    objNewMenu.Caption = cMenuCaption
    If sMenuID = "" Then sMenuID = GetUniqueMenuID  ' tie menu to program instance
    objNewMenu.Tag = sMenuID

    Set objNewMenuItem = objNewMenu.Controls.Add(Type:=msoControlButton)
    With objNewMenuItem
        .Caption = "&Increment"
        .FaceId = 329
        .BeginGroup = True
        .OnAction = "'D:\VBA Development\Experiments\AddInModule.xla'!Increment(""rope"")"
    End With ' objNewMenuItem

    Set objNewMenuItem = objNewMenu.Controls.Add(Type:=msoControlButton)
    With objNewMenuItem
        .Caption = "Stop!"
        .FaceId = 480
        .BeginGroup = False
        .OnAction = "'D:\VBA Development\Experiments\AddInModule.xla'!stopper"
    End With ' objNewMenuItem

The xla contains two subs, Increment and Stopper:

Code: Select all

Private slCounter As Long

Public Sub Increment(sAction As String) ' increment counters;
                                        ' document workbook name/counter values to immediate
    Dim wbCaller As Workbook
    Static slSubCounter As Long

    Debug.Assert False
    Set wbCaller = ActiveWorkbook
    slCounter = slCounter + 1
    slSubCounter = slSubCounter + 1
    Debug.Print sAction; " "; wbCaller.Name, "Counter="; slCounter, "Sub Counter="; slSubCounter

End Sub ' Increment

Public Sub Stopper()
Debug.Assert False
End Sub ' Stopper

My expectation was that clicking 'increment' should print a line in the immediate window.
It does that, then it prints a second line!

rope TestBook3.xls Counter= 1 Sub Counter= 1
rope TestBook3.xls Counter= 2 Sub Counter= 2

The sub is obviously being executed twice, but I can't figure out how or why!
The Debug.Assert False I put in to halt execution, so I could look at what was going on, is ignored.
Stopper works as expected...Click the control, execution halts in the sub.
Any explanations or insights appreciated in advance.
Regards,
ZK

[HansV]

I can't explain this behavior, but I too have noticed that the action of custom toolbar buttons/menu actions gets called more than once. I don't think you can prevent that.

[Zauberkind]

That's scary, since there's no real way to detect it!
ZK

[HansV]

I found a solution (Google is my friend): if you pass an argument in the OnAction property, enclose the call in single quotes.

Code: Select all

        .OnAction = "'D:\VBA Development\Experiments\AddInModule.xla'!'Increment(""rope"")'"

or

Code: Select all

        .OnAction = "'D:\VBA Development\Experiments\AddInModule.xla'!'Increment ""rope""'"

I believe this technique, which is not documented by Microsoft, was first "discovered" by Excel MVP Tom Ogilvy.

[Zauberkind]

Being the curious (and somewhat obsessive) sort, I tried a few things. I don't have any explanation, but an observation:
.OnAction = "'D:\VBA Development\Experiments\AddInModule.xla'!Increment(""rope"")"
.OnAction = "'D:\VBA Development\Experiments\AddInModule.xla'!IncrementQ()"
The sub gets executed twice; the embedded DEBUG.ASSERT FALSE does not halt execution.
.OnAction = "'D:\VBA Development\Experiments\AddInModule.xla'!'Increment ""rope""'"
The sub gets executed once; the embedded DEBUG.ASSERT FALSE halts execution.

I don't know why, and I doubt I'll ever find out for sure, but it's the PARENs that do it!

ZK

[HansV]

My results are slightly different:

Code: Select all

.OnAction = "'Test.xlam'!Increment ""Hello"""      ' causes an error - macro not found
.OnAction = "'Test.xlam'!Increment(""Hello"")"     ' runs twice
.OnAction = "'Test.xlam'!'Increment ""Hello""'"    ' runs once
.OnAction = "'Test.xlam'!'Increment(""Hello"")'"   ' runs once

[Zauberkind]

Hi Hans,

Your results are slightly different because you tested slightly different things.
I've run through all eight variations of what can be after the xla name in the .OnAction property:

Code: Select all

!SubName - runs once
!SubName() - runs twice
!SubName "parm" - macro not found
!SubName("parm")  - runs twice
!'SubName' - runs once
!'SubName()' - macro not found
!'SubName "parm"' - runs once
!'SubName ("parm")' - runs once

Odd observation: when the sub runs twice, DEBUG.ASSERT and debugger breakpoints are ignored

Your first solution was correct: the key seems to be wrapping the call in single quotes. It must have something to do with how the .OnAction string is parsed.

Regards,
ZK

[rory]

It's the same behaviour if you use application.run and include parentheses in the routine name.

[Zauberkind]

While it seems clear that some common mechanism is at work here, there is still a trap for the unwary.

Application.Run should pass on the return value if [Macro] is a function.

I extended the TestCase() sub to be a function which returns the counter as a long, and added two new testcases in the driver:

Code: Select all

lCounter = Application.Run(sXLAname & "'TestCase ""nine"", ""nineA""'")
Debug.Print "lCounter="; lCounter
lCounter = Application.Run(sXLAname & "TestCase", "ten", "tenA")
Debug.Print "lCounter="; lCounter

The first passes the macroname and a single string in single quotes; the second passes the macroname and two strings.
Both execute once and receive two parameters (as expected); DEBUG.ASSERT FALSE functions normally.

The results in the immediate window:
Testcase Double Tap Driver.xls Parm=nine Len= 4 Counter= 58
Testcase Double Tap Driver.xls Parm2=nineA Len= 5
lCounter= 0
Testcase Double Tap Driver.xls Parm=ten Len= 3 Counter= 59
Testcase Double Tap Driver.xls Parm2=tenA Len= 4
lCounter= 59

The first call appears to operate normally, but the return value gets lost en route.

Regards,
ZK

[HansV]

Yep, you have to pass the arguments separately, not as part of the Macro argument.