Excel VBA – Material Color Palette

Excel VBA – Material Color Palette

![VBAMaterial-Color-Palette-BG](https://i.ibb.co/DwbX8NL/VBAMaterial-Color-Palette-BG.png "VBAMaterial-Color-Palette-BG") Hello there, this article is about how to get Material Color Palette [https://material.io/guidelines/style/color.html#] into...

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

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:

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à

To VBA and beyond - building a RESTful backend using plain Microsoft Excel macros

When my coworkers and I discussed backend technologies for an upcoming project, someone jokingly mentioned Excel as people widely misused it as a terrible database replacement. Although we settled for .NET, the idea of using Excel as a backend fascinated me. Since I just recently finished my bachelor's thesis and had some spare time, I thought I'd give it a shot and see how far I'd get.

When my coworkers and I discussed backend technologies for an upcoming project, someone jokingly mentioned Excel as people widely misused it as a terrible database replacement. Although we settled for .NET, the idea of using Excel as a backend fascinated me. Since I just recently finished my bachelor's thesis and had some spare time, I thought I'd give it a shot and see how far I'd get.

This article consists of three main parts, an introduction to webserver internals, getting Excel to answer http requests and adding some special sauce to make it a RESTful backend. If you can't wait to read through the source, you can check out the repository webxcel, which I will refer to throughout this article.

Webservers

When using express or ASP.NET, we usually think about http routes and request bodies, but we never really care about how requests are handled inside the framework. Deep down, every http request consists of a TCP connection, where the client and server exchange various messages and eventually close the connection. A very basic request might look like this:

POST /api/cities HTTP/1.1
Host: localhost
User-Agent: Mozilla/5.0 (Windows; U; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 2.0.50727)
Content-Type: application/json
Content-Length: 29

{
"name": "Springfield"
}

This post request sends a JSON payload to /api/cities, and we'd usually expect the server to create a city named "Springfield". If our backend is a simple express server, it might look something like this:

const express = require("express"),
app = express();

function handler(req, res) {
// create city

res.status(201)
.send("woo hoo!"); //
}

app.post("/api/cities", handler);
app.listen(8080);

Before executing handler, express parses the request, extracts the request method and requested resource to determine what action to perform. It also parses the request headers and - depending on the Content-Type - the request body, too. This looks somewhat similar to the following piece of pseudo-js:

// ...

const requestText = socket.readToEnd(),
request = parseRequest(requestText),
contentType = request.headers.get("Content-Type");

if (canParseRequestBody(contentType)) {
request.body = parseRequestBody(body, contentType);
}

const handler = registeredHandlers.findHandler(request.method, request.url),
response = new HttpResponse();;

if (handler) {
handler(request, response);
}

sendResponse(response);

// ...

The express example above also showed adding arbitary text to the response using res.send() and setting the response's status using res.status(). As you can see in the pseudo-js, after calling the request handling function, the underlaying framework will convert the response object to an HTTP response similar to the following and send it back to the client:

HTTP/1.1 201 Created
X-Powered-By: Express
Content-Length: 8

woo hoo!

In our client, we'd then evaluate the status code, headers and response text after parsing everything again.

Hello, this is Excel

Now, how do we make Excel answer our requests? Microsoft Office comes with a really cool toolbelt called macros, which people now hate because of ransomware. You could argue that macros are obsolete by now and coding them in Visual Basic isn't cool when you could use any modern programming language instead, but the concept behind them is pretty neat in fact.

VBA macros

Macros were originally conceived to save the user from repeating the same task over and over again. For this, users could record macros, which would then repeat what they did earlier. Internally the macro host would create a "script" of what's happening and interpret it later. These scripts happen to be generated in Visual Basic, or - to be more specific - Visual Basic for Applications (VBA). To interact with the application, macro hosts "inject" functionality into the VBA interpreter, like the Range function in Excel, which can be used to access a collection of cells. As VBA is extremely easy to learn if you don't have a programming background, users quickly adopted and combinded injected functions and classes to Subs and Functions to e.g. automatically generate cell values based on more complex calculations.

Crafting Excel files for version control

A major problem when developing macros is version control. Office files are usually zip containers, so adding them in binary would prevent any sane way of diffing. Extracting and re-zipping the project would be the way to go, if macros weren't binary encoded in a separate container using a special format called OLE. There are some OLE macro extractors like decalage2's oletools out there, but strangely I didn't find any library to create these containers the easy way.

Instead of reading the specification and creating our own library, we may try something else first: we can control Excel (or any other Office application) using .NET and let Excel do all the hard work for us. Using this approach, we need our macros' code in plain-text files, and an importer, which starts Excel and imports our macros. We'll be using PowerShell for this, which comes with full access to the .NET framework and because we don't have to compile these scripts.

In PowerShell, we can create an Excel instance, which we can then use to create workbooks and import our macros. You can take a look at the build script build.ps1 in the repository.

Escaping interop hell - using Windows Sockets in VBA

After we got our version control problems out of our way, we can get straight to the core of creating our server. As mentioned earlier, building a webserver requires handling TCP connections. The bad news is, VBA doesn't come with a TCP implementation by default and I can't really think of a reason why it should. But don't worry, Microsoft thought of somebody needing questionable features, so they baked C interop into VBA as well.

Everybody who's done C interop from a high-level language like C# knows the pain of AccessViolationException when incorrectly marshalling parameters. In VBA it's basically the same, except that both the debugger and the IDE aren't really meant to develop interop-heavy applications, and thus debugging isn't as easy as you might be used to.

The "easiest" way of getting a TCP server running using only interop and no external libraries (like a C# library which implements the HTTP server already - that'd be too easy for us), is to use Windows Sockets (winsocks). If you haven't used winsocks yet, this is what it basically looks like in C++:

// we can skip all variable declarations as they're not that important here

// setup winsocks
WSAStartup(mode, &wsa);

// create a server socket, this is similar to bsd sockets
server = socket(AF_INET, SOCK_STREAM, 0);

// bind the server socket to an address and port, which it'll listen to later
addr.sin_port = htons(8080);
result = bind(server, &addr, sizeof(sockaddr_in));
// usually we'd check the result and handle errors, but that's not important here

// start listening on the server socket and allow queuing up backlog clients
result = listen(server, backlog);
// check result, see above

// get the first client socket in the backlog queue
client = accept(server, &clientAddr, sizeof(sockaddr));

// at this point, the connection is active and we can send/receive data
send(client, message, messageLength, flags);

// cleanup after we're done
closesocket(client);
closesocket(server);
WSACleanup();

The first good news is: we can translate this straight to VBA by importing all required methods in a module and simply call all of them in the right order to get a TCP server up and running.

Since we have a working TCP connection by now, we can continue our server development by parsing incoming HTTP requests. As shown above, requests consist of a protocol line, the headers and a request body. Parsing the protocol line is probably the easiest, we can split it into three parts: the request method, the resource and http version.

Before actually splitting the line, we should make sure we're dealing with an http request. To do so, we can use VBA's text comparison feature Like, which checks if some text matches a very simple pattern, similar to regular expressions. By evaluating

' prevent comparison errors if clients send lower case requests
Dim upperLine As String
upperLine = UCase(line)

' this is somewhat similar to
' /.* HTTP/1.1/.test(upperLine)
' in js
If Not upperLine Like "* HTTP/1.1" Then
' we're concentrating on http 1, since version 2 is a bit more complex to implement
Err.Raise StatusCode.ErrorHttpRequestInvalidFormat
End If

' now we know the request is an http request and can continue parsing it

we can make sure to only process http requests and extract the request method and resource. Splitting the headers is a piece of cake as well, we just need to use Split(line, ":", 2) on each header line, where 2 represents the maximum count of parts the split function should return, and we're set. To keep it simple, we're not going to parse the request body for now. Since we want our server to return a very simple response, we're just going to echo the request.

Similar to express, we'll handle requests using response objects. Our response class contains headers, a status and a body. Using this class, we can create a simple echo server by reading all incoming text, parsing the request and sending our response:

Dim server As TcpServer
Set server = New TcpServer

' listen for incoming connections on port 8080
server.BindTo 8080

' accept an incoming connection ...
Dim client As TcpClient
Set client = server.AcceptTcpClient()

' ... and receive the request text
Dim requestText As String
requestText = client.ReceiveString()

Dim request As HttpRequest
Set request = New HttpRequest

request.Parse requestText

Dim response As HttpResponse
Set response = New HttpResponse

' send "200 OK" and the body
response.StatusCode = 200
response.Body = "Called " & request.Url & " with this body:" & vbCrLf & vbCrLf & request.Body

Dim responseText As String
responseText = response.ToString()

' actually send the response back to the client
client.SendString responseText

' and do some cleanup
client.Dispose
server.Dispose

Webservers like nginx and apache can be configured to send the server version, so we're going to do the same with Excel. In our response class, we're using a ToString method to convert our object to a string containing all response information. When examining our server with the axios node.js module, we'll receive a very satisfying response:

> const axios = require("axios");
> axios.post("http://localhost:8080", "it works").then(response => {
... console.log(response.status, response.statusText);
... console.log(response.headers);
... console.log(response.data);
... });

// outputs
200 'Nobody Needs This Anyway'

{ 'content-length': '39',
connection: 'close',
server: 'Microsoft Excel/16.0' }

Called /hello with this body:

it works

As you can see, we're also adding a header Connection: close to our response, but most servers usually send Connection: keep-alive. This is due to the http specification, which allows reusing the current socket for future requests, and webservers use this to gain some extra performance. Since our webserver isn't going to be as fast as any other server anyway, we might as well skip this and close the sockets, which is easier than keeping connections open, too.

It's blocking the gui, how do I stop it now?

We got an echo server working, great! But it only works for one request and we'll have to restart the macro everytime a client requests something, so let's put it in a loop and we're good to go.

Well, not exactly. If we execute a while (true) { } style loop in a macro, we'll see a lot of white and a "Microsoft Excel (not responding)" kind of titlebar. This is due to how Excel handles macro execution. As you might guess, macros are executed on the main thread, so whatever we'll do, our server will prevent us from accessing Excel or even stopping the macro.

In our macro, however, we can do as much as we want, e.g. implement a kill-switch. Our kill-switch will be a file, which we'll create when the server starts and which the server will monitor. If the file gets deleted, the server stops, easy as that.

But we're not done here just yet. Calling accept to get a client socket also blocks the macro execution until a client connects to our server. Searching for "winsocks accept timeout" takes us onto another C++ adventure: porting the FD_SET and FD_ZERO macros to VBA to use the select method, which in turn gives us the count of available client sockets.

After we successfully ported these C++ macros to VBA, we can pass a timeval object to select and check if there's a client before blocking with accept. Adding this to our server, we're finally able to do as many requests as we wish, plus we can stop the server using our kill-switch. Awesome!

Update 2017-10-09: As Michiel van der Blonk pointed out, calling DoEventsfrom VBA will pause the macro execution until Excel finished processing its event queue. Adding this to our server loop allows us to access Excel while the server is running.

Creating a modular server architecture

If we want to get on the same level as express or any real-world webserver, we must to be able to configure http routes. VBA doesn't contain any form of inline functions, but it does contain basic inheritance. We can use this feature to create an abstract IWebController base class, which we then subclass for our specific controllers.

Besides actually handling requests, each controller should also contain a method like MatchesUrl, which the server can use to find the appropriate controller for a request. Encapsulating this in a WebControllerCollection and adding such a collection to our server, we're now able to add any business logic to our server (like a FileSystemWebController to serve static files).

Getting some REST

The title of this article promised a RESTful backend in Excel, and to this point we only got a basic http server. Since Excel is basically a set of tables, we might use this to our advance and read/modify the table's data using an IWebController subclass.

CREATE TABLE

When we create a table in any real-world relational database, we're using something like this:

CREATE TABLE cities (
id INT PRIMARY KEY,
name VARCHAR(200),
fk_states VARCHAR(200)
);

In Excel, we can use worksheets as tables and the current workbook as our database. But how do we create columns? We can't change the column headings from "A", "B", "C", ... to anything else, so our best bet is to use the first line for our columns. Defining the primary key in a column needs to be easy as well. The easiest way to show that something is important, is to make it red or bold. Excel supports many different red tones, so marking our primary keys bold is probably the best idea:

Reading all entities from a table is easy as well, we just need to iterate over all rows until the primary key column is empty. Inserting works the same way, except we're looking for the first empty primary key cell to insert our record.

For any create or update REST action we'll need to parse the request body. Since most frontend frameworks use JSON exclusively, we'll need a new JsonParser, which emits JsonObjects and JsonArrays. To keep it simple, we're using a hand-written recursive-descent top-down parser, which counts braces/brackets and then recursively calls the appropriate parse method.

Now that we have tables with primary keys and a JSON parser, we can go ahead and create REST endpoints in a WorkbookWebController. To not iterate over all our tables on every request, we can add a route prefix like /workbook (e.g. /workbook/cities). In the ProcessRequest method of our controller, we can then analyze which sheet was requested and which REST method we should perform. This yields a basic REST backend, which can return all entries in a table, and return, update or delete a single entity.

Let's call it WRM

Doing basic REST stuff is not good enough though: real web frameworks like ASP.NET map relationships of entities. If we'd have above schema in a database used by an ASP.NET app, the underlying persistence framework would resolve all foreign keys (e.g. fk_states) and map these to their actual entities. We can create something similar in Excel, using not an object relationship mapper, but rather a worksheet relationship mapper (WRM).

In our WRM, we can read all table entries, but before returning the data to the client or inserting it to our tables, we're iterating over all columns and try to resolve each column starting with "fk_". Everytime we find such a column, we'll get the matching entity of the foreign table and use it instead of the raw value. Once everything is resolved, we might get something like this when accessing /workbook/cities from the above schema:

[
{
"id": "1",
"city": "Seattle",
"states": {
"short_name": "WA",
"full_name": "Washington"
}
},
{
"id": "2",
"city": "Springfield",
"states": null
}
]
Putting it all together

We can now combine the contents of this article and build highly complex database schemas in Excel, which we can access using REST methods. As quickly noted before, our Excel server also supports serving static files, so it makes it an ideal platform to prototype our future web applications - at least if you're prototyping on Windows (maybe macOS support will come one day).

To showcase webxcel's ease-of-use, the repository contains a React todo app with an Excel backend in the example folder.


By :  Michael Neu


Dynamically Select Cells For Use in a VBA Function

I've written the function below that I would like to use dynamically in my excel sheet where the function would assess the value of the variable selected in the formula and based on that value, perform a simple calculation referencing values in cells in different columns but in the same row.

I've written the function below that I would like to use dynamically in my excel sheet where the function would assess the value of the variable selected in the formula and based on that value, perform a simple calculation referencing values in cells in different columns but in the same row.

I know that the code below works, but how can I rewrite the ranges so that my function can be used dynamically for each row of the same column?

Function AddedValue(TabSize As Integer)

Select Case TabSize

Case 2
    AddedValue = Range("K3") * (Range("N3") * (Range("H3") * 0.001))

Case 4
    AddedValue = Range("K3") * (Range("O3") * (Range("H3") * 0.001))

Case 6
    AddedValue = Range("K3") * (Range("P3") * (Range("H3") * 0.001))

Case 8
    AddedValue = Range("K3") * (Range("Q3") * (Range("H3") * 0.001))

Case 10
    AddedValue = Range("K3") * (Range("R3") * (Range("H3") * 0.001))

End Select
End Function


How to automatically insert a copied row after a specific value in a cell with VBA

I have a table that contains a set of bundles that have to be broken down into their components. For this I'm looking for VBA instructions that will copy any row that contains the tag "-edubnd" at the end of the 'sku' cell (please see table bellow for example) twice underneath itself.

I have a table that contains a set of bundles that have to be broken down into their components. For this I'm looking for VBA instructions that will copy any row that contains the tag "-edubnd" at the end of the 'sku' cell (please see table bellow for example) twice underneath itself.

It might be easier to ignore the tag component and use a specific set of values that the code looks for, that's also possible as the values marked as bundles are always the same in the column. What I mean is, instead of looking for the -edubnd tag, the code just looks for a specific value in that column,

I have created a sample table below that is similar-enough to my table in excel that it should serve to illustrate the question.

I'm currently filtering out the dataset, copying it into a different excel document, then running this:

Sub insertrows()

Dim I As Long

Dim xCount As Integer

LableNumber:

xCount = 2

For I = Range("A" & Rows.CountLarge).End(xlUp).Row To 1 Step -1

Rows(I).Copy

Rows(I).Resize(xCount).Insert

Next

Application.CutCopyMode = False

End Sub

_

CURRENT TABLE:
column1   |    column2    |  column3 |  column3
A | pear | blue | 10
A | apple | orange | 50
A | orange | yellow | 30
A | kiwi | yellow | 20
A | orange-edubnd | blue | 100
A | apple | green | 10
A | pear-edubnd | green | 50
A | mango | pink | 60

_

DESIRED TABLE

Note: the copied row after each distinct column2 with the -edubnd tag

 column1   |    column2    |  column3 |  column3

A | pear | blue | 10
A | apple | orange | 50
A | orange | yellow | 30
A | kiwi | yellow | 20
A | orange-edubnd | blue | 100
A | orange-edubnd | blue | 100
A | orange-edubnd | blue | 100
A | apple | green | 10
A | pear-edubnd | green | 50
A | pear-edubnd | green | 50
A | pear-edubnd | green | 50
A | mango | pink | 60