Late Binding for Excel CommandBar and CommandBarControl

[samadams_2006]

Hello,

I have the following piece of code in an Excel .xla add-in:

---------------------------------------------------------------------------------------------------------------------------

Dim MenuBar As CommandBar
Dim MenuItem As CommandBarControl

Application.CommandBars("Worksheet Menu Bar").Controls("New
Menu").Delete
Set MenuBar = Application.CommandBars("Worksheet Menu Bar")
Set MenuItem = MenuBar.Controls.Add(Type:=msoControlPopup)
MenuItem.Caption = "New Menu"

---------------------------------------------------------------------------------------------------------------------------

This works fine when I have the following Reference checked:

Microsoft Office 11.0 Object Library

Since not all people who will be using this .xla will have this version
of the Object Library, I would like to change this from Early Binding
to Late Binding. Unchecking this Reference gives me numerous problems
when I try to run the program.

Can anyone tell me what the corresponding code would be for "Late
Binding"?

Thanks,
Sam.

 

[Bob Phillips]

I must admit to having never come across this problem.

I don't see how you can get around it as you have to reference a library to
develop it.

My only thought is to develop in your user's lowest version if you can get
hold of a copy.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Peter T]

Hi Sam,

In general Late Binding means declaring all object references that will use
a given library 'As Object' and when automating use CreateObject(some app)
rather than New some-app. Some other things like using constant values
rather than named constants from the library.

However I don't think this is the issue of your problem and converting to
Late binding won't resolve. Simply compile and save your xla in the lowest
version of Excel & Office of any of your anticipated users.

Regards,
Peter T