back to article 'Microsoft Office has been the bane of my life, while simultaneously keeping me employed'

Welcome back to Line Break, our weekly roundup of terrible code you've seen in the wild. Over the past six weeks, we've featured all sorts of broken or ugly source – from insecure web apps to write-once-read-never-again scientific programs. Now we turn to ... the monthly report. The sort of thing you automate ASAP with a handy …

Silver badge

MS Orifice - so aptly named

I shall be glad when I dinnaly call it a day and retire. No more MS Office and no more 'ribbon'. Ugh! Personally, I hate the evil thing. It slow me down so much.

Then there are the projects that have their whole lifecycle in a single Excel spreadsheet. all the issues, hitsroy and fixed in on effing/sodding great spreadsheet. God how I hate this practice. Almost as bad as ***** powerpoint presentations.

roll on an Office free world.

27
4
Anonymous Coward

Re: MS Orifice - so aptly named

Entire test suites kept in a spreadsheet, with Excel trying to guess that your test data is actually a number and stripping out the leading 0, or not allowing your data to begin with a dash or an equals.

7
0
Silver badge

Re: MS Orifice - so aptly named

Produced a flat CSV file to push some data into a third party system. User has Excel and proceeds to double click the file. Excel helpfully and silently truncates any leading zeros. User closes and is prompted to save changes, of course answering yes. Now file won't import (best case) or imports wrong data (which is really hard to recover from in this case). Total PITA.

0
0

Re: MS Orifice - so aptly named

MS has the powershell Output-CSV cmdlet output all fields with the data surrounded by quotes*. I had to look it up, but the use of quotes in this way to force interpretation as strings is in fact *legitimate* for CSV.

* Amusing they have to build in the workaround to their own stupid product.

0
0
Silver badge

@Trixr Re: MS Orifice - so aptly named

I think you're missing the point. The problem isn't Excel exporting CSVs, it's when Excel imports a CSV. That's when it "helpfully" removes leading zeros, re-formats dates, etc.

0
0

The deep mysteries of VBA bugs

I spend a fair bit of time in VBA and I share that poor writer's pain. The most recently encountered bug was one that, apparently randomly, reset the options in Find and Replace in Word. It had a very low chance of occurring - maybe one in every twenty thousand uses or so - but it's there. And if you're not guarding against it, it will screw you over. There are tons of these.

6
0
Silver badge

Re: The deep mysteries of VBA bugs

That "wait" issue is as old as Windows itself. If you're doing hard loop processing, mess with a UI context directly (like switching sheets) but fail to Yield/SleepEx or otherwise defer to the message queue to process the WM_PAINT etc. then bad things can happen.

With a proper language you can use worker threads and sync objects. With VBA I imagine best practice is to behave like you're programming for Win16 (or something equally primitive, like JS async callbacks).

17
1

Re: The deep mysteries of VBA bugs

I'm fortunate that I have no coworker to come after me for that fudge, in the event that I ever leave the report will revert back to someone physically having to copy and paste data manually.

It's also a fudge that works but has no discernible detrimental effect on the remaining code.

Ironically MS Office on a Mac does have one huge advantage that the Windows version doesn't have. You can work on other applications at the same time the macro is running. Which is great if the macro is still running at lunch time as it means it'll continue running in the background even while you're reading El Reg.

9
0
Silver badge

Re: The deep mysteries of VBA bugs

This is why at my last place of work i feigned ignorance on anything vba related.

I also knew that the original developer of most of it for Excel 2000.... Was pushing up the daisy's so no chance of finding out just why it did certain things. (also meant that office 2k was still in use in 2014..*sigh*)

5
0
Devil

Re: the original developer of most of it for Excel 2000

I'm surprised he upgraded to 2000. Everything past 97 was crap, and 97 is all you need.

24
1
Happy

Re: the original developer of most of it for Excel 2000

> Everything past 97 was crap, and 97 is all you need.

Indeed ...

Your comment has made me very happy.

Been triyng to get that in many co-workers' heads for the longest while.

Cheers.

12
0
DJV
Unhappy

Re: the original developer of most of it for Excel 2000

Regretfully, if someone sends you a spreadsheet with more than 256 columns then opening it in Office 97 (and possibly 2000, if I recall correctly) will just silently truncate each line at 256 columns leaving you none the wiser that there was ever any more data available.

