VBA & Macro Problems

How to Convert a VBA Color Constant String to Its Long Value in Excel

Rana Garcia Oct 13, 2026 868 views

Question details

The user is looking for a way to convert VBA color constant names provided as strings (such as "vbGreen") into their corresponding numeric Long values to apply colors dynamically.

How to Convert a VBA Color Constant String to Its Long Value in Excel

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Running a VBA macro where color parameters are passed dynamically as strings from external inputs or cell text, requiring translation into usable numeric values for formatting.
Observed behavior: VBA does not natively convert enumeration strings to numbers, and functions like CallByName cannot evaluate constant string names to their underlying Long values.

Before you start

Ensure you have the Developer tab enabled in your spreadsheet software to easily access the VBA Editor (usually via the ALT + F11 shortcut).

Solution 1Recommended

Method 1: Create a Custom VBA Lookup Function

Since VBA cannot natively evaluate string constants, the most reliable method is to write a custom function utilizing a Select Case statement to map string names to their exact Long values.

This approach gives you complete control over which strings are accepted and allows you to seamlessly integrate the conversion into your larger macros without modifying the workbook structure.

Open the VBA Editor

Press ALT + F11 to open the Visual Basic for Applications window, right-click on your VBAProject, and select Insert > Module to create a new code module.

Write the Select Case Function

Paste a custom function like this: Function GetColorLong(colorStr As String) As Long Select Case UCase(colorStr) Case "VBRED": GetColorLong = vbRed Case "VBGREEN": GetColorLong = vbGreen Case "VBBLUE": GetColorLong = vbBlue Case Else: GetColorLong = vbWhite End Select End Function

Call the Function in Your Macro

In your main subroutine, pass your string to this function to assign a color. For example: Range("A1").Interior.Color = GetColorLong("vbGreen")

Method 1: Create a Custom VBA Lookup Function

Scalability: You can easily expand the Select Case statement to include any custom RGB colors or other built-in VBA constants you frequently use.

Solution 2

Method 2: Use a Reference Cell to Extract the Color Property

If you don't want to code a lookup table, you can format reference cells with the desired colors and have your macro read the Interior.Color property directly.

Solution 3

Method 3: Implement an Excel Lookup Table Using ColorIndex

For scenarios relying on worksheet data rather than hardcoded scripts, maintain a simple table mapping text names to numeric ColorIndex values, and use VLOOKUP in VBA to retrieve them.

Efficient VBA Macro Support

Write and Run VBA Macros Seamlessly in WPS Spreadsheet

WPS Office offers robust support for VBA macros, enabling you to build custom functions, utilize Select Case statements, and automate color formatting easily. It provides a familiar VBA Editor environment identical to what you are used to.

1. Open WPS Spreadsheet: Launch WPS Office and open your macro-enabled workbook.
2. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon. If it is hidden, enable it from the Options menu.
3. Open the VBA Editor: Click on 'VBA Editor' to launch the coding environment where you can insert modules and paste your custom Color Lookup functions.

Built-in VBA editor for writing and debugging macrosFull compatibility with Microsoft Excel .xlsm and .xlsb formatsLightweight application that loads macros quicklyFree to use with comprehensive spreadsheet capabilities

microsoft office alternative - wps office

Frequently Asked Questions

Why can't I use CallByName to convert a VBA color string?

The VBA.CallByName function is specifically designed to get or set properties of an object or invoke its methods dynamically. It cannot evaluate built-in enumeration constants like 'vbGreen' because these constants are not objects or object properties.

What is the difference between Color and ColorIndex in Excel VBA?

The 'Color' property requires an exact Long numeric value corresponding to an RGB color (e.g., vbRed is 255). The 'ColorIndex' property points to a specific slot (1 through 56) in the classic Excel color palette. For exact color matching, the Color property is preferred.

Can I use the Evaluate function to resolve 'vbGreen'?

No, the Application.Evaluate function in VBA is used to parse and compute standard worksheet formulas or range references. It does not recognize VBA-specific built-in constants like vbGreen or vbRed.

How can I quickly find the exact Long numeric value of a VBA color constant?

You can find this easily inside the VBA Editor. Press CTRL + G to open the Immediate Window, type '?vbGreen' (without quotes), and press Enter. The editor will return the underlying Long numeric value (65280) immediately.

Excel & Data Problems