excel-messenger: A Terrible Experiment In VBA

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

p2pwindow

  • Have a send message and an inbox cell in each workbook

p2p message

  • Make each person’s inbox cell reference the other persons send message cell

external ref

You’re done!

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 client workbooks and one server workbook

cs-window

  • Clients can input a nickname and a message

cs-client

  • 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.

cs-server

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.

cs-client-real

 

See source code and excel files on github.

 

 

 

21 thoughts on “excel-messenger: A Terrible Experiment In VBA

Leave a Reply

Your email address will not be published. Required fields are marked *