Forem: Michael Neu

Unit Testing VBA Macros

Michael Neu — Tue, 26 Nov 2019 22:24:50 +0000

Webxcel, the first webserver written in plain Visual Basic macros in Microsoft Excel, is getting more and more mature. Last time I blogged about getting PHP support going, this time we'll look into increasing webxcel's stability - through unit testing.

This is what it looks like:

Figure 1.1: Running VBA unit tests from PowerShell

Why are you still doing this?

You're right, I didn't invent unit testing in VBA and why should anyone even bother? Rubberduck already built tooling for people to improve their VBA workflow, however it requires you to install their add-in, and I think we can do testing in a semi-automated way without even interacting with a GUI.

Also, I decided to take a course called "Peer-To-Peer Systems and Security" at my university, which allowed students to build a predefined project in any language on any platform.

Obviously, I chose Microsoft Word, since enterprise environments need P2P systems and I take course work very seriously (I passed the course with an A+, but from what I heard, the instructors now consider limiting it to more "conservative" platforms). One of the project requirements however was (unit) testing, so I had to come up with something.

How it works

Similar to the build script introduced in the first post about automating version control for VBA projects, unit testing can be achieved by utilising Microsoft Office interop functionality.

Testing can be broken down to:

1) Identify test suites and tests
2) Setup tests (i.e. run beforeAll and beforeEach methods before all or individual tests)
3) Run tests
4) Tear down tests (i.e. run afterAll and afterEach)

Let's look into these steps in detail.

Identifying test suites and tests

.NET supports accessing the VBA document model, so we can list all modules and modify their code (i.e. importing and exporting). For simplicity and also developer experience, let's assume our test suites all have to start with "Test", since we can't annotate modules or declare them "testable" without parsing VBA ourselves. For instance, inheritance like JUnit's TestCase class would require us to understand VBA's inheritance from our unit testing tooling; checking whether a module starts with "Test" is a lot easier:

Figure 1.2: A list of modules (green background) and their corresponding test suites (blue background).

After identifying our test suites, we need to find our tests. Again we can take the naming shortcut, to only test functions starting with "Test" rather than parsing docstrings. With our test cases in place, we can start setting up and running our tests.

Fun fact: the interop code to access the individual lines of a VBA module looks a lot like Visual Basic. To access an individual module, one has to call modules.Item(index), and index starts at 1, which is typical to VB. Similarly, modules expose a module.CodeModule.Lines(fromIndex, toIndex) function, whose indices start at 1 once again. It might be a coincidence, however it looks a lot like that code has not been touched in a long time.

Setup and tear down tests

It might be helpful for tests to share some setup, e.g. when a module needs an instance of a class in all tests, it might be a good idea to create it once and share it among all tests.

To do so, we'll also search for BeforeAll, BeforeEach, AfterEach and AfterAll functions in our test suites and call them before our actual tests.

Running tests

Running a test is rather easy: interop provides an app.Run(macroName, arg1, arg2, ...) method, which we can use to call macros. We can even pass arguments or get its return value if it's an instance of a class. Note that VBA has something similar to structs, i.e. custom types, which can only be used inside VBA. Accessing a custom type returned via app.Run will not work.

To find out whether a test was run successfully, we'll need to signal our result to the test script somehow. Since we can return arbitrary objects, we can create an Assert class, which has two properties:

AssertSuccessful: indicates whether the test assertion was ok
AssertMessage: a helpful message to indicate test failure or success

By also providing a factory method to create such Assert objects like AreEqual, special messages like "expected 'foo', got 'bar'" can be crafted in case of a failure:

Figure 1.3: A failed assertion, indicating the expected and actual value

Conclusion

VBA projects can now be unit tested without running tests from a GUI.

I'm still looking for a way to run tests on a CI server for a "Build: Passing" badge on GitHub, so if you happen to know a CI provider offering preinstalled Microsoft Office for FOSS projects, let me know.

I WebRTC you - building a video chat in JavaScript

Michael Neu — Tue, 20 Aug 2019 13:51:32 +0000

For a recent university project, our team was tasked to deliver a video calling feature for both our iOS and web app. There are many solutions out there that promise video calling, but only few are free and mostly just work for one platform. As we had to build it for iOS and the web, we decided to use plain WebRTC, 'cause "can't be that hard, right ¯\_(ツ)_/¯"

