Excel macros: How to optimize your work process with the popular spread­sheet programs

If you regularly work with word pro­cess­ing programs, database systems, or spread­sheet ap­pli­ca­tions, then you know how tedious it can be to enter the same di­rec­tives over and over again. More than anything, complex, multi-stage program sequences can be a real test of patience. Macros record a sequence of commands so that they can be executed au­to­mat­i­cal­ly by the software user at any given time. Even though macros are written in a pro­gram­ming language, pro­gram­ming knowledge generally isn’t needed, as many programs — including Excel – can record simple macros and provide as­sis­tance in de­vel­op­ment. For the Microsoft spread­sheet software Excel, macros are one of the most important features.

Microsoft Excel is the most sought-after solution for pro­cess­ing, analyzing, and pre­sent­ing data. Using Excel spread­sheets, which forms a key component of the Microsoft Office Suite, Windows sub­scribers have been working with this program for years to create budgets, in­di­vid­ual calendars, and a whole host of other projects. Excel is also used in business: With the software, project plans, hours lists, or budget plans are realized just as easily as graphical rep­re­sen­ta­tions of sales figures, profits, or losses. Anyone familiar with the program learns to ap­pre­ci­ate the various functions but at the same time, most people quickly develop a re­luc­tance to con­stant­ly repeat routine tasks or repet­i­tive actions that can’t easily be performed using the standard user interface.

So it comes as no surprise that the pos­si­bil­i­ty to create macros in Excel is one of the core features of the spread­sheet program. There’s an in­te­grat­ed macro-recording tool for this purpose that uses the Visual Basic for Ap­pli­ca­tion (VBA) script language, which is also used in the Microsoft Office Word, Pow­er­Point, Access, and Outlook programs. Thanks to the language, you can easily create your own Excel macros that au­to­mat­i­cal­ly execute defined routine commands, or add new functions to the spread­sheet cal­cu­la­tion (i.e., al­go­rithms for data analysis).

Excel provides various elements of the user interface in the form of hi­er­ar­chi­cal­ly organized program objects. Each one features specific prop­er­ties and methods. All available objects are related to each other in the hi­er­ar­chi­cal object model and are reflected ap­prox­i­mate­ly in the visual user interface, which provides buttons and input boxes for in­ter­ac­tion with the ap­pli­ca­tion. By setting the prop­er­ties and invoking their methods, you can edit the different objects. For example, a “Close” method exists for the “Workbook” object that can be used to close the selected workbook as well as the “Ac­tiveSheet” property that rep­re­sents the sheet currently active in the workbook.

With the help of col­lec­tions, macros can also perform actions on a whole group of objects. For example, the “Work­sheets” col­lec­tion object causes the state­ments in the macro to apply to all work­sheets. For the execution of a macro, you have the following three options:

• Selection in the macro menu
• Click on an in­di­vid­u­al­ly created button
• In­di­vid­ual key com­bi­na­tions

If you pre­vi­ous­ly, either con­scious­ly or un­con­scious­ly, refrained from creating macros in Excel, this doesn’t nec­es­sar­i­ly have a negative effect on your Excel files. But you have chosen a rather tedious way to work. Macro tech­nol­o­gy offers a vast number of decisive ad­van­tages that make it a must for anyone who wants to get the most out of their cal­cu­la­tion software:

• Macros reduce the error rate: With every directive that you manually enter into Excel, you increase the prob­a­bil­i­ty of an incorrect entry. Es­pe­cial­ly in complex command sequences and fre­quent­ly repeated tasks, mistakes are quickly made that derail the entire func­tion­al­i­ty of the sheets you are working on. In a macro, only the creation process has error potential. If no errors manage to sneak in there, then the Excel macro will operate error-free every time.

• Macros reduce the work effort: You only create macros one time. You can then start using the practical command com­bi­na­tion as often as you want by pressing the assigned key com­bi­na­tion. It saves you a lot of time that you can now invest in other areas of work.

• Macros expand Excel: With VBA, it’s not just a case of pro­gram­ming macros that group commands together, you can also develop new functions. This extends the in­te­grat­ed function col­lec­tion of your cus­tomized user-defined workbook and sig­nif­i­cant­ly sim­pli­fies your formulas. Excel also presents your cus­tomized functions as if they were native functions. In addition, you have the option of linking all of the macros that have been created with a button in the toolbar, which can then be used to trigger their functions.

