Fix Excel Macro ObjectThemeColor Error When Changing Shape Color
Question details
The user encounters an out-of-range error with the ObjectThemeColor property when running an Excel VBA macro that was recorded to change a shape's fill color.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Executing a recorded VBA macro to automatically apply theme colors to a shape in a worksheet.
- Observed behavior
- The macro execution stops and displays an out-of-range error on the ObjectThemeColor property, often because a theme constant lacks a valid value or an object library reference is missing.
Before modifying your VBA code, press Ctrl + S to save a backup of your current workbook and ensure the Developer tab is visible on your Excel ribbon.
Add the Microsoft Office Object Library Reference
Enabling the correct Object Library reference ensures that Excel VBA can recognize and correctly apply theme constants like msoThemeColorAccent5.
When recording macros that interact with shapes and theme colors, VBA relies on the Microsoft Office Object Library to understand the theme constants. If this reference is missing or disabled in your workbook, the code will fail to evaluate the colors.
Press Alt + F11 on your keyboard to launch the Visual Basic for Applications (VBA) editor.
In the top menu bar, click on 'Tools' and select 'References' from the dropdown list.
Scroll through the available references and check the box next to 'Microsoft Office 16.0 Object Library' (the version number may vary depending on your Office installation).
Click 'OK' to apply the changes, close the References dialog, and run your macro again.
Hard-code the Theme Constant Numeric Value
If the theme constant evaluates to 'Empty' in a specific workbook, replacing the variable with its numeric equivalent can bypass the error entirely.
Reference the Shape Directly in VBA
Using direct object referencing instead of the default 'Selection' method improves macro reliability and prevents targeting errors.
A Lightweight Alternative for Spreadsheet Management
If you frequently encounter VBA issues or require a reliable, lightweight suite for your data, WPS Office is an excellent free alternative. It offers high compatibility with Microsoft formats, ensuring a seamless transition.
- 1. Download and Install: Visit the official WPS Office website to download the free installer and follow the setup instructions.
- 2. Open Your Files: Launch WPS Spreadsheet and easily open your existing .xlsx or .xlsm files.
- 3. Work Seamlessly: Enjoy familiar spreadsheet features, formulas, and formatting tools without compatibility concerns.
Frequently Asked Questions
What causes the ObjectThemeColor out-of-range error in Excel VBA?
This error typically occurs when the Microsoft Office Object Library reference is missing in the VBA editor, or when the workbook fails to assign a valid numeric value to standard theme color constants like msoThemeColorAccent5.
How do I access the VBA References menu in Excel?
Open the VBA Editor by pressing Alt + F11. Then, click on 'Tools' in the top menu bar, and select 'References' from the dropdown list to manage your active libraries.
Can I avoid using the Selection object for modifying shapes in VBA?
Yes, it is highly recommended to reference shapes directly. You can do this by defining a Shape variable and assigning it the specific shape by name, for example: Set shp = ActiveSheet.Shapes("Rectangle 1"), which greatly improves code reliability.