tl;dr

I remember myself skimming through blog posts and tutorials, trying to find the minimum required steps, eventually even reading through the Signal iOS repository. So here's the bare gist of what you need to know to get going with WebRTC (or at least search for the things that don't work in your project):

STUN is similar to traceroute: it collects the "hops" between you and a STUN server; those hops are then called ICE candidates
ICE candiates are basically ip:port pairs; you can "contact" your app using these candidates
you'll need a duplex connection to exchange data between the calling parties. Consider using a WebSocket server, since it's the easiest way to achieve this
when one party "discovers" an ICE candidate, send it to the other party via the WebSocket/your duplex channel
get your device's media tracks and add them to your local RTCPeerConnection
create a WebRTC offer on your RTCPeerConnection, and send it to the other party
receive and use the offer, then reply with your answer to it

If this didn't help you with your problems, or you're generally interested in WebRTC, keep on reading. We'll first look at what WebRTC is and then we'll build ourselves a small video chat.

What is WebRTC?

I'll just borrow the "about" section from the official website:

WebRTC is a free, open project that provides browsers and mobile applications with Real-Time Communications (RTC) capabilities via simple APIs. The WebRTC components have been optimized to best serve this purpose.
— webrtc.org

In a nutshell, WebRTC allows you to build apps, that exchange data in real-time using a peer-to-peer connection. The data can be audio, video, or anything you want. For instance, Signal calls are done over pure WebRTC, and due to the peer-to-peer nature, work mostly without sending your call data through a third party, e.g. like Skype does now.

STUN

To establish the peer-to-peer connection between two calling parties, they need to know how to connect to each other. This is where STUN comes in. As mentioned above, it's similar to traceroute.

When you create a WebRTC client object in JavaScript, you need to provide iceServerUrls, which are essentially URLs for STUN servers. The client then goes through all hops until it reaches the STUN server. The following sequence diagram shows how it works in a simplified way:

The "further" a candidate is away from Alice (the more hops it takes to reach her), the higher its network cost is. localhost:12345 is closer to her than public_ip:45678, so the localhost cost could be 10, whereas the public_ip one could be 100. WebRTC tries to establish a connection with the lowest network cost, to ensure a high bandwidth.

Offers, answers and tracks

If you want to FaceTime with a friend, they might be interested in knowing how you're calling them, i.e. they want to see whether you're using only audio or video, or even if you're not using FaceTime at all and just call them from your landline.

WebRTC offers are similar to this: you specify what you'll be sending in the upcoming connection. So when you peer.createOffer(), it checks which tracks, e.g. video or audio, are present and includes them in the offer. Once the called party receives an offer, it peer.createAnswer() specifying its own capabilities, e.g. if it'll also send audio and video.

Signalling

An important part of WebRTC is exchanging information before the peer-to-peer connection is established. Both parties need to exchange an offer and answer, and they need to know the other side's ICE candidates, or they won't know where to send their audio and video streams after all.

That's where signalling comes in: you need to send said information to both parties. You can use anything you want to do this, but it's easiest to use a duplex connection that e.g. WebSockets provide. Using WebSockets, you'll be "notified" whenever there's an update from your signalling server.

A typical WebRTC handshake looks something like this:

First, Alice signals she wants to call Bob, so both parties initiate the WebRTC "handshake". They both acquire their ICE candidates, which they send to the other party via the signalling server. At some point, Alice creates an offer and sends it to Bob. It doesn't matter who creates the offer first (i.e. Alice or Bob), but the other party must create the answer to the offer. As both Alice and Bob know how to contact each other and what data will be sent, the peer-to-peer connection is established and they can have their conversation.

Building it

Now we know how WebRTC works, we "just" have to build it. This post will focus only on using web clients, if there's interest for an iOS version in the comments, I'll summarise the pitfalls in a new post. Also, I currently implemented the web client as a React hook useWebRTC, which I might create a post for as well.

The server will be in TypeScript, whereas the webapp will be plain JavaScript to not have a separate build process. Both will use only plain WebSockets and WebRTC - no magic there. You can find the sources to this post on GitHub.

Server

We'll use express, express-ws and a bunch of other libraries, which you can find in the package.json.

WebSocket channels

