What are macros 1

The great Excel macro special part 1: What are Excel macros?

To start with, a statement: Excel is a great program. So, now it's out. Anyone who has to work a lot with tables and calculations in their day-to-day work or just wants to prepare data in a clear manner can work an incredible amount with Excel. The often overlooked program from the Microsoft Office series is often much easier than many think - because at first you are a bit overwhelmed by Excel's wide range of functions. We have already written some tutorials for this purpose, which explain, for example, how Excel formulas are structured or what the most important Excel functions are.

Anyone who has familiarized themselves with the program a little will quickly appreciate the many functions. Even if you don't have to work with Excel, you will be pleased how easily everyday things can be done with it. Because if necessary, you can sort a shopping list yourself with Excel in such a way that you don't have to rush from one shelf to another in the supermarket.

But Excel can do a lot more work for its users than most people think. There are two ways to do this. The Excel macros and the programming language for Microsoft, VBA.

Virtual Basic for Applications (VBA) is a simple programming language for the Microsoft Office range of products that is also used by some vendors such as Corel for user input. This programming language is a topic for another special, which is why we only deal with the Excel macros in this Excel special.

At first, creating macros sounds much more difficult or complex than it actually is. So don't be shy: Here we explain in detail what macros are, why they are so good.

In software development and the language of programmers, the word macro is basically the name for a series of instructions that are to be executed automatically. Hence the name macro - from the English “macroinstruction”, meaning super command. In programs for Windows in particular, macros are an automation of the user interface and not "real" programming.

This is also where a macro differs from more complex programming. Because in a macro only instructions are executed one after the other and there is no possibility of creating dependencies.

Anyone who deals with macros should already have familiarized themselves with the basic functions of Excel - because this is Excel for advanced users. All you really need to program a good macro is a solid idea of ​​the tasks you are going to do with Excel. If you know which tasks Excel should do in which order, then you can also create a macro.

Nevertheless, programming knowledge for macros is not necessary. It is useful to have a basic understanding of VBA, for example if you want to quickly delete a function from a macro, but it is not necessary. At most it makes the job a little easier, but the end result is the same.

What are Excel Macros?

As already mentioned, Excel is a very versatile and powerful program with which you can do an almost endless range of simple and complex tasks.

Many of the tasks that you do with Excel are likely to be repeated relatively often. With a macro, you can command Excel to work through a series of tasks independently with just one key combination. Sound practical? It is! Because at the latest when you want to process very large amounts of data regularly, or just want to save time because you have to stupidly enter the same commands in Excel every day, you will be happy when Excel can do everything all at once - at the push of a button .

What can Excel macros be used for?

Typical work steps that Excel users have to repeat over and over again are, for example, importing external data [link tutorial to .csv files], setting margins, column and cell sizes, inserting filters and selecting a conditional formatting.

A concrete example of macros:

Anyone who deals with bookkeeping in their day-to-day work often works with an accounting program. However, if the data is to be prepared clearly and graphically, then many pure accounting programs reach their limits. Excel, on the other hand, is more suitable than almost any other program.

So to be able to present the results of the bookkeeping from the last month to your boss, you probably use Excel. Here you can sort the data clearly in any imaginable way (after all, you can create individual filters) and collect data from different tables in a pivot table [link tutorial for pivot tables here]. Creating different, clear diagrams is also no problem at all with Excel.

The workflow here naturally includes a comparatively large number of work steps. The data must be imported, then organized in a table. The data and the table itself must be formatted and then converted into a diagram.

You can automate all of these work steps with a corresponding Excel macro.

At the push of a button, Excel imports the data from a .csv file, formats it in a table and displays the data in a diagram. Then you as the user only have to check whether Excel has actually worked correctly. For example, you can specify in the macro that Excel applies conditional formatting to a table so that you can see duplicate data immediately, for example.

What are the limits of Excel macros?

You can use macros to automate Excel. However, with macros, Excel can only manage automations that do not contain any if-then functions. That said, you can only use macros to program Excel to perform simple, linear tasks.

As soon as the tasks contain either repetitions or branches, macros are no longer the right tool. Even if other programs or even the operating system are to be included in the programming, macros no longer work. Simple VBA programming would be suitable for this. With these you can, for example, automatically transfer file names to an Excel table - a practical means of transferring projects, documents or, for example, invoices in a table. Unfortunately, this is not possible with macros. Information from Word, for example, cannot be captured using macros.

Macros are therefore only suitable for defining a series of tasks of any length that should always be carried out one after the other.

This is why the easiest way to create a macro is to use the “Record macro” function - which we will come to in the practical part of this Excel special.

You will learn how to create an Excel macro in part 2 of our macro special.