I gave up on Office after 2000 and now use WPS or Libre as they are both less hassle.

7
1

Re: The deep mysteries of VBA bugs

How are async callbacks primitive???

1
0
Silver badge

How are async callbacks primitive???

Have a look at the WSA... socket functions in the Win16/32 API and you'll see what I mean. The concept was dreamed up to support "cooperative multitasking" (translation: "we can't do multi-threading properly") so that blocking operations could be fudged by posting WM_ messages to the primary message queue to trigger "events".

It avoids synchronization objects (Critical Sections, Mutexes) at the cost of implementing state machines and (usually) global variables all over the place. Modern versions of Windows support something similar via the WaitForMultipleObjects() API, but that is mostly for avoiding busy I/O waits in worker threads. Using it to bundle blocking I/O into the primary UI thread is almost always an anti-pattern.

JS, like Win16 (and VBA), is inherently single threaded so has to implement the same workarounds MSFT did 25 years ago.

5
1

Re: How are async callbacks primitive???

Async callbacks are used even for multi-threaded code, unless you use the anti-pattern of using one thread per request (whatever a request is in yuor domain), which will kill any chance of implementing a scalable architecture.

5
0
Silver badge

Async callbacks are used even for multi-threaded code..

Yes. That's I/O completion ports and WaitForMultipleObjects() etc. since we're talking Windows. I was discussing the approach in the context of single threaded scripting languages (JS, VBA) and/or operating systems (Win16).

As for anti-pattern: I agree if one defines "scalable" to mean "web scale", but there are other games in town. If you have an industrial control system communicating with 8 gizmos it may be that the only scaling physically possible within the factory is 32 gizmos and persistent state with deterministic timing is everything (Robotics, for example).

In such cases one thread per peer is perfectly valid and eliminates some timing and race condition traps that an async/state machine/thread pool approach needs to code around with sync objects. Global locks to transfer a state object between threads are expensive, for example.

0
1
Silver badge

Re: The deep mysteries of VBA bugs

That "wait" issue is as old as Windows itself. If you're doing hard loop processing, mess with a UI context directly (like switching sheets) but fail to Yield/SleepEx or otherwise defer to the message queue to process the WM_PAINT etc. then bad things can happen.

Does DoEvents not handle it?

1
0
Silver badge

Re: the original developer of most of it for Excel 2000

Anything up to 2003 has a maximum of 256 columns and 65536 rows.

1
0
Anonymous Coward

Re: the original developer of most of it for Excel 2000

97 was the last version to run on NT 3.5.1

So NT 3.5.1 is all the OS you'll ever need.

2
0
Silver badge

Does DoEvents not handle it?

