ALTOM: Few appreciate the power of Microsoft Office

I can think of few products that I’ve cursed more soundly over the years than Microsoft’s Office applications. But I’m also hard-pressed to think of more versatile and powerful tools. Talk about love-hate.

I mean, even up to a few years ago, Microsoft Word documents had a tendency to corrupt on me for no reason at all. I can’t begin to count how many I’ve had to reconstruct over the years. The Web was full of advice for recovering them. Sending them as e-mail attachments was a crapshoot, yielding corrupted files at the far end so often I’d taken to converting them routinely into Adobe Acrobat files before sending them, because Acrobat files didn’t blow up as readily.

Yet, I kept using Word, and not entirely because everybody else also did. There are free or very cheap alternatives to Microsoft Office, from Google Docs and OpenOffice to IBM’s Lotus Suite. I continued using Word partly because, when it worked, it had unparalleled flexibility. I could publish newsletters with it, devise interactive forms and then lock the form fields, as well as write the equivalent of “War and Peace” if I were so inclined, by hooking chapter files together.

I could even program in it. The earliest Word programming language was a kind of Word-specific macro language, but Microsoft eventually concocted a language that was more powerful, calling it Visual Basic for Applications, or as the industry quickly dubbed it, VBA. VBA also was built into the other Office tools, which allowed exchanging data and commands between applications, as well as turning documents, spreadsheets and presentations into their own computer programs.

Excel also has won my heart, despite the occasional attack of heartburn. Among the Office applications, Excel is unusual in having two ways of programming (technically, I suppose Access has two as well, but that’s getting rather wonkish). Excel has “functions” that are really a kind of prewritten macro.

There are hundreds of them, from the simple to the complicated. You click in a cell, then select or type a function, along with its “arguments,” which are those numbers or cell locations in the parentheses.

Too few users ever explore functions, which is a shame because they can simplify repetitive tasks enormously. They can be simple programs in themselves, with “if-then” branching to build a little simple-minded intelligence into the function.

Almost any commonplace task has some kind of function that works with it. For text alone, functions can compare two sets of text for equality, format text, convert it to lower or uppercase, find specific text, and much more. They even operate across different worksheets. There are mathematical functions, statistical functions, lookup functions, and even database functions.

Like the other applications in Office, Excel has VBA, which takes programming to a stratospheric level. If you have an itch to learn the programmer’s art, VBA is a good way to experiment. VBA is what programmers call “object-centric,” meaning it operates mostly on “objects” in the application. In Excel, for example, a cell is an object that allows various operations to be done with it and inside it. In VBA, you don’t tell Excel to make the text in cell B12 red. You directly “address” the text in the cell and simply tell it that henceforth it’s red.

If all this sounds too icky to be fun, you might be right. Programming is one of those skills that either enchants or infuriates. Programmers love the creativity and sense of control. Other, more reasonable human beings tend to disagree. This leads to the “wizard effect” in offices, where at least one member of any fair-sized organization gets programmer’s disease and pretty soon everybody else is lining up at his cubicle for help making their applications sing and dance the hula, just like the wizard’s do.

Microsoft Office’s VBA isn’t an ideal programming environment, but it is convenient and relatively easy to learn. There are shelves full of books that show you how to program in it, as well as tutorials and guides all over the Web.

As might be expected, Excel is the popular leader, because Excel users tend to be more technically inclined. VBA in Word is useful, but not as popular. Word already does just about everything that 99 percent of humanity would want to do in it.

Most users never even get to all the built-in features. VBA in PowerPoint and Access goes almost entirely unused. Microsoft itself strains a bit to find examples of using VBA in PowerPoint. In my mind, even PowerPoint’s built-in glitziness is more than I ever want to see. If I never have to sit through another dizzying array of text and picture blocks flying in from all sides during a presentation, I’ll count myself a lucky guy.•

__________

Altom is an independent local technology consultant. His column appears every other week. He can be reached at taltom@ibj.com.