Many WebSocket libraries allow sending data in channels. At its core, a channel is just a field in the message (e.g. like { channel: "foo", data: ... }), allowing the server and app to distinguish where the message belongs to.

We'll need 5 channels:

start_call: signals that the call should be started
webrtc_ice_candidate: exchange ICE candidates
webrtc_offer: send the WebRTC offer
webrtc_answer: send the WebRTC answer
login: let the server know who you are

The browser implementation of WebSockets lacks the ability to send who you are, e.g. adding an Authorization header with your token isn't possible. We could add our token through the WebSocket's URL as a query parameter, but that implies it'll be logged on the web server and potentially cached on the browser - we don't want this.

Instead, we'll use a separate login channel, where we'll just send our name. This could be a token or anything else, but for simplicity we'll assume our name is secure and unique enough.

As we're using TypeScript, we can easily define interfaces for our messages, so we can safely exchange messages without worrying about typos:

interface LoginWebSocketMessage {
  channel: "login";
  name: string;
}

interface StartCallWebSocketMessage {
  channel: "start_call";
  otherPerson: string;
}

interface WebRTCIceCandidateWebSocketMessage {
  channel: "webrtc_ice_candidate";
  candidate: RTCIceCandidate;
  otherPerson: string;
}

interface WebRTCOfferWebSocketMessage {
  channel: "webrtc_offer";
  offer: RTCSessionDescription;
  otherPerson: string;
}

interface WebRTCAnswerWebSocketMessage {
  channel: "webrtc_answer";
  answer: RTCSessionDescription;
  otherPerson: string;
}

// these 4 messages are related to the call itself, thus we can
// bundle them in this type union, maybe we need that later
type WebSocketCallMessage =
  StartCallWebSocketMessage
  | WebRTCIceCandidateWebSocketMessage
  | WebRTCOfferWebSocketMessage
  | WebRTCAnswerWebSocketMessage;

// our overall type union for websocket messages in our backend spans
// both login and call messages
type WebSocketMessage = LoginWebSocketMessage | WebSocketCallMessage;

As we're using union types here, we can later use the TypeScript compiler to identify which message we received from just inspecting the channel property. If message.channel === "start_call", the compiler will infer that the message must be of type StartCallWebSocketMessage. Neat.

Exposing a WebSocket

We'll use express-ws to expose a WebSocket from our server, which happens to be an express app, served via http.createServer():

const app = express();
const server = createServer(app);

// serve our webapp from the public folder
app.use("/", express.static("public"));

const wsApp = expressWs(app, server).app;

// expose websocket under /ws
// handleSocketConnection is explained later
wsApp.ws("/ws", handleSocketConnection);

const port = process.env.PORT || 3000;
server.listen(port, () => {
  console.log(`server started on http://localhost:${port}`);
});

Our app will now run on port 3000 (or whatever we provide via PORT), expose a WebSocket on /ws and serve our webapp from the public directory.

User management

As video calling usually requires > 1 person, we also need to keep track of currently connected users. To do so, we can introduce an array connectedUsers, which we update every time someone connects to the WebSocket:

interface User {
  socket: WebSocket;
  name: string;
}

let connectedUsers: User[] = [];

Additionally, we should add helper functions to find users by their name or socket, for our own convenience:

function findUserBySocket(socket: WebSocket): User | undefined {
  return connectedUsers.find((user) => user.socket === socket);
}

function findUserByName(name: string): User | undefined {
  return connectedUsers.find((user) => user.name === name);
}

For this post we'll just assume there are no bad actors. So whenever a socket connects, it's a person trying to call someone soon. Our handleSocketConnection looks somewhat like this:

function handleSocketConnection(socket: WebSocket): void {
  socket.addEventListener("message", (event) => {
    const json = JSON.parse(event.data.toString());

    // handleMessage will be explained later
    handleMessage(socket, json);
  });

  socket.addEventListener("close", () => {
    // remove the user from our user list
    connectedUsers = connectedUsers.filter((user) => {
      if (user.socket === socket) {
        console.log(`${user.name} disconnected`);
        return false;
      }

      return true;
    });
  });
}

WebSocket messages can be strings or Buffers, so we need to parse them first. If it's a Buffer, calling toString() will convert it to a string.

Forwarding messages

