Morioh
Login
Petr Kateřiňák

Petr Kateřiňák

4 years ago

Excel VBA – Material Color Palette

Hello there,
this article is about how to get Material Color Palette [https://material.io/guidelines/style/color.html#] into VBA code and how to deal with it. Plus, one little specialty – color interpolation to get full gradient of material colors.

GitHub

You can find full code & example on my repo https://github.com/inDeev/Material-Colors-in-Excel-VBA

Preparation

We need to prepare Excel file with macro support, so open new Excel sheet and save it as MaterialColorPalette.xlsm. While sheet is opened, run VBA window by [Alt+F11].

matColor Function

Insert new module into Project [Menu->Insert->Module] and rename it to materialColors in Properties window
Rename module
Now open materialColor module by double click on it and insert code pasted below

Option Explicit
Public Function matColor(Name As String, Optional intensity As Integer = 500) As Long
  Dim colorValue As Long

  matColor = colorValue
End Function

When you look at material color website, you may notice, that each color in palette is defined by color name and number (50, 100, 200, …, 900) which defines color intensity. Because of that we use arguments Name (String) and intensity(integer), which is optional with default value of 500 (“Google suggests using the 500 colors as the primary colors”). Return type of our function is Long, because this is type which color in Excel VBA is presented by. colorValue is temporary variable which allows us to assign RGB values before they are returned by function.

Handle of black and white color

If Name = "White" Then matColor = RGB(255, 255, 255): Exit Function
If Name = "Black" Then matColor = RGB(0, 0, 0): Exit Function

Color names definition

Material palette comes with 19 color shades. For so many values we rather use Select Case-Case-End Select conditions instead of If-ElseIf-Else-End If.

If Name = "White" Then matColor = RGB(255, 255, 255): Exit Function
If Name = "Black" Then matColor = RGB(0, 0, 0): Exit Function
Select Case Name ‘Requires exact color name e.g. “Light Blue”
  Case "Red"
  Case "Pink"
  Case "Purple"
  Case "Deep Purple"
  Case "Indigo"
  Case "Blue"
  Case "Light Blue"
  Case "Cyan"
  Case "Teal"
  Case "Green"
  Case "Light Green"
  Case "Lime"
  Case "Yellow"
  Case "Amber"
  Case "Orange"
  Case "Deep Orange"
  Case "Brown"
  Case "Grey"
  Case "Blue Grey"
End Select

Now by little modification we allow to call color name by case insensitive values by modification of Name argument value and color name cases turned to lowercase:

Name = LCase(Name)

If Name = "white" Then matColor = RGB(255, 255, 255): Exit Function
If Name = "black" Then matColor = RGB(0, 0, 0): Exit Function

Select Case Name ‘Allows case insensitive names e.g. “Light blue”, “liGHt Blue”
  Case "red"
  Case "pink"
  Case "purple"
  Case "deep purple"
  ...
End Select

By next modification of Name argument value and color name cases (remove spaces) we allow to enter two-word names with or without spaces:

Name = LCase(Replace(Name, " ", ""))

If Name = "white" Then matColor = RGB(255, 255, 255): Exit Function
If Name = "black" Then matColor = RGB(0, 0, 0): Exit Function

Select Case Name ‘Allows case insensitive names w/ or w/o spaces e.g. “lIghtBlUe”
  Case "red"
  Case "pink"
  Case "purple"
  Case "deeppurple"
  ...
End Select

And finally, we allow to use grey/gray values for last two colors:

  Case "gray", "grey"
  Case "bluegray", "bluegrey"

Color assign tree

Here comes the most boring part of whole function – manual copy & paste of color values. But with a little problem – colors are described by hexadecimal codes, VBA needs RGB defined by decimal values. In code below are that values converted and assigned to colorValue variable by RGB command.

Name = LCase(Replace(Name, " ", ""))

If Name = "white" Then matColor = RGB(255, 255, 255): Exit Function
If Name = "black" Then matColor = RGB(0, 0, 0): Exit Function

Select Case Name ‘Allows case insensitive names w/ or w/o spaces e.g. “lIghtBlUe”
  Case "red"
    Select Case intensity
      Case 50: colorValue = RGB(255, 235, 238)
      Case 100: colorValue = RGB(255, 205, 210)
      Case 200: colorValue = RGB(239, 154, 154)
      Case 300: colorValue = RGB(229, 115, 115)
      Case 400: colorValue = RGB(239, 83, 80)
      Case 500: colorValue = RGB(244, 67, 54)
      Case 600: colorValue = RGB(229, 57, 53)
      Case 700: colorValue = RGB(211, 47, 47)
      Case 800: colorValue = RGB(198, 40, 40)
      Case 900: colorValue = RGB(183, 28, 28)
    End Select
  Case "pink"
    Select Case intensity
      Case 50: colorValue = RGB(252, 228, 236)
      Case 100: colorValue = RGB(248, 187, 208)
      Case 200: colorValue = RGB(244, 143, 177)
      Case 300: colorValue = RGB(240, 98, 146)
      Case 400: colorValue = RGB(236, 64, 122)
      Case 500: colorValue = RGB(233, 30, 99)
      Case 600: colorValue = RGB(216, 27, 96)
      Case 700: colorValue = RGB(194, 24, 91)
      Case 800: colorValue = RGB(173, 20, 87)
      Case 900: colorValue = RGB(136, 14, 79)
    End Select
  Case "purple"
    Select Case intensity
      Case 50: colorValue = RGB(243, 229, 245)
      Case 100: colorValue = RGB(225, 190, 231)
      Case 200: colorValue = RGB(206, 147, 216)
      Case 300: colorValue = RGB(186, 104, 200)
      Case 400: colorValue = RGB(171, 71, 188)
      Case 500: colorValue = RGB(156, 39, 176)
      Case 600: colorValue = RGB(142, 36, 170)
      Case 700: colorValue = RGB(123, 31, 162)
      Case 800: colorValue = RGB(106, 27, 154)
      Case 900: colorValue = RGB(74, 20, 140)
    End Select
  Case "deeppurple"
    Select Case intensity
      Case 50: colorValue = RGB(237, 231, 246)
      Case 100: colorValue = RGB(209, 196, 233)
      Case 200: colorValue = RGB(179, 157, 219)
      Case 300: colorValue = RGB(149, 117, 205)
      Case 400: colorValue = RGB(126, 87, 194)
      Case 500: colorValue = RGB(103, 58, 183)
      Case 600: colorValue = RGB(94, 53, 177)
      Case 700: colorValue = RGB(81, 45, 168)
      Case 800: colorValue = RGB(69, 39, 160)
      Case 900: colorValue = RGB(49, 27, 146)
    End Select
  Case "indigo"
    Select Case intensity
      Case 50: colorValue = RGB(232, 234, 246)
      Case 100: colorValue = RGB(197, 202, 233)
      Case 200: colorValue = RGB(159, 168, 218)
      Case 300: colorValue = RGB(121, 134, 203)
      Case 400: colorValue = RGB(92, 107, 192)
      Case 500: colorValue = RGB(63, 81, 181)
      Case 600: colorValue = RGB(57, 73, 171)
      Case 700: colorValue = RGB(48, 63, 159)
      Case 800: colorValue = RGB(40, 53, 147)
      Case 900: colorValue = RGB(26, 35, 126)
    End Select
  Case "blue"
    Select Case intensity
      Case 50: colorValue = RGB(227, 242, 253)
      Case 100: colorValue = RGB(187, 222, 251)
      Case 200: colorValue = RGB(144, 202, 249)
      Case 300: colorValue = RGB(100, 181, 246)
      Case 400: colorValue = RGB(66, 165, 245)
      Case 500: colorValue = RGB(33, 150, 243)
      Case 600: colorValue = RGB(30, 136, 229)
      Case 700: colorValue = RGB(25, 118, 210)
      Case 800: colorValue = RGB(21, 101, 192)
      Case 900: colorValue = RGB(13, 71, 161)
    End Select
  Case "lightblue"
    Select Case intensity
      Case 50: colorValue = RGB(225, 245, 254)
      Case 100: colorValue = RGB(179, 229, 252)
      Case 200: colorValue = RGB(129, 212, 250)
      Case 300: colorValue = RGB(79, 195, 247)
      Case 400: colorValue = RGB(41, 182, 246)
      Case 500: colorValue = RGB(3, 169, 244)
      Case 600: colorValue = RGB(3, 155, 229)
      Case 700: colorValue = RGB(2, 136, 209)
      Case 800: colorValue = RGB(2, 119, 189)
      Case 900: colorValue = RGB(1, 87, 155)
    End Select
  Case "cyan"
    Select Case intensity
      Case 50: colorValue = RGB(224, 247, 250)
      Case 100: colorValue = RGB(178, 235, 242)
      Case 200: colorValue = RGB(128, 222, 234)
      Case 300: colorValue = RGB(77, 208, 225)
      Case 400: colorValue = RGB(38, 198, 218)
      Case 500: colorValue = RGB(0, 188, 212)
      Case 600: colorValue = RGB(0, 172, 193)
      Case 700: colorValue = RGB(0, 151, 167)
      Case 800: colorValue = RGB(0, 131, 143)
      Case 900: colorValue = RGB(0, 96, 100)
    End Select
  Case "teal"
    Select Case intensity
      Case 50: colorValue = RGB(224, 242, 241)
      Case 100: colorValue = RGB(178, 223, 219)
      Case 200: colorValue = RGB(128, 203, 196)
      Case 300: colorValue = RGB(77, 182, 172)
      Case 400: colorValue = RGB(38, 166, 154)
      Case 500: colorValue = RGB(0, 150, 136)
      Case 600: colorValue = RGB(0, 137, 123)
      Case 700: colorValue = RGB(0, 121, 107)
      Case 800: colorValue = RGB(0, 105, 92)
      Case 900: colorValue = RGB(0, 77, 64)
    End Select
  Case "green"
    Select Case intensity
      Case 50: colorValue = RGB(232, 245, 233)
      Case 100: colorValue = RGB(200, 230, 201)
      Case 200: colorValue = RGB(165, 214, 167)
      Case 300: colorValue = RGB(129, 199, 132)
      Case 400: colorValue = RGB(102, 187, 106)
      Case 500: colorValue = RGB(76, 175, 80)
      Case 600: colorValue = RGB(67, 160, 71)
      Case 700: colorValue = RGB(56, 142, 60)
      Case 800: colorValue = RGB(46, 125, 50)
      Case 900: colorValue = RGB(27, 94, 32)
    End Select
  Case "lightgreen"
    Select Case intensity
      Case 50: colorValue = RGB(241, 248, 233)
      Case 100: colorValue = RGB(220, 237, 200)
      Case 200: colorValue = RGB(197, 225, 165)
      Case 300: colorValue = RGB(174, 213, 129)
      Case 400: colorValue = RGB(156, 204, 101)
      Case 500: colorValue = RGB(139, 195, 74)
      Case 600: colorValue = RGB(124, 179, 66)
      Case 700: colorValue = RGB(104, 159, 56)
      Case 800: colorValue = RGB(85, 139, 47)
      Case 900: colorValue = RGB(51, 105, 30)
    End Select
  Case "lime"
    Select Case intensity
      Case 50: colorValue = RGB(249, 251, 231)
      Case 100: colorValue = RGB(240, 244, 195)
      Case 200: colorValue = RGB(230, 238, 156)
      Case 300: colorValue = RGB(220, 231, 117)
      Case 400: colorValue = RGB(212, 225, 87)
      Case 500: colorValue = RGB(205, 220, 57)
      Case 600: colorValue = RGB(192, 202, 51)
      Case 700: colorValue = RGB(175, 180, 43)
      Case 800: colorValue = RGB(158, 157, 36)
      Case 900: colorValue = RGB(130, 119, 23)
    End Select
  Case "yellow"
    Select Case intensity
      Case 50: colorValue = RGB(255, 253, 231)
      Case 100: colorValue = RGB(255, 249, 196)
      Case 200: colorValue = RGB(255, 245, 157)
      Case 300: colorValue = RGB(255, 241, 118)
      Case 400: colorValue = RGB(255, 238, 88)
      Case 500: colorValue = RGB(255, 235, 59)
      Case 600: colorValue = RGB(253, 216, 53)
      Case 700: colorValue = RGB(251, 192, 45)
      Case 800: colorValue = RGB(249, 168, 37)
      Case 900: colorValue = RGB(245, 127, 23)
    End Select
  Case "amber"
    Select Case intensity
      Case 50: colorValue = RGB(255, 248, 225)
      Case 100: colorValue = RGB(255, 236, 179)
      Case 200: colorValue = RGB(255, 224, 130)
      Case 300: colorValue = RGB(255, 213, 79)
      Case 400: colorValue = RGB(255, 202, 40)
      Case 500: colorValue = RGB(255, 193, 7)
      Case 600: colorValue = RGB(255, 179, 0)
      Case 700: colorValue = RGB(255, 160, 0)
      Case 800: colorValue = RGB(255, 143, 0)
      Case 900: colorValue = RGB(255, 111, 0)
    End Select
  Case "orange"
    Select Case intensity
      Case 50: colorValue = RGB(255, 243, 224)
      Case 100: colorValue = RGB(255, 224, 178)
      Case 200: colorValue = RGB(255, 204, 128)
      Case 300: colorValue = RGB(255, 183, 77)
      Case 400: colorValue = RGB(255, 167, 38)
      Case 500: colorValue = RGB(255, 152, 0)
      Case 600: colorValue = RGB(251, 140, 0)
      Case 700: colorValue = RGB(245, 124, 0)
      Case 800: colorValue = RGB(239, 108, 0)
      Case 900: colorValue = RGB(230, 81, 0)
    End Select
  Case "deeporange"
    Select Case intensity
      Case 50: colorValue = RGB(251, 233, 231)
      Case 100: colorValue = RGB(255, 204, 188)
      Case 200: colorValue = RGB(255, 171, 145)
      Case 300: colorValue = RGB(255, 138, 101)
      Case 400: colorValue = RGB(255, 112, 67)
      Case 500: colorValue = RGB(255, 87, 34)
      Case 600: colorValue = RGB(244, 81, 30)
      Case 700: colorValue = RGB(230, 74, 25)
      Case 800: colorValue = RGB(216, 67, 21)
      Case 900: colorValue = RGB(191, 54, 12)
    End Select
  Case "brown"
    Select Case intensity
      Case 50: colorValue = RGB(239, 235, 233)
      Case 100: colorValue = RGB(215, 204, 200)
      Case 200: colorValue = RGB(188, 170, 164)
      Case 300: colorValue = RGB(161, 136, 127)
      Case 400: colorValue = RGB(141, 110, 99)
      Case 500: colorValue = RGB(121, 85, 72)
      Case 600: colorValue = RGB(109, 76, 65)
      Case 700: colorValue = RGB(93, 64, 55)
      Case 800: colorValue = RGB(78, 52, 46)
      Case 900: colorValue = RGB(62, 39, 35)
    End Select
  Case "gray", "grey"
    Select Case intensity
      Case 50: colorValue = RGB(250, 250, 250)
      Case 100: colorValue = RGB(245, 245, 245)
      Case 200: colorValue = RGB(238, 238, 238)
      Case 300: colorValue = RGB(224, 224, 224)
      Case 400: colorValue = RGB(189, 189, 189)
      Case 500: colorValue = RGB(158, 158, 158)
      Case 600: colorValue = RGB(117, 117, 117)
      Case 700: colorValue = RGB(97, 97, 97)
      Case 800: colorValue = RGB(66, 66, 66)
      Case 900: colorValue = RGB(33, 33, 33)
    End Select
  Case "bluegray", "bluegrey"
    Select Case intensity
      Case 50: colorValue = RGB(236, 239, 241)
      Case 100: colorValue = RGB(207, 216, 220)
      Case 200: colorValue = RGB(176, 190, 197)
      Case 300: colorValue = RGB(144, 164, 174)
      Case 400: colorValue = RGB(120, 144, 156)
      Case 500: colorValue = RGB(96, 125, 139)
      Case 600: colorValue = RGB(84, 110, 122)
      Case 700: colorValue = RGB(69, 90, 100)
      Case 800: colorValue = RGB(55, 71, 79)
      Case 900: colorValue = RGB(38, 50, 56)
    End Select
End Select

Now, if you call it from your application e.g.

  Range(“A1”).Interior.Color = matColor(“Light blue”, 700)
  Range(“A1”).Font.Color = matColor(“white”)
  Range(“A1”).Value = “White on blue”

First cell of your sheet gets right colors defined by Material Color Palette:
A1 White on Blue

Allowing usage of full color gradients

Now we have exactly 10 shades of each of 19 colors. But what if we need to create smooth gradients or use other shade of given colors than predefined ones? In this case, we need to create linear interpolation between given color values.
In next step, we will create scale of Intensity from 0 to 1000, where 0 is white, 50 is first lighter color given by Material Palette, 900 is darker color of Material Palette and 1000 is black.
Firstly, we do a modification of white and black condition lines to correspond to 0 and 1000 intensity value of any color.

  If Name = "white" Or intensity <= 0 Then matColor = RGB(255, 255, 255): Exit Function
  If Name = "black" Or intensity >= 1000 Then matColor = RGB(0, 0, 0): Exit Function

Then we divide color assignment into two parts – colors with intensity assigned by Material Design Palette and colors with intensity not equal to values 0, 50, 100, 200, 300, …, 1000

If intensity Mod 100 = 0 Or intensity = 50 Then 'intensity equals to 50, 100, 200, ...
  Select Case Name
    Case “red”
      Select Case intensity
        Case 50
        ...
      End Select
    ...
  ...
  End Select
Else
End If

Now we need to create function which interpolates color values between values given by Material Design Palette. There is no possibility to interpolate directly between Long type color values. That values needs to be disassembled into red, green and blue components, then interpolated by factor (1-99) and combined back into Long type by RGB function. In function arguments bellow is lighter color inputs defined by Variant type, not Long. It is because of some VBA issue which causes error, when I try to give Long type number to Long type argument. This problem doesn’t occur on darker color argument.

Public Function interpolateColor(lighter As Variant, darker As Long, factor As Integer) As Long
  Dim r1, g1, b1 As Integer
  r1 = lighter Mod 256
  g1 = (lighter \ 256) Mod 256
  b1 = (lighter \ 256 \ 256) Mod 256

  Dim r2, g2, b2 As Integer
  r2 = darker Mod 256
  g2 = (darker \ 256) Mod 256
  b2 = (darker \ 256 \ 256) Mod 256

  interpolateColor = RGB(Int(r1 - ((r1 - r2) * factor / 100)), _
                         Int(g1 - ((g1 - g2) * factor / 100)), _
                         Int(b1 - ((b1 - b2) * factor / 100)))
End Function

Now we need to find nearest greater and nearest lower value of intensity given by Material Color Palette. E.g. we need to get red color with intensity 234: greater intensity value is 300 and lower 200. Material Color Palette contains intensity with value 50, so we need to break first hundred into two parts of condition and then one part for values from 100 to 1000. Code below is part Else section of condition above.

Else
  Dim lighter, darker As Long
  Dim factor As Integer
  If intensity < 50 Then ' 0-50
    lighter = matColor(Name, 0)
    darker = matColor(Name, 50)
    factor = intensity * 2
  ElseIf intensity < 100 Then ' 50-100
    lighter = matColor(Name, 50)
    darker = matColor(Name, 100)
    factor = (intensity - 50) * 2
  Else ' 100-1000
    lighter = matColor(Name, Floor(intensity, 100)) ‘find closest lower intensity in Material Color Palette
    darker = matColor(Name, Ceiling(intensity, 100)) ‘find closest greater intensity in Material Color Palette
    factor = intensity - Floor(intensity, 100)
  End If
End If

Because of VBA doesn’t know math functions Floor and Ceiling, we need to create them.

Public Function Ceiling(ByVal X As Double, Optional ByVal factor As Double = 1) As Double
  Ceiling = (Int(X / factor) - (X / factor - Int(X / factor) > 0)) * factor
End Function

Public Function Floor(ByVal X As Double, Optional ByVal factor As Double = 1) As Double
  Floor = Int(X / factor) * factor
End Function

Now we interpolate two colors which are present in Material Color Palette (lighter, darker) by factor and we get final interpolated color.

Else
  Dim lighter, darker As Long
  Dim factor As Integer
  If intensity < 50 Then ' 0-50
    ...
  ElseIf intensity < 100 Then ' 50-100
    ...
  Else ' 100-1000
    ...
  End If
  colorValue = interpolateColor(lighter, darker, factor)
End If

That’s all, now we can call any shade of Material Color Palette e.g.

something = matColor(“lime”, 842)

and we get darker color than “Lime 800” but lighter than “Lime 900”.
For easy check that anything is OK, let’s create Function, which fill up Excel sheet with all Material Palette Colors gradients – intensity step by 20 because it is easier to see 50 rows of values on screen at once than 1000 rows. Step 1 is visible on this article heading. For that test case I use SelectionChange event of Worksheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A:S").Interior.color = RGB(255, 255, 255)
Dim i As Integer
Dim j As Integer: j = 1
Dim cVal As Long
    For i = 0 To 1000 Step 20
        cVal = matColor("red", i)
        Range("A" & j).Interior.color = cVal
        cVal = matColor("Pink", i)
        Range("B" & j).Interior.color = cVal
        cVal = matColor("Purple", i)
        Range("C" & j).Interior.color = cVal
        cVal = matColor("DeepPurple", i)
        Range("D" & j).Interior.color = cVal
        cVal = matColor("Indigo", i)
        Range("E" & j).Interior.color = cVal
        cVal = matColor("blue", i)
        Range("F" & j).Interior.color = cVal
        cVal = matColor("Lightblue", i)
        Range("G" & j).Interior.color = cVal
        cVal = matColor("Cyan", i)
        Range("H" & j).Interior.color = cVal
        cVal = matColor("Teal", i)
        Range("I" & j).Interior.color = cVal
        cVal = matColor("Green", i)
        Range("J" & j).Interior.color = cVal
        cVal = matColor("LightGreen", i)
        Range("K" & j).Interior.color = cVal
        cVal = matColor("Lime", i)
        Range("L" & j).Interior.color = cVal
        cVal = matColor("Yellow", i)
        Range("M" & j).Interior.color = cVal
        cVal = matColor("Amber", i)
        Range("N" & j).Interior.color = cVal
        cVal = matColor("Orange", i)
        Range("O" & j).Interior.color = cVal
        cVal = matColor("deepOrange", i)
        Range("P" & j).Interior.color = cVal
        cVal = matColor("Brown", i)
        Range("Q" & j).Interior.color = cVal
        cVal = matColor("Grey", i)
        Range("R" & j).Interior.color = cVal
        cVal = matColor("blueGrey", i)
        Range("S" & j).Interior.color = cVal
        j = j + 1
    Next i
End Sub

And voilà
Gradients STEP 20

#VBA #Excel #Material #Design #Color

Excel VBA – Material Color Palette
47.35 GEEK