t-hom’s diary

主にVBAネタを扱っているブログ…とも言えなくなってきたこの頃。

VBA 新しい色の指定方法 ~XlRgbColor定数

VBAで使える色定数は以下の8種類がある。

  • vbBlack
  • vbBlue
  • vbCyan
  • vbGreen
  • vbMagenta
  • vbRed
  • vbWhite
  • vbYellow

私も今までこれ以外使ったことが無かったが、先日オブジェクトブラウザを探索していたらXlRgbColor列挙型なるものを発見した。rgbAliceBlueとか、rgbAntiqueWhiteといった色名が登録されており、定数として利用できる。

わざわざ色名なんて調べなくてもRGB関数で好きな色を作れるのだが、あえて色名で指定するのも情緒があって良い。

しかし実際にどんな色なのかは、いちいち指定してみないとわからないので面倒くさい。

ということで、一覧を作ることにした。

まずMSDNに色名の表があったので、そちらを選択してExcelに張り付ける。
https://msdn.microsoft.com/ja-jp/library/office/ff197459.aspx

f:id:t-hom:20160822215419p:plain

そして値を全選択し、
f:id:t-hom:20160822215510p:plain

以下のマクロを実行すれば完成

Sub 色付け()
    Dim R As Range
    For Each R In Selection
        R.Interior.Color = R.Value
    Next
End Sub

f:id:t-hom:20160822215624p:plain

気に入った色があれば、次のように指定できる。

Range("A1").Interior.Color = rgbLavender

また、色名の一覧から選択したい場合は列挙型名の「XlRgbColor」を入力し、ドット入力すると候補一覧が表示される。
f:id:t-hom:20160822221639p:plain

選択すると以下のようになるが、XlRgbColorは有っても無くても動作に変わりはない。
(同名の自作変数や関数が存在する場合を除く)

Range("A1").Interior.Color = XlRgbColor.rgbLavender

さて、MSDNのアドレスが変わってしまったりするとアレなので、一応単体で色一覧を出力するマクロも作ってみた。
(ちょっと長いけれど、これを実行するとアクティブシートに色一覧が作成される。)