I believe so (I don't do BASIC). As I understand it DoEvents amounts to the same as Application.ProcessMessages in FreePascal which in turn amounts to something like:

procedure DoEvents()

var

msg : TMsg;

retval : Integer;

begin

while (PeekMessage(msg, NULL, 0, 0, PM_NOREMOVE )) do

begin

retval = GetMessage(msg, NULL, 0, 0); //Message Pump

case retval of

-1 : raise EFatalException.Create;

0 : PostQuitMessage(msg.wParam);

else begin

TranslateMessage(msg);

DispatchMessage(msg);

end;

end;

end

0
0
Devil

Excel 97 bugs...

I have one with my name on it (as the guy that reported it...^^)

Intern, had to migrate a bunch (1500+) excel files (and their macros...) to consolidate on another excel.

I decided to do take the easy way and learned my two bits of VBA, and basically mass cut and past every single cell concerned. Work once, test twice, fiddle with Netscape on the job while the macro is running... good plan.

Alas, cut and paste do not appreciate to have a ";" in a cell.

As in copies the first part, then stops and pastes everything before the ";"

Cue MS Tech support : "yes, it is a bug. will be solved in the next version" ARRRGH

Solution : one macro to replace ";" as "ß" | launch original macro | replace all "ß" by ";"

lazyness, full internet acces, and the first time I ever said "sorry, macros are running, I cannot do what you need' aka:compile time ^^

0
0
Silver badge

Re: the original developer of most of it for Excel 2000

"NT 3.5.1 is all the OS you'll ever need."

Bearing in mind that everything past that point has been progressively less stable... "Amen"

0
0

That's bound to hurt!

"sobbing with your hands in your head"

That image that is going to stay with me for a while.....

10
0

Difficult to describe this one

I have used VBA in Orifice over many years (though less so these days). My biggest problem was with automation between Access and other programs, particularly Excel.

If I had been asked to program a formatted and detailed output to Excel (with things like charts and stuff), often the 'easiest' way was to get Access to create an Excel instance in the background, create a workbook and output to that, without having to show the user anything.

Now, often such outputs to Excel would take a few minutes, and the user would go off and do something else while they were waiting, like check other Excel files.

So, cue problem: as far as I could ever figure out, Microsoft never made it possible to prevent the background Excel application from being visible to Windows Explorer. This meant that whenever a user used Explorer to find an Excel file to open and double-clicked it, instead of Windows opening a new instance of Excel, it would always seek an already running one, which would make the background Excel visible and all of the automation occurring from Access into the output file, often mucking up the process as well. Grrrrr.

These days I really try to avoid such things, and I'm trying to get into proper programming, like C++. It's safer than VBA.

8
0

Re: Difficult to describe this one

There's plenty of middle ground between scripting in VBA and close-to-the-metal C++, you know. If you have to produce Excel worksheets for people, there are some nice Apache Commons Java libraries.

0
5

Re: Difficult to describe this one

At one company I worked at their entire print and control system was written in Access and ran on a timer overnight to print thousands of letters each night. The macros built into the Access database would open a form, show or hide images embedded into the form (creating client's company logos for the letters) and then a bespoke printer driver would select the printer for the letter to be printed on.

Along with that there was a reporting database (again Access) which would also run overnight producing the reports. The VBA code for that one would open a Excel spreadsheet, write the results of an SQL query to it and then send it via Outlook to a list of people.

Invariably something would go wrong every day.

9
0

Re: Difficult to describe this one

I've always found the biggest issue to be the amount of people who use select / activate far too much. You don't need to select sheets, ranges etc to perform actions on them. Just be specific.

i.e.

Sheet.Select

Range.Select

Selection.Copy

Sheet.Select

Sheet.Range.Select

Selection.Paste

Anything that interrupts that flow of selection (especially during debug) ruins your report.

If you're concise and explicit, that problem goes away.

Sheets(name).Range(Range).Copy

Sheets(otherSheet).Range(range).PasteSpecial xlPasteValues

The other one is where you open secondary workbooks. Noone ever seems to instance them into a variable, and as such rely on the filename as a handle, and when saving out date-specific versions to different places, that becomes a major issue just tracking the last name you saved it as.

I used to manage an estate of 90 reports, all run through excel vba, which were triggered through the Win2K task manager (I know, I know.. but it worked, mostly). By making the code concise and explicit, I could have 10 reports running simultaneously on the same machine, without fearing they'd screw each other up.

VBA is not evil in and of itself (the differences between versions, and even the versions used in different Office applications of the same vintage not withstanding...). Bad VBA is, however, and it is endemic, as most coders of it seem to learn only through recording macros and regurgitating what it records.

12
0
Silver badge

4 Major N

I havn't automated a Microsoft Excel sheet for years (2000 was the last version I worked on at this level) but I immediately thought what you said about activating and selecting when I read the account.

Your comment about "only so much VBA and no further" attitudes is spot on. There is a double standard inculcated in Western CS graduates that if they didn't get indoctrinated in it in college it dowsn'tbwork, and therefore it is the language's shortcomings to blame rather than a lack of proper know-how on the part of the programmer.

While I wouldn't use VBA to script anything these days it is because I don't write macro code for office and have script tools better suited to the tasks I do want to do than any "brokenness" in the language per se.

And of course, automation that self-launches in any but the most controlled environments is horribly dangerous in any computer language with late binding.

6
0

Re: Difficult to describe this one

There's plenty of middle ground between scripting in VBA and close-to-the-metal C++, you know. If you have to produce Excel worksheets for people, there are some nice Apache Commons Java libraries.

I'm not learning C++ to do anything in Office with it, it's for server side programming. Producing Excel worksheets via automation is now a thing of the past for me (I hope!)

3
0
Silver badge
Thumb Up

Re: Difficult to describe this one

Also, when dealing with large amounts of data, I copy it into arrays in memory to work on it - it's an awful lot quicker than operating directly in the sheets.

3
0

Re: Difficult to describe this one

I've stopped using VBA in favour of R and Python, but do remember seeing a lot of this. In correcting one common mistake, you've made another one.

"Select" and "Active" in macros are bad, but so is copy and paste. What happens if the user manually copies something while your macro is running?

Sheets(otherSheet).Range(range).formula = Sheets(name).Range(Range).value

2
0
Anonymous Coward

The worst think about office

It's when you have to fix someone else's access database, I've lost count of the number of hours trawling through uncommented code that references queries named after pet dogs and other databases hidden on the network that no one knew about, all with zero documentation except "Put data here and press button". Don't even get me started on a button to VBA to a macro script to run a few VBA procedures with helpful names like proc_22, Proc_2 in no particular order and all the common sense attributed to attempting to herd cats. The problem in the end was two tables unhelpfully named as suplis supcod not having a new supplier entered which really should have been in the documentation.

5
0

Re: The worst think about office

It's even worse when you also get a call to SQL procedures and have to then trace that through a labyrinth of SQL views and tables. Remembering not to refresh certain views as for some reason someone coded triggers on every single table and updating one single field in one record creates a cascade effect...

4
0
Anonymous Coward

THING, THING goddammit

.(some letters)

3
0

You got me at the title...

I've always said that, for better or worse, if it wasn't for Microsoft, I would be busking on the streets... and I can't play any instruments.

7
0
Silver badge
Go

Re: You got me at the title...

I was going to say you could drop the word 'Office' from the title and it would still work, but then I realised you could put almost any program or OS in there.

Perhaps "Computers have been the bane of my life, while simultaneously keeping me employed" would be the most accurate title.

11
0
Silver badge

Fucking VBA.

Fucking Office.

Cant some kickstart an pyhton automated Lubre Office bundle - Uno and all that?

6
0
Silver badge

Why bother?

Just use xlwings (works also on MacOS). Though for this particular task I'd be tempted to do it all in Python, with my-developer-of-a-Python-Excel-library hat on, as the overhead of communicating with Excel severely limits any parallelism.

1
0
Silver badge

I was also thinking that I would use use Python with an off the shelf library to suck the data out of the master file, figure out the report, and then generate the report files. That's assuming that MS Excel is even needed anywhere in the process at all and you can't simply go from database to final presentation format. I wouldn't be surprised if doing the report in Python (or some other similar language) cut the run time down to being limited only by how fast the PC could write the output files to disk (i.e., seconds).

Many years ago I re-wrote a report system for a critical product quality system from a database + proprietary language (which will remain nameless to protect the guilty) to using a "proper" programming language with in-memory data. I had inherited this system and had previously put much optimisation work into cutting the daily report run time from "all night long" to "a few hours".

Upon testing the all new "proper" system with real data, the report was on the screen before I finished raising my finger from the "enter" key. I spent much time with a debugger before convincing myself that it was working correctly and it really was that fast. That made a big impression on me which has lasted to this day.

The thing which makes me most nervous about VBA + MS Excel reports though is just how hard it is to understand what's in there and what it's doing. Nobody but the original author seems to understand it. Code auditing, unit tests, revision history (who changed what and why), etc. are usually non-existent. And yet you can find many companies whose bean counters quibble for hours over the provenance of every expense receipt, but are perfectly happy to plug those numbers into a rat's nest of VBA macros and an untraceable chain of spreadsheets to come up with the company's bottom line. It's a horror show that I'm happy to stay well away from.

9
0

LibreOffice is scriptable from ruby and perl IIRC, I mainly use the ruby binding to extract data from .XLS files rather than to produced finished outputs..

0
0
Silver badge

"bean counters quibble for hours over the provenance of every expense receipt, but are perfectly happy to plug those numbers into a rat's nest of VBA macros"

If you want to blow their minds, ask them if they understand what those VBA macros do and if they've been properly audited.

It's about then that they go from thinking "computer says no" to "this was written by someone, somewhere who wasn't an accountant and doesn't understand double entry bookkeeping, or WORSE, was an accountant and whose coding skills are on par with mine"

2
0

Load bearing breakpoint

Came across one today very similar to the VBA one above.

When stepping through the code it worked, when run normally it didn't. Took me a while to track down that it was reading a DB value that was updated in one scope that hadn't quite updated fully, and due to the read uncommitted isolation level, I was still picking up the old value.

6
0
Silver badge

Re: Load bearing breakpoint

I'm told that (an earlier version of) a Fortran program I once supported would run fine in debug mode but give wrong results once compiled normally. This was eventually traced to 16-bit values being assigned to 8-bit long variables (or 32 to 16, can't remember), which, thanks to the generosity of the debug mode, happened to always be located at 16 (or 32) bit intervals in memory, which enabled the overflow to be stored "accidentally".

The (minimal) optimization of the compiler of course removed this margin...

7
0

Re: Fortran program, [ran] fine in debug, but wrong results once compiled normally.

IME of fortran, most (almost all) weird heisenbugs were due to out-of-bound array references or data size mismatches. Except for the one that turned out to be due to tiny differences in numerical integration, combined with (crossing or not) the branch cut in the complex square root function.

2
0
Anonymous Coward

Bernie Madoff had two programmers that wrote reports that gave as output what Bernie wanted, rather than what was based on the input. They are both in jail.

When the first Senior Manager gets jailed for instructing what should be the output, IT will dramatically improve in quality.

7
0
Silver badge

"When the first Senior Manager gets jailed for instructing what should be the output, IT will dramatically improve in quality."

I am more worried by the possibility that senior management had actually been making decisions based on the garbage data point, and that replacing it with anything with rand() in it would suddenly cause major changes in company policy. For better or worse, that's the question.

I once worked for a company which made some products in very low volume (like <1 a month.) As they were rather expensive, the MD asked if there was a way of using statistical analysis to forecast production volumes. Based on six months of history. I quite liked the old boy, but the ensuing conversation reminded me forcibly of rules I had formulated years before:

Rule 1: Do not discuss statistics with mechanical engineers.

Rule 2: See Rule 1.

5
0
Silver badge

The purpose of reports is not what you think it is.

I often spent my time examining office 'documents' in the vain hope of being able to apply sensible software practices to the flotsam and jetsam oozing out of offices.

I once worked out what a massive report that took a couple of days to run on the company accountants top of the range PC which sucked data from the mainframe db until his PC was overflowing with data he simply analysed in excel and knocked up a bit of code and some sql and got it down to about 5 minutes run time.

I then found out there was a very good non-operational reason why this report had to take two days to run. I've had lots of different jobs since then but almost all of them involve people who dont like very simple procedures being run by anyone other than themselves for reasons that their boss wouldnt like - unless they are in on it too as is sometimes the case. I do wonder if this is why Office is so popular when there are far better cheaper solutions.

9
1

Re: The purpose of reports is not what you think it is.

My first Excel report. Ran on a 486 laptop and took 20 minutes to run. The manager resisted all attempts to shift the report onto one of the (then) brand new pentium machines. Reason being, once the report was kicked off it gave him just enough time to sneak a full english breakfast with the production shift in the canteen. I eventually moved the report onto the new PC, but continued with the traditional breakfast break on the grounds I got to chat with the production staff and hear what issues they were having in an informal setting prior to the main engineering meeting (that the report was being run for).

5
0
Silver badge

Re: The purpose of reports is not what you think it is.

..."once the report was kicked off it gave him just enough time to sneak a full english breakfast with the production shift in the canteen. I eventually moved the report onto the new PC, but continued with the traditional breakfast break on the grounds I got to chat with the production staff and hear what issues they were having in an informal setting prior to the main engineering meeting

Good heavens; Manager meets staff for discussions in informal setting

In my experience the value of that sort of approach is almost beyond measure; different levels - perhaps different disciplines / departments - chat in relaxed setting. The outcomes are almost inevitably beneficial.

11
0

Re: I can imagine a coworker coming for someone's kneecaps

This would be one of those times when a comment in front of the code would be appropriate.

REM: No idea WTF this fixes the crashing as it is effectively a null function, but it does. Feel free to spend a week hunting the root cause if you have the time.

3
2

Page:

POST COMMENT House rules

Not a member of The Register? Create a new account here.

  • Enter your comment

  • Add an icon

Anonymous cowards cannot choose their icon

Forums

Biting the hand that feeds IT © 1998–2018