To create your own command col­lec­tion, you first have to activate the VBA editor, which is a part of the developer tools. These are not ac­ces­si­ble on the menu list by default (also called the “ribbon” in Microsoft Office ap­pli­ca­tions), so you need to add the cor­re­spond­ing tab in the first step.

Sub here stands for sub­rou­tine and also for a macro, which is in itself a kind of sub­rou­tine for Excel. For the re­spec­tive macro, the entire code between Sub and End Sub is executed. If you would like to conduct a small test, you can extend the code of the sample “Hello” macro as follows:

Click on the “OK” button to close the window and stop running the macro. Then save the macro workbook in the VBA editor.

Note: You can also set a key com­bi­na­tion for running a macro instead (“Developer” -> “Macros” -> “Options”).

In the first four code lines under the Sub line, each beginning with an apos­tro­phe, are the com­men­tary lines. These have no influence on the overall func­tion­al­i­ty of the macros and primarily simply offer a better un­der­stand­ing of the code. You can also tem­porar­i­ly de­ac­ti­vate code lines with the help of com­men­tary. For this example macro, you don’t need the four au­to­mat­i­cal­ly created lines, so you can safely delete them.

The next line contains the selection method, selecting “Table1," which was necessary for the manual rename execution before you could give it a new name. VBA scripts don’t need objects to be selected in order to be edited, so these code lines aren’t necessary for macros in Excel. Com­plete­ly revised, the code looks like this:

VBA scripts enable the au­toma­tion of various Excel tasks. With the help of the recording option, you will simplify your own work process and also acquire knowledge about the high-per­for­mance pro­gram­ming language. Don’t hesitate to use the VBA tool if you suspect that an Excel macro will make a par­tic­u­lar task easier.

Having already presented the general approach to macro-building in the previous para­graphs, the following two examples will now help by giving you a concise overview of how versatile the command au­toma­tion is.

A common function of Excel tables is the vi­su­al­iza­tion of input data in the form of a diagram. With this quite often tedious task, a macro can provide valuable as­sis­tance. For this, you’ll first create a macro with the name “As­sort­ed­Tasks” and declare the variable for your diagram object:

In the next line, create a diagram object for the mydiagram variable to refer to:

The last step is to fill the diagram object with data, which can then be vi­su­al­ized ac­cord­ing­ly. Inputting the values is required for this, as well as a further ad­just­ment of the Excel macro since it still lacks in­for­ma­tion regarding where the in­for­ma­tion should be displayed. Within the With … End With con­struc­tion, now use the Set­Source­Da­ta method and specify the Selection value, which will cause the values of all cells that are marked when the macro is run to be taken into account. The code of the complete macro looks as follows:

With the help of the macro tool, you can also create an input form through which the user can interact with Excel. For example, to create an in­ter­ac­tion window whose input value is au­to­mat­i­cal­ly written into a defined cell, start by creating and naming the macro as usual. In our example, we give the command col­lec­tion the name “InputForm”. Then you name the sub­rou­tine des­ti­na­tion of the value entered later by the user:

The macro ensures that the input of the user is trans­ferred to the A1 cell of Table 1. The value of the field (Value) now needs to be assigned in the second part of the statement. We decided on the InputBox command as an example, which is very similar to the MsgBox command used in the “Hello” macro. Combined with the following three arguments, which are enclosed in quotation marks in the VBA code, a suitable input window is created.

• Prompt: With the help of the first argument, define the text of the dialog window that supports the user during input.
• Title: The “Title” argument displays the title of the input form.
• Default: Finally, you can also specify a standard input with “Default”.

A possible example code of the complete “InputForm” macro including the box and text looks as follows:

To export a macro, open the Visual Basic Editor, click on the “File” tab and then on “Export file”. Give your macro col­lec­tion a mean­ing­ful name, choose the desired index, and complete the process by clicking “Save”. The exported file is only a few bytes big and can easily be sent via e-mail or trans­port­ed via a portable storage device to the desired target computer. To import a macro file, proceed the same way initially but then choose “Import file” and specify the save location of the file.