By Hiran de Silva
Just before Christmas in 1996, I took on a six-week temp assignment. The task was to assist a colleague with year-end reconciliations. The job was critical because two organisations had merged earlier that year, and their two sets of books had been brought together into the new company. It was, in many ways, a mechanical role — but that gave me space in my mind to observe and think about bigger things.
I was also working with Excel 5 at the time, which gave me the chance to dig deeply into VBA.
The Seed Was Planted Earlier
A month earlier, I had attended a Microsoft roadshow to launch Office 97 at the Natural History Museum in London. I left with mixed feelings. On the one hand, I could see the enormous strides Microsoft was making in office productivity, particularly in empowering employees to build their own productivity solutions. On the other hand, I felt frustrated: I didn’t have a role at the time where I could apply these tools. And, in any case, the technologies showcased wouldn’t filter into companies for at least a year.
So when I landed that temporary assignment, I saw my chance.
My strategy for learning VBA was simple. I already knew that Excel was far more than a cosmetic spreadsheet tool. I had been using spreadsheets for over 15 years — starting with Multiplan, then Lotus Symphony, and finally Excel. My earlier role as Head of Accounting and Finance in industry had given me ample room to innovate, so I already knew there was more beneath the surface. Somewhere along the way, I had also learned that modern software was being developed under a new paradigm called Object-Oriented Programming (OOP).
That might sound like rocket science, but it’s actually quite simple — something anyone can understand. Let me explain with an analogy.
Understanding Objects: The Kettle and the Car
Imagine you want to make a cup of tea. You need boiling water. For that, you use a kettle.
You don’t create electricity. You don’t fetch water from a river. You don’t rub sticks together to make fire. Instead, you fill the kettle with water, plug it into the mains, press the switch, and a few minutes later you have boiling water.
The kettle itself is an object. It has:
- Properties: e.g., brand, colour, weight, model number. Some are fixed (you can’t change its height or the fact that it’s blue). Others can be adjusted, like a thermostat setting for temperature.
- Methods: things the object can do. In the kettle’s case, methods include “Switch On” and “Switch Off.” These methods are triggered either by pressing the button or automatically when the water reaches boiling point.
The same is true of a motor car. Its properties include make, model, colour, year of manufacture, road tax status, MOT certificate, and so on. Its methods include “Start Engine,” “Drive,” “Turn Left,” “Turn Right.”
The key idea of OOP is that software engineers create abstract objects like these so that end-users (or power users) can manipulate them without needing to touch the underlying complexity. We don’t build electricity plants or combustion engines — we just set properties and fire off methods.
Now here’s the leap: Excel is built entirely of objects.
What looks like a sheet of paper on your screen is just a rendering of these objects. A sheet itself is an object. Rows, columns, ranges, and cells are all objects. When you type into a cell, Excel isn’t storing text on a sheet of paper. Instead, it’s assigning a value to a property of a cell object and then rendering that visually for you.
For example, when you type “Hello” into cell D4, Excel interprets that as:
Range("D4").Value = "Hello"
That’s all that’s happening. Excel is watching your keyboard or mouse input, translating it into object manipulation, and rendering the results onto the screen.
Understanding this shattered the illusion of Excel as “just a big piece of paper” and opened the door to real power.
Macros: Clearing Up a Common Misconception
To build further on this, let’s talk about macros, because they are often misunderstood.
Back in the 1980s, before Windows, computers only had keyboards — no mouse, no microphone. Early word processing packages (like WordPerfect) introduced the concept of keyboard macros. Secretaries in offices, often typing contracts and letters, would record repeated sections (like standard disclaimers or boilerplate paragraphs) so they could insert them with a keystroke rather than retyping. High-end “memory typewriters” did the same thing: record every keystroke and replay it.
In word processing, this was enormously useful.
But what about spreadsheets? Numbers entered into spreadsheets are usually unique. You don’t enter the same set of numbers tomorrow that you entered yesterday. The whole point of a spreadsheet is calculation, not repetition. So keyboard macros were practically useless in spreadsheets.
When Windows arrived in the late 1980s, things changed again. With multiple documents open and copy-paste available, even word processing no longer needed keystroke macros. You could store boilerplate text in a library document and paste it in.
So why did Microsoft keep the word “macro” in Excel?
Because although the old “keystroke macro” idea no longer mattered, the new macro recorder served a different purpose: it recorded object manipulations.
When you record a macro in Excel, it doesn’t capture keystrokes. It watches what object you touched, what property you changed, and what method you triggered.
So if you type “Hello” into D4 with the recorder running, Excel writes out the VBA code that changes the Value property of the Range("D4") object.
This was my way into VBA. I recorded everything I did, then studied the code it produced. Sometimes it was bloated (Excel would output three lines of code when one would suffice), but it was accurate enough to show me how Excel really worked under the hood.
The Epiphany: Recording “Get External Data”
Now we come to my true penny drop moment.
Excel 5 had a menu option under Data → Get External Data. This launched a wizard (MS Query) that allowed you to connect to an external data source, such as an Access database. You could choose a table, select columns, and import them into Excel.
This was mid-1990s technology — long before Power Query.
Of course, I turned on the macro recorder. I wanted to see how Excel interpreted this action.
What it produced astonished me. The generated code contained a SQL statement:
SELECT [Column1], [Column2] FROM [TableName]
I recognised it immediately, because I already knew some SQL. That single discovery changed everything.
I realised that I wasn’t limited to what the wizard offered. I could edit that SQL.
- Replace the column list with
*→ it fetched all columns. - Change the table name → it pulled from a different table.
- Add filters, sorts, aggregates → it obeyed.
Then came the experiment that blew my mind.
What if, instead of SELECT, I tried INSERT?
I rewrote the SQL, ran the macro, and to my amazement — Excel carried out the instruction on the database. A new row was inserted.
Encouraged, I tried UPDATE. It worked. Then DELETE. That worked too.
In one stroke, I had discovered that Excel could perform all four basic SQL data operations (SELECT, INSERT, UPDATE, DELETE) directly on an external relational database.
This was revolutionary.
It meant Excel could act not just as a passive consumer of data, but as a client-server interface: storing, updating, and manipulating data in a proper relational database, while still giving users the familiar Excel front end.
I realised immediately the enormous opportunities this opened up.
From Temp to Consultant: The Tripling of My Pay
Armed with this discovery, I began applying it to real challenges my client faced — problems that nobody else knew how to solve. Using these methods, I cracked “impossible” business problems, including the notorious QCA Audit crisis at Edexcel.
The CFO, John Unsworth, and the Head of IT, John Hood, saw the potential. Within weeks, my six-week temp role turned into an open-ended consultancy. They told me to stay as long as I wanted and to “do anything you like” because the results were so tangible.
They also tripled my pay — converting me from a temp to a consultant. That was the first time I experienced what I later called “Tripling My Pay with Excel.”
Confirmation from Microsoft
Years later, I stumbled across a recording of a Microsoft developer telecast from December 1993, where Satya Nadella himself demonstrated exactly this connectivity — Excel interacting live with a relational database through SQL.
He showed the same principle I had discovered independently in 1997: recording a macro to reveal the SQL under the hood.
At the time, the plumbing was messy, but within a few years Microsoft had packaged it neatly into ActiveX Data Objects (ADO). From then on, the method became not just possible, but simple.
The Legacy
That discovery in early 1997 became the foundation of my entire career. For more than two decades since, I’ve applied this simple but profound principle — Excel as a client, database as a server — to solve countless real-world business challenges.
It is the origin of what I now call the Digital Librarian method.
The core technology is deceptively simple:
- Excel is a front-end for manipulating objects.
- Relational databases (like Access or SQL Server) store and manage the data.
- SQL statements — generated or written — bridge the two.
But the applications are limitless. The creativity lies not in the technology itself but in how you use it to solve real business problems.
That was my penny drop moment — the one that set me on a lifelong journey.
Add comment