BOFH: You can take our lives, but you'll never take OUR MACROS

Never mind the blood, Mr Beancounter: MAGIC BEANS!

Episode 6

"...And I can't seem to import all of the data I need," the user explains.

"And you're importing into Excel from what... a CSV file?" the PFY asks.

"Yes."

"And the import fails?"

"I just stops. It says something about resources."

"So perhaps you should get rid of some of the data in the spreadsheet?"

"It's an almost empty spreadsheet. All it has is about eight macros and a three extra tabs which are used by the macros."

"So it's probably the data then..."

"But I import the same data every month."

"What data?"

"Just the financials data."

"What, like monthly General Ledger summaries, etc?"

"No, the transactions."

"For accounts receivable, accounts payable or something?"

"For AR, AP as well as GL, Fixed Assets..."

"So pretty much all transactions?" the PFY asks.

"Yes."

"For that month?"

"No, just for the tax withholding period."

"So you're replicating the entire financials system for what, 7 YEARS? Or is it 10?"

"I'm just exporting the transactions!"

"Yes, that's what the financials system is. Transactions."

"Well, Anyway, I do 11 years - just to be safe".

"Into Excel?"

"Yes."

"So that's most likely your problem. Too much data."

"But it's been working up until now!"

"Yes, because now you've gone over some limit. How long does the export take?"

"I don't know, I just set it going before I go home."

"Why the hell would you need all the data?"

"I like to be able to plot trends."

"ELEVEN YEARS ISN'T A BLOODY TREND!"

"It is if I wanted to know what the average sales per annum is over the last 11 years. I use it to make graphs."

"So why don't you have a separate spreadsheet with 2003, 2004, etc. to 2014. Then you'd only need to export this year's data."

"Our financial calendar is July to July."

"So label it by the financial period."

"I do - it's in one of the macros. I do a left substring on the date in a macro and move everything with a year and month before July into the previous year's tab, and everything with a year and a month after July into an..."

"Sorry, I think I just slipped into a coma, but I'm back now. Save previous years' data in separated spreadsheets."

"I can't - we don't close off the financial years immediately."

"You don't close them off for 10 years."

"No, but it's possible that we might find a historic transaction that would affect the numbers."

"And, in the event that this happened - an event with a probability in the same far-fetched region as the SETI program ever hearing something or intelligent life being found in Luton, you could just reimport that one year's spreadsheet."

"But that number might affect all subsequent spreadsheets!"

"Reimport them!"

"But my macro does this all autom..."

"That's what it's about isn't it - your macro?" the PFY asks insightfully."

"I don't follow."

"Bloody beancounters," the PFY says. "You'd write email in excel if you could figure out how to do it in a font you like.

"I hardly think that's a deserved comm..."

"Okay, TELL ME you don't have a spreadsheet at home with all your DVDs and CDs in it which orders them by some star rating, the price you paid for them, where you bought them, who referred you to it, what else they referred you to, a convoluted SUM function to tell you the total cost of them all and then an algorithm to tell you their current value, based upon rarity, purchase price, depreciation and movements in the US dollar."

"I need a record for insurance purposes!"

"And what about the macro that tells you when you last watched/listened to them?"

"How did you know about that?"

"I didn't - I just said the saddest thing I could think of. No, wait, a movie rating algorithm based on a weighted calculation of... uh.. guns, fast cars, tits... and... uuuuhm... people getting dropped into helicopter blades."

"It's a more accurate method than R ratings!!"

"So's the bloke at the video shop!"

"Look, this isn't getting my data imported," he snaps, getting back to the point.

"Because you can't import it," I say. "You've run into one of Excel's many limitations."

"The main limitation being that it has an inbuilt stupidity limiter," the PFY explains. "When the data reaches a certain point, the stupidity limiter kicks in and you have to do it a proper way."

"Like learning SQL and using that," I add. "Or using the database's reporting system. Or a third-party reporting system which talks to the database."

"Perhaps you could use Access?" the PFY suggests.

"Access?" he asks, no doubt in similar tones to those used to say the words "Musket?" and "Shiny beads?" in the founding years of the Empire.

"Yes. It's a Microsoft product. It's a database. You could import all the data into that and then talk to that with Excel."

"So I could still use my macro and make graphs?"

"Sure."

Sometimes, just briefly, my conscience pricks me. But then I suffocate it and bury the corpse deep in my subconscious where no one goes. Sure, I'll have to do it again next week, when we have the SQL Server lite conversation, but for now, I'm at peace...

• BOFH: The whole shebang
• The Compleat BOFH Archives 95-99