VBA valid RGB value throws out of range exception

Question

I pieced together the following code from other StackOverflow articles that solved similar problems.

Dim light As MsoRGBType, dark As MsoRGBType
light = RGB(195, 214, 155)
dark = RGB(79, 98, 40)

ws.Shapes("Arrow1").Fill.ForeColor.RGB = dark

In the last line of code, I get the error:

Runtime Error '-2147024809 (80070057)': The value is out of range.

Fetching the shape does not throw the error, I checked that. I cannot ascertain Excel's native way of setting shape colors because, strangely enough, recording the manual changing process produces an empty macro.

The shapes I am working with are standard lines with arrow-style endpoints, in case that is relevant to the coloring process.

try `ws.Shapes("Arrow1").Line.ForeColor.RGB = dark` – BrakNicku Mar 02 '15 at 12:48 — BrakNicku, Mar 02 '15 at 12:48

score 0 · Accepted Answer · answered Mar 02 '15 at 13:15

0

Even though Excel recognises a standard line as a Shape object, some properties of Shape are not accessible for lines.

Seeing as a line is not categorised as a shape with a border and content, Fill does not compute. Using Shape.Line instead yields the desired result.

In code:

ws.Shapes("Arrow1").Line.ForeColor.RGB = dark

Thanks to user3964075 for his enlightening comment!

answered Mar 02 '15 at 13:15

bhnn

225
5
17

You should probably allow @user3964075 to answer the question and take the credit for it... – David Zemens Mar 02 '15 at 16:12
@DavidZemens I will retract my answer should he choose to do so, of course. At the time I thought it wise to hold the solution somewhere so it can be readily accessed should he not write one himself. – bhnn Mar 03 '15 at 12:09

VBA valid RGB value throws out of range exception

1 Answers1