Sub 色一覧作成()
    Dim R As Range: Set R = Range("A1")
    R.Interior.Color = rgbAliceBlue: R.Offset(0, 1).Value = "rgbAliceBlue": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbAntiqueWhite: R.Offset(0, 1).Value = "rgbAntiqueWhite": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbAqua: R.Offset(0, 1).Value = "rgbAqua": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbAquamarine: R.Offset(0, 1).Value = "rgbAquamarine": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbAzure: R.Offset(0, 1).Value = "rgbAzure": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbBeige: R.Offset(0, 1).Value = "rgbBeige": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbBisque: R.Offset(0, 1).Value = "rgbBisque": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbBlack: R.Offset(0, 1).Value = "rgbBlack": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbBlanchedAlmond: R.Offset(0, 1).Value = "rgbBlanchedAlmond": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbBlue: R.Offset(0, 1).Value = "rgbBlue": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbBlueViolet: R.Offset(0, 1).Value = "rgbBlueViolet": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbBrown: R.Offset(0, 1).Value = "rgbBrown": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbBurlyWood: R.Offset(0, 1).Value = "rgbBurlyWood": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbCadetBlue: R.Offset(0, 1).Value = "rgbCadetBlue": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbChartreuse: R.Offset(0, 1).Value = "rgbChartreuse": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbCoral: R.Offset(0, 1).Value = "rgbCoral": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbCornflowerBlue: R.Offset(0, 1).Value = "rgbCornflowerBlue": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbCornsilk: R.Offset(0, 1).Value = "rgbCornsilk": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbCrimson: R.Offset(0, 1).Value = "rgbCrimson": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbDarkBlue: R.Offset(0, 1).Value = "rgbDarkBlue": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbDarkCyan: R.Offset(0, 1).Value = "rgbDarkCyan": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbDarkGoldenrod: R.Offset(0, 1).Value = "rgbDarkGoldenrod": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbDarkGray: R.Offset(0, 1).Value = "rgbDarkGray": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbDarkGreen: R.Offset(0, 1).Value = "rgbDarkGreen": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbDarkGrey: R.Offset(0, 1).Value = "rgbDarkGrey": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbDarkKhaki: R.Offset(0, 1).Value = "rgbDarkKhaki": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbDarkMagenta: R.Offset(0, 1).Value = "rgbDarkMagenta": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbDarkOliveGreen: R.Offset(0, 1).Value = "rgbDarkOliveGreen": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbDarkOrange: R.Offset(0, 1).Value = "rgbDarkOrange": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbDarkOrchid: R.Offset(0, 1).Value = "rgbDarkOrchid": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbDarkRed: R.Offset(0, 1).Value = "rgbDarkRed": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbDarkSalmon: R.Offset(0, 1).Value = "rgbDarkSalmon": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbDarkSeaGreen: R.Offset(0, 1).Value = "rgbDarkSeaGreen": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbDarkSlateBlue: R.Offset(0, 1).Value = "rgbDarkSlateBlue": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbDarkSlateGray: R.Offset(0, 1).Value = "rgbDarkSlateGray": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbDarkSlateGrey: R.Offset(0, 1).Value = "rgbDarkSlateGrey": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbDarkTurquoise: R.Offset(0, 1).Value = "rgbDarkTurquoise": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbDarkViolet: R.Offset(0, 1).Value = "rgbDarkViolet": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbDeepPink: R.Offset(0, 1).Value = "rgbDeepPink": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbDeepSkyBlue: R.Offset(0, 1).Value = "rgbDeepSkyBlue": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbDimGray: R.Offset(0, 1).Value = "rgbDimGray": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbDimGrey: R.Offset(0, 1).Value = "rgbDimGrey": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbDodgerBlue: R.Offset(0, 1).Value = "rgbDodgerBlue": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbFireBrick: R.Offset(0, 1).Value = "rgbFireBrick": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbFloralWhite: R.Offset(0, 1).Value = "rgbFloralWhite": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbForestGreen: R.Offset(0, 1).Value = "rgbForestGreen": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbFuchsia: R.Offset(0, 1).Value = "rgbFuchsia": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbGainsboro: R.Offset(0, 1).Value = "rgbGainsboro": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbGhostWhite: R.Offset(0, 1).Value = "rgbGhostWhite": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbGold: R.Offset(0, 1).Value = "rgbGold": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbGoldenrod: R.Offset(0, 1).Value = "rgbGoldenrod": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbGray: R.Offset(0, 1).Value = "rgbGray": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbGreen: R.Offset(0, 1).Value = "rgbGreen": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbGreenYellow: R.Offset(0, 1).Value = "rgbGreenYellow": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbGrey: R.Offset(0, 1).Value = "rgbGrey": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbHoneydew: R.Offset(0, 1).Value = "rgbHoneydew": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbHotPink: R.Offset(0, 1).Value = "rgbHotPink": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbIndianRed: R.Offset(0, 1).Value = "rgbIndianRed": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbIndigo: R.Offset(0, 1).Value = "rgbIndigo": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbIvory: R.Offset(0, 1).Value = "rgbIvory": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbKhaki: R.Offset(0, 1).Value = "rgbKhaki": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbLavender: R.Offset(0, 1).Value = "rgbLavender": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbLavenderBlush: R.Offset(0, 1).Value = "rgbLavenderBlush": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbLawnGreen: R.Offset(0, 1).Value = "rgbLawnGreen": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbLemonChiffon: R.Offset(0, 1).Value = "rgbLemonChiffon": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbLightBlue: R.Offset(0, 1).Value = "rgbLightBlue": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbLightCoral: R.Offset(0, 1).Value = "rgbLightCoral": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbLightCyan: R.Offset(0, 1).Value = "rgbLightCyan": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbLightGoldenrodYellow: R.Offset(0, 1).Value = "rgbLightGoldenrodYellow": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbLightGray: R.Offset(0, 1).Value = "rgbLightGray": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbLightGreen: R.Offset(0, 1).Value = "rgbLightGreen": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbLightGrey: R.Offset(0, 1).Value = "rgbLightGrey": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbLightPink: R.Offset(0, 1).Value = "rgbLightPink": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbLightSalmon: R.Offset(0, 1).Value = "rgbLightSalmon": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbLightSeaGreen: R.Offset(0, 1).Value = "rgbLightSeaGreen": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbLightSkyBlue: R.Offset(0, 1).Value = "rgbLightSkyBlue": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbLightSlateGray: R.Offset(0, 1).Value = "rgbLightSlateGray": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbLightSteelBlue: R.Offset(0, 1).Value = "rgbLightSteelBlue": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbLightYellow: R.Offset(0, 1).Value = "rgbLightYellow": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbLime: R.Offset(0, 1).Value = "rgbLime": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbLimeGreen: R.Offset(0, 1).Value = "rgbLimeGreen": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbLinen: R.Offset(0, 1).Value = "rgbLinen": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbMaroon: R.Offset(0, 1).Value = "rgbMaroon": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbMediumAquamarine: R.Offset(0, 1).Value = "rgbMediumAquamarine": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbMediumBlue: R.Offset(0, 1).Value = "rgbMediumBlue": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbMediumOrchid: R.Offset(0, 1).Value = "rgbMediumOrchid": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbMediumPurple: R.Offset(0, 1).Value = "rgbMediumPurple": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbMediumSeaGreen: R.Offset(0, 1).Value = "rgbMediumSeaGreen": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbMediumSlateBlue: R.Offset(0, 1).Value = "rgbMediumSlateBlue": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbMediumSpringGreen: R.Offset(0, 1).Value = "rgbMediumSpringGreen": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbMediumTurquoise: R.Offset(0, 1).Value = "rgbMediumTurquoise": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbMediumVioletRed: R.Offset(0, 1).Value = "rgbMediumVioletRed": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbMidnightBlue: R.Offset(0, 1).Value = "rgbMidnightBlue": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbMintCream: R.Offset(0, 1).Value = "rgbMintCream": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbMistyRose: R.Offset(0, 1).Value = "rgbMistyRose": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbMoccasin: R.Offset(0, 1).Value = "rgbMoccasin": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbNavajoWhite: R.Offset(0, 1).Value = "rgbNavajoWhite": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbNavy: R.Offset(0, 1).Value = "rgbNavy": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbNavyBlue: R.Offset(0, 1).Value = "rgbNavyBlue": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbOldLace: R.Offset(0, 1).Value = "rgbOldLace": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbOlive: R.Offset(0, 1).Value = "rgbOlive": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbOliveDrab: R.Offset(0, 1).Value = "rgbOliveDrab": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbOrange: R.Offset(0, 1).Value = "rgbOrange": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbOrangeRed: R.Offset(0, 1).Value = "rgbOrangeRed": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbOrchid: R.Offset(0, 1).Value = "rgbOrchid": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbPaleGoldenrod: R.Offset(0, 1).Value = "rgbPaleGoldenrod": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbPaleGreen: R.Offset(0, 1).Value = "rgbPaleGreen": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbPaleTurquoise: R.Offset(0, 1).Value = "rgbPaleTurquoise": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbPaleVioletRed: R.Offset(0, 1).Value = "rgbPaleVioletRed": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbPapayaWhip: R.Offset(0, 1).Value = "rgbPapayaWhip": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbPeachPuff: R.Offset(0, 1).Value = "rgbPeachPuff": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbPeru: R.Offset(0, 1).Value = "rgbPeru": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbPink: R.Offset(0, 1).Value = "rgbPink": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbPlum: R.Offset(0, 1).Value = "rgbPlum": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbPowderBlue: R.Offset(0, 1).Value = "rgbPowderBlue": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbPurple: R.Offset(0, 1).Value = "rgbPurple": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbRed: R.Offset(0, 1).Value = "rgbRed": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbRosyBrown: R.Offset(0, 1).Value = "rgbRosyBrown": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbRoyalBlue: R.Offset(0, 1).Value = "rgbRoyalBlue": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbSalmon: R.Offset(0, 1).Value = "rgbSalmon": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbSandyBrown: R.Offset(0, 1).Value = "rgbSandyBrown": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbSeaGreen: R.Offset(0, 1).Value = "rgbSeaGreen": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbSeashell: R.Offset(0, 1).Value = "rgbSeashell": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbSienna: R.Offset(0, 1).Value = "rgbSienna": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbSilver: R.Offset(0, 1).Value = "rgbSilver": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbSkyBlue: R.Offset(0, 1).Value = "rgbSkyBlue": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbSlateBlue: R.Offset(0, 1).Value = "rgbSlateBlue": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbSlateGray: R.Offset(0, 1).Value = "rgbSlateGray": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbSnow: R.Offset(0, 1).Value = "rgbSnow": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbSpringGreen: R.Offset(0, 1).Value = "rgbSpringGreen": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbSteelBlue: R.Offset(0, 1).Value = "rgbSteelBlue": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbTan: R.Offset(0, 1).Value = "rgbTan": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbTeal: R.Offset(0, 1).Value = "rgbTeal": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbThistle: R.Offset(0, 1).Value = "rgbThistle": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbTomato: R.Offset(0, 1).Value = "rgbTomato": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbTurquoise: R.Offset(0, 1).Value = "rgbTurquoise": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbViolet: R.Offset(0, 1).Value = "rgbViolet": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbWheat: R.Offset(0, 1).Value = "rgbWheat": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbWhite: R.Offset(0, 1).Value = "rgbWhite": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbWhiteSmoke: R.Offset(0, 1).Value = "rgbWhiteSmoke": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbYellow: R.Offset(0, 1).Value = "rgbYellow": Set R = R.Offset(1, 0)
    R.Interior.Color = rgbYellowGreen: R.Offset(0, 1).Value = "rgbYellowGreen": Set R = R.Offset(1, 0)
