How to Late-Bind the Microsoft Office Object Library in VBA
Question details
The user needs to late-bind the Microsoft Office Object Library to remove hardcoded references for distributing files (such as an Access ACCDE) but encounters an error when using CreateObject.
- Product
- VBA
- Device & OS
- not provided
- Scenario
- Attempting to remove the Microsoft Office Object Library reference by using late binding via CreateObject("Office.Application").
- Observed behavior
- The code fails because the Office Object Library does not expose an Office.Application object that can be instantiated with the CreateObject function.
Before transitioning your project from early binding to late binding, ensure you have a backup of your VBA code and temporarily keep the Office Object Library reference active to easily look up constant values.
Declare Variables as Object and Replace mso Constants
Use generic Object declarations and substitute named enumerations with their numeric equivalents to run code without the Office Object Library reference.
The Microsoft Office Object Library does not contain an 'Application' class, which means CreateObject("Office.Application") will always fail. To successfully late-bind and remove the library reference, you must declare all Office-related objects generically and replace built-in constants with their actual numerical values.
In your VBA editor, locate any variables declared with specific Office types (e.g., Dim cb As CommandBar) and change them to generic objects (e.g., Dim cb As Object).
While the reference is still active, press F2 in the VBA editor to open the Object Browser. Select 'Office' from the top-left dropdown, and search for the specific constants you are using (such as msoControlButton). Click on the constant to view its numeric value at the bottom of the window.
Replace the named Microsoft Office constants in your VBA code with the numeric values you found. Alternatively, define them yourself at the top of your module (e.g., Const msoControlButton = 1).
Go to Tools > References in the VBA editor menu, find 'Microsoft Office [Version] Object Library', uncheck the box next to it, and click OK. Run your code to ensure no 'User-defined type not defined' errors occur.
Experience Seamless VBA Support with WPS Office
If you are working extensively with VBA macros and looking for a cost-effective solution, WPS Office offers a highly compatible and lightweight alternative to Microsoft Office. It fully supports VBA in spreadsheets and documents without the heavy subscription fees.
- 1. Download WPS Office: Visit the official WPS Office website and download the free installation package.
- 2. Open your macro files: Launch WPS Office and open your existing .xlsm, .docm, or other macro-enabled files.
- 3. Enable and run macros: Click 'Enable Macros' when prompted by the security warning to allow your VBA code to execute seamlessly.
Frequently Asked Questions
Why does CreateObject("Office.Application") throw an error in VBA?
The Microsoft Office Object Library is a shared component library, not a standalone application. Therefore, it does not expose an application-level object named 'Office.Application' that can be instantiated via CreateObject. You must bind to specific applications like 'Excel.Application' or 'Word.Application'.
What is the main advantage of late binding in VBA?
Late binding resolves object types at runtime rather than compile time. This prevents broken 'MISSING' reference errors when distributing your file to users who might have different versions of Office installed.
How do I find the numeric value of an mso constant without the Object Browser?
If you have already removed the reference and cannot use the Object Browser, you can temporarily add the reference back, or look up the specific Microsoft Office enumeration (such as MsoControlType) in the official Microsoft documentation online to find the corresponding integer values.
Does WPS Office support late binding in macros?
Yes, WPS Office's built-in Macro editor fully supports standard VBA syntax, including late binding techniques using the CreateObject function for supported application objects.