Our signalling server essentially forwards messages between both calling parties, as shown in the sequence diagram above. To do this, we can create another convenience function forwardMessageToOtherPerson, which sends the incoming message to the otherPerson specified in the message. For debugging, we may even replace the otherPerson field with the sender sending the original message:

function forwardMessageToOtherPerson(sender: User, message: WebSocketCallMessage): void {
  const receiver = findUserByName(message.otherPerson);
  if (!receiver) {
    // in case this user doesn't exist, don't do anything
    return;
  }

  const json = JSON.stringify({
    ...message,
    otherPerson: sender.name,
  });

  receiver.socket.send(json);
}

In our handleMessage, we can login our user and potentially forward their messages to the other person. Note that all call related messages could be combined under the default statement, but for the sake of more meaningful logging, I explicitly put each channel there:

function handleMessage(socket: WebSocket, message: WebSocketMessage): void {
  const sender = findUserBySocket(socket) || {
    name: "[unknown]",
    socket,
  };

  switch (message.channel) {
    case "login":
      console.log(`${message.name} joined`);
      connectedUsers.push({ socket, name: message.name });
      break;

    case "start_call":
      console.log(`${sender.name} started a call with ${message.otherPerson}`);
      forwardMessageToOtherPerson(sender, message);
      break;

    case "webrtc_ice_candidate":
      console.log(`received ice candidate from ${sender.name}`);
      forwardMessageToOtherPerson(sender, message);
      break;

    case "webrtc_offer":
      console.log(`received offer from ${sender.name}`);
      forwardMessageToOtherPerson(sender, message);
      break;

    case "webrtc_answer":
      console.log(`received answer from ${sender.name}`);
      forwardMessageToOtherPerson(sender, message);
      break;

    default:
      console.log("unknown message", message);
      break;
  }
}

That's it for the server. When someone connects to the socket, they can login and as soon as they start the WebRTC handshake, messages will be forwarded to the person they're calling.

Web app

The web app consists of the index.html, and a JavaScript file web.js. Both are served from the public directory of the app, as shown above. The most important part of the web app are the two <video /> tags, which will be used to display the local and remote video stream. To get a consistent video feed, autoplay needs to be set on the video, or it'll be stuck on the initial frame:

<!DOCTYPE html>
<html>
  <body>
    <button id="call-button">Call someone</button>

    <div id="video-container">
      <div id="videos">
        <video id="remote-video" autoplay></video>
        <video id="local-video" autoplay></video>
      </div>
    </div>

    <script type="text/javascript" src="web.js"></script>
  </body>
</html>

Connecting to the signalling server

Our WebSocket is listening on the same server as our web app, so we can leverage location.host, which includes both hostname and port, to build our socket url. Once connected, we need to login, as WebSockets don't provide additional authentication possibilities:

// generates a username like "user42"
const randomUsername = `user${Math.floor(Math.random() * 100)}`;
const username = prompt("What's your name?", randomUsername);
const socketUrl = `ws://${location.host}/ws`;
const socket = new WebSocket(socketUrl);

// convenience method for sending json without calling JSON.stringify everytime
function sendMessageToSignallingServer(message) {
  const json = JSON.stringify(message);
  socket.send(json);
}

socket.addEventListener("open", () => {
  console.log("websocket connected");
  sendMessageToSignallingServer({
    channel: "login",
    name: username,
  });
});

socket.addEventListener("message", (event) => {
  const message = JSON.parse(event.data.toString());
  handleMessage(message);
});

Setting up WebRTC

Now this is what we've been waiting for: WebRTC. In JavaScript, there's a RTCPeerConnection class, which we can use to create WebRTC connections. We need to provide servers for ICE candidate discovery, for instance stun.stunprotocol.org:

const webrtc = new RTCPeerConnection({
  iceServers: [
    {
      urls: [
        "stun:stun.stunprotocol.org",
      ],
    },
  ],
});

webrtc.addEventListener("icecandidate", (event) => {
  if (!event.candidate) {
    return;
  }

  // when we discover a candidate, send it to the other
  // party through the signalling server
  sendMessageToSignallingServer({
    channel: "webrtc_ice_candidate",
    candidate: event.candidate,
    otherPerson,
  });
});

Sending and receiving media tracks

Video calling works best when there's video, so we need to send our video stream somehow. Here, the user media API comes in handy, which provides a function to retrieve the user's webcam stream.

