How to set array of colors using vba?

Question

I wish to set Array of colors using VBA.
that was not simple as i thought it would be.

using Vb.NET you can declare array of color like that:

Dim ar() As Color = {Color.Yellow, Color.Red, Color.Green}

so i tried something similer in VBA (and was not so surprised that compilation error was thrown, i wonder what data type is vbYellow(Enum?)):

Private Sub CommandButton1_Click()

Dim ar(3) As Object
Dim a, b, c As Object

Set a = vbYellow
Set b = vbRed
Set c = vbGreen

ar(0) = a
ar(1) = b
ar(2) = c

End Sub 

thank you.

Answer 1

vbYellow returns a numeric value, so you need to define your variable as Long. So, there is no need to Set it before.

You could use direct approach, with ar(0) = vbYellow (you don't need the a,bandc` variables as the "middle-man").

Private Sub CommandButton1_Click()

Dim ar(3) As Long
Dim a As Long, b As Long, c As Long

a = vbYellow
b = vbRed
c = vbGreen

ar(0) = a
ar(1) = b
ar(2) = c

End Sub