End Sub

実行結果はこんな感じ。
f:id:t-hom:20160822220353p:plain

上記の長ったらしいマクロは、マクロを使って書いた。

MSDNからコピーした表の色名を全選択し、
f:id:t-hom:20160822220618p:plain

以下のようなマクロを作って実行すると、

Sub 色一覧作成マクロ生成()
    Debug.Print "Sub 色一覧作成()"
    Debug.Print "    Dim R As Range: Set R = Range(""A1"")"
    For Each x In Selection
        Debug.Print _
            "    R.Interior.Color = " & x.Value & _
            ": R.Offset(0,1).Value = """ & x.Value & """" & _
            ": Set R = R.Offset(1,0)"
    Next
    Debug.Print "End Sub"
End Sub

イミディエイトウインドウに色一覧作成マクロが生成される。
f:id:t-hom:20160822220807p:plain

あとはコピペするだけ。

プログラムにプログラムを書かせるテクニックは過去にも紹介しているので興味があればどうぞ。
thom.hateblo.jp
thom.hateblo.jp

当ブログは、amazon.co.jpを宣伝しリンクすることによってサイトが紹介料を獲得できる手段を提供することを目的に設定されたアフィリエイト宣伝プログラムである、 Amazonアソシエイト・プログラムの参加者です。