navigator
  .mediaDevices
  .getUserMedia({ video: true })
  .then((localStream) => {
    // display our local video in the respective tag
    const localVideo = document.getElementById("local-video");
    localVideo.srcObject = localStream;

    // our local stream can provide different tracks, e.g. audio and
    // video. even though we're just using the video track, we should
    // add all tracks to the webrtc connection
    for (const track of localStream.getTracks()) {
      webrtc.addTrack(track, localStream);
    }
  });

webrtc.addEventListener("track", (event) => {
  // we received a media stream from the other person. as we're sure 
  // we're sending only video streams, we can safely use the first
  // stream we got. by assigning it to srcObject, it'll be rendered
  // in our video tag, just like a normal video
  const remoteVideo = document.getElementById("remote-video");
  remoteVideo.srcObject = event.streams[0];
});

Performing the WebRTC handshake

Our handleMessage function closely follows the sequence diagram above: When Bob receives a start_call message, he sends a WebRTC offer to the signalling server. Alice receives this and replies with her WebRTC answer, which Bob also receives through the signalling server. Once this is done, both exchange ICE candidates.

The WebRTC API is built around Promises, thus it's easiest to declare an async function and await inside it:

// we'll need to have remember the other person we're calling,
// thus we'll store it in a global variable
let otherPerson;

async function handleMessage(message) {
  switch (message.channel) {
    case "start_call":
      // done by Bob: create a webrtc offer for Alice
      otherPerson = message.otherPerson;
      console.log(`receiving call from ${otherPerson}`);

      const offer = await webrtc.createOffer();
      await webrtc.setLocalDescription(offer);
      sendMessageToSignallingServer({
        channel: "webrtc_offer",
        offer,
        otherPerson,
      });
      break;

    case "webrtc_offer":
      // done by Alice: react to Bob's webrtc offer
      console.log("received webrtc offer");
      // we might want to create a new RTCSessionDescription
      // from the incoming offer, but as JavaScript doesn't
      // care about types anyway, this works just fine:
      await webrtc.setRemoteDescription(message.offer);

      const answer = await webrtc.createAnswer();
      await webrtc.setLocalDescription(answer);

      sendMessageToSignallingServer({
        channel: "webrtc_answer",
        answer,
        otherPerson,
      });
      break;

    case "webrtc_answer":
      // done by Bob: use Alice's webrtc answer
      console.log("received webrtc answer");
      await webrtc.setRemoteDescription(message.answer);
      break;

    case "webrtc_ice_candidate":
      // done by both Alice and Bob: add the other one's
      // ice candidates
      console.log("received ice candidate");
      // we could also "revive" this as a new RTCIceCandidate
      await webrtc.addIceCandidate(message.candidate);
      break;

    default:
      console.log("unknown message", message);
      break;
  }
}

Starting a call from a button

The main thing we're still missing, is starting the call from the "Call someone" button. All we need to do, is send a start_call message to our signalling server, everything else will be handled by our WebSocket and handleMessage:

const callButton = document.getElementById("call-button");
callButton.addEventListener("click", () => {
  otherPerson = prompt("Who you gonna call?");
  sendMessageToSignallingServer({
    channel: "start_call",
    otherPerson,
  });
});

Conclusion

If we open the app on Chrome and Safari at the same time, we can call ourselves on different browsers. That's kinda cool!

But besides calling, there's a lot more to do that wasn't covered by this post, e.g. cleaning up our connection, which I might cover in a future post (i.e. using React Hooks for WebRTC and WebSockets). Feel free to check out the repo, where you can re-trace everything that's presented in this post as well. Thanks for reading!

Excel'ing at PHP

Michael Neu — Tue, 14 May 2019 20:59:36 +0000

It's been a while since I published my writeup on building a webserver using plain VBA macros in Microsoft Excel. You might have guessed by the title picture, this time we're not building a REST backend, but rather add support for PHP to webxcel, and basically any FastCGI enabled language, like Perl or Python.

This post will be divided into two sections: first, we'll look into how off-the-shelf webservers integrate PHP by inspecting its protocol, and later we'll see how to build this ourselves. If you're just here for the code, feel free to check out the webxcel repo.

Do you even PHP?

