How to set an array of colors using vba?

Question

I want to set an array of colors using VBA.
it was not as simple as I thought it would be.

using Vb.NET, you can declare an array of this color:

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

so I tried something similer in VBA (and was not so surprised that there was a compilation error, I wonder what data type 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.

Answer 1

vbYellowreturns a numeric value, so you need to define your variable as Long. Thus, before this is not necessary Set.

, ar(0) = vbYellow ( a, b and c` " " ).

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

Answer 2

VBA/VB6 Object , . .NET, , enum ( Long integer, ) .

Variant, Array:

Dim colors As Variant
colors = Array(vbRed, vbGreen, vbBlue, vbYellow)

---

VB.NET :

Dim a, b, c As Object

3 Object .

VBA/VB6 c Object a b Variant: .

Answer 3

For VBA you can use this function (I made it and used it successfully in several projects)

  Function GetColorFromColrName(ColrName As String) As Long
  Dim rgbc As Long
  Select Case ColrName
    Case "red"
      rgbc = RGB(255, 0, 0)
    Case "brown"
  rgbc = RGB(165, 42, 42)
Case "coral"
  rgbc = RGB(255, 127, 80)
Case "orange red"
  rgbc = RGB(255, 69, 0)
Case "orange"
  rgbc = RGB(255, 165, 0)
Case "gold"
  rgbc = RGB(255, 215, 0)
Case "yellow"
  rgbc = RGB(255, 255, 0)
Case "yellow green"
  rgbc = RGB(154, 205, 50)
Case "lawn green"
  rgbc = RGB(124, 252, 0)
Case "green"
  rgbc = RGB(0, 128, 0)
Case "lime"
  rgbc = RGB(0, 255, 0)
Case "aqua"
  rgbc = RGB(0, 255, 255)
Case "blue"
  rgbc = RGB(0, 0, 255)
Case "blue violet"
  rgbc = RGB(138, 43, 226)
Case "dodgerblue"
  rgbc = RGB(30, 144, 255)
Case "dark magenta"
  rgbc = RGB(139, 0, 139)
Case "dark violet"
  rgbc = RGB(148, 0, 211)
Case "purple"
  rgbc = RGB(128, 0, 128)
Case "magenta"
  rgbc = RGB(255, 0, 255)
Case "chocolate"
  rgbc = RGB(210, 105, 30)
Case "black"
  rgbc = RGB(0, 0, 0)
Case "dim gray"
  rgbc = RGB(105, 105, 105)
Case "gray"
  rgbc = RGB(188, 188, 188)
Case "silver"
  rgbc = RGB(192, 192, 192)
Case "white"
  rgbc = RGB(255, 255, 255)
Case ""

   End Select
   GetColorFromColrName = rgbc
End Function

All the best!