It was a slow day at work and another intern suggested finding a way to send messages discreetly between our computers on the local network.
To accomplish this I chose any true programmer’s favorite tools: excel sheets and VBA.
Part 1: peer-to-peer
The first idea we had:
- Create two excel workbooks on a local network drive
- Have a send message and an inbox cell in each workbook
- Make each person’s inbox cell reference the other persons send message cell
But….there’s one problem.
If I change my message I need to save my excel file, otherwise the references don’t update.
Here’s where things get real. We’re going to have to bust out some VBA to solve this.
First we make a subroutine that autosaves every 20 seconds. Saving was pretty slow on our computers.
Sub autosave() Dim update_period As Integer update_period = 20 ' seconds ActiveWorkbook.Save Application.OnTime Now + TimeValue("00:00:" & update_period), "autosave" End Sub
Next you can throw in a sub that will automatically start our saving routine when the workbook opens.
Private Sub Auto_Open() Call autosave End Sub
Done. We now have our first p2p messaging app.
Part 2: client-server
This is where the madness begins.
The first version of the app was…okay.
It worked but it wasn’t meeting the intense enterprise demands we were facing in the market. We needed something scalable, something more dynamic to meet customer needs. To fix this I pivoted our app to a client-server infrastructure.
Here’s how it went:
- Instead of two workbooks referencing each other, we create n client workbooks and one server workbook
- Clients can input a nickname and a message
- The server loops through all the clients in the folder, grabs nicknames and messages, and creates the master chat window by shifting everything up and ignoring duplicates.
That’s basically it. Now we just make all the workbooks autosave every 20 seconds to keep everyone sync’d. Clients all just reference the server sheet.
Here’s what the final result looks like, as a bonus I skinned the client sheet to look like a regular worksheet.
See source code and excel files on github.