CODEWITHSUNDEEP
excelvbacolors
Jonny
Jonny

Reputation: 3955

How to store RGB colour in variable?

I'm looking to store an RGB colour in a variable in an Excel VBA project, to set the background color of various cell/ranges throughout a sub.

I want to set the colour once in a variable, so if I decide to change it throughout I only need to do it in one place.

Dim clrBlue As ColorFormat
clrBlue = RGB(0, 0, 256)

Range("a2").Interior.Color = clrBlue
Range("b3").Interior.Color = clrBlue

With the above code, I'm getting runtime error:

Object variable or With block variable not set

I could write separate functions (SetBlue, SetRed, SetGreen) to apply each colour, but that feels messy.

Can anyone suggest what I'm doing wrong?

Upvotes: 28

Views: 104064

Answers (3)

Tom
Tom

Reputation: 9878

RGB returns a Long, so you need to declare clrBlue as Long instead of as ColorFormat. 

Dim clrBlue As Long

clrBlue = RGB(0, 0, 255)

Application.union(Range("A2"), Range("B3")).Interior.Color = clrBlue

Upvotes: 52

RIck_R
RIck_R

Reputation: 121

I haven't tried this and I'm not disputing any of the previous commenters.

I do notice that the original code sample has: clrBlue = RGB(0, 0, 256)

The highest number allowed in RGB is 255. That might be the problem.

Upvotes: 2

Eric Harlan
Eric Harlan

Reputation: 384

As others have said, RGB() returns a Long, so you'll need to use that instead of ColorFormat. On a somewhat related note, I really like the Color enum in C#, and I started mimicking that in my VBA modules. You can create your own enum to store the values of colors in your project, then reference the color with Color.Blue.

This also makes it really easy to modify a color, if you decide to go with a different shade of blue. Update the enum, and all of the places you've used Color.Blue will update.

Example:

Public Enum Color
    Black = 0         'RGB(0, 0, 0)
    Blue = 14390640   'RGB(112, 149, 219)
    Gray = 11842740   'RGB(180, 180, 180)
    Red = 6118894     'RGB(238, 93, 93)
    White = 16777215  'RGB(255, 255, 255)
End Enum

To get the long value of the RGB value to store, I just threw the value into the Immediate window and copied the output.

In Immediate Window, type:

? RGB(112, 149, 219)

The output will be 14390640. There might be an easier way to get the value.

Upvotes: 17

Related Questions