When it comes to spreadsheets, Excel is the main reference, although not the only one. And although it has an enormous amount of resources, very few people make the most of its potential. However, when it comes to automating processes, it is necessary to take a step beyond the basic tools and dive into macros, which open up a world of possibilities, streamlining tasks and simplifying work in a simple way.
What are Excel macros?
Macros are codes that allow you to perform functions automatically, with the advantage that they can be customized by adding your own characteristics. This allows various functions to stop being done manually, automating basic functions such as formatting cells, copying values and calculating totals, even more complex ones, speeding up processes where repetitive data is constantly entered.
The use of macros allows you, thanks to a personalized configuration, to export data, organize information, optimize a database, clean empty rows or columns, copy and paste data from other sources and create graphs that are easy to see and also print.
Advantages of using macros
Macros are a very powerful resource, especially for those who use Excel very frequently. Among its main advantages, we highlight:
- Once a macro is created, they can be modified to perfect or adjust the executing ones.
- They allow to reduce the error rate by the fact that the executing actions are previously recorded
- They accelerate the process, helping reduce the work time.
How to create macros
Enable “Programmer” tab
In the main menu, go to the “File” tab and click on “More”. In the dialog that opens, select the “Customize Option Ribbon”, activate the “Programmer” box that is displayed in the “Main Tabs” box and conclude the action with “OK”.
Record macros
- Recording a macro begins by selecting the “Programmer” tab from the main menu.
- Then, click on “Record Macro” and a dialog opens where you must assign a name to the macro and a path where it will be saved.
- Optionally, the program allows you to determine an abbreviated access command and a description.
- From that moment on, Excel will record all the actions you do in your spreadsheet until you stop recording, which will be repeated when you press the shortcut or command created for that action.
As long as the recording is activated, the macro records each action performed, so the clicks on the cells, the entered data, formulas and format, among other actions, will be recorded.
When saving a spreadsheet with macros, it is important to note that, by default, files with the extension “.xlsx” cannot include macros, so the option “.xlsm” must be selected.
Using the VBA editor
To create macros in Excel using the Visual Basic Editor (VBA), the first step is to open a spreadsheet and enter the shortcut Alt + F11 (if you use Mac, it should be replaced by Fn + Shift + F11), which will automatically open the VBA Editor window.
It is important to consider that macros are made up of files, also called “modules” with a VBA code. To insert a module, just click on “insert” and then on “module” so that a blank space appears where the desired code is entered.
However, how do we code a macro to automate the actions we want?
Let’s use as an example a database with information on workers of a company, the area to which they belong and the salary of each one, where we need to move data using the copy and paste functions, which are some of the most common and frequent operations in Excel.
Copy, cut and paste are simple actions and can be done manually. However, when doing it repeatedly, it is important to have a resource that can do it automatically, as it will save a lot of time. With a macro, this work could be done in an automated way and for that we present how to do it:
Copy cells with VBA
If we need to copy the data from the A, B and C columns of our example, using VBA to move them into the D, E and F columns, consider the code below:
Range (“Insert range”). Copy.
In our example it would be:
Range (“A: C”). Copy ← copy column A to C
Range (“A1: C100”). Copy ← copies the range A1: C100
Also, you should always include the Sub Nameofmacro () and End sub lines at the top and bottom of the code. In Excel, when you type the term “Sub” at the beginning of the code followed by the name of the macro, the final sub will automatically appear on the bottom line.
Paste cells with VBA
If what you need is to paste cells, the most frequent suggestions are the following:
Range (“The cell / area where you want to paste”). Pastespecial ← to paste regularly (formulas and format)
Range (“The cell / area where you want to paste”). PastespecialxlPasteValues ← to paste only values
Cut cells using VBA
Following the logic used to copy, we can change the location of the data using the cut facility. To do so we need:
Range (“Insert range here”). Cut
Range (“Insert where you want to paste”). Select
ActiveSheet.Paste
Add loops to VBA
In addition to these three basic functions, a worker may face the need to repeatedly perform a longer or complex action in a few seconds.
In those cases the option arises to create a loop to, for example, correct a spreadsheet where the data is out of order and fix it manually can take a long time.
How to do it? Just generate the following code:
Sub loops ()
Range (“A1”) .Select
For i = 1 To 500
ActiveCell.Offset (3, 0) .Select
Selection.Delete Shift:=x1ToLeft
Next
End Sub
- The indication “Range (“ A1 ”) .Select” determines that the loop begins in the upper left cell of the sheet.
- The line “For i = 1 To 1000” is used to determine the number of times the loop can be executed, 1000 being an example for this simulation, but it can be adapted according to the amount of data that the spreadsheet has that is going to work.
- The lines “ActiveCell.Offset (3, 0) .Select” and “Selection.Delete Shift: = x1ToLeft” combine the action of recognizing an active cell, telling Excel to move a number of rows down, in this case every 3 rows, and select it, making it a new active cell. The next line of code tells Excel what to do with this newly selected cell, in this case, delete it and move the cells to the right of it to the left.
- Finally, “Next” tells Excel that there are no more actions inside the loop.
“IF” Statements: A little logic to the spreadsheet
A spreadsheet can also incorporate actions that imply a logical decision. For that, like the IF function in Excel, we can introduce the logic “if-this-then-that” to our codes, which are known as “IF” statements.
If we use as an example a database that has been imported with errors, presenting data every 3 rows shifted one or two columns to the right, we can add an “IF” statement to automatically correct this problem. For that, you have to do the following:
- We develop a loop following the previous example, incorporating the “IF” statement that will determine the action we want it to perform.
Sub loopsandifstatements ()
Range (“A1”) .Select
For i = 1 To 500
ActiveCell.Offset (3, 0) .Select
If ActiveCell. Offset (0, 1) = “” Then
Selection.Delete Shift:=x1ToLeft
Selection.Delete Shift:=x1ToLeft
Else
Selection.Delete Shift:=x1ToLeft
End If
Next
End Sub
- The first part of the “IF” statement indicates that, if the cell to the right of the active cell is blank, an action must be performed (If Activecell.Offset (0,1) = “” Then), in this case, move it to the left (Selection.Delete Shift: = x1ToLeft). We see that the code is repeated in our example because the instruction is to move two cells to the left of its row.
- Then, with the command “Else” we associate a new instruction (Selection.Delete Shift: = x1ToLeft) for when we need the displacement to be just one cell.
The IF statement must always end with End If to tell Excel to finish executing it.
These are some functions that will allow you to explore automated solutions for managing databases using Excel. There are a number of free online Excel courses that can be taken to further your knowledge. Now just a little practice and put what you have learned to use to get even more out of your databases, optimizing time and resources when working.
Try DataScope now clicking here!
DataScope is a platform which allows various industries to streamline, organize and evaluate the work of the field staff thanks to online forms that provide real time indicators, 100% adaptable to any field.
