Top Menu

Tag Archives | macros

What is Office Automation?

So you use Microsoft Office programs a fair bit. You probably use Excel to analyse data. You might extract that data from the company database or just another spreadsheet file. Really advanced users might even link the Excel data to a Word document so they can publish a report. In any of these scenarios, if you find yourself carrying out repetitive actions, or a lot of long-winded copying and pasting, formatting, checking for correct values, etc then automating these actions through macros and programming could relieve you of the unnecessary tedium if the actions need to be carried out on a regular basis – freeing you to concentrate on other important work.

What is a macro?

A macro can be regarded as a shortcut to a task that you do repeatedly.  A macro is really a series of tasks that you record in your Office program that can be replayed time-and-time again. When you record a macro, your program stores information about each step you take as you perform a series of commands. You then run the macro to repeat, or play back, the commands.

For example, if you need to perform the same action on, say, 12 different Excel workbooks, you can record a macro while you perform the task on the first workbook and then let the macro repeat your action on the other workbooks.

The downside of this recording process is if you make a mistake when you record the macro, corrections you make are also recorded. Macros also are not that clever. If you record a macro to process ten rows of data in Excel then it won’t know how to handle 100 rows next time. The macro will not be able to make decisions, and if you need to interact with the user then macros will not be able to help.

How can programming help?

All Microsoft Office macros are actually saved using a programming language called Visual Basic for Applications, or VBA. VBA is really a very powerful programming language, and it can be edited and added to to make what might be a very basic macro into an extremely useful extension to the Office program that not only can perform repetitive tasks, but can make intelligent decisions, interact with the user to ask for their input, work on data that changes from one month to the next or changes in data size, and even control other Microsoft Office programs.

For example, in almost any office, you can find lots of people who don’t really understand how to use computers. Using VBA, you can make it easy for these inexperienced users to perform some useful work. For example, you can set up a foolproof data-entry template so you don’t have to waste your time doing mundane work.

Assume you are a sales manager and need to prepare a month-end sales report to keep your boss happy. If the task is straightforward, you could develop a VBA program to do it for you.

Although Excel includes numerous built-in functions (such as SUM and AVERAGE), you can create custom worksheet functions that can greatly simplify your formulas. You’ll be surprised by how easy this is. Even better, the Insert Function dialog box displays your custom functions, making them appear built-in.

If you have a need for automation, you can either learn how to do it – I have 3-day Office training courses to teach you – or I could do it for you. Give me a call for a no-obligation chat!