When I started doing webdev in school, PHP was the way to go. Being on Windows back then, the easiest solution to get up and running was to simply install a preconfigured bundle locally. My bundle of choice was XAMPP, it came with an Apache webserver, PHP, MySQL and a mail server, all configured to work together out of the box. Once you had your site ready, you could deploy it to a webhoster, which ran PHP and a webserver for you.

It's 2019 now, and people use more sophisticated setups, e.g. something like this with separate containers for each service. To cover the basics: PHP itself supports two main modes of operation, CLI and FastCGI. If you run something like this, you can launch PHP scripts directly from your shell.



$ cat foo.php
<?php echo "hello world\n"; ?>
$ php foo.php
hello world

Adding PHP support to a server using the CLI would be convenient, since we could start the process each time we get a request from our server. That's essentially what plain CGI was: launching processes on each request. But this approach would also mean a relatively big overhead (individual memory allocations per process, a "cold VM" and process limits inside containers - to name a few), so PHP ships with FastCGI as well, to reuse processes for multiple requests.

The '90s called, they want their protocol back

FastCGI was originally shipped in the 1990s and set out to make the Common Gateway Interface (CGI), well, faster. To understand how it works internally, we should read the manual, but it's more fun to monitor NGINX making FastCGI calls to PHP. Usually, Wireshark would be my tool of choice, but by implementing a quick and dirty Python relay proxy instead, which essentially listens for incoming data and forwards it to our PHP container, we have full control over the raw data sent back and forth.

To understand the messages, we'll still have to read the specification though. FastCGI messages start with a 2 bytes version and message type field, each one char wide, following 6 bytes for the rest of the messages' header. The header specifies the content length of the body in a 2 byte integer, which ranges from 0 to 65,535. So each time our FastCGI server needs to send more than 64kB of data, it'll have to split it into multiple packets adding an 8 bytes header each time. For a 1MB website that means 16 chunks with 64kB data and an 8 bytes header each, which means our 1MB file will cause 128 bytes overhead in total. If you're after that "1µs optimization", you should consider minifying your webpages before passing them to your webserver, but on the other hand, who really cares about that kind of overhead ¯\_(ツ)_/¯

Depending on the message, the body may be either a struct or plain text. Most messages are pretty easy to deserialize, except for the "most complicated" message, the FastCGI params. Each param is sent as a ${key.length}${value.length}${key}${value} string, which needs to be parsed, too. As documented in the specification, a param's key or value may not exceed 255 characters, since the length is encoded as a single byte each. That means, if we wanted our client to send a long param to our server, we'd have to split it into multiple seperate headers and join it ourselves. On a sidenote: the probably longest user agent string from the most common browsers is from Edge and it spans 140 bytes, which is obviously < 255, so not an issue either.

A typical FastCGI request looks somewhat like this:

To initiate a FastCGI request, the webserver has to send a "begin request", which describes PHP's role and contains some flags to modify the protocol. Usually, we'll only see the webserver sending FCGI_RESPONDER to PHP, since we want it to respond to us with a website. After sending our params, the webserver sends our stdin to PHP, which means it'll send our request body. If we're doing a GET request, we don't have a body, so we can send an empty stdin right away. In case of a POST or UPDATE request, we'll have to signal PHP that we're done sending our stdin. To do so, the webserver sends an empty stdin. The same message flow is used for the params, too, as you can see in the diagram above.

Now PHP can execute our script - it received the script name via the params and got our request body from stdin. Once it's done, it sends back the stdout right away. Note, there's also an stderr message type, but even by raising an error in PHP, we'll usually only receive stdout messages. Similar to stdin, PHP will send an empty message to signal once it's done sending our website in stdout. Eventually, PHP will send an "end request" message, which contains a field for the script's exit code and whether the protocol ended successfully.

Integrating FastCGI into webxcel

How can we get this into webxcel now? First, we'll need to find a way to connect to arbitrary sockets, then we'll "just" have to de-/serialize FastCGI messages and we're done. Piece of cake.

VBA hell

If you've ever written VBA macros and had problems debugging them: imagine importing native functions from a fragmented documentation on how to do winsocks. Using sockets isn't hard per se, in Python one just has to import socket and get going, but in VBA we'll need to import the socket functions ourselves. Luckily, most of the stuff was already lying around from building webxcel's TCP server, but there appear to be multiple different ways to connect to a socket using winsocks, so finding the right approach with VBA's "this variable is 0" debugger can be considered difficult.

To spare you the frustrating process, eventually:

The key here was to use set the right values in sockaddr_in and implicitely cast it to the generic sockaddr type, which allows us to use connect(SOCKET*, sockaddr, sizeof(sockaddr)).

Write(UInt16 value)

VBA is from a different time. When it was created, memory was more sacred than it is now, and for an integrated scripting language, a 2 byte signed integer was just fine. But we want to communicate with a server that uses plain old 4 byte ints, so how can we do this?

Sending an int over the network is usually done in big endian, so e.g. the number 1337 or 0x0539 will be sent as 39 05. In VBA, Long is 4 bytes, Integer is 2 bytes and Byte obviously is 1 byte. Shifting a value i >> n 'ough times will more or less yield the nth byte, that can eventually be used to marshal the value to a big endian byte array, which in turn can then be sent over the network.

VBA doesn't offer a shifting operator though, but dividing by 0xFF and using the rest and result to build the byte representation works just as well. Also, VBA doesn't have a raw byte array, but strings can be used to simulate them: VBA provides raw access to strings, just like a char array in C, which also consist of 1 byte chars. So to marshal a Long to its bytes, all there is to do is divide it often enough and assign its bytes to a properly sized String.

FastCGI has both 2 and 4 byte integers, which will be marshalled to 2 and 4 byte wide strings. Fixed size strings can be allocated by Dim foo As String * size, but this is restricted to a constant size and it would be nice to have a more dynamic solution. So to recreate a malloc(size) function, one could try to naively add a RepeatString(size, char) function, which basically repeats a string, e.g. \0, by size times.

Turns out, this actually works. You can allocate memory from VBA using arbitrary strings (up to 2GB per string), which we have raw access to. And I thought we needed C to do this.



' the first malloc(n) with O(n) runtime complexity!
Public Function Malloc(ByVal size As Long) As String
  Malloc = ""

  Dim i As Long
  For i = 1 To size
    Malloc = Malloc & Chr(0)
  End For
End Function

Fun fact: writing to memory outside of your allocated string likely results in a segmentation fault, causing Excel to crash.

Parsing and serializing messages

With marshalling in place, FastCGI messages can then be de-/serialized. By creating an abstract class IFastCGIRecord, all messages can inherit a consistent interface for de-/serialization. Each message contains a header and a body, so there should be a FastCGIHeader field inside every message, and also fields for the message body, e.g. a role field for the FastCGIBeginRequest message. De-/serializing the records can now follow the classic composition pattern: first serialize the message header by calling the header's serialization method, then the send the body, like this:



' overload for IFastCGIRecord.WriteToTcpClient
Private Sub IFastCGIRecord_WriteToTcpClient(client As TcpClient)
    Dim header As IFastCGIRecord
    Set header = m_header
    ' serialize the header first
    header.WriteToTcpClient client

    Dim bytes As String
    bytes = ""

    bytes = bytes & Marshal.Int16ToBytes(Role)
    bytes = bytes & Marshal.Int8ToBytes(Flags)
    bytes = bytes & Reserved

    client.SendString bytes
End Sub

VBA's 2 byte Integer is signed and as soon as we try to deserialize real life messages, we'll quickly see the message size uses that one extra bit the sign stole from us. Let's just use a Long instead and call it a day, since there's probably not going to be a UInt16 in VBA anytime soon.

In webxcel, more functionality can easily be added using IWebController classes. By writing a new FastCGIWebController, we can then connect to a FastCGI server and exchange FastCGI messages.

Wrapup

So, we got PHP to webxcel. Why exactly did we want this?

In all sillyness, this is actually exciting. We wrote our own basic FastCGI implementation, which not only allows us to run PHP, but any arbitrary FastCGI-capable language, e.g. Perl. And let's be honest, who doesn't want to run Perl scripts from within Excel?

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

Michael Neu — Sat, 07 Oct 2017 21:20:14 +0000

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.

NEW: Webxcel also supports PHP - see Excel'ing at PHP.

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 DoEvents from 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.

Edit on 2018-03-27 23:00
Fixed VBA syntax highlighting.

Edit on 2019-07-31 12:30
Added link to FastCGI post.