Can't Call Procedure "Invalide Qualifier"

[grrr223]

I'll try to explain this as simply as possible.

I am creating an add-in with a bunch of toolbars. I want the common subs such as AddToolbartoMenu() and DeleteToolbar() to be contained in one module for the obvious simplicity in making changes. But I want the individual toolbars to each have a module with its create statement and associated macros for its menus and buttons.

My problem is that I have been VERY unsuccessful calling the CreateToolbar() subs in the individual modules. Here is what I am doing:

The CreateToolbar() sub is called from the common sub ShowToolbar(). If the toolbar doesn't exist, it is created first by calling the CreateToolbar() sub, and then made visible.

Sub ShowToolbar(Optional TOOLBAR As String, Optional MODNAME As String)

' TOOLBAR is just the name of the toolbar
' MODNAME is the name of the module it is in

' If toolbar exists toggle display
Dim ComBar As CommandBar

On Error Resume Next
Set ComBar = Application.CommandBars(TOOLBAR)
If Err.Number = 0 Then
ComBar.Visible = Not ComBar.Visible
On Error GoTo 0
Exit Sub
Else
' THIS IS WHAT'S CAUSING THE PROBLEM
Call MODNAME.CreateToolbar(TOOLBAR)
End If
On Error GoTo 0
End Sub

So the command that I'm trying to run when the workbook opens is simply

Call ShowToolbar("Navigation Toolbar", "NavigationToolbar&quot

However when it runs I get "Invalid Qualifier" referring to the variable MODNAME.

PLEASE, How do I call this procedure with all these variables in it? If I replace the variable name MODNAME with the text itself, it works like this:

CALL NavigationToolbar.Createtoolbar(TOOLBAR)

But if I do this, it won't work still

MODNAME = "NavigationToolbar"
CALL MODNAME.CreateToolbar(TOOLBAR)

I don't want to change the type of MODNAME to VBCodeModule if I don't have to because of the security issues, but if it's the only way I guess I can.

Thank you all in advance

 

[pvw]

Hi grrr223

Instead of
Call MODNAME.CreateToolbar(TOOLBAR)
use
Application.Run MODNAME & ".CreateToolbar", TOOLBAR

In your code both arguments of CreateToolbar are optional. You can call the sub without these arguments:
Application.Run MODNAME & ".CreateToolbar"

If only the second parameter (MODNAME) should be used, you can pass the parameter as named argument:
Application.Run MODNAME & ".CreateToolbar", arg2:="xyz"

HTH
Philipp

 

[grrr223]

Thank you very much, it would have been nice if it were mentioned ANYWHERE in the help for the CALL command that you can't pass it the name of a procedure as a string variable. Oh well, live and learn. The frustrating part is that it is such a simple fix, yet I probably spent 4 hours trying to get it to run with CALL, what a @#$@#$.

I haven't tried it yet, but this is copied from the entry for Application.run in the help system for VBE:

Remarks
You cannot use named arguments with this method. Arguments must be passed by position.

While I haven't tried it, I think this means that your last example won't work. In order to pass just the second argument, you have to just add another comma, like this:

Application.Run MODNAME & ".CreateToolbar", ,"xyz"

The optional arguments in the sub above are for the ShowToolbar() procedure which is called from a menu item, and when it is called that way, I again was having trouble passing arguments to the procedure so I just pull the arguments out of the Controlbars.ActionControl property. The one argument for CreateToolbar() is not optional.

 

[pvw]

Hi grrr223

I just checked the Excel VBA help. The remark about named arguments is correct but incomplete. My last example works because I used the argument name of the run method instead of the argument name of the CreateToolbar sub. Assuming CreateToolbar has 10 optional arguments and the 10th argument is needed, it's no problem to use
Application.Run MODNAME & ".CreateToolbar", arg10:="xyz"
Ok, the programmer has to know that the parameter he wants to pass is at position 10 since he must write 'arg10'. However, passing by position is not really required like
[].Run MODNAME & ".CreateToolbar", , , , , , , , , , "xyz"

But you're right. In this special case I didn't use the term 'named argument' in a proper way.

Philipp