How to store RGB colour in variable?

Question

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?

Thanks, I just found the same through this post (http://stackoverflow.com/a/1427293/1448678) - though not quite a duplicate. If you post as an answer I'll accept! — Jonny, Aug 08 '14 at 10:22
"*I could write separate functions (SetBlue, SetRed, SetGreen)to apply each colour*" A function that essentially replaces 1 line of code is usually not useful. — Jean-François Corbett, Aug 08 '14 at 11:30

Tom · Accepted Answer · 2019-11-01T10:29:40.860

51

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

edited Nov 01 '19 at 10:29

answered Aug 08 '14 at 10:30

Tom

9,725
3
31
48

1

I think it would be more helpful for the OP and further visitors, whenn you add some explaination for your intension. – Reporter Aug 08 '14 at 10:50
2

@reporter: He wrote the explanation in a comment to the question above... I'll merge it into the answer. – Jean-François Corbett Aug 08 '14 at 11:28

score 16 · Answer 2 · answered Aug 08 '14 at 13:44

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.

score 2 · Answer 3 · answered Dec 07 '17 at 18:34

2

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.

answered Dec 07 '17 at 18:34

RIck_R

111
3

4

255 is the highest significant value, any values provided that are higher will be assumed as 255. In other words, RGB(0, 0, 256) will return the same as RGB(0, 0, 255) without raising an error. – Carrosive Jan 26 '18 at 11:17

How to store RGB colour in variable?

3 Answers3