Excel is one of the most widely used business software packages. Because of this position and its functionalities, it is also one of the digital tools where users make the most repetitive entries and tasks. Nevertheless, Excel automation via a macro, or via other relatively easy-to-use automation software, makes users’ work easier, with just a few clicks.
In the first part, we’ll look at how to use a macro in Excel. Then we’ll show you alternatives that are easier to set up and maintain over time.
If you had only one thing to remember from this article :
Macros in Excel pose security, compatibility and maintenance problems, while making a workbook more vulnerable to errors and harder for other users to understand. By using simple alternatives, companies can implement better solutions.
Excel automation with the macro recorder
What is the Excel automation macro recorder?
To be able to automate Excel, it’s important to set up a multi-stage process. Steps can take the following forms:
- Alphanumeric input
- Clicking on cells
- Clicking on menus and submenus
- Formatting functions for cells, rows or columns
- Interconnection with other Microsoft Office services such as Outlook.
The Excel macro recorder then consolidates the entire sequence in VBA(Visual Basic Application code) to automate it. We’re talking about a programming language here, but the special feature of VBA is that it can be used without any computer development skills, thanks to the low-code macro recorder.
The macro recorder puts powerful technology at the service of any Excel user. Nevertheless, a high level of control is required to achieve this. The recorder captures all interactions. Any error in a path causes it to fail, requiring direct modification of the VBA code (skill request) or re-recording of the sequence from scratch.
To avoid this problem, there are two possible solutions:
- Formalize the list of steps upstream to avoid mistakes during registration
- Create several mini macros instead of just one.
That’s why, in the rest of this article, we’re going to show you some simpler, more flexible alternatives.
A final point of attention: the definition of the cell range is very important. The macro runs only in the cells of the range. Beware of new lines
How do I record a macro?
- Developer tab, in Code group, click on “Record a macro”.
- In the “Macro name” field, enter an explicit name for your process to facilitate the search. Note that the first character must be a letter, and spaces are not allowed.
- You can assign a keyboard shortcut to make your actions more efficient. Please note that this function replaces all default shortcuts, so be careful not to override the classics: A, Z, C, X, V, F, etc.
- Select the storage location from the “Save macro” drop-down list. If you want the macro to be used for other Excel files, select “Personal macro folder”.
- If you wish, write a description in the dedicated field
- Perform the recording by completing each step of your sequence. Then, when finished, click on “Stop recording” in the “Developer” tab, or use the Alt+T+M+R keyboard shortcut.
How to use a macro recorded in Excel
- In the “Developer” tab, click on Macros.
- Then select the macro you wish to use
- Click on “Run” to launch it
Other important information :
- If you want to test a macro in debug mode in Visual Basic Editor, click on “Detailed step-by-step”.
- If you want to modify the macro in VBA Editor, click on “Modify”.
- To delete a macro, click on “Delete”.
- To modify keyboard shortcuts, click on “Options”.
Complements :
- If you’re using your macro for the first time, without having tested it beforehand, don’t hesitate to save a copy before running it. Otherwise, close the workbook without saving any changes.
- Security settings can be defined for macros.
- A macro can be executed in a number of ways: Keyboard shortcuts, Button, Toolbar, Object (by clicking on “Assign macro”), Opening Excel file…
- Microsoft Visual Basic Editor lets you copy a macro into another workbook.
How do I use code saved in VBE (Visual Basic Editor)?
Visual Basic Editor can be used for more advanced functions than the macro recorder. For example, to add variables or controls, or to delete unnecessary code.
Admittedly, this is VBA programming, but it’s still fairly easy to decipher. Nevertheless, unless you absolutely must use macros, we’d advise you to use alternatives that will quickly change your life and make it easier to automate repetitive tasks in Excel.
Find out more in our article on process automation
Excel automation in just a few clicks with modern software
The benefits of Excel automation
Reduce non-value-added data entry
Laborious tasks, repetitive actions, multiple entries of the same information in several places… all this is over with Excel automation and frees up employees’ time.
No more input errors
A data-entry error can go very far down a company’s value chain, and easily degrade margins. With Excel automation, there are no more data entry errors.
Up-to-date data in your dashboards
Excel task automation sends relevant data directly to your BI tools for easier decision-making.
Integration with all your software
Say the information once, and it will automatically flow into any of your software packages (CRM, ERP, vertical software, Planning…). And vice versa, other software can feed Excel.
Collaboration made easy
Opening an Excel file to other users can be complex. The spreadsheet can be degraded, multiple tabs can be created, multiple versions can magically appear. Automation platforms solve this problem and facilitate collaboration.
Automation is not the same as dehumanization
Automation is often taken to mean the replacement of humans, but this is not true, and automation platforms have created functionalities that enable an efficient link between humans and automation.
There are many solutions for automating Excel files
This software helps you to structure, secure and streamline your spreadsheet, turning it into a simple database for your digitalization process.
Very simply and without any technical skills, an Excel spreadsheet can be transformed into a visual, dynamic and interactive mini-software application.
The main advantages of this software :
- Ultra-customization to suit your needs.
- No more data entry errors or missing information. User data entry is supported by dynamic forms.
- Database security.
- Rights management.
- Automated calculations in the spreadsheet
- Connection to all your company’s other software, to avoid multiple entries of the same information in several programs, and to enable data to be displayed without risk on management or ERP software…
- Dashboards for effective supervision of activities.
- and many other features…
Examples of Excel automation solutions
Three families of automation tools can be envisaged:
Automation software (e.g. Zapier, Make)
This software connects various online applications and services, making it easy to automate simple, repetitive tasks. They are ideal for small to medium-sized companies with limited automation needs.
Pricing is based on the number of automations per month per user. This can quickly become expensive, even if their call-out price is relatively low, between $9 and $19 per month.
Business process automation platforms (e.g. Lapala, Power Automate, Workato)
Designed for SMEs and large companies, these platforms offer a complete solution for digitizing, automating and optimizing business operations. They are ideal for creating efficient sequences between human and automated tasks.
Rates vary according to the platform and your needs, but are relatively affordable.
Robot Process Automation (RPA) tools (e.g. UiPath, Automation Anywhere)
The use of robots is generally the last chance for automation if automation software and platforms don’t cover a need. This is particularly the case for software that does not respect standards in terms of data communication and architecture.
RPA tools are generally very expensive, and require implementation services provided by integrators.
Now you know all about Excel automation. Now it’s your turn! See you soon for more content!
